Question 1. What Is Pl/sql?
PL/SQL is Oracle’s Procedural Language extension to SQL. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.
Question 2. Is There A Pl/sql Engine In Sql*plus?
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and send to the database individually.
Question 3. Is There A Limit On The Size Of A Pl/sql Block?
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the maximum code size is 100K. You can run the following select statement to query the size of an existing package or procedure.
SQL> select * from dba_object_size where name = 'procedure_name'
Question 4. Can One Read/write Files From Pl/sql?
Included in Oracle 7.3 is a UTL_FILE package that can read and write files. The directory you intend writing to has to be in your INIT.ORA file (see UTL_FILE_DIR=… parameter). Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE fileHandler UTL_FILE.FILE_TYPE; BEGIN fileHandler := UTL_FILE.FOPEN('/home/oracle/tmp','myoutput','W'); UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn',func1(1)); UTL_FILE.FCLOSE(fileHandler); END;
Question 5. What Is The Use Of Cascade Constraints?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.
Question 6. Which Command Displays The Sql Command In The Sql Buffer, And Then Executes It?
Question 7. What Command Is Used To Get Back The Privileges Offered By The Grant Command?
Question 8. What Operator Performs Pattern Matching?
Question 9. What Operator Tests Column For The Absence Of Data?
IS NULL operator.
Question 10. Which Function Is Used To Find The Largest Integer Less Than Or Equal To A Specific Value?
Question 11. Describe The Use Of %rowtype And %type In Pl/sql?
%ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with a single column type.
Question 12. What Packages (if Any) Has Oracle Provided For Use By Developers?
Oracle provides the DBMS_ series of packages. There are many which developers should be aware of such as DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.
Question 13. Describe The Use Of Pl/sql Tables ?
PL/SQL tables are scalar arrays that can be referenced by a binary integer. They can be used to hold values for use in later queries or calculations. In Oracle 8 they will be able to be of the %ROWTYPE designation, or RECORD.
Question 14. When Is A Declare Statement Needed ?
The DECLARE statement is used in PL/SQL anonymous blocks such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand alone file if it is used.
Question 15. In What Order Should A Open/fetch/loop Set Of Commands In A Pl/sql Block Be Implemented If You Use The %notfound Cursor Variable In The Exit When Statement? Why?
OPEN then FETCH then LOOP followed by the exit when. If not specified in this order will result in the final return being done twice because of the way the %NOTFOUND is handled by PL/SQL.
Question 16. What Are Sqlcode And Sqlerrm And Why Are They Important For Pl/sql Developers?
SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.
Question 17. How Can You Find Within A Pl/sql Block, If A Cursor Is Open?
Use the %ISOPEN cursor status variable.
Question 18. How Can You Generate Debugging Output From Pl/sql?
Use the DBMS_OUTPUT package. Another possible method is to just use the SHOW ERROR command, but this only shows errors. The DBMS_OUTPUT package can be used to show intermediate results from loops and the status of variables as the procedure is executed. The new package UTL_FILE can also be used.
Question 19. What Are The Types Of Triggers?
There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE and ALL key words:
BEFORE ALL ROW INSERT AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT etc.
Question 20. Explain The Difference Between A Hot Backup And A Cold Backup And The Benefits Associated With Each ?
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
Question 21. You Have Just Had To Restore From Backup And Do Not Have Any Control Files. How Would You Go About Bringing Up This Database?
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
Question 22. How Do You Switch From An Init.ora File To A Spfile?
Issue the create spfile from pfile command.
Question 23. Explain The Difference Between A Data Block, An Extent And A Segment ?
A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
Question 24. Where Would You Look For Errors From The Database Engine?
In the alert log.
Question 25. Compare And Contrast Truncate And Delete For A Table ?
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
Question 26. Give The Reasoning Behind Using An Index ?
Faster access to data blocks in a table.
Question 27. Give The Two Types Of Tables Involved In Producing A Star Schema And The Type Of Data They Hold ?
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
Question 28. What Type Of Index Should You Use On A Fact Table ?
A Bitmap index.
Question 29. Give Two Examples Of Referential Integrity Constraints ?
primary key and a foreign key.
Question 30. A Table Is Classified As A Parent Table And You Want To Drop And Re-create It. How Would You Do This Without Affecting The Children Tables?
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.
Question 31. How Would You Determine The Time Zone Under Which A Database Was Operating?
select DBTIMEZONE from dual;
Question 32. Explain The Use Of Setting Global_names Equal To True ?
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.
Question 33. What Command Would You Use To Encrypt A Pl/sql Application?
Question 34. Explain The Difference Between A Function, Procedure And Package ?
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
Question 35. Explain The Use Of Table Functions ?
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.
Question 36. Name Three Advisory Statistics You Can Collect ?
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics.
Question 37. Where In The Oracle Directory Tree Structure Are Audit Traces Placed?
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer.
Question 38. Explain Materialized Views And How They Are Used ?
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
Question 39. When A User Process Fails, What Background Process Cleans Up After It?
Question 40. What Background Process Refreshes Materialized Views?
The Job Queue Processes.
Question 41. How Would You Determine What Sessions Are Connected And What Resources They Are Waiting For?
Use of V$SESSION and V$SESSION_WAIT
Question 42. How Would You Force A Log Switch ?
ALTER SYSTEM SWITCH LOGFILE;
Question 43. Give Two Methods You Could Use To Determine What Ddl Changes Have Been Made ?
You could use Log miner or Streams.
Question 44. What Does Coalescing A Table Space Do?
Coalescing is only valid for dictionary-managed table spaces and de-fragments space by combining neighboring free extents into large single extents.
Question 45. What Is The Difference Between A Temporary Tablespace And A Permanent Tablespace?
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
Question 46. Name A Tablespace Automatically Created When You Create A Database ?
The SYSTEM tablespace.
Question 47. When Creating A User, What Permissions Must You Grants To Allow Them To Connect To The Database?
Grant the CONNECT to the user.
Question 48. How Do You Add A Data File To A Tablespace?
ALTER TABLESPACE ADD DATAFILE SIZE ;
Question 49. How Do You Resize A Data File?
ALTER DATABASE DATAFILE RESIZE ;
Question 50. What View Would You Use To Look At The Size Of A Data File?
Question 51. What View Would You Use To Determine Free Space In A Tablespace?
Question 52. How Would You Determine Who Has Added A Row To A Table?
Turn on fine grain auditing for the table.
Question 53. How Can You Rebuild An Index?
ALTER INDEX REBUILD;
Question 54. Explain What Partitioning Is And What Its Benefit Is ?
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.
Question 55. You Have Just Compiled A Pl/sql Package But Got Errors, How Would You View The Errors?
Question 56. How Can You Gather Statistics On A Table?
The ANALYZE command.
Question 57. How Can You Enable A Trace For A Session?
Use the DBMS_SESSION.SET_SQL_TRACE or
Use ALTER SESSION SET SQL_TRACE = TRUE;
Question 58. What Is The Difference Between The Sql*loader And Import Utilities?
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.
Question 59. Name Two Files Used For Network Connection To A Database ?
TNSNAMES.ORA and SQLNET.ORA
Question 60. Explain The Difference Between Archivelog Mode And Noarchivelog Mode And The Benefits And Disadvantages To Each ?
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time
NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
Question 61. What Command Would You Use To Create A Backup Control File?
Alter database backup control file to trace.
Question 62. Give The Stages Of Instance Startup To A Usable State Where Normal Users May Access It?
- STARTUP NOMOUNT – Instance startup
- STARTUP MOUNT – The database is mounted
- STARTUP OPEN – The database is opened
Question 63. What Column Differentiates The V$ Views To The Gv$ Views And How?
The INST_ID column which indicates the instance in a RAC environment the information came from.
Question 64. How Would You Go About Increasing The Buffer Cache Hit Ratio?
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.
Question 65. Explain An Ora-01555 ?
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.
Question 66. Explain The Difference Between $oracle_home And $oracle_base ?
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
Question 67. What Is A Mutating Table Error And How Can You Get Around It?
This happens with triggers. It occurs because the trigger is trying to update a row it is currently using. The usual fix involves either use of views or temporary tables so the database is selecting from one while updating the other.
Question 68. How Do You Generate File Output From Sql?
By use of the SPOOL command
Question 69. How Do You Prevent Oracle From Giving You Informational Messages During And After A
Sql Statement Execution?
The SET options FEEDBACK and VERIFY can be set to OFF.
Question 70. How Do You Prevent Output From Coming To The Screen?
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.
Question 71. What Is The Difference Between Sql And Pl/sql?
Structured Query Language (SQL) is a non-procedural language that interacts with the database, and is used for database manipulation using the Data Definition Language (DDL) and Data Manipulation Language (DML) statements. Control statements cannot be used in SQL, which is compiled and executed statement by statement at the runtime (late-binding).
PL/SQL is a programming language that allows the usage of Cursor Control statements and Transaction Control statements, such as if…then…else. It integrates with SQL functions and statements to interact and manipulate with the database. Unlike SQL, PL/SQL statements are compiled and processed as a block of code into the machine-readable code, which is executed at runtime (early binding); and therefore, improves the performance.
Question 72. What Are The Advantages Of Pl/sql?
PL/SQL is a transaction processing procedural language that has the following advantages:
- Integration with database —PL/SQL supports SQL statements, thus enabling integration between procedural language capabilities and the database technology.
- Better performance —SQL statements are grouped within a PL/SQL block and sent together to database for parsing; therefore, it reduces the network traffic and increases the performance.
- Higher productivity —-PL/SQL helps the package reusable code within the well-defined modular blocks; and therefore, increases the productivity.
- Portability —Programs packages and libraries written in PL/SQL can be reused in different environments.
Question 73. What Are The Mandatory Keywords In A Pl/sql Program Block?
The BEGIN and END keywords are mandatory for any PL/SQL block.
Question 74. Can Arrays Be Used Within A Pl/sql Program Block?
Yes, arrays can be defined and used in a PL/SQL block using the composite datatypes, such as RECORD, TABLE, NESTED TABLE, and VARRAY.
Question 75. Can A Variable Be Assigned A Value From Database? If Yes, Then How?
Yes, a variable can be assigned a value from a database. You can fetch the value from the database and assign it to the variable using the into keyword, as shown in the following code:
select emp_sal * 0.20 into var_increment from t_emolovee where emo_no = emo_id
Question 76. Does A Not Null Constraint Of A Database Column Apply To %type Variables ?
No, a NOT NULL constraint does not apply to variables declared using the %TYPE attribute. These variables can be assigned a null value in a PL/SQL block, as shown in the following code snippet:
DECLARE var_emp_code t_employee.emp_codeflPE; BEGIN var_emp_code NULL;
Question 77. Can Sql Group Functions Be Used Within The Procedural Statements?
The SQL group functions, such as AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE, cannot be directly used within PL/SQL. However, they can be embedded within SQL statements and then can be utilized in the procedural statements.
Question 78. What Is The Maximum Length Of An Identifier That Can Be Used?
The maximum length of an identifier in a PL/SQL block is 30.
Question 79. What Is The Equivalent Of Decode Function In The Procedural Statements?
The if…then…else statement is the equivalent of DECODE function within the procedural statements.
Question 80. Can Ddl Commands Be Used Within A Procedure? If So, Then How?
DDL commands, such as create, alter, revoke, and grant, cannot be used directly within the procedural language block. They can only be called using built-in Native Dynamic SQL or Dynamic SQL Package, which allows the execution of the DDL statements at runtime.
Question 81. What Is An Anonymous Block And How Is It Executed?
Anonymous blocks, as the name suggests, are PL/SQL blocks that are not given any name and cannot be stored in the database. These blocks can call another blocks but they cannot be called by any other block, as they do not have a name. An anonymous block is executed by either storing the block code in a file or writing the block code at the SQL prompt.
Question 82. What Is A Bind Variable And How Is It Used?
A bind variable is a variable within a SQL statement that has to be replaced by a valid value on the successful execution of the statement. This value can be referenced into the PL/SQL block. Bind variable is always preceded by a colon (:), as shown in the following code:
Declare y_emp_age number; Begin Select emp-age into :v_empLage from t_employee where emp_code.121; End; In the preceding code, v_emp_age is a bind variable.
Question 83. How Is Global Variable Declared Within A Nested Block?
Any variable declared within a block is local to the block and global to the nested blocks. The variables declared within the nested blocks are unknown to the outer blocks. They override the references to any outer declared variables with the same name unless they are used with the block label name.
Question 84. What Is The Difference Between Implicit Cursor And Explicit Cursor?
A cursor is a SQL memory work area. Any SQL statement always uses a cursor for the execution of statements and fetching of results. This is implicitly defined and is called implicit cursor.
When a programmer defines a cursor for fetching and parsing results of a SQL query, which returns more than one row, it is called an explicit cursor.
Question 85. Why Does Sql%isopen Always Returns The False Value?
SQL is the name of the implicit cursor, which is always opened when the database executes the SQL statement. SQL%ISOPEN always evaluates to FALSE as ORACLE closes the implicit cursor as soon as it executes the query.
Question 86. What Are Open, Fetch, And Close Cursor Commands? Are They Used With The Implicit Or Explicit Cursor?
These commands are used with explicit cursors. First, the cursor is opened to make it active using the OPEN command. Then, the FETCH command is used to retrieve the rows from the active cursor. After all the rows are retrieved, the cursor is closed using the CLOSE command that can again be reopened. Any other operation on a closed cursor returns the predefined exception, INVALID_CURSOR. However, these commands cannot be used to control the SQL implicit cursors.
Question 87. What Are Transaction Control Statements? Can They Be Used Within The Pl/sql Block?
Transaction Control statements are the COMMIT and REVOKE commands that control the logic of transactions within a database. These statements are valid within a PL/SQL block. The COMMIT command terminates the active transaction and makes the changes permanent to the database. The ROLLBACK command terminates the active transaction but cancels any changes that were made to the database.
Question 88. What Is A Savepoint Command?
The SAVEPOINT command is used to set a point within a transaction to which you may rollback. This command helps in cancelling the portions of the current transaction. Using ROLLBACK with the SAVEPOINT TO clause, a transaction can be undone in parts rather than rolling back the entire transaction.
Question 89. What Are Pseudo-columns? How Can You Use Pseudo Columns In Procedural Statements?
Pseudo columns are not actual columns in a table but they behave like columns. They are used in SQL statements to retrieve specific information. PL/SQL recognizes pseudo-columns as a part of SQL statements but they cannot be used directly in a procedural language. The following pseudo-columns are used in SQL:
Question 90. How Can You Check If An Update Statement In Pl/sql Is Successfully Executed?
You can use the SQL%NOTFOUND attribute to check if the UPDATE statement has successfully updated any rows or not. This attribute returns the TRUE value if the last executed SQL statement has not affected any rows.
Question 91. What Is A Record Datatype?
A RECORD is a collection of data items, which differ from each in other in datatype, but are logically related. For example, an employee record with name, age, address, and telephone number. These are all part of employee record but have different datatypes.
In PL/SQL, the record type is defined first and then the record of that type is declared as follows:
TYPE emp_record_tyPe is RECORD (emp_name varchar2(50), emp_age number(2), emp_address varchar2(100). emp_tel_no number(10)); emp_record emp_record_type;
We have defined the record type by the name emp_record_type and then declared a record, emp_record, of that type.
Question 92. What Is The Function Of Open Cursor Command?
The following operations occur when the OPEN cursor command is executed for a cursor:
- Memory area for processing is allocated
- The SELECT statement is parsed
- The input variables are assigned the values by obtaining the memory addresses
- The active set of rows that fulfill the select criteria are identified
- The pointer is set just before the first row of the active set
Question 93. What Is The Advantage Of Using The %rowtype Datatype?
The advantages of using the %ROWTYPE datatype to declare variables are given as follows:
- It is useful to retrieve an entire row from a table. If you do not use the %ROWTYPE datatype, then you have to declare variables for each column separately.
- It can be used even if datatype of the table columns is not known
- It ensures that datatype of the variable changes dynamically if the underlying table is altered.
Question 94. How Can Explicit Cursors Be Used To Fetch Multiple Rows?
A loop must be defined to fetch multiple rows from an explicit cursor. It will fetch one row from the active cursor set on each iteration of the loop, as shown in the following code:
Declare v_emp_code t_employees.emp_code%TYPE; v_emp_name t_employees.emp_name%TYPE; v_emp_salary t_employees.emp_salary%TWE: v_increment number (10.2); Cursor emp_cursor IS Select emp_code, emp_name, emp_salary from t_employees: Begin Open emp_cursor; LOOP FETCH emp_cursor into v_emp_code, v_emp_name, v_emp_salary: EXIT WHEN emp_cursoMBOutOuNT > 10 or emp_cursor%NOTPOuND or emp_cursor%NOTFOuND IS NULL; V_increment := v_emp_salary • 0.20 DBMS_OUTPUT. PUTLINE(v_emp_code II' 'Ilv_emp_name It " II v_increment); END LOOP; CLOSE emp_cursor; END;
Question 95. Can You Define A Record Type Based On An Explicit Cursor?
Yes, It is possible to define a record type based on the selected columns of an explicit cursor. This helps in retrieving the entire row together into the defined record and further processing. The values of the row are directly loaded into the fields of the record.
Question 96. What Is A Cursor For Loop And How Does It Differ From An Explicit Cursor?
Cursor FOR loop is a shortcut to use explicit cursors. It does not require explicit opening, fetching, and closing of the cursor. In other words, the cursor FOR loop implicitly opens the cursor, fetches the record, and closes the cursor. In the cursor FOR loop, the cursor is defined and its attributes are checked within the processing of the FOR loop. The records are also implicitly defined within the loop, as shown in the following code:
Declare v_increment number(10.2); Cursor em P_cursor IS Select emp_code. emp_name. emp_salary from t_employeeS; Begin FOR emp_record IN em p_cursor v_increment emp_record.emp_salary • 0.20 D13MS_OUTPUT.PUTLINE (emp_FREOFthent_COOR 11 'Ilemp_record.emp_name II " II v_increment); END LOOP; END;
It is also possible to use a cursor FOR loop with a subquery, where the cursor name is not defined. In this case, the attributes of the cursor cannot be checked as no explicit cursor name is defined.
Question 97. What Is Select For Update And How Can It Be Used In A Cursor?
The SELECT FOR UPDATE clause is used to lock rows in a table when an update or delete transaction is performed on the active set. It selects the rows of the table that have to be modified and locks each row in the result set. This prevents any other user from accessing the same set of records while modification is being executed on the table. You should add the NOWAIT clause to the SELECT FOR UPDATE statement to prevent the procedure from waiting indefinitely in case the lock cannot be acquired. If the NOWAIT clause is used, the control will return to the program in case the requested rows have been locked by another user.
Question 98. How Is It Possible To Fetch A Current Row From A Cursor Without Using Rowid?
The WHERE CURRENT OF clause is used to reference the current row of an active explicit cursor. When this clause is used, ROWID is not needed to access the current row for modifications.
Question 99. Which Datatypes Can Be Used With A Record Type?
The datatype of a REORD Type can be a NUMBER, VRCHAR2, %TYPE, or %ROWTYPE; however, it cannot be a’REF CURSOR.
Question 100. Is It Possible To Label A Loop? If Yes, Then What Is Its Benefit?
Yes, It is possible to label a loop similar to a PL/SQL block. Loop labels are undeclared identifiers enclosed within the double angle brackets. This helps in better readability in case of nested loops. In addition, this helps in using the EXIT statement with the loop label in the nested loops. Label of a loop should appear before the LOOP statement, as shown in the following code snippet:
«first» LOOP ----- «second» LOOP ------- EXIT second WHEN ------ END LOOP second; END LOOP first;
Question 101. In A For-loop, Can The Counter Value Be Assigned A Value Within The Loop?
The counter of a FOR-LOOP can only be used as a constant within the loop. It cannot be assigned a value inside the loop, as that would change the bounds of the loop, which is logically not possible.
Question 102. Is It Possible To Assign A Dynamic Value To A Loop Counter?
Yes, it is possible to assign a dynamic value to the loop counter at the time of execution, as shown in the following code snippet:
In the preceding code snippet, the employee count is derived from the table at runtime and the counter is used to iterate the FOR-LOOP.
Question 103. What Will Be The Consequence If The Upper Bound Of The For-loop Counter Gets A Smaller Value Than The Lower Bound?
If the upper bound of a FOR-LOOP is assigned a value smaller than the lower bound of the loop, then the statements within the loop are not executed and the control is passed to the statement after the loop.
Question 104. Can Index-by Table Be Stored In A Database? If Not, Then Which Type Of Collection Can Be Used To Store In A Database?
No, Index-By table cannot be stored in a database. The nested tables and VARRAYs collection types can be used to store in database.
Question 105. What Is The Difference Between Nested Tables And Varrays?
Both nested tables and VARRAYs are collection types that can be stored in the database. They differ in two properties, bounding and sparsity. Bounding refers to the limit on the number of elements that a collection can have; whereas, sparsity means whether there can be gaps within the subscripts or not.
Nested tables are not bounded and can be sparse; whereas, VARRAYs are bounded and cannot be sparse. It means that VARRAYs have a limit on the number of elements and are bounded; whereas, nested tables does not have a limit on the number of elements and are unbounded. Nested tables can become sparse if elements are deleted; whereas, VARRAYs can never become sparse.
Question 106. What Is The Difference In The Usage Of Select Into And Fetch?
The SELECT INTO clause should be used when the result of the query is known to return a single row. When more than one row is to be returned by the SQL query, then the FETCH clause should be used. If the FETCH clause is not used and the query returns more than one row, then the runtime error is generated in the code.
Question 107. How Can Two Pl/sql Records Be Compared?
Two records can be compared by using the equality (comparison) operator for each field of the record. However, the entire record cannot be compared as a whole.
Question 108. Which Datatype Is Not Allowed In The Definition Of Pl/sql Record?
A collection type cannot be used for defining the datatype of fields; only scalar datatype can be used for a PL/SQL record.
Question 109. What Is The Value Of Sql%found Attribute After And Before An Insert Statement Is Executed?
The value of SQL%F0UND attribute will be NULL before an INSERT statement is executed and TRUE after the successful insertion into a table.
Question 110. What Is A Constructor And Why Is It Used?
A constructor is a special built-in function that is used to initialize nested tables and VARRAYs. Constructor has the same name as the collection and can take any number of arguments, as shown in the following code:
Question 111. How Are Elements Added To An Index-by Table?
Index-By tables are instantiated automatically. No special constructor is required to initialize an Index-By table. Whenever an element of the collection is referred, it is initialized.
Question 112. Can An If-then-else If Block Have Infinite Number Of Else Clauses?
No, the IF-THEN-ELSIF block can have only one ELSE clause at the end of the it, which is executed if no other condition is evaluated to TRUE.
Question 113. What Will Be The Value Of %n0tf0und Attribute After A Cursor Is Opened But Before A Fetch Statement Is Executed?
The value of %NOTFOUND attribute will be NULL after the cursor is opened but before the FETCH statement is executed. After the FETCH statement, the value of %NOTFOUND attribute will be TRUE OR FALSE depending on the result of the FETCH statement.
Question 114. Is It Possible To Define A Default Value For A Record Field? If Yes, Then How?
Yes, it is possible to define a default value for a record field, as shown in the following code:
Question 115. What Are Implicitly Defined Records?
Implicitly defined records are those that do not have the need to describe each field separately in the record definition. The structure of the record is not defined using the TYPE statement; instead, the %ROWTYPE attribute is used to define the implicit record to have the same structure as the database record. Following code shows the declaration of the implicit defined records:
Question 116. What Is The Advantage Of Defining Implicit Records?
Implicit records are useful as there is no hard-code description needed. As they are based on a database table record, any change in the database table record will be automatically reflected in the record.
Question 117. How Can An Implicitly Defined Record Be Populated?
The implicitly defined record can be populated field by field or the entire record can be populated in one statement, as shown in the following code:
Question 118. Can A Record Definition Have Another Record As Datatype?
Yes, a record definition can have scalar datatypes or another record as a datatype.
Question 119. Is It Possible To Define A Not Null Field In A Record?
Yes, it is possible to define a NOT NULL field in a record so that it will always require a value.
Question 120. Is It Possible To Define A Constant Value In A Record?
No, a CONSTANT value is not allowed in a record.
Question 121. Is It Possible To Copy Contents Of One Record To Another?
Yes, the contents of one record can be copied to another by copying either each field using the assignment operator or whole record using the assignment operator.
Question 122. Do Records Have Methods?
No, records do not have methods.
Question 123. Can A Varray Be Used In A Record Definition?
No, VARRAY is a collection and collections cannot be used for record definition.
Question 124. Can An Uninitialized Nested Table Be Referenced?
No, an uninitialized nested table cannot be referenced. It can only be checked for nullity.
Question 125. What Is The Difference Between Initialization Of Index-by Table And Nested Table?
Index-By table is initialized automatically by referencing an element in the collection; whereas, nested tables have to be initialized using a built-in function called constructor.
Question 126. Do Collections Have Methods? If Yes, Give An Example ?
Yes, collections have built-in methods, which are used to examine and manipulate the attributes of the collection, as shown in the following code:
Question 127. Can Collections Be Compared With Boolean Operators?
No, collections cannot be compared with Boolean operators.
Question 128. Can The Exists Method Be Used For Index-by Table?
Yes, the EXISTS method can be used for all collection types. It returns a Boolean value, TRUE, if the collection element is initialized and FALSE, if the collection element is not initialized.
Question 129. What Will The Result Of The Next(
) Method, If X Is The Last Element Of The Collection?
The NEXT (
) method will return NULL if the last element of the collection is reached.
Question 130. What Is The Difference In Accessing Objects Of Lob And Long Datatypes?
The LOB datatype supports random access; whereas, the LONG datatype supports only sequential access of objects.
Question 131. What Is The Difference Between Lob Objects Stored In-line And Out-of-line?
The LOB objects can be stored in-line or out-of-line. The in-line storage means that objects are stored along with the row; whereas, the out-of-line storage means that objects are stored outside the row.
Question 132. Which Package Is Used To Manipulate Lob Objects?
Oracle-supplied package called DBMS.J_OB is used to manipulate the LOB objects.
Question 133. What Are Lob Locators?
LOB locators are values, which specify the location of the large object.
Question 134. What Are The System Privileges That Are Required By A Schema Owner (user) To Create A Trigger On A Table?
A user must be able to alter a table to create a trigger on the table. The user must own the table and either have the ALTER TABLE privilege on that table or have the ALTER ANY TABLE system privilege. In addition, the user must have the CREATE TRIGGER system privilege. User should have the CREATE ANY TRIGGER system privilege to be able to create triggers in any other user account or schema.
A database-level event trigger can be created if the user has the ADMINISTER DATABASE TRIGGER system privilege.
Question 135. What Are The Different Types Of Triggers?
There are following two types of triggers:
- Database triggers are executed implicitly whenever a Data Manipulation Language (DML) statement is carried out on a database table or a Data Definition Language (DDL) statement, such as CREATE OR ALTER, is performed on the database. They may also be executed when a user or database event occurs, such as a user logs on or a database is shutdown.
- Application triggers are executed implicitly whenever a DML event takes place within an application, such as WHEN_NEW_FORM_INSTANCE in the Oracle Forms application.
Question 136. Which Data Manipulation Operations Raise A Trigger?
Data manipulation operations such as INSERT, UPDATE, and DELETE on a table or a view are the main triggering events that cause a trigger to execute.
Question 137. Can Triggers Reference Other Tables Through A Role?
Triggers may require referencing other tables while triggering an event. Therefore, privileges required to reference other tables must be granted directly to the owner of the trigger, as the trigger cannot reference other user tables through roles.
For example, consider a trigger, which is used for any audit table. To insert a row in the audit table, the owner of the trigger must have the insert privilege on that audit table.
Question 138. What Is The Difference Between Row-level Triggers And Statement-level Triggers?
- Row-level triggers are activated once for each row, which is modified by a DML statement. For example, consider a table, STUDENT_MARKS_AUDIT, used for the auditing of the STUDENT_MARKS table. A row-level trigger would insert a row in the STUDENT_MARKS_AUDIT table every time any value is modified in any row of the STUDENT_MARKS table.
- Statement-level triggers are executed only once for each DML statement. For example, if there is an INSERT statement, which inserts 100 rows in a STUDENT_MARKS table, then the statement-level trigger would be executed only once on that table; whereas, a row-level trigger would be executed 100 times.
- Row-level triggers are generally used for data auditing applications; whereas, statement-level triggers are used to enforce security features for the types of actions that may be performed on a table.
Question 139. Is It Possible To Combine Several Triggering Events Into One Trigger?
Yes, it is possible to combine various triggering events and create one trigger. Special conditional predicates such as INSERTING, UPDATING, and DELETING are used within the trigger body to represent the triggering events, as shown in the following code snippet:
Question 140. How Can Triggers Be Used For The Table Auditing?
Triggers can be used to track values for data operations on tables. This is done using the old and new qualifiers within the trigger code. These two clauses help keep track of the data that is being inserted, updated, or deleted in the table; and therefore, facilitate in application auditing of DML statements. The audit trail can be written to a user-defined table and audit records can be generated for both row-level and statement-level triggers.
Question 141. What Are Instead Of Triggers?
The INSTEAD OF triggers are used in association with views. The standard table-based triggers cannot be used by views. These triggers inform the database of what actions are to be performed instead of the actions that invoked the trigger. Therefore, the INSTEAD OF triggers can be used to update the underlying tables, which are part of the views. They can be used on both relational views and object views. The INSTEAD OF triggers can only be defined as row-level triggers and not as statement-level triggers.
Question 142. What Is The Difference Between Database Trigger And Stored Procedure?
- The main difference between database trigger and stored procedure is that the trigger is invoked implicitly and stored procedure is invoked explicitly.
- Transaction Control statements, such as COMMIT, ROLLBACK, and SAVEPOINT, are not allowed within the body of a trigger; whereas, these statements can be included in a stored procedure.
Question 143. How Can The Performance Of A Trigger Be Improved?
The performance of a trigger can be improved by using column names along with the UPDATE clause in the trigger. This will make the trigger fire when that particular column is updated and therefore, prevents unnecessary action of trigger when other columns are being updated.
Question 144. What Are The Events On Which A Database Trigger Can Be Based?
Database triggers are based on system events and can be defined at database or schema level. The various events on which a database trigger can be based are given as follows:
- Data definition statement on a database or schema object
- Logging off or on of a specific user
- Database shutdown or startup
- On any specific error that occurs
Question 145. What Is A Call Statement? Explain With An Example ?
A CALL statement within a trigger enables you to call a stored procedure within the trigger rather than writing the Procedural Language/Structured Query Language (PL/SQL) code in it, The procedure may be in PL/SQL, C, or Java language. Following is an example of the CALL statement:
Question 146. What Is A Mutating Table?
A mutating table is a table, which is in the state of transition. In other words, it is a table, which is being updated at the time of triggering action. If the trigger code queries this table, then a mutating table error occurs, which causes the trigger to view the inconsistent data.
Question 147. What Are Schema-level Triggers?
Schema-level triggers are created on schema-level operations, such as create table, alter table, drop table, rename, truncate, and revoke. These triggers prevent DDL statements, provide security, and monitor the DDL operations.
Question 148. What Is A Database Event Trigger?
Trigger that is executed when a database event, such as startup, shutdown, or error, occurs is called a database event trigger. It can be used to reference the attributes of the event and perform system maintenance functions immediately after the database startup.
Question 149. In What Condition Is It Good To Disable A Trigger?
It is good to disable triggers during data load operations. This improves the performance of the data loading activities. The data modification and manipulation that the trigger would have performed has to be done manually after the data loading.
Question 150. Which Column Of The Userjtriggers Data Dictionary View Displays The Database Event That Will Fire The Trigger?
The Description column of the USERJTRIGGERS view combines information from many columns to display the trigger header, which includes the database event.
Question 151. What Is The Meaning Of Disabling A Trigger?
When a trigger is disabled, it does not mean that it is deleted. The code of the trigger is still stored in the data dictionary but the trigger will not have any effect on the table.
Question 152. Can Triggers Stop A Dml Statement From Executing On A Table?
Yes, triggers have the capability of stopping any DML statement from execution on a table. Any logical business rule can be implemented using PL/SQL to block modification on table data.
Question 153. Can A View Be Mutating? If Yes, Then How?
No, a view cannot be mutating like a table. If an UPDATE statement fires an INSTEAD OF trigger on a view, the view is not considered to be mutating. If the UPDATE statement had been executed on a table, the table would have been considered as mutating.
Question 154. Can A Commit Statement Be Executed As Part Of A Trigger?
No, A COMMIT statement cannot be executed as a part of a trigger because it is a Transaction Control statement, which cannot be executed within a trigger body. Triggers fire within transactions and cannot include any Transaction Control statement within its code.
Question 155. What Is The Difference Between Alter Trigger And Drop Trigger Statements?
An ALTER TRIGGER statement is used to recompile, disable, or enable a trigger; whereas, the DROP TRIGGER statement is used to remove the trigger from the database.
Question 156. Do Triggers Have Restrictions On The Usage Of Large Datatypes, Such As Long And Long Raw?
Triggers have restrictions on the usage of large datatypes as they cannot declare or reference the LONG and LONG RAW datatypes and cannot use them even if they form part of the object with which the trigger is associated. Similarly, triggers cannot modify the CLOB and BLOB objects as well; however, they can reference them for read-only access.
Question 157. Are Ddl Triggers Fired For Ddl Statements Within A Pl/sql Code Executed Using The Dbms.sql Package?
No, DDL triggers are not executed for DDL statements, which are executed within the PL/SQL code using the DBMS_SQL package.
Question 158. Does A User_objects View Have An Entry For A Trigger?
Yes, the USER_OBJECTS view has one row entry for each trigger in the schema.
Question 159. How Can You View The Errors Encountered In A Trigger?
The USERJERRORS view can be used to show all the parsing errors that occur in a trigger during the compilation until they are resolved.
Question 160. Does User-triggers Have Entry For Triggers With Compilation Errors?
Yes, USER_TRIGGERS have entries for all triggers that are created in the schema with or without errors.
Question 161. Is It Possible To Pass Parameters To Triggers?
No, it is not possible to pass parameters to triggers. However, triggers fired by INSERT and UPDATE statements can reference new data by using the mew prefix. In addition, the triggers fired in response to UPDATE and DELETE statements can reference old, modified, or deleted data using the :old prefix.
Question 162. Can A Select Statement Fire A Trigger?
No, a SELECT statement cannot fire a trigger. DML statements, such as INSERT, UPDATE, and DELETE, can cause triggers to fire.
Question 163. Can Cursors Be Part Of A Trigger Body?
Yes, cursors can be a part of code in trigger.
Question 164. Is It Possible To Create Startup Or Shutdown Trigger For On-schema?
No, It is not possible to create STARTUP or SHUTDOWN triggers for ON-SCHEMA.
Question 165. What Does The Base_object_type Column Shows In The User.triggers Data Dictionary View?
The BASE_OBJECT_TYPE column identifies the type of database object with which the trigger is associated. It shows whether the object of database is a TABLE, VIEW, SCHEMA, or DATABASE.
Question 166. Is It Possible To Define An Exception Within A Trigger?
Yes, it is possible to define and use an exception within a trigger, as shown in the following code snippet:
Question 167. Can Instead Of Triggers Be Used To Fire Once For Each Statement On A View?
No, INSTEAD OF triggers cannot be used for each statement; however, It can only be used for each row on a view.
Question 168. Is It Possible To Include An Insert Statement On The Same Table To Which The Trigger Is Assigned?
If an INSERT statement is used on the same table to which the trigger is associated, then this will give rise to a mutating table, as it is not possible to change the same table that is already in the process of being changed.
Question 169. What Are Conditional Predicates?
Triggers use conditional predicates, such as INSERTING, UPDATING, and DELETING, to determine which particular event will cause the trigger to fire. All the three predicates have Boolean values and are useful in triggers, such as AFTER INSERT or UPDATE.
Question 170. Write The Alter Statement To Enable All The Triggers On The T.students Table ?
The ALTER statement is given as follows:
ALTER TABLE T_STUDENTS ENABLE ALL TRIGGERS;
Question 171. Which Column In The User.triggers Data Dictionary View Shows That The Trigger Is A Pl/sql Trigger?
The ACTION_TYPE column of the USER_TRIGGERS data dictionary view shows that the trigger is a PL/SQL trigger.
Question 172. What Is A Subprogram? What Are Its Advantages?
A named Procedural Language/Structured Query Language (PL/SQL) block is called a subprogram. It has three sections: declarative, executable, and exception handling (optional). The main advantage of a subprogram is that it can be compiled and stored in a database. In addition, it has the following advantages:
- Modularization —Refers to a property, which allows you to break the large programs into smaller modules and make the program easy to write and debug.
- Easy maintenance —Refers to a property, which allows you to make the modifications in a code because the code is modular and written in one place.
- Reusability—Refers to a property, which allows you to reuse the same subprogram within any application. The code of the subprogram need not be re-written each time.
- Data integrity and security —Refers to a property, which prevents unauthorized users from accessing the subprogram without the proper rights.
- Better performance —Helps in avoiding repeated parsing on subsequent calls to the same subprogram as the code is compiled, parsed, and available in the shared Structured Query Language (SQL) area. This reduces the number of calls to the database; and therefore, increases the network performance.
- Code clarity —Allows you to give proper names to the PL/SQL blocks. As a result, the code becomes simpler to understand and gets better clarity.
Question 173. What Is An Anonymous Block?
Any PL/SQL block, which does not have a name, is called an anonymous block. It is directly written in the application code and compiled by the PL/SQL engine at the time of execution.
Question 174. What Are The Two Different Types Of Subprograms In Pl/sql?
The two different types of subprograms in PL/SQL are procedures and functions. Both can be called from an external environment or application. In addition, they can accept parameters.
Question 175. What Is The Basic Difference Between Procedure And Function?
A procedure is executed as a PL/SQL statement. It can accept more than one parameter as an input from the calling environment and may return none, one, or more than one value.
A function is invoked as a part of expression. It can have more than one parameter as an input from the calling environment and it should return a single value to the calling environment using the RETURN statement.
Question 176. What Is The Difference Between The Code Syntax Of An Anonymous Block And Named Subprogram Block?
A subprogram has two parts: subprogram specification and subprogram body.
- The subprogram specification is the header section, which contains the details such as program name, subprogram type, parameter list, and return clause (for functions).
- The subprogram body is a PL/SQL block, which has the declaration, executable, and exception sections.
An anonymous block has no specification or header section. It only consists of a PL/SQL block in the body that contains the declaration, executable, and exception sections.
Question 177. Give The Basic Block Structure Of A Subprogram ?
The block structure of a subprogram is given as follows:
Question 178. What Is A Procedure? Can It Be Stored In The Database?
A procedure is a named PL/SQL block (subprogram) that can have more than one parameter as an input from the calling environment and it may return none, one, or more than one value.
Yes, it can be compiled and stored in the database as a schema object and used for execution at any point of time.
Question 179. What Does The Replace Option Indicates In The Procedure Definition?
The REPLACE option specifies that if the procedure with the same name exists in the database, it will be dropped and recreated in the database as a new version.
Question 180. Can The Size Of The Parameter Passed To The Procedure Be Restricted In The Procedure Definition?
No, the size of the parameter passed to the procedure cannot be restricted in the procedure declaration. The datatype for the parameter has to be specified only.
Question 181. What Is The Difference Between Formal And Actual Parameters In A Subprogram Specification?
Formal parameters are variables declared as the parameter list in the subprogram specification.
Actual parameters are the values that are passed to the subprogram from the calling environment. The actual parameter values are evaluated and resultant values are assigned to the formal parameters when the program is executed. Both formal and actual parameters should be of compatible datatypes. Following code shows the formal parameters of the function:
In the preceding function, p_student_id and p_percentage are the formal parameters.
In the following function code, v_student_id and v_percentage are the actual parameters:
proc_disp_percentage (v_student_id, v_percentage);
Question 182. Which Datatypes Can Be Used For Parameters Within A Procedure?
Explicit datatypes, %TYPE and %ROWTYPE, without size specification can be used for parameters in a procedure.
Question 183. What Are The Different Parameter Modes, Which Can Be Used In The Procedure Specification?
There are three parameter modes for a procedure, given as follows:
- IN —Refers to a constant value, which is assigned to this parameter from the calling environment. The IN parameter value cannot be modified within a procedure. It is a default mode.
- OUT— Refers to a return value, which is passed from the procedure back to the calling environment.
- IN OUT —Refers to a value, which is assigned from the calling environment to this parameter and a same or different value is passed back from the procedure to the calling environment using this parameter.
Question 184. How Can The Out Parameters Be Viewed On The Execution Of Procedure In The Sql Plus Environment?
The OUT parameters can be viewed in the SQL PLUS environment by declaring variables for the same, as shown in the following code:
On the SQL PLUS prompt, variable is created and the procedure is executed, as shown in the following code:
Question 185. How Can A Parameter Be Initialized In A Procedure?
The IN OUT parameter mode can be used to initialize a parameter in a procedure, as any value can be initially assigned to this parameter when the procedure is called from the calling environment. The value of this parameter can be changed during the execution of the procedure and the result can be returned back to the calling environment from the procedure.
Question 186. Can Default Values Be Assigned To In Out Parameters In A Procedure?
No, default values cannot be assigned to IN OUT parameters; only IN parameters can be assigned default values.
Question 187. What Are The Different Methods For Passing Parameters To A Procedure?
There are following three different methods to pass values to a procedure:
- Positional —Refers to a method in which actual parameters of a procedure are passed in the same order as the formal parameters.
- Named—Refers to a method in which actual parameters of a procedure are passed in any arbitrary order but are associated with their corresponding formal parameters.
- Combination—Refers to a method in which some parameters are passed as positional and some are passed as named parameters.
Question 188. Can A Procedure Be Declared And Defined Within A Pl/sql Block?
Yes, any procedure can be declared and defined within a PL/SQL block.
Question 189. What Is The Scope Of The Procedure Defined Within A Pl/sql Block?
The scope of any procedure that is defined within the PL/SQL block will be limited as it can be called only within the PL/SQL block and not by any other procedure or calling environment.
Question 190. How Can You Reference Remote Tables In Procedures?
Remote tables can be referenced within procedures through SQL statements. A remote table can be queried through a database link in the procedure. The following code inserts a record in the STUDENT_MARKS table, which is in another schema, using the CONNECT_STUDENT database link:
Question 191. What Happens If An Exception Is Unhandled Within A Procedure?
If an exception is unhandled within a procedure, the control is passed back to the calling environment. Any DML statements, which were executed within the procedure, are rolled back.
Question 192. What Is The Basic Specification For A Function?
The basic specification for a function is given as follows:
Question 193. Can There Be Multiple Return Statements Within A Function?
Yes, there can be multiple RETURN statements within a function though only one is executed. After the value is retuned, the control passes back to the calling environment and function processing is stopped.
Question 194. Is It Possible To Define A Return Statement In The Exception Part Of A Function?
Yes, it is possible to define RETURN statement in the exception section. This RETURN statement would be executed if an exception is raised and control is passed to the exception section of the code.
Question 195. What Are The Different Parameter Modes, Which Can Be Used In The Function Specification?
The three different modes, IN, OUT, and IN OUT, can be used in the function specification. However, it is recommended to use only the IN mode. This is because functions are supposed to accept values from the calling environment and return a single value.
Question 196. Can A Function Be Defined Without A Return Statement?
No, a function cannot be defined without a RETURN statement because it has to return a value back to the calling environment. In case a RETURN statement is not defined, runtime error is raised.
Question 197. Can User Defined Functions Be Called Within Sql Statements? If Yes, Then How?
Yes, user defined functions can be called in SQL statements as shown in the following code:
Question 198. Can Functions Be Stored Within A Database As Database Objects?
Yes, functions can be stored as database objects in the database.
Question 199. Which Datatypes Can Be Used Within Functions That Can Be Invoked In Sql Statements?
Stored functions can use valid SQL datatypes, such as NUMBER, VARCHAR2, and DATE. However, they cannot use PL/SQL datatypes, such as BOOLEAN, RECORD, and TYPE.
Question 200. What Restrictions Can Be Applied On Stored Functions, Which Are Called From Sql Statements?
The main restrictions on stored functions that are called from SQL statements are:
- These functions can use only positional methods for passing the parameters.
- These functions can use only SQL specific datatypes. PL/SL datatypes, such as RECORD or TYPE, cannot be used.
- User should have EXECUTE privilege on the function to use it within SQL statements
- These functions cannot have the OUT and IN OUT parameter modes
Question 201. What Is The Difference Between Create Or Replace Function And Drop Function Commands?
The CREATE OR REPLACE FUNCTION command creates the function if it does not exist and replaces it with the new version if it already exists. The DROP FUNCTION command deletes the function altogether from the database. Any privilege assigned on the function remains the same when the CREATE OR REPLACE FUNCTION command is used; however, in the case of the DROP FUNCTION command, all the privileges are revoked.
Question 202. Can A Role Be Used To Define Privileges To Access Database Objects Of Another Schema In Procedures And Functions?
Database objects of another schema cannot be accessed in a procedure or function through roles; however, explicit privileges have to be granted to the user for those database objects.
Question 203. How Can A Compiled Object Code Be Viewed?
Compiled object code cannot be viewed, as it is not accessible to the user.
Question 204. What Is The Use Of The User_errors Data Dictionary View?
The USER_ERRORS data dictionary view is used to view the compilation errors of the subprograms.
Question 205. Write The Sql Query To View All The Procedures Within A Schema ?
SQL query to view all the procedures within a schema is as follows:
Question 206. How Can The Text Of A Procedure Or Function Specification Be Viewed?
The text of a procedure or function specification can be viewed using the USER_SOURCE data dictionary view.
Question 207. Write The Syntax For The Show_error Command ?
The syntax for the SHOW_ERROR command is as follows:
Question 208. How Can The Parameter List For A Function Or Procedure Be Displayed?
The parameter list for a function or procedure can be displayed using the DESCRIBE command, given as follows:
Question 209. What Are The Advantages Of Using Stored Procedures And Functions?
Stored procedures are a collection of SQL, PL/SQL, and Java statements that allow a programmer to shift the code from the application side to the database side. This enables the business rules and application logic to be stored as procedures within the database. The main advantages of using stored procedures and functions are as follows:
- The complex business rules processing is performed within the database on the server side. Therefore, this change of processing from the application on the client side to the database on the server side enhances and improves the performance of the application.
- The procedural code stored within the database can be reused anywhere within the application.
- When a stored procedure is executed for the first time, the shared SQL area in the System Global Area (SGA) of the database stores the parsed versions of the executed commands. After this, whenever the command is executed, it takes the advantage of the parsing, which was performed the first time, and improves the performance of the procedure’s execution.
- Developmental efforts are reduced by keeping the code in a stored procedure. As the business rules are part of the database, they need not be re-written in each code module of the application; and therefore, saves the creation time of the application.
- As the code is stored in a common place, any change can be easily managed; and therefore, reduces the maintenance effort.
Question 210. What Is A Package And How Does It Differ From Procedure And Function?
A package is a group of logically related PL/SQL subprograms bundled together with PL/SQL types and other associated objects. It consists of the following two parts:
- Package specification —Includes declaration of all the objects and subprograms that are part of the package.
- Package body—Includes the total definition of all the parts of a package that have been declared in the specification.
The whole package is fully loaded into the memory when any package construct is called for the first time. Therefore, it does not require any disk input/output on later calls to constructs in the same package.
Question 211. What Is The Difference Between Private Package Construct And Public Package Construct?
- Public package constructs are declared in the package specification but defined in the package body and can be invoked from any other object in the oracle environment.
- Private package constructs are those that are declared and defined only within the package body. They are not declared in the package specification and can only be referenced by other constructs, which are part of the same package.
- Private package construct is always defined before a public package construct within the package body.
Question 212. Can A Complete Package Be Called?
No, a complete package is not possible to call, invoke, or parameterize; however, any construct from the package can be called or invoked.
Question 213. What Is The Difference Between Local And Global Variables Within A Package?
A local variable is defined within a package body and is only available to the constructs within the same package.
A global variable is one that is declared in the package specification. This can be referenced outside the package and is visible, to external objects that have access privilege on the package.
Question 214. Is It Possible To Write A Package Specification Without A Package Body?
Yes, it is possible to write a package specification without a package body but not the vice versa. This is specifically used if there is a package, which is only used for the declaration of public variables, types, constants, and exceptions. In such case, there is no need for a package body and only package specification is enough.
Question 215. What Is Written As Part Of The Package Specification And The Package Body?
The package specification consists of the declaration of all the global variables, cursors, types, procedures, and functions that are public and required to be called from within or outside the package.
The package body consists of the definition of all the public constructs that are declared within the package specification and the definition of all the private variables, cursors, types, procedures, and functions that are required only within the package. Following code snippet shows the implementation of the package specification and the package body:
Question 216. Suppose A Procedure, A_proc, Is Defined With A Number Parameter As Input Within A Package Called A_package In The A_user Schema. Write The Statement To Invoke The
A_proc Procedure On The Sql Prompt From The User,b_user ?
The statement is given as follows:
Question 217. When Does A Package Need To Be Recompiled?
A package needs to be recompiled completely whenever any changes are made in the package specification. Any change to the package body does not require recompilation of the package.
Question 218. What Are The Advantages Of Using Packages?
The main advantages of using packages are given as follows:
- Encapsulation —Packages provide encapsulation of all the logically related constructs together in a single module, which is easy to code and understand.
- Better application design —Both modularity and encapsulation leads to better designing of applications. Separate package specification and body helps in simplifying the coding and better definition of the subprograms.
- Hiding —Public and private constructs can be defined and declared so that any definition can be hidden to protect integrity of the package. Any changes in the private constructs only need the package body to be recompiled without the need for the entire application to be recompiled.
- Better performance —A call to any construct within the package loads the entire package in the memory; therefore, enables better performance of the application.
Question 219. Is Drop Package Command Used To Drop The Package Specification Or The Package Body?
The DROP PACKAGE command is used to drop the entire package including the specification and the body.
Question 220. Is It Possible To Drop The Package Body Without Dropping The Package Specification?
Yes, it is possible to drop the package body without dropping the package specification using the DROP PACKAGE BODY command.
Question 221. How The Concept Of Overloading Is Implemented Within A Package?
PL/SQL allows you to overload the subprogram names and type methods. The package can include subprograms that have the same name as long as they have different formal parameters. PL/SQL determines which subprogram to be called depending on the formal parameters. In PL/SQL, only packaged subprograms can be overloaded.
Question 222. What Is Forward Declaration And How Can It Be Achieved Within A Package?
PL/SQL does not allow the use of any subprogram before it is declared and defined. However, in a package, forward declaration is allowed in case the subprogram specification is terminated by a semi-colon. This enables the subprogram to be defined in a logical manner and groups the subprograms in a package. It also helps in defining mutually recursive programs that call each other directly or indirectly.
Question 223. What Is A One-time-only Procedure And How Is It Declared Within A Package?
A One-Time-Only procedure is executed only once, when the package is called for the first time. While declaring a One-Time-Only procedure the keyword END is not used at the end of the one-time-only procedure; however, the procedure uses the package body’s END clause, as the procedure’s code is placed at the bottom of the package body.
Question 224. Which Privileges Are Required To Execute A Subprogram Owned By Another User?
To execute a subprogram owned by another user, the user must be granted either the EXECUTE privilege on a procedure or function or the EXECUTE ANY PROCEDURE system privilege.
Question 225. How Can A Procedure Or Function Be Executed Which Is Owned By Another User?
A procedure or function, which is owned by another user, may be executed by either creating a synonym for the subprogram or prefixing it with the user name, as shown in the following code snippet:
Question 226. What Is The Significance Of Authid Property While Creating A Procedure?
The AUTHID property affects the name resolution and privilege checking of SQL statements at runtime; however, it does not affect the compilation, and has no meaning for blocks that have no code, such as collection types.
Any procedure may be created with its AUTHID property set to either the DEFINER or the INVOKER rights. If a procedure is created with the DEFINER rights, then the user executing the procedure need not have an access to database objects, which the procedure is accessing. However, if a procedure is created with the INVOKER rights, then the user must have access rights to all the objects that the procedure is accessing.
Question 227. How Can You Bug A Procedure Or Function For Errors?
The procedures or functions can be debugged using the SHOW_ERROR command or the DBMS_OUTPUT command within the subprogram code.
Question 228. When Does A Subprogram Need Recompilation?
When any database object associated with the subprogram changes, the program needs to be recompiled. When the program is executed the next time, after the object is changed, the system will automatically perform runtime recompilation. To avoid this performance overhead, it is better to explicitly recompile the subprogram using the ALTER [PROCEDURE | FUCNTION] command, as shown in the following statement:
ALTER PROCEDURE proc_get_marks compile;
Question 229. When Should A Procedure And A Function Be Used?
Procedure is used when there are more than one return values; however, if there is only one return value, then a function should be used. Although functions may have more than one OUT parameters, it is considered as a poor programming style. In addition, when a subprogram has to be invoked within an SQL statement, function should be used.
Question 230. Can Stand-alone Programs Be Overloaded?
No, stand-alone programs cannot be overloaded; however, packaged sub-programs can be overloaded within the same package.
Question 231. What Are Function Purity Levels?
Function purity levels define what type of data structures can be read or modified by a function. There are four function purity levels, which are given as follows:
- WNDS —Writes no database state. The function does not modify any database table using any DML statement.
- RNDS—Reads no database state. The function does not read any database tables using the SELECT statement.
- WNPS —Writes no package state. The function does not modify any packaged variables.
- RNPS— Reads no package state. The function does not read any packaged variables.
Question 232. What Are The Restrictions On Functions That Are Called Within Sql Statements?
The functions that are called within SQL statements have the following restrictions:
- They must be stored in a database.
- They cannot modify any database table.
- They can only take the IN parameter modes. The OUT and IN OUT parameter modes are not allowed in a function.
- They can only use valid SQL datatypes, such as NUMBER, VARCHAR2, and DATE. However, they cannot use PL/SQL datatypes, such as BOOLEAN, RECORD, and TYPE.
- The return type of a function should be of SQL datatype.
- They cannot have the COMMIT and ROLLBACK statements.
- They cannot have the ALTER SESSION and ALTER SYSTEM commands.
Question 233. Suppose A Procedure Proc__calc_student_marks Inserts The Values Into The Student_total_marks Table. If The Table Is Modified By Adding Another Column, What Needs To Be Done To Make The Procedure Valid?
Oracle recompiles the procedure automatically when the procedure is invoked. Therefore, no action has to be taken if the INSERT statement is using column names. However, if the INSERT statement is just adding values without using the column names, then the procedure has to be modified, as another column has been added.
Question 234. Suppose A Procedure Is Created Using The Create Procedure Command; However, It Gives A Syntax Error. Which Data Dictionary Views Will Have The Source Code Of The Procedure?
The USER_SOURCE, ALL_SOURCE, and DBA_SOURCE data dictionary views will have the source code, as a procedure is stored in the database even if it has a syntax error.
Question 235. Can A Parameter Use A Sequence Generator For The Assignment Of Default Value To A Parameter?
No, a sequence generator cannot be used to assign a default value to a parameter.
Question 236. Can An Out Parameter, V_totalmarks, Be Referenced Within The Procedural Code Without Being Assigned A Value?
Any OUT parameter cannot be referenced without being assigned a value, as the OUT parameter cannot be assigned a default value. Therefore, the v_total_marks parameter cannot be referred within the code until the executable statements of the procedure have begun; the OUT parameter is assigned a value using the PL/SQL assignment statements.
Question 237. Can An In Parameter Be Assigned A Value Within A Procedure?
No, an IN parameter cannot be assigned values within a procedure.
Question 238. Suppose A Procedure Proc_get_highest_rank Is Stored In The User Schema, User_a. How Can You Access The Procedure Within This Schema?
The proc_get_highest_rank procedure can be accessed within the user_a schema from another stored procedure. It can also be accessed either from an anonymous PL/SQL block executed on the SQL prompt logged in as user_a or directly at the SQL prompt using the EXECUTE command. In addition, it can be accessed by other users if they have the EXECUTE privilege.
Question 239. Can Boolean Datatype Be Used In Functions That Are Called From Sql Statements?
No, BOOLEAN datatype cannot be used in functions that are called from SQL statements.
Question 240. Is It Possible To Make Procedure Or Function Part Of The If Then Else Block?
Procedure cannot be called from within the IF THEN ELSE block; however, functions can become a part of the IF THEN ELSE block because functions return the value in such a manner that the function call in the block acts like a variable.
Question 241. Can You Invoke A Stored Function Or Procedure From The Oracle Forms In The Database?
Yes, a stored procedure or function can be invoked from the Oracle Forms and the result can be used for further programming.
Question 242. What Is The Meaning Of The Following Error Statement: Ora-06575: Package Or Function Func_test Is In An Invalid State?
This error is displayed if the function is executed when it is in the invalid state. The ORA-06575 error indicates that for some reason the function is not valid and needs to be checked and compiled again.
Question 243. Suppose A Function Named Func_test_a Calls Another Function, Func_test_b. If The Func_test_b Function Is Dropped, What Will Be The Effect On The Func_test_a Function?
The calling function func__test_a becomes invalid. It has to be edited and compiled again to become valid.
Question 244. Can Functions Be Called As Stand-alone Programs Like Procedures?
No, functions cannot be called as stand-alone programs like procedures. They have to be a part of an expression or they should be invoked as part of SQL statements.
Question 245. Can Functions Stored In Oracle Forms Or Reports Be Invoked From Sql Statements?
No, functions stored on the client side in Oracle Forms or Reports cannot be invoked from SQL statements. They can only be used as PL/SQL expressions in the program units of the same client program.
Question 246. Can A Function Be Compiled Without A Return Statement?
Yes, a function can be compiled without a RETURN statement, but it cannot be executed.
Question 247. Can A Return Statement In The Function Code Be Followed By Other Valid Pl/sql Statements?
It is advisable to keep the RETURN statement at the end of the function code. This is because the function processes the statements until the RETURN statement is encountered and then the control is passed back to the calling environment.
Question 248. Suppose A Function Is Stored In An Oracle Form On The Client Side. Can The Function Be Called From Another Program Unit In Another Oracle Form?
It is not possible to call a function stored in one Oracle Form from another Oracle Form unless it is defined and declared in a PL/SQL library, which is shared between the two Oracle Forms.
Question 249. How Does A Package Support Information Hiding?
A package consists of two parts: package specification and package body. The package specification contains the names of procedures and functions, their parameters, and return types; whereas, the subprogram code is part of the package body. Developers who require calling the procedures or functions just need to have access on the package specification. Therefore, the package owner can grant privilege on the package specification while hiding the package body. In this way, the developers can use the package without seeing the actual code. In addition, package owners can create procedures and functions that are private for the package and need not be part of the package specification. In this way, information hiding is achieved within a package.
Question 250. Where Can A Package Be Stored?
A package can be stored on the server side within a schema in the database. It can also be stored on the client side in the form of Oracle Reports, Oracle Forms, or as part of PL/SQL library.
Question 251. Does A Package Is Recompiled If The Database Objects That The Package Uses Are Modified?
Yes, if the database objects associated with the package are modified, the package specification and the package body become invalid; and therefore, the package needs recompilation using the following syntax:
Question 252. Are Global Constructs Declared In Packages Available To The Entire User Session?
Global constructs once declared in package are available throughout the user session. Until the user session is terminated, the global construct retains its value across the PL/SQL blocks. This is applicable for all the packaged global constructs, such as constants, exceptions, and cursors. For example, a globally declared cursor may be opened in one PL/SQL block, fetched in another block, and closed in some other block.
Question 253. Where Can A Constant Be Referenced If The Constant Is Declared In A Package Specification?
The constant declared in a package specification can be referenced in the same package where it is declared, stored procedure, stored function, or an anonymous b : c.
Question 254. Can A Variable Be Referenced In A Package If It Is Declared In The Package Body Of Another Package?
Variables declared in a package body are private variables. Therefore, these cannot be referenced in other packages.
Question 255. How Many Entries Does The User_objects Data Dictionary Have For A Package?
Two separate entries are created for package specification and package body in the USER_OBJECTS data dictionary.
Question 256. What Is The Minimum Requirement To Create A Package?
Package specification, which declares constructs, such as variables, constants, user-defined exceptions, and cursors, is enough to create a complete package. Package body is not required if there are no procedures and functions to be defined in the package.
Question 257. How Can A Public Exception, Which Is Global In Nature, Be Created In A Package?
An exception is called a public exception if it is declared in a package specification as a separate construct, as shown in the following code:
Question 258. Write A Statement To Display The Text Of The Test_packl Package From Data Dictionary View ?
The statement is given as follows:
Question 259. What Is An Exception?
An exception is an identifier, which defines an error condition within the Procedural Language/Structured Query Language (PL/SQL) code. When an error occurs, it is trapped and the program control unconditionally branches to the exception section of the PL/SQL block.
Question 260. How Is An Exception Raised And Handled?
When a runtime error occurs, an exception is raised. The exception is handled when this error is successfully handled by the PL/SQL code and is not allowed to propagate to the outward environment. Compilation error cannot be handled within the program; only runtime errors can be handled within the PL/SQL code.
An exception can be raised:
- By a runtime Oracle error
- By the RAISE statement
- By calling the RAISE_APPLICATION_ERROR procedure
After an exception is raised, the control is passed to the exception section, where the code is written about how to handle the error that has occurred. The control cannot pass back to the executable section of the same block after the exception is handled.
Question 261. When Is An Exception Propagated To The Outside Environment?
An exception is raised within the program and if there is no corresponding exception handler, the PL/SQL block terminates unsuccessfully and the exception is propagated to the calling environment.
Question 262. Give The Block Structure Of An Exception Section Within The Pl/sql Code ?
The structure of an exception section within the PL/SQL code is given as follows:
Question 263. What Are The Different Types Of Exceptions?
The three types of exceptions that can be handled at runtime are as follows:
- Predefined Oracle error —Refers to an undeclared error, which is raised by the Oracle server implicitly.
- Non-predefined Oracle error—Refers to an error declared in the declarative section of the code that is raised by the Oracle server implicitly.
- User-defined error —Refers to an error declared in the declarative section of the code that is raised explicitly.
Question 264. Describe The Others Exception Handler ?
The exception section handles only those errors that are specified; however, other errors that are not specified cannot be handled. To handle these errors explicitly, the OTHERS exception handler is used that is always defined as the last exception handler.
Question 265. Can The Pl/sql Block Process More Than One Exception At A Time?
No, the PL/SQL block cannot handle more than one exception. It can process only one exception handler before terminating.
Question 266. Is It Possible To Have More Than One Others Clause?
No, it is not possible to have more than one OTHERS clause in the exception section.
Question 267. Give A Few Pre-defined Oracle Errors ?
Pre-defined Oracle errors are as follows:
- NO_DATA_FOUND —Refers to an error when a SELECT statement does not return any result set.
- DUP_VAL_ON_INDEX —Refers to an error when a program tries to store duplicate values in an indexed column.
- TOO_MANY_ROWS —Refers to an error when a SELECT INTO statement returns more than one row.
- VALUE_ERROR —Refers to an error when an arithmetic, conversion, truncation, or size-constraints error occurs.
- ROWTYPE_MISMATCH —Refers to an error when the actual and formal parameters do not have the same datatypes
Question 268. What Is The Advantage Of Others Exception Handler?
The OTHERS exception handler ensures that no exception goes unhandled and the program terminates successfully.
Question 269. Describe The Sqlcode And Sqlerrm Functions ?
The SQLCODE function returns the Oracle error number for internal exceptions. This error number can be passed to the SQLERRM function to get the message associated with the error number. The SQLCODE function returns the value +1; whereas, the SQLERRM function returns the User-Defined Exception message for the user-defined exceptions.
Question 270. How Can The Sqlcode And Sqlerrm Functions Be Used For Exception Handling?
These functions can be used with the OTHERS exception handler to get the error number and the error message that has caused the OTHERS exception to be raised, as shown in the following code snippet;
Question 271. How Are User-defined Exceptions Trapped?
User-defined exceptions are defined in the declarative section of the PL/SQL code and raised explicitly using the RAISE statement, as shown in the following code snippet:
Question 272. What Happens To The Program Control When The Raise_application_error Procedure Is Encountered?
When the RAISE_APPLICATION_ERROR procedure is encountered, it ends the program unit and returns the error number and message to the application. The error number and message can be trapped like any Oracle error.
Question 273. What Happens After A Sub-block Handles An Exception?
When a sub-block handles an exception, the control passes to the next statement in the executable section of the enclosing block immediately after the END statement of the sub-block.
Question 274. Can Raise_application_error Be Called In The Executable Section Of The Program Unit?
Yes, the RAISE_APPLICATION_ERROR procedure can be called in the executable section as well as the exception section of the PL/SQL code.
Question 275. What Happens If An Exception Is Not Handled In A Sub- Block?
If an exception is not handled in a sub-block, the exception propagates to the enclosing blocks until the associated handler is found. If none of the blocks has a handler for the exception, an unhandled exception error is passed back to the host environment as follows:
Question 276. Can Processing Be Resumed From The Point Exception Was Raised After The Exception Is Handled?
After the exception is handled, processing cannot be resumed within the executable section of the current block from where the exception was raised. The current block where the exception handler is declared will be terminated. The control will pass to the statement in the enclosing executable block. If there is no enclosing block, control will pass back to the calling host environment.
Question 277. How Can A Transaction Be Retried After An Exception?
The following method can be used to retry a transaction after an exception is raised:
- The transaction should be written within a sub-block
- The sub-block can be placed inside a loop that repeats the transaction.
- Mark a savepoint before starting the transaction. If the transaction succeeds, then commit and exit from the loop. If the transaction fails, the control is transferred to the exception handler where the transaction is rolled back to the savepoint to undo the changes, and then try to fix the problem.
Question 278. Does Raise_application_error Overwrite The Error Stack Or Append Messages To The Error Stack?
The default behavior of the RAISE_APPLICATION_ERROR procedure is to overwrite the error message stack. However, if the parameter is passed, then the messages can be appended to the error stack as well. Following is the syntax of the RAISE_APPLICATION_ERROR procedure:
In the preceding syntax, TRUE adds the messages to the error stack; whereas, FALSE overwrites the error stack.
Question 279. What Causes The Invalid_cursor Exception?
This exception is raised when the program attempts to perform an illegal operation, such as closing an unopened cursor.
Question 280. How Can A Current Exception Be Re-raised?
The RAISE statement can be used to re-raise the current exception. It stops the normal execution of a PL/SQL program and transfers the control to an exception handler, as shown in the following code:
Question 281. Where Will The Control Be Transferred If An Exception Is Raised Within An Exception Section Of The Block?
When an exception is raised within an exception section of a block, the control is transferred to the exception section of the enclosing block.
Question 282. When Is The Cursor_already_open Exception Raised?
This exception is raised when the program tries to open an already opened cursor. The cursor should be closed before it can be opened again.
Question 283. When Is The Row_type_mismatch Exception Raised?
The ROW__TYPE_MISMATCH exception is raised when a host cursor variable and a PL/SQL cursor variable involved in an assignment have incompatible return types.
Question 284. What Is The Advantage Of Having A Separate Exception Section Within A Pl/sql Code?
The advantages of having a separate exception section within a PL/SQL code are as follows:
- It is easier to understand the program logic as it is easily visible and readable
- Any error in any statement will be detected and handled easily
Question 285. What Happen When There Is No Handler For A Raised Exception?
If an exception is raised and there is no handler in the current block, the exception would propagate to the enclosing block. If no handler is found in the enclosing block, the exception will be propagated to the calling environment as an unhandled exception.
Question 286. What Is The Difference Between User-defined Exceptions And Oracle-defined Exceptions?
User-defined exceptions are explicitly raised; whereas, Oracle- defined exceptions are raised implicitly.
Question 287. What Is The Difference Between Raise And Raise_application_error?
- RAISE is used to call pre-defined exceptions declared in the declarative section of the PL/SQL block.
- RAISE_APPLICATION_ERROR is used to call user-defined ORA – error messages from stored subprograms.
Question 288. What Is The Meaning Of Pragma Keyword?
The PRAGMA keyword signifies that the statement is a compiler directive, which is not processed when the PL/SQL is executed. It is a pseudo-instruction that tells the compiler to interpret all the occurrences of exception name within the block with the associated Oracle server number.
Question 289. What Are Oracle Supplied Packages?
Oracle server provides the Oracle Supplied Packages to allow Procedural Language/Structured Query Language (PL/SQL) to access certain features of SQL, which are not available in PL/SQL. They also help in extending the functionality of the database.
Question 290. What Is Catproc.sql?
The catproc.sql is a SQL file, which has the script to create packages in the database, This file has to be executed on the SQL prompt for the packages to be available in the database.
Question 291. What Is Dynamic Sql And What Is Its Usage?
Dynamic SQL is used by PL/SQL to execute Data Definition Language (DDL) statements, Data Control (DCL) statements, or Transaction Control statements within PL/SQL blocks. These statements are not stored within the source code but are stored as character variables in the program. The SQL statements are created dynamically at runtime by using variables. This is used either using native dynamic SQL or through the DBMS_SQL package.
Question 292. How Is An Sql Statement Processed? What Are The Steps That An Sql Statement Undergoes For Compilation?
SQL statements are compiled using the following steps:
- Parse—Refers to a step in which statements are checked for existence of all objects used and the syntax is validated.
- Bind— Refers to a step In which the values for the variables are obtained. This process is also called binding variables.
- Execute —Refers to a step in which the statement is executed by Oracle.
- Fetch —Refers to a step in which rows are selected and retrieved one by one until last row is fetched.
Question 293. What Is The Use Of Dbms_sql Package?
The DBMS_SQL package is used to access dynamic SQL and dynamic PL/SQL from within a PL/SQL program. It allows you to execute statements that are otherwise impossible from within PL/SQL programs, which includes DDL, DCL, or Transaction Control statements.
Question 294. Does Dynamic Sql Support All Sql Data Types?
Yes, dynamic SQL supports all SQL data types. It is possible to define variables and bind arguments of collection type, LOBs, and REFs.
Question 295. Are The Pl/sql Specific Datatypes Supported By Dynamic Sql To Define Variables And Bind Arguments?
No, PL/SQL datatypes are not supported by dynamic SQL; only PL/SQL RECORD datatype can be used in the INTO clause. No other PL/SQL datatype, such as BOOLEAN, can be used within dynamic SQL.
Question 296. What Is The Use Of Execute Immediate Command? Explain With Example ?
The native dynamic SQL can be executed within a PL/SQL block using the EXECUTE IMMEDIATE command, as shown in the following example:
Question 297. Give The Usage Of Dbms_ddl Package ?
The DBMSJDDL package provides access to certain DDL statements within PL/SQL blocks. This package runs with the privileges of the calling user and not the owner of the SYS package. Some of the procedures included in this package are as follows:
- DBMS_DDL.ALTER_COMPILE object_type, owner, object__name)
- DBMS_DDL.ANALYZE_OBJECT(object_type, owner, object_name, method)
Question 298. Which Oracle Package Is Used For Scheduling Job?
The DBMS_JOB package is used for scheduling and executing PL/SQL programs.
Question 299. Which Are The Subprograms Of The Dbms_job Package?
The subprograms of the DBMS_JOB package are as follows:
- SUBMIT—Submits a job to the job queue
- REMOVE—Removes the specified job from the job queue
- CHANGE —Alters the specified job that has already been submitted to the job queue
- WHAT—Alters the specified job
- NEXT__DATE—Alters the execution time
- INTERVAL —Alters the interval between execution of specified job
- BROKEN—Disables the job execution
- RUN—Forces a specified job to be executed
Question 300. Which Procedure Of The Dbms_job Package Is Used To Disable The Execution Of A Particular Job?
The DBMS_JOB.BROKEN procedure disables the execution of the specified job. This job will not be executed by the Oracle server.
Question 301. Give The Parameters Of The Dbms_job.submit Procedure ?
The DBMS_JOB.SUBMIT procedure adds a new job to the job queue. It takes four parameters as input and returns the job number as the output though the JOB parameter. The parameters of the DBMSJOB.SUBMIT procedure are as follows:
- JOB— Refers to the OUT parameter that gives the output of the job number, which is the unique identifier for the job
- WHAT— Refers to the IN parameter, which provides the PL/SQL code to be executed
- NEXT_DA TE —Refers to the IN parameter, which provides the next execution date of the job
- INTERVAL —Refers to the IN parameter for the interval used to calculate the next execution date
- NO_PARSE —Refers to the IN parameter, which is a Boolean flag indicating whether to parse the job at the time of job submission
Question 302. Provide A Program Code In Which The Dbms_job.submit Procedure Is Used To Schedule A Database Backup Job In The Queue For Midnight ?
The following program code is used to schedule a database backup job using the procedure pk_db_activities.proc_db_backup in the queue for midnight:
Question 303. When Is An Exception Raised By The Dbms_job Package?
An exception is raised if the time specified for the job does not evaluate to a job in future.
Question 304. What Is Use Of The Dbms_job.next_date Parameter?
The DBMS_JOB.NEXT_DATE parameter is used to reschedule a particular job. This parameter is used when the next execution date for a particular job needs to be changed.
Question 305. Give A Statement In Which The Dbms_job.change Procedure Is Used To Change The Backup Job Submitted To Morning 8am And Above ?
The job can be rescheduled as follows:
Question 306. Is It Possible To Run A Job Scheduled For A Particular Time To Be Run Immediately? If Yes, Then How?
Yes, It is possible to run a job scheduled for a particular time immediately using the DBMS_JOB.RUN procedure.
Question 307. Which Data Dictionary View Is Used To See The Status Of Submitted Jobs?
The DBA_JOBS data dictionary view is used to display the status of the submitted jobs.
Question 308. Which Data Dictionary View Is Used To See The Status Of Currently Running Jobs?
The DBA_JOBS_RUNNING data dictionary view is used to display the status of the jobs currently running.
Question 309. What Is The Use Of Dbms_output Package?
The DBMS_OUTPUT package is used to display output messages from the PL/SQL block.
Question 310. How Can The Dbms_output Package Used To Get An Array Of Lines From Buffer Into The Procedure?
The DBMS_OUTPUT package uses the GET_LINES procedure to get an array of lines from output buffer into the procedure.
Question 311. What Is The Utl_file Package?
The UTL_FILE package provides text files input/output functionality within PL/SQL. The security on client-side is handled using normal operating system file permissions; whereas, server-side security is handled though restrictions on the directories that are accessed.
Question 312. What Is Essential To Be Set In The Init.ora File Before Using The Utl_file Package?
It is essential to initialize the UTL_FILE_DIR parameter in the init.ora file before using the UTL_FILE package. This parameter is initialized to the directory that is to be accessed as follows:
Question 313. How Can All The Directories Be Made Available To The Utl_file Package?
The following settings will turn off all the database permissions on the directories that are accessible to the database processes and make all of them available to the UTL_FILE package:
Question 314. What Is The Difference Between The Put_line And Putf Functions Of The Utl_file Package?
The difference between the PUT_LINE and PUTF functions of the UTL_FILE package is as follows:
- PUT_LINE —Refers to a function used to write text string, which is stored in the buffer, to the opened file with a line terminator.
- PUTF— Refers to a function used to write text string, which is stored in the buffer, to the opened file but in a formatted form with format specifiers – %s and n. The %s format specifier is used to substitute a value in the output string and n is a new line character.
Question 315. What Is The Use Of Fflush Procedure Of The Utl_file Package?
The FFLUSH procedure is used to write all data buffered in memory to a file.
Question 316. Name A Few Exceptions Of The Utl_file Package ?
A few exceptions of the UTL_FILE package are as follows:
- INVALID_PATH — Specifies that a file location or filename is invalid
- INVALID_MODE — Specifies that the OPEN_MODE parameter in the FOPEN procedure is incorrect
- READ__ERROR — Specifies that an operating system error has occurred during the read operation
- WRITE_ERROR — Specifies that an operating system error has occurred during the write operation
- INVALID__OPERATION — Specifies that a file cannot be opened or used
Question 317. Can The Utl_file Package Raise Predefined Pl/sql Error?
Yes, the UTL_FILE package can raise predefined exceptions, such as NO_DATA_FOUND or VALUE_ERROR.
Question 318. What Is The Difference Between Fopen And Is_open Functions?
The difference between FOPEN and IS_OPEN functions is given as follows:
FOPEN —Opens a file for input/output and returns a file handle used in I/O operations. Following is the syntax of the FOPEN function:
IS_OPEN — Returns a Boolean value when the file handle is passed as a parameter to the function to an open file. Following is the syntax of the IS_OPEN function:
Question 319. Explain The Functionality Of Utl_http Package ?
The UTL_HTTP package enables PL/SQL and SQL to access the Internet data using the REQUEST and REQUEST_PIECES functions. These functions take the Uniform Resource Locator (URL) of the website as input and return the HyperText Markup Language (HTML) data from the website.
Question 320. What Is The Size Of Data That Can Be Retrieved For The Database Using The Utl_http Package?
The size of the data that can be accessed from the Internet is based on the functions used. The REQUEST function returns up to the first 2000 bytes of data retrieved from the website. The REQUEST_PIECES function returns a PL/SQL table of 2000 bytes from the accessed URL.
Question 321. Which Exception Is Raised If The Utl_http Package Fails Due To Wrong Url Specified?
If wrong URL is specified in the UTL_HTTP package call, the REQUEST_FAILED exception is raised.
Question 322. What Is The Utility Of Utl_tcp Package? How Is The Connection Opened And Closed?
The UTL_TCP package is used by PL/SQL applications to access external Transmission Control Protocol/Internet Protocol (TCP/IP) based servers using the TCP/IP protocol.
The connection is opened using the OPEN_CONNECTION function, which opens a TCP/IP connection when the remote host, local host, and both the port numbers are specified. It returns a connection of PL/SQL RECORD type.
The connection is closed using the CLOSE_CONNECTION function, which closes the connection of the port whose details are provided as input.
Question 323. In Which Situations Exceptions Are Raised While Using The Utl_tcp Package?
An exception is raised using the UTL_TCP package in the following situations:
- The buffer size is too small for the input
- A network error occurs
- No more data is available to read from the connection
- Invalid arguments are passed to the function
Question 324. What Is The Difference Between Close_connection And Close_all_connections Procedure Of The Utl_tcp Package?
The CLOSE_CONNECTION procedure closes the connection whose details are provided as input where as the CLOSE_ALL_CONINJECTIONS procedure closes all the opened connections.
Question 325. Which Oracle Package Is Used To Check The Status Of A Schema And Analyze The Objects Of The Schema?
The DBMS_UTILITY package is used to check the status of a schema and analyze the objects of the schema.
Question 326. What Is The Use Of Dbms_transaction Package?
The DBMS_TRANSACTION package controls logical transactions and improves the performance of short and non-distributed transactions.
Question 327. Is It Possible To Access Sql Session Information?
Yes, it is possible to access the SQL sessions using the DBMS_SESSION package.
Question 328. Is There A Built-in Random Number Generator In Oracle?
Yes, there is a built-in random number generator in the DBMS_RANDOM package.
Question 329. Which Oracle Package Is Used To Manage The Oracle Lock Management Services?
The DBMS_LOCK package is used to manage the Oracle Lock Management services. This package is used to request, convert, and release the locks using the Oracle Lock Management services.
Question 330. What Is The Utility Of The Dbms_logmnr_d Package?
This package queries the dictionary tables of the current database and creates text based file with their contents.
Question 331. What Is The Difference Between Blob And Clob?
The BLOB datatype is used to store a binary large object, such as a video image file; whereas, the CLOB datatype is used to store a character large object.
Question 332. Is It Possible To Convert Long Objects To Lob Objects?
Yes, it is possible to convert LONG and LONG RAW objects to LOB objects using the TO_LOB function.
Question 333. What Are The Two Components Of Lob Datatype?
The two components of the LOB datatype are given as follows:
- LOB locator — Refers to a locator, which points to the location in the database where the value is stored. This value is stored along with the record in the table row and is similar to a pointer to the actual location of LOB value.
- LOB value — Refers to an actual image or value of the LOB datatype.
Question 334. Can Lob Datatype Be Used For Pl/sql Variables Or Parameters?
Yes, a LOB datatype can be used as a PL/SQL variable or parameter.
Question 335. What Is The Difference Between Internal And External Lobs?
LOB datatype can be internal or external. Internal LOBs are CLOB, NCLOB, and BLOB that are stored in the database; whereas, external LOBs are BFILEs that are stored outside the database as external files. Although both LOB types have the pointer to the location, the BFILEs are stored externally across hard disks or on external storage; whereas, internal LOBs have to be stored in the different location within the database itself.
Question 336. Can Bfiles Be Used For Transactions?
No, BFILEs are read-only and cannot participate in transactions.
Question 337. Are The Bfiles Deleted When Lob Column Record Is Deleted?
When the LOB record is deleted, the BFILE type of external file is not actually deleted; only the locator to the location in the database table is deleted.
Question 338. How Many Bfiles Can Be Opened In A Session?
The number of BFILEs that can be opened in a session is dependent on the parameter SESSION_MAX_OPEN_FILES, which is set in the init.ora file. Its default value is 10. The maximum value is either 50 or the value set in the parameter.
Question 339. Which Constraints Are Allowed For Lob Objects?
The NULL and NOT NULL constraints are allowed for LOB objects.
Question 340. What Is The Difference Between To_blob And To_clob Functions?
The TO_CLOB function is used to convert the LONG, VARCHAR2, and CHAR datatypes to the CLOB datatype.
The TO_BLOB function is used to convert the LONG RAW and RAW datatype to the BLOB datatype.
Question 341. Is It Possible To Convert Clob To Char Type?
Yes, it possible to convert CLOB to CHAR type using the TO_CHAR function.
Question 342. What Is The Usage Of Dbms_lob Package?
The DBMS_LOB package helps PL/SQL programs to access and manipulate the LOB objects.
Question 343. Does The Dbms_lob Package Support Concurrency Control?
No, the DBMS_LOB package does not support concurrency control. The user is responsible for locking the row explicitly before any transactions. The DBMS_LOB package does not provide implicit locking of the row with LOB values.
Question 344. What Are Mutators And Observers In Relation With The Dbms_lob Package?
The DBMS_LOB package has functions and procedures to manipulate LOB objects. These can be categorized into mutators and observers. Mutators can modify LOB values, such as APPEND, COPY, WRITE, FILECLOSE, and FILEOPEN; whereas, observers can only read the LOB values, such as COMPARE, INSTR, GETLENGTH, FILEEXISTS, and FILEGETNAME.
Question 345. How Can A Lob Column Be Added To An Existing Table T_students?
A LOB column can be added using the following syntax:
ALTER TABLE t_students ADD(stud_id_file BLOB);
Question 346. Is It Possible To Query And See The Data In Blob And Bfile Format Using The Select Statement?
No, the BLOB and BFILE data cannot be seen using the SELECT statement query. Relevant software, such as Oracle Forms or Reports, should be used for this purpose.
Question 347. Is It Possible To Update Blob And Clob Columns Without Setting It To An Empty Locator?
No, the BLOB and CLOB columns cannot be updated until the value is set using the EMPTY_BLOB or EMPTY_CLOB function through an UPDATE statement.
Question 348. Does Oracle Maintain Data Integrity And Concurrency For Lob Datatypes?
Yes, Oracle maintains data integrity and concurrency for internal LOBs (BLOBs and CLOBs) but not for BFILEs, which are external LOBs.
Question 349. What Is The Benefit Of Storing The Lob Datatype Out-of- Line?
This allows the database to avoid scanning LOB data each time it reads rows from the table. The LOB data will be read only when it is required; however, at other times, only the LOB locator values are read.
Question 350. Is It Possible To Set Null Values To Bfile Columns?
Yes, BFILE can be set to NULL values; whereas, BLOB and CLOB cannot be set to NULL values. They have to be initialized using the EMPTY_BLOB or EMPTY_CLOB function.
Question 351. Which Functions Should Be Used Before Performing Data Manipulation On Bfiles?
Following are the procedures or functions of the DBMS_LOB package that should be used before manipulating the BFILE data:
- FILEOPEN— Opens files
- FILEGETNAME—GeXs the filename
- FILEEXISTS —Checks whether the file exists
- FILEISOPEN— Checks whether the file is open
These procedures are used, as Oracle does not maintain the integrity of the external file; only the pointer is maintained.
Question 352. Can Lob Values Be Deleted From The Database?
When Internal LOB datatype (BLOB, CLOB, and NCLOB) column values are deleted, both locator and values are deleted. If an external LOB (BFILE) value is deleted, then only the locator value is deleted. The external file has to be manually deleted from the server file system.
Question 353. What Is The Difference Between Referenced Object And Dependent Object?
If the definition of an object A references object B, then A is the dependent object and B is the referenced object. If a table is being queried in a procedure, then the table is the referenced object and the procedure is the dependent object. However, if the definition of the table is modified, the procedure may or may not execute correctly.
Question 354. Does Oracle Automatically Record The Dependencies Among The Schema Objects?
Yes, Oracle automatically records the dependencies among objects through the USER_OBJECTS data dictionary view, where the schema objects have a valid or invalid status.
Question 355. Which Schema Objects Can Be Referenced Directly Or Indirectly?
Any procedure or function can be referenced directly or indirectly using the following objects:
- Package procedures or functions
Question 356. What Is Indirect Reference Of Schema Objects?
When any procedure or function references another schema object through an intermediate procedure, function, or view, then the reference is called indirect reference.
Question 357. How Does An Object Get An Invalid Status?
If there is any change in any of the referenced object, the dependent object will get an invalid status. For example, if the structure of base table of a view is modified, the view will get an invalid status.
Question 358. What Happens When An Object Has An Invalid Status?
Whenever an invalid object is called again, the Oracle server automatically recompiles the object and it gets a valid status. For example, if a view with an invalid status is queried, it will be recompiled before the results are displayed.
Question 359. Which Data Dictionary Views Are Used To Display The Dependencies Between The Schema Objects?
The USER_DEPENDENCIES, DBA_DEPENDENCIES, and ALL_DEPENDENCIES data dictionary views display the dependencies among the schema objects.
Question 360. Which Column In The User_dependencies View Indicates The Type Of The Reference Object?
The REFERENCED_TYPE column of the USER_DEPENDENCIES view indicates the type of the referenced object.
Question 361. Write A Query To Display All Object Types That Are Dependent On Other Objects In The Database ?
The following query will display the object types that are referenced by other objects in the database:
Question 362. Suppose The Student_grades View Is Based On The Student_marks Table. If The Length Of The Student_name Column Is Modified, Will The View Become Invalid?
If the STUDENT_GRADES view Includes the STUDENT_NAME column then it will become invalid; and therefore, needs to be recompiled. If the view does not include the column, which is modified, then it is valid.
Question 363. What Is Cascading Invalidation?
Suppose object A references object B, which in turn references object C, then A is an indirect dependent of object C. If object C is changed, then object B is invalidated; and therefore, invalidates object A as well. This is called cascading invalidation.
Question 364. What Is The Effect On The Dependent Objects If An Object Is Renamed?
All the dependent objects become invalid if an object, which the dependent objects reference, is renamed.
Question 365. What Is The Use Of Deptree And Ideptree Views?
These views contain information on the object direct and indirect dependency tree.
Question 366. How Do You Create The Deptree And Ideptree Views?
These views are created by executing the utldtree.sql file which is found in the $ORACLE_HOME/rdbms/admin folder.
Question 367. What Is The Significance Of The Nested_level Column In The Deptree View?
The NESTED_LEVEL column shows the level of nesting of a particular object within the dependency tree. The parent object has a NESTED_LEVEL of 0 and all other objects are numbered as per their level in relation to the parent object.
Question 368. What Is The Meaning Of Nested_level 2 For An Object In The Dependency Tree?
NESTED_LEVEL 2 for an object in the dependency tree means that the object is dependent on another object, which has NESTED_LEVEL 1.
Question 369. What Is The Ideptree View And How Is It Different From The Deptree View?
The IDEPTREE view is a pre-indented representation of the hierarchical structure of the schema objects, It is a sorted version of the DEPTREE view.
Question 370. Which Procedure Has To Be Executed Before Using The Ideptree View?
The DEPTREE_FILL procedure needs to be executed before the IDEPTREE view can be used.
Question 371. What Information Is Stored In The Ideptree View?
The IDEPTREE view has the hierarchical information of the dependency tree in a single column called DEPENDENCIES. This column stores a single string of information showing the dependent objects indented as per the hierarchy.
Question 372. Which View Is Used To Track The Security Dependencies?
The security dependencies can be tracked using the USER_TAB_PRIVS data dictionary view.
Question 373. What Is The Effect On The Dependent Object When A Package Body Is Modified?
When a package body is modified, there is no change in the dependent objects.
Question 374. Is A View Invalidated When A New Column Is Added To The Table It References?
No, a view is not invalidated if a new column is added to the table it references.
Question 375. What Is The Advantage Of Referring Tables Indirectly Using Views?
Referencing tables indirectly using views allows:
- Adding columns to the table without invalidating dependent views or dependent PL/SQL objects
- Modifying or deleting columns of the table that are not referenced by the view without invalidating the dependent objects
Question 376. How Does Oracle Manage Dependencies Across Remote Databases On Distributed Networks?
Oracle does not manage dependencies across remote schema objects on distributed networks other than local-procedure-to- remote procedure dependencies. It only manages dependencies among stored procedures (procedures, functions, and packages) using time-stamped checking or signature checking. If there is a local view based on a remote table, and the remote table definition is altered, the local view is not invalidated.
Question 377. Does A Change To A Remote Procedure Automatically Change The Status Of Dependent Objects In Local Database To Invalid?
No, a change to a remote procedure does not automatically change the status of dependent objects in local database to invalid.
Question 378. How Is Time-stamped Checking Used For The Management Of Remote Dependencies?
Time-stamped checking is based on the fact that every program unit carries a time stamp, which is set when it is created or recompiled. Whenever a program unit or a schema object is altered, all its dependent units are invalidated and must be recompiled before they can be executed. The time stamp is recorded in the p-code of the procedure.
The time stamp of the remote procedure is also stored in the p-code of the local procedure when it is compiled. A local procedure, which refers to a remote procedure, is invalidated if the remote procedure is recompiled after the local procedure is compiled. When the time stamp of the remote procedure, which is stored in the local procedure, is compared with the time stamp of the recompiled remote procedure, both the time stamps differ. This invalidates the local procedure and returns a runtime error.
Question 379. What Parameter Is Used To Determine Whether The Mode Of Checking For The Management Of Remote Dependencies Is Time-stamped Or Signature Checking?
The parameter used to determine if the mode of checking for the management of remote dependencies is time-stamped or signature checking is set in the init.ora parameter file as REMOTE_DEPENDENCIES_MODE = value, where value can be TIMESTAMP or SIGNATURE.
Question 380. What Is The Meaning Of Signature Of A Procedure?
The signature of a procedure refers to:
- The name of the procedure
- The datatype of the parameters
- The modes of the parameters
- The number of parameters
- The datatype of the return value in case of function
Question 381. What Is The Disadvantage Of Time-stamped Checking?
The disadvantage of time-stamped checking is that it causes unnecessary recompilation of the dependent objects across networks when it may not be required, leading to the degradation of system performance.
Question 382. How Is Signature Checking Better Than The Time-stamped Checking?
Signature checking eliminates the problem of unnecessary recompilation of the program units across networks. The signature of the remote procedure is stored in the p-code of the local procedure. In signature checking, the signature of the remote procedure is compared and not the time stamp. If the remote program is modified and recompiled but its signature does not change, then the local procedure can be executed without any runtime error. Therefore, signature checking does not degrade the system performance by repetitive compilation of the local procedure.
Question 383. What Happens If The Time-stamped Checking Is Used For Dependency Checking Across Remote Databases In Different Time Zones?
The time stamp of the remote procedure and the local procedure will not match and the dependency checking will not be successful. Therefore, while working on remote databases across time zones, signature checking should be used.
Question 384. What Happens If A Private Referenced Table Is Dropped, But A Public Table With The Same Name Exists?
When a private table, which is referenced by a dependent procedure, is dropped, the dependent procedure is invalidated. When the procedure is recompiled and a public table of the same name exists with all the columns required by the procedure, then the procedure will recompile successfully but refer to the public table.
Question 385. Give A Few Guidelines To Minimize The Dependency Failures ?
A few guidelines that would minimize the dependency failures are as follows:
- Refer to tables indirectly using views
- Include column list with INSERT statements
- Query with SELECT * notation
- Declare records with the %ROWTYPE attribute
- Declare variables with the %TYPE attribute
- Use packages for procedures and functions
Question 386. Can A Table Or Synonym Be Invalidated?
A table or synonym can never be invalidated; only dependent objects are invalidated.
Question 387. Suppose A Schema 2 Has Granted Necessary Privileges To The Schema 1 Through Roles. Is It Possible For A Program Unit In The Schema 1 To Refer The Database Objects In The
Schema 2 ?
No, a program unit in the schema 1 cannot reference the database objects in the schema 2, even if the schema 2 has granted necessary privileges to the schema 1 through roles. This is because granting privileges through roles does not work for program units. They can reference other schemp objects only if the necessary privilege has been granted directly to the schema.
Question 388. Suppose A Schema A Has The Create Any Trigger Privilege And The Select Privilege On The Table X In The Schema B. Can The Schema A Create A Trigger On The
Yes, schema A can create a trigger on the table X of schema B.
Question 389. Why Should Column Names Be Used In The Insert Statements?
The INSERT statements should have column names so that if any unrelated change to the table is made after the program unit is compiled, automatic recompilation should succeed.
Question 390. In The Time-stamped Checking Mode, Which Value Of The Two Program Units Is Compared To Determine The Dependency Checking?
The LAST_DDL_TIME column of the USER_OBJECTS data dictionary view records the last time a SQL statement was issued to change the structure of the objects. This column is compared to determine if the referenced program unit has been changed since the dependent unit was last compiled.
Question 391. If The Return Type Of A Remote Function Is Changed And The System Is In The Signature Mode Of The Dependency Checking, Then Does It Cause The Local Procedure To Be
Yes, the status of the local procedure will be set to invalid if the RETURN type of the remote function is changed because the signature of the function is changing.
Question 392. What Is A System Privilege?
A system privilege is the right to perform a particular action in the database. Privileges are granted to a schema. They may be granted to a role, which in turn is granted to schemas.
Question 393. What Is The Meaning Of Granting A Privilege To Public?
Granting a privilege to PUBLIC means it is granted to all the schemas in the database that already exist or created in the future.
Question 394. What System Privileges Are Required By An End User?
The end user may not require any system privilege except the CREATE ANY SESSION, which grants them the privilege to log in as an Oracle user.
Question 395. What Is The Difference Between The Create Table And Create Any Table Privileges?
The CREATE TABLE privilege grants the schema to create tables in its own schema; however, CREATE ANY TABLE grants the privilege to create any table owned by any other schema as well.
Question 396. What Is The Meaning Of Granting System Privilege With Admin Option?
Granting system privilege WITH ADMIN OPTION means that the schema can in turn grant the same privilege to any other schema.
Question 397. Suppose A Schema A Grants The Create Any Trigger With Admin Option Privilege To A Schema B And The Schema B Grants The Create Any Trigger Privilege To A Schema C. If The Schema A Revokes This Privilege On The Schema B, Then Does The Privilege On Schema C Be Revoked As Well?
No, the privilege on the schema C will not be revoked automatically; only the privilege on the schema B is revoked.
Question 398. What Is The Advantage Of Using Roles For System Privilege?
Roles are easy to manage and helpful in granting multiple system privileges to schemas. Any privilege can be added to a role or removed from a role. This would automatically affect all the schemas with that role.
Question 399. What Does Create Any Procedure System Privilege Grant To A Schema?
The CREATE ANY PROCEDURE system privilege allows the schema to create any package, procedure, or function within its schema or owned by any other schema.
Question 400. Can A Schema A With The Create Any Procedure Privilege Alter Or Modify A Package X In A Schema B?
No, a schema A with the CREATE ANY PROCEDURE privilege cannot alter or modify a package X in the schema B. The schema must have the ALTER ANY PROCEDURE system privilege for altering or modifying any program unit.
Question 401. What Is The Difference Between The Owner Rights Program Unit And The Invoker Rights Program Unit?
A program unit created as an owner rights program unit executes according to the privileges that the owner of the program unit have at the time of execution; whereas, a program unit created as an invoker rights program unit executes according to the privileges that the invoker of the program unit have at the time of execution.
Question 402. Which Keyword Is Used To Define The Program Unit As An Invoker Rights Program Unit?
The keyword AUTHID CURRENT_USER is used to define a program unit as an invoker rights program unit.
Question 403. Write A Statement To Remove Create Any View Privilege On A Schema Sample_schema ?
The statement to remove the CREATE ANY VIEW privilege on the schema SAMPLE_SCHEMA is as follows:
Question 404. What Are Indirectly Granted Privileges?
Indirectly granted privileges are those privileges that have been granted to a role, which in turn granted to a schema through a role.
Question 405. Which Data Dictionary View Is Used To View Information About All The Privileges That Have Been Made By And To The Current Schema?
The USER_TAB_PRIVS data dictionary view is used to view information about all the privileges that have been made by and to the current schema.
Question 406. Which Data Dictionary View Shows All Directly And Indirectly Granted System Privileges In Effect To A Schema?
The SESSION_PRIVS data dictionary view shows all the directly and indirectly granted system privileges to a schema.
Question 407. Suppose A Package Pack.a With A Procedure Proc_1 Exists In The Schema Sample_one_schema. If The Schema Sample_two_schema Needs To Be Given Privilege To Execute The Procedure Proc_1, Which Privilege Has To Be Granted ?
The statement is as follows:
GRANT EXECUTE ON PACK_A TO SAMPLE_TWO_SCHEMA
The privilege is always granted on the whole package and not on the individual procedures or functions.
Question 408. Which Privilege Is Required To Be Able To Drop A Trigger?
The CREATE TRIGGER privilege is required to create, alter, and drop triggers in a schema.
Question 409. Suppose The Trig_1 Trigger On The Tab_1 Table Exists In The Schema A, And The Schema B Has Privilege To Access The Tab_1 Table. What Additional Privilege Is Required By Schema B To Be Able To Execute The Trig_1 Trigger?
No additional privilege is required to execute the trigger as the schema B already has access privilege to the triggered table, TAB_1.
Question 410. Write A Pl/sql Statement To Show The Privileges On A Procedure Proc_1 Within The Schema A ?
The statement is given as follows:
Question 411. Which Column In The User_sys_privs View Determines Whether The System Privilege Is With Admin Option ?
The ADMIN OPTION column in the USER_SYS_PRIVS view is a YES/NO column that indicates if the privilege was granted WITH ADMIN OPTION.
Question 412. Which Data Dictionary View Will Give The Owner Name Along With The Privileges On A Schema Object?
The USER_TABS_PRIVS data dictionary view gives the list of system and object privileges on the object along with the owner name of the object.
Question 413. Explain The Grant And Revoke Dcl Statements ?
DCL statements control the security of the database. The two statements, GRANT and REVOKE, are used to give permissions to users to access the different schema objects; therefore, enabling security and access control on the database.
Question 414. Suppose A Procedure Proc_1 Refers To A Procedure Proc_2 Within The Pack_1 Package. If The Definition Of The Proc_2 Procedure Is Changed And It Is Recompiled Again, Then
Does The Proc_1 Procedure Is Invalidated ?
If the declaration of the proc_2 procedure is not altered in the package specification, then the proc_1 procedure is not invalidated. However, if the specification of the procedure is altered, then the dependent procedure gets the invalid status.
Question 415. Give The Sql Statement To Drop The Package Specification For Pack_a Without Dropping The Package Body ?
It Is not possible to drop a package specification and leave the package body. If the package is dropped, the whole package is removed from a database.
Question 416. Is It Possible To Create A Package Body And Store It In A Database?
Yes, it is possible to create a package body and store it in the database, although it would be in an invalid state and cannot be executed.
Question 417. Give A Sql Statement To Grant Access On The Procedure Proc_sample In The Schema, Schema_a.
The SQL statement is given as follows:
Question 418. Give A Sql Statement So That The Procedure Proc_1 In The Package Pack_sample Is Granted Access To All The Users In The Database ?
The SQL statement is given as follows:
Question 419. Do Stored Procedures And Functions Within The Same Schema Share The Same Namespace?
Yes, stored procedures and functions within the same schema share the same namespace.
Oracle 10g Interview Questions
Oracle 10g Tutorial
Oracle DBA Interview Questions
Oracle DBA Tutorial
PL/SQL and Informatica Interview Questions
Netezza Interview Questions
Oracle Forms And Reports Interview Questions
Oracle 10g Interview Questions
Oracle Apex Interview Questions
Powerbuilder Interview Questions
Oracle DBA Interview Questions
Pro E Interview Questions
Oracle D2K Interview Questions
PL/SQL and Informatica Interview Questions
Netezza Interview Questions
Oracle Forms And Reports Interview Questions
Oracle Apex Interview Questions
Powerbuilder Interview Questions
Pro E Interview Questions