Top 100+ Pl/sql Interview Questions And Answers
Question 1. What Is Pl/square?
Answer :
PL/SQL is Oracle's Procedural Language extension to SQL. The language consists of object oriented programming techniques inclusive of encapsulation, function overloading, data hiding (all however inheritance), and so, brings latest programming to the Oracle database server and a selection of Oracle tools.
Question 2. Is There A Pl/square Engine In Sql*plus?
Answer :
No. Unlike Oracle Forms, SQL*Plus does no longer have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much extra efficient as SQL statements aren't stripped off and ship to the database for my part.
Oracle 10g Interview Questions
Question 3. Is There A Limit On The Size Of A Pl/square Block?
Answer :
Currently, the most parsed/compiled size of a PL/SQL block is 64K and the most code length is 100K. You can run the following choose statement to query the size of an existing package or process.
SQL> pick out * from dba_object_size where call = 'procedure_name'
Question 4. Can One Read/write Files From Pl/square?
Answer :
Included in Oracle 7.Three is a UTL_FILE bundle which can study and write documents. The directory you propose writing to has to be to your INIT.ORA file (see UTL_FILE_DIR=... Parameter). Before Oracle 7.3 the simplest approach of writing a record turned into to apply DBMS_OUTPUT with the SQL*Plus SPOOL command.
DECLARE
fileHandler UTL_FILE.FILE_TYPE;
BEGIN
fileHandler := UTL_FILE.FOPEN('/domestic/oracle/tmp','myoutput','W');
UTL_FILE.PUTF(fileHandler, 'Value of func1 is %sn',func1(1));
UTL_FILE.FCLOSE(fileHandler);
END;
Oracle 10g Tutorial
Question 5. What Is The Use Of Cascade Constraints?
Answer :
When this clause is used with the DROP command, a figure desk can be dropped even when a baby desk exists.
Oracle DBA Interview Questions
Question 6. Which Command Displays The Sql Command In The Sql Buffer, And Then Executes It?
Answer :
RUN.
Question 7. What Command Is Used To Get Back The Privileges Offered By The Grant Command?
Answer :
REVOKE.
Oracle DBA Tutorial PL/SQL and Informatica Interview Questions
Question eight. What Operator Performs Pattern Matching?
Answer :
LIKE operator.
Question 9. What Operator Tests Column For The Absence Of Data?
Answer :
IS NULL operator.
Netezza Interview Questions
Question 10. Which Function Is Used To Find The Largest Integer Less Than Or Equal To A Specific Value?
Answer :
FLOOR.
Question eleven. Describe The Use Of %rowtype And %type In Pl/square?
Answer :
%ROWTYPE allows you to associate a variable with an entire table row. The %TYPE buddies a variable with a single column type.
Oracle Forms And Reports Interview Questions
Question 12. What Packages (if Any) Has Oracle Provided For Use By Developers?
Answer :
Oracle provides the DBMS_ series of applications. There are many which builders should be aware about which includes DBMS_SQL, DBMS_PIPE, DBMS_TRANSACTION, DBMS_LOCK, DBMS_ALERT, DBMS_OUTPUT, DBMS_JOB, DBMS_UTILITY, DBMS_DDL, UTL_FILE.
Oracle 10g Interview Questions
Question thirteen. Describe The Use Of Pl/square Tables ?
Answer :
PL/SQL tables are scalar arrays that may be referenced through a binary integer. They may be used to maintain values for use in later queries or calculations. In Oracle eight they will be able to be of the %ROWTYPE designation, or RECORD.
Question 14. When Is A Declare Statement Needed ?
Answer :
The DECLARE declaration is used in PL/SQL anonymous blocks consisting of with stand alone, non-saved PL/SQL strategies. It must come first in a PL/SQL stand alone record if it's far used.
Question 15. In What Order Should A Open/fetch/loop Set Of Commands In A Pl/sq. Block Be Implemented If You Use The %notfound Cursor Variable In The Exit When Statement? Why?
Answer :
OPEN then FETCH then LOOP accompanied by means of the exit when. If not targeted on this order will result in the very last go back being performed twice due to the manner the %NOTFOUND is dealt with by means of PL/SQL.
Oracle Apex Interview Questions
Question sixteen. What Are Sqlcode And Sqlerrm And Why Are They Important For Pl/sq. Developers?
Answer :
SQLCODE returns the fee of the mistake variety for the final error encountered. The SQLERRM returns the real blunders message for the ultimate blunders encountered. They can be utilized in exception handling to report, or, keep in an errors log table, the error that passed off within the code. These are mainly beneficial for the WHEN OTHERS exception.
Question 17. How Can You Find Within A Pl/sq. Block, If A Cursor Is Open?
Answer :
Use the %ISOPEN cursor fame variable.
Powerbuilder Interview Questions
Question 18. How Can You Generate Debugging Output From Pl/square?
Answer :
Use the DBMS_OUTPUT package deal. Another possible approach is to just use the SHOW ERROR command, however this simplest indicates errors. The DBMS_OUTPUT package deal can be used to reveal intermediate consequences from loops and the reputation of variables because the process is completed. The new bundle UTL_FILE can also be used.
Oracle DBA Interview Questions
Question 19. What Are The Types Of Triggers?
Answer :
There are 12 forms of triggers in PL/SQL that encompass combos 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 and many others.
Question 20. Explain The Difference Between A Hot Backup And A Cold Backup And The Benefits Associated With Each ?
Answer :
A warm backup is basically taking a backup of the database even as it is still up and strolling and it need to be in archive log mode. A bloodless backup is taking a backup of the database whilst it is close down and does no longer require being in archive log mode. The gain of taking a warm backup is that the database is still to be had for use while the backup is going on and you may recover the database to any point in time. The advantage of taking a cold backup is that it is usually less complicated to manage the backup and recuperation system. In addition, considering the fact that you take bloodless backups the database does now not require being in archive log mode and hence there can be a mild overall performance benefit as the database is not reducing archive logs to disk.
Pro E Interview Questions
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?
Answer :
I could create a textual content primarily based backup manipulate file, stipulating where on disk all the statistics files wherein after which issue the get better command with the the usage of backup manipulate report clause.
Question 22. How Do You Switch From An Init.Ora File To A Spfile?
Answer :
Issue the create spfile from pfile command.
Question 23. Explain The Difference Between A Data Block, An Extent And A Segment ?
Answer :
A information block is the smallest unit of logical storage for a database object. As gadgets grow they take chunks of extra storage which can be composed of contiguous data blocks. These groupings of contiguous information blocks are known as extents. All the extents that an object takes when grouped together are taken into consideration the section of the database object.
Oracle D2K Interview Questions
Question 24. Where Would You Look For Errors From The Database Engine?
Answer :
In the alert log.
PL/SQL and Informatica Interview Questions
Question 25. Compare And Contrast Truncate And Delete For A Table ?
Answer :
Both the truncate and delete command have the favored outcome of getting rid of all the rows in a table. The difference among 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, to be able to produce a rollback and thus take longer to finish.
Question 26. Give The Reasoning Behind Using An Index ?
Answer :
Faster access to statistics blocks in a desk.
Question 27. Give The Two Types Of Tables Involved In Producing A Star Schema And The Type Of Data They Hold ?
Answer :
Fact tables and size tables. A reality desk carries measurements even as dimension tables will include statistics on the way to help describe the truth tables.
Netezza Interview Questions
Question 28. What Type Of Index Should You Use On A Fact Table ?
Answer :
A Bitmap index.
Question 29. Give Two Examples Of Referential Integrity Constraints ?
Answer :
primary key and a overseas 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?
Answer :
Disable the overseas key constraint to the discern, drop the table, re-create the desk, enable the overseas key constraint.
Question 31. How Would You Determine The Time Zone Under Which A Database Was Operating?
Answer :
choose DBTIMEZONE from twin;
Question 32. Explain The Use Of Setting Global_names Equal To True ?
Answer :
Setting GLOBAL_NAMES dictates how you may hook up with 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're linking.
Question 33. What Command Would You Use To Encrypt A Pl/sq. Application?
Answer :
WRAP
Oracle Forms And Reports Interview Questions
Question 34. Explain The Difference Between A Function, Procedure And Package ?
Answer :
A function and system are the identical in that they're intended to be a collection of PL/SQL code that includes a unmarried challenge. While a method does no longer must return any values to the calling utility, a function will go back a single value. A package however is a collection of functions and methods which might be grouped collectively primarily based on their commonality to a commercial enterprise feature or utility.
Question 35. Explain The Use Of Table Functions ?
Answer :
Table functions are designed to go back a fixed of rows through PL/SQL logic however are intended for use as a ordinary desk or view in a SQL announcement. They are also used to pipeline information in an ETL method.
Question 36. Name Three Advisory Statistics You Can Collect ?
Answer :
Buffer Cache Advice, Segment Level Statistics, & Timed Statistics.
Oracle Apex Interview Questions
Question 37. Where In The Oracle Directory Tree Structure Are Audit Traces Placed?
Answer :
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer.
Question 38. Explain Materialized Views And How They Are Used ?
Answer :
Materialized views are objects that are reduced sets of statistics which have been summarized, grouped, or aggregated from base tables. They are typically utilized in facts warehouse or selection assist systems.
Question 39. When A User Process Fails, What Background Process Cleans Up After It?
Answer :
PMON
Question 40. What Background Process Refreshes Materialized Views?
Answer :
The Job Queue Processes.
Powerbuilder Interview Questions
Question 41. How Would You Determine What Sessions Are Connected And What Resources They Are Waiting For?
Answer :
Use of V$SESSION and V$SESSION_WAIT
Question 42. How Would You Force A Log Switch ?
Answer :
ALTER SYSTEM SWITCH LOGFILE;
Pro E Interview Questions
Question forty three. Give Two Methods You Could Use To Determine What Ddl Changes Have Been Made ?
Answer :
You could use Log miner or Streams.
Question 44. What Does Coalescing A Table Space Do?
Answer :
Coalescing is best legitimate for dictionary-managed desk spaces and de-fragments area by way of combining neighboring unfastened extents into large unmarried extents.
Question forty five. What Is The Difference Between A Temporary Tablespace And A Permanent Tablespace?
Answer :
A brief tablespace is used for temporary objects which include type structures at the same time as permanent tablespaces are used to store the ones gadgets intended for use as the proper items of the database.
Question forty six. Name A Tablespace Automatically Created When You Create A Database ?
Answer :
The SYSTEM tablespace.
Question forty seven. When Creating A User, What Permissions Must You Grants To Allow Them To Connect To The Database?
Answer :
Grant the CONNECT to the consumer.
Question 48. How Do You Add A Data File To A Tablespace?
Answer :
ALTER TABLESPACE ADD DATAFILE SIZE ;
Question forty nine. How Do You Resize A Data File?
Answer :
ALTER DATABASE DATAFILE RESIZE ;
Question 50. What View Would You Use To Look At The Size Of A Data File?
Answer :
DBA_DATA_FILES
Question fifty one. What View Would You Use To Determine Free Space In A Tablespace?
Answer :
DBA_FREE_SPACE
Question 52. How Would You Determine Who Has Added A Row To A Table?
Answer :
Turn on high-quality grain auditing for the desk.
Question fifty three. How Can You Rebuild An Index?
Answer :
ALTER INDEX REBUILD;
Question fifty four. Explain What Partitioning Is And What Its Benefit Is ?
Answer :
Partitioning is a way of taking massive tables and indexes and splitting them into smaller, greater practicable portions.
Question 55. You Have Just Compiled A Pl/square Package But Got Errors, How Would You View The Errors?
Answer :
SHOW ERRORS;
Question fifty six. How Can You Gather Statistics On A Table?
Answer :
The ANALYZE command.
Question 57. How Can You Enable A Trace For A Session?
Answer :
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?
Answer :
These two Oracle utilities are used for loading statistics into the database. The difference is that the import software relies on the information being produced through another Oracle utility EXPORT at the same time as the SQL*Loader application permits information to be loaded that has been produced by other utilities from exclusive facts assets just so lengthy because it conforms to ASCII formatted or delimited files.
Question fifty nine. Name Two Files Used For Network Connection To A Database ?
Answer :
TNSNAMES.ORA and SQLNET.ORA
Question 60. Explain The Difference Between Archivelog Mode And Noarchivelog Mode And The Benefits And Disadvantages To Each ?
Answer :
ARCHIVELOG mode is a mode that you could placed the database in for growing a backup of all transactions which have occurred inside the database so you can get better to any factor in time
NOARCHIVELOG mode is largely the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any factor in time. NOARCHIVELOG mode does have the advantage of no longer having to write down transactions to an archive log and as a result increases the overall performance of the database barely.
Question 61. What Command Would You Use To Create A Backup Control File?
Answer :
Alter database backup manage file to hint.
Question 62. Give The Stages Of Instance Startup To A Usable State Where Normal Users May Access It?
Answer :
STARTUP NOMOUNT - Instance startup
STARTUP MOUNT - The database is established
STARTUP OPEN - The database is opened
Question sixty three. What Column Differentiates The V$ Views To The Gv$ Views And How?
Answer :
The INST_ID column which indicates the example in a RAC surroundings the data got here from.
Question 64. How Would You Go About Increasing The Buffer Cache Hit Ratio?
Answer :
Use the buffer cache advisory over a given workload after which query the v$db_cache_advice desk. If a trade changed into essential then I would use the adjust gadget set db_cache_size command.
Question sixty five. Explain An Ora-01555 ?
Answer :
You get this error when you get a photograph too antique inside rollback. It can normally be solved by way of increasing the undo retention or growing the size of rollbacks. You must also look at the common sense involved inside the utility getting the mistake message.
Question 66. Explain The Difference Between $oracle_home And $oracle_base ?
Answer :
ORACLE_BASE is the foundation listing for oracle. ORACLE_HOME placed below ORACLE_BASE is wherein the oracle products live.
Question sixty seven. What Is A Mutating Table Error And How Can You Get Around It?
Answer :
This happens with triggers. It happens because the cause is trying to update a row it's far presently the usage of. The traditional restore involves both use of views or transient tables so the database is selecting from one whilst updating the alternative.
Question sixty eight. How Do You Generate File Output From Sql?
Answer :
By use of the SPOOL command
Question sixty nine. How Do You Prevent Oracle From Giving You Informational Messages During And After A Sql Statement Execution?
Answer :
The SET alternatives FEEDBACK and VERIFY may be set to OFF.
Question 70. How Do You Prevent Output From Coming To The Screen?
Answer :
The SET choice TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This alternative can be shortened to TERM.
Question seventy one. What Is The Difference Between Sql And Pl/sq.?
Answer :
Structured Query Language (SQL) is a non-procedural language that interacts with the database, and is used for database manipulation the usage of the Data Definition Language (DDL) and Data Manipulation Language (DML) statements. Control statements can not be used in SQL, that's compiled and finished assertion through declaration on the runtime (overdue-binding).
PL/SQL is a programming language that allows the use of Cursor Control statements and Transaction Control statements, such as if...Then...Else. It integrates with SQL features and statements to have interaction and control with the database. Unlike SQL, PL/SQL statements are compiled and processed as a block of code into the system-readable code, which is accomplished at runtime (early binding); and consequently, improves the performance.
Question seventy two. What Are The Advantages Of Pl/square?
Answer :
PL/SQL is a transaction processing procedural language that has the subsequent advantages:
Integration with database —PL/SQL supports SQL statements, hence permitting integration among procedural language capabilities and the database era.
Better overall performance —SQL statements are grouped inside a PL/SQL block and sent together to database for parsing; consequently, it reduces the network traffic and will increase the performance.
Higher productivity —-PL/SQL facilitates the bundle reusable code within the well-defined modular blocks; and consequently, will increase the productivity.
Portability —Programs programs and libraries written in PL/SQL can be reused in exclusive environments.
Question seventy three. What Are The Mandatory Keywords In A Pl/sq. Program Block?
Answer :
The BEGIN and END key phrases are obligatory for any PL/SQL block.
Question seventy four. Can Arrays Be Used Within A Pl/sq. Program Block?
Answer :
Yes, arrays may be described and utilized in a PL/SQL block using the composite datatypes, including RECORD, TABLE, NESTED TABLE, and VARRAY.
Question 75. Can A Variable Be Assigned A Value From Database? If Yes, Then How?
Answer :
Yes, a variable can be assigned a fee from a database. You can fetch the price from the database and assign it to the variable using the into key-word, as proven inside the following code:
pick emp_sal * 0.20 into var_increment from
t_emolovee where emo_no = emo_id
Question seventy six. Does A Not Null Constraint Of A Database Column Apply To %kind Variables ?
Answer :
No, a NOT NULL constraint does no longer practice to variables declared using the %TYPE attribute. These variables can be assigned a null fee in a PL/SQL block, as proven within the following code snippet:
DECLARE
var_emp_code t_employee.Emp_codeflPE;
BEGIN
var_emp_code NULL;
Question seventy seven. Can Sql Group Functions Be Used Within The Procedural Statements?
Answer :
The SQL group capabilities, which include AVG, MIN, MAX, COUNT, SUM, STDDEV, and VARIANCE, cannot be at once used within PL/SQL. However, they can be embedded within SQL statements after which may be utilized inside the procedural statements.
Question 78. What Is The Maximum Length Of An Identifier That Can Be Used?
Answer :
The maximum period of an identifier in a PL/SQL block is 30.
Question seventy nine. What Is The Equivalent Of Decode Function In The Procedural Statements?
Answer :
The if...Then...Else statement is the equivalent of DECODE feature inside the procedural statements.
Question eighty. Can Ddl Commands Be Used Within A Procedure? If So, Then How?
Answer :
DDL commands, such as create, regulate, revoke, and grant, can not be used directly in the procedural language block. They can most effective be referred to as the use of 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?
Answer :
Anonymous blocks, as the name suggests, are PL/SQL blocks that aren't given any call and can't be stored within the database. These blocks can name every other blocks however they can not be referred to as by way of every other block, as they do now not have a call. An anonymous block is executed by both storing the block code in a record or writing the block code at the SQL spark off.
Question 82. What Is A Bind Variable And How Is It Used?
Answer :
A bind variable is a variable inside a SQL announcement that has to get replaced by means of a valid price at the a success execution of the announcement. This cost can be referenced into the PL/SQL block. Bind variable is constantly preceded via a colon (:), as proven inside the following code:
Declare
y_emp_age wide variety;
Begin
Select emp-age into :v_empLage from t_employee
wherein 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?
Answer :
Any variable declared inside a block is neighborhood to the block and global to the nested blocks. The variables declared inside the nested blocks are unknown to the outer blocks. They override the references to any outer declared variables with the equal name until they're used with the block label name.
Question eighty four. What Is The Difference Between Implicit Cursor And Explicit Cursor?
Answer :
A cursor is a SQL reminiscence paintings area. Any SQL declaration always uses a cursor for the execution of statements and fetching of consequences. This is implicitly defined and is called implicit cursor.
When a programmer defines a cursor for fetching and parsing outcomes of a SQL query, which returns a couple of row, it is known as an explicit cursor.
Question 85. Why Does Sqlpercentisopen Always Returns The False Value?
Answer :
SQL is the call of the implicit cursor, that's usually opened while the database executes the SQL statement. SQLpercentISOPEN usually evaluates to FALSE as ORACLE closes the implicit cursor as quickly as it executes the question.
Question 86. What Are Open, Fetch, And Close Cursor Commands? Are They Used With The Implicit Or Explicit Cursor?
Answer :
These instructions are used with explicit cursors. First, the cursor is opened to make it active the usage of the OPEN command. Then, the FETCH command is used to retrieve the rows from the lively cursor. After all of the rows are retrieved, the cursor is closed the use of the CLOSE command that can once more be reopened. Any other operation on a closed cursor returns the predefined exception, INVALID_CURSOR. However, those commands can't be used to govern the SQL implicit cursors.
Question 87. What Are Transaction Control Statements? Can They Be Used Within The Pl/sq. Block?
Answer :
Transaction Control statements are the COMMIT and REVOKE commands that control the good judgment of transactions within a database. These statements are valid inside a PL/SQL block. The COMMIT command terminates the active transaction and makes the modifications permanent to the database. The ROLLBACK command terminates the energetic transaction but cancels any changes that were made to the database.
Question 88. What Is A Savepoint Command?
Answer :
The SAVEPOINT command is used to set a point inside a transaction to which you may rollback. This command allows in cancelling the quantities of the cutting-edge transaction. Using ROLLBACK with the SAVEPOINT TO clause, a transaction can be undone in parts in preference to rolling returned the entire transaction.
Question 89. What Are Pseudo-columns? How Can You Use Pseudo Columns In Procedural Statements?
Answer :
Pseudo columns are not actual columns in a desk however they behave like columns. They are utilized in SQL statements to retrieve unique statistics. PL/SQL acknowledges pseudo-columns as part of SQL statements however they cannot be used immediately in a procedural language. The following pseudo-columns are used in SQL:
ROWID
ROWNUM
LEVEL
CURRVAL
NEXTVAL
Question ninety. How Can You Check If An Update Statement In Pl/square Is Successfully Executed?
Answer :
You can use the SQLpercentNOTFOUND characteristic to test if the UPDATE statement has efficiently updated any rows or now not. This attribute returns the TRUE cost if the remaining accomplished SQL statement has not affected any rows.
Question ninety one. What Is A Record Datatype?
Answer :
A RECORD is a group of information items, which vary from each in different in datatype, however are logically related. For example, an worker report with name, age, deal with, and smartphone quantity. These are all a part of worker record but have different datatypes.
In PL/SQL, the report kind 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 range(2),
emp_address varchar2(one hundred).
Emp_tel_no variety(10));
emp_record emp_record_type;
We have defined the document kind via the call emp_record_type after which declared a report, emp_record, of that type.
Question 92. What Is The Function Of Open Cursor Command?
Answer :
The following operations arise when the OPEN cursor command is accomplished for a cursor:
Memory place for processing is allotted
The SELECT assertion is parsed
The enter variables are assigned the values by way of acquiring the memory addresses
The active set of rows that fulfill the pick criteria are recognized
The pointer is ready just earlier than the first row of the lively set
Question 93. What Is The Advantage Of Using The %rowtype Datatype?
Answer :
The benefits of the usage of the %ROWTYPE datatype to declare variables are given as follows:
It is useful to retrieve a whole row from a table. If you do now not use the %ROWTYPE datatype, then you need to declare variables for each column separately.
It may be used even if datatype of the table columns isn't always regarded
It guarantees that datatype of the variable adjustments dynamically if the underlying table is altered.
Question 94. How Can Explicit Cursors Be Used To Fetch Multiple Rows?
Answer :
A loop have to be described to fetch a couple of rows from an explicit cursor. It will fetch one row from the energetic cursor set on every generation of the loop, as proven in the following code:
Declare
v_emp_code
t_employees.Emp_code%TYPE;
v_emp_name t_employees.Emp_namepercentTYPE;
v_emp_salary
t_employees.Emp_salary%TWE:
v_increment variety (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_cursorpercentNOTFOuND IS NULL;
V_increment := v_emp_salary • zero.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?
Answer :
Yes, It is viable to outline a document type based on the chosen columns of an express cursor. This helps in retrieving the complete row together into the defined record and in addition processing. The values of the row are at once loaded into the fields of the report.
Question ninety six. What Is A Cursor For Loop And How Does It Differ From An Explicit Cursor?
Answer :
Cursor FOR loop is a shortcut to apply specific cursors. It does no longer require specific commencing, fetching, and remaining of the cursor. In different words, the cursor FOR loop implicitly opens the cursor, fetches the record, and closes the cursor. In the cursor FOR loop, the cursor is described and its attributes are checked in the processing of the FOR loop. The statistics are also implicitly described within the loop, as shown within the following code:
Declare
v_increment range(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 • zero.20
D13MS_OUTPUT.PUTLINE
(emp_FREOFthent_COOR 11 'Ilemp_record.Emp_name II " II v_increment);
END LOOP;
END;
It is also viable to use a cursor FOR loop with a subquery, in which the cursor call isn't always described. In this situation, the attributes of the cursor can't be checked as no explicit cursor call is described.
Question 97. What Is Select For Update And How Can It Be Used In A Cursor?
Answer :
The SELECT FOR UPDATE clause is used to lock rows in a table whilst an replace or delete transaction is finished on the lively set. It selects the rows of the table that ought to be modified and locks each row in the result set. This prevents every other person from accessing the equal set of information whilst amendment is being finished on the table. You ought to add the NOWAIT clause to the SELECT FOR UPDATE statement to save you the method from ready indefinitely in case the lock can't be acquired. If the NOWAIT clause is used, the manipulate will return to the program in case the asked rows had been locked by using another person.
Question ninety eight. How Is It Possible To Fetch A Current Row From A Cursor Without Using Rowid?
Answer :
The WHERE CURRENT OF clause is used to reference the current row of an energetic specific cursor. When this clause is used, ROWID isn't needed to get admission to the modern-day row for adjustments.
Question ninety nine. Which Datatypes Can Be Used With A Record Type?
Answer :
The datatype of a REORD Type can be a NUMBER, VRCHAR2, %TYPE, or %ROWTYPE; however, it can't be a'REF CURSOR.
Question a hundred. Is It Possible To Label A Loop? If Yes, Then What Is Its Benefit?
Answer :
Yes, It is feasible to label a loop just like a PL/SQL block. Loop labels are undeclared identifiers enclosed inside the double attitude brackets. This helps in higher readability in case of nested loops. In addition, this enables in using the EXIT assertion with the loop label in the nested loops. Label of a loop must seem earlier than the LOOP announcement, as proven in the following code snippet:
«first»
LOOP
-----
«2d»
LOOP
-------
EXIT second WHEN ------
END LOOP 2d;
END LOOP first;
Question one zero one. In A For-loop, Can The Counter Value Be Assigned A Value Within The Loop?
Answer :
The counter of a FOR-LOOP can only be used as a consistent in the loop. It can't be assigned a value within the loop, as that would exchange the bounds of the loop, that is logically no longer feasible.
Question 102. Is It Possible To Assign A Dynamic Value To A Loop Counter?
Answer :
Yes, it's miles feasible to assign a dynamic value to the loop counter at the time of execution, as shown inside the following code snippet:
In the preceding code snippet, the worker 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?
Answer :
If the top bound of a FOR-LOOP is assigned a price smaller than the decrease bound of the loop, then the statements in the loop are not completed and the control is passed to the announcement after the loop.
Question 104. Can Index-through Table Be Stored In A Database? If Not, Then Which Type Of Collection Can Be Used To Store In A Database?
Answer :
No, Index-By table cannot be stored in a database. The nested tables and VARRAYs series types may be used to shop in database.
Question one hundred and five. What Is The Difference Between Nested Tables And Varrays?
Answer :
Both nested tables and VARRAYs are collection types that may be stored in the database. They differ in two houses, bounding and sparsity. Bounding refers to the limit on the number of elements that a collection can have; whereas, sparsity manner whether there may be gaps inside the subscripts or now not.
Nested tables aren't bounded and can be sparse; while, VARRAYs are bounded and can't be sparse. It way that VARRAYs have a restriction at the quantity of factors and are bounded; while, nested tables does no longer have a limit at the variety of factors and are unbounded. Nested tables can turn out to be sparse if elements are deleted; whereas, VARRAYs can by no means become sparse.
Question 106. What Is The Difference In The Usage Of Select Into And Fetch?
Answer :
The SELECT INTO clause should be used when the result of the query is thought to return a unmarried row. When a couple of row is to be again by using the SQL question, then the FETCH clause should be used. If the FETCH clause isn't used and the query returns multiple row, then the runtime mistakes is generated within the code.
Question 107. How Can Two Pl/square Records Be Compared?
Answer :
Two facts can be compared through the use of the equality (contrast) operator for every field of the file. However, the complete file cannot be in comparison as a whole.
Question 108. Which Datatype Is Not Allowed In The Definition Of Pl/square Record?
Answer :
A series type can't be used for defining the datatype of fields; best scalar datatype may be used for a PL/SQL document.
Question 109. What Is The Value Of Sqlpercentdetermined Attribute After And Before An Insert Statement Is Executed?
Answer :
The value of SQLpercentF0UND characteristic could be NULL earlier than an INSERT assertion is accomplished and TRUE after the a success insertion into a table.
Question a hundred and ten. What Is A Constructor And Why Is It Used?
Answer :
A constructor is a special built-in characteristic this is used to initialize nested tables and VARRAYs. Constructor has the same name as the collection and can take any quantity of arguments, as proven within the following code:
Question 111. How Are Elements Added To An Index-through Table?
Answer :
Index-By tables are instantiated routinely. 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?
Answer :
No, the IF-THEN-ELSIF block can have best one ELSE clause at the give up of the it, that's completed 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?
Answer :
The fee of %NOTFOUND characteristic will be NULL after the cursor is opened however before the FETCH announcement is completed. After the FETCH declaration, the fee of %NOTFOUND characteristic may be TRUE OR FALSE depending at the result of the FETCH announcement.
Question 114. Is It Possible To Define A Default Value For A Record Field? If Yes, Then How?
Answer :
Yes, it's far viable to define a default value for a file area, as shown within the following code:
Question a hundred and fifteen. What Are Implicitly Defined Records?
Answer :
Implicitly described records are those that do not have the want to describe every area separately in the document definition. The shape of the document isn't always described the use of the TYPE declaration; as a substitute, the %ROWTYPE characteristic is used to define the implicit file to have the identical shape because the database file. Following code suggests the announcement of the implicit described statistics:
Question 116. What Is The Advantage Of Defining Implicit Records?
Answer :
Implicit statistics are useful as there's no hard-code description wanted. As they may be based on a database table file, any change within the database desk file will be mechanically meditated in the document.
Question 117. How Can An Implicitly Defined Record Be Populated?
Answer :
The implicitly defined file can be populated area with the aid of discipline or the whole document may be populated in one statement, as proven within the following code:
Question 118. Can A Record Definition Have Another Record As Datatype?
Answer :
Yes, a report definition may have scalar datatypes or every other document as a datatype.
Question 119. Is It Possible To Define A Not Null Field In A Record?
Answer :
Yes, it's far feasible to outline a NOT NULL area in a file which will constantly require a value.
Question a hundred and twenty. Is It Possible To Define A Constant Value In A Record?
Answer :
No, a CONSTANT price isn't allowed in a document.
Question 121. Is It Possible To Copy Contents Of One Record To Another?
Answer :
Yes, the contents of 1 record can be copied to some other by copying both each field the usage of the task operator or entire file the use of the task operator.
Question 122. Do Records Have Methods?
Answer :
No, facts do no longer have techniques.
Question 123. Can A Varray Be Used In A Record Definition?
Answer :
No, VARRAY is a group and collections can't be used for record definition.
Question 124. Can An Uninitialized Nested Table Be Referenced?
Answer :
No, an uninitialized nested table cannot be referenced. It can best be checked for nullity.
Question one hundred twenty five. What Is The Difference Between Initialization Of Index-by using Table And Nested Table?
Answer :
Index-By table is initialized mechanically with the aid of referencing an element inside the collection; whereas, nested tables have to be initialized using a built-in feature referred to as constructor.
Question 126. Do Collections Have Methods? If Yes, Give An Example ?
Answer :
Yes, collections have built-in methods, which are used to look at and control the attributes of the collection, as proven in the following code:
Question 127. Can Collections Be Compared With Boolean Operators?
Answer :
No, collections cannot be as compared with Boolean operators.
Question 128. Can The Exists Method Be Used For Index-by way of Table?
Answer :
Yes, the EXISTS technique can be used for all series types. It returns a Boolean cost, TRUE, if the gathering detail is initialized and FALSE, if the gathering element is not initialized.
Question 129. What Will The Result Of The Next() Method, If X Is The Last Element Of The Collection?
Answer :
The NEXT (<x>) method will go back NULL if the last detail of the collection is reached.
Question a hundred thirty. What Is The Difference In Accessing Objects Of Lob And Long Datatypes?
Answer :
The LOB datatype helps random get admission to; while, the LONG datatype helps only sequential get entry to of items.
Question 131. What Is The Difference Between Lob Objects Stored In-line And Out-of-line?
Answer :
The LOB gadgets can be saved in-line or out-of-line. The in-line storage means that objects are stored along side the row; whereas, the out-of-line garage way that objects are saved outside the row.
Question 132. Which Package Is Used To Manipulate Lob Objects?
Answer :
Oracle-provided bundle called DBMS.J_OB is used to manipulate the LOB gadgets.
Question 133. What Are Lob Locators?
Answer :
LOB locators are values, which specify the area of the large item.
Question 134. What Are The System Privileges That Are Required By A Schema Owner (user) To Create A Trigger On A Table?
Answer :
A consumer ought to be able to regulate a table to create a cause at the desk. The user have to personal the table and either have the ALTER TABLE privilege on that desk or have the ALTER ANY TABLE gadget privilege. In addition, the user ought to have the CREATE TRIGGER system privilege. User should have the CREATE ANY TRIGGER device privilege for you to create triggers in another user account or schema.
A database-level event trigger may be created if the person has the ADMINISTER DATABASE TRIGGER gadget privilege.
Question 135. What Are The Different Types Of Triggers?
Answer :
There are following two forms of triggers:
Database triggers are accomplished implicitly on every occasion a Data Manipulation Language (DML) announcement is accomplished on a database table or a Data Definition Language (DDL) statement, such as CREATE OR ALTER, is carried out at the database. They can also be finished when a consumer or database event takes place, including a person logs on or a database is shutdown.
Application triggers are performed implicitly whenever a DML event takes vicinity within an utility, which include WHEN_NEW_FORM_INSTANCE inside the Oracle Forms software.
Question 136. Which Data Manipulation Operations Raise A Trigger?
Answer :
Data manipulation operations together with INSERT, UPDATE, and DELETE on a desk or a view are the primary triggering occasions that motive a trigger to execute.
Question 137. Can Triggers Reference Other Tables Through A Role?
Answer :
Triggers may also require referencing other tables even as triggering an occasion. Therefore, privileges required to reference other tables have to be granted without delay to the owner of the trigger, as the trigger can't reference other person tables through roles.
For instance, do not forget a trigger, that is used for any audit table. To insert a row within the audit desk, the owner of the cause have to have the insert privilege on that audit table.
Question 138. What Is The Difference Between Row-level Triggers And Statement-level Triggers?
Answer :
Row-stage triggers are activated once for each row, which is modified with the aid of a DML statement. For instance, don't forget a desk, STUDENT_MARKS_AUDIT, used for the auditing of the STUDENT_MARKS desk. A row-degree trigger might insert a row within the STUDENT_MARKS_AUDIT table every time any price is modified in any row of the STUDENT_MARKS desk.
Statement-degree triggers are accomplished best as soon as for every DML statement. For example, if there may be an INSERT announcement, which inserts one hundred rows in a STUDENT_MARKS table, then the declaration-degree cause would be accomplished handiest once on that table; while, a row-level trigger would be performed one hundred instances.
Row-degree triggers are generally used for statistics auditing applications; whereas, assertion-level triggers are used to put into effect protection features for the types of actions that can be done on a desk.
Question 139. Is It Possible To Combine Several Triggering Events Into One Trigger?
Answer :
Yes, it is feasible to mix diverse triggering events and create one cause. Special conditional predicates inclusive of INSERTING, UPDATING, and DELETING are used in the trigger frame to represent the triggering occasions, as shown inside the following code snippet:
Question a hundred and forty. How Can Triggers Be Used For The Table Auditing?
Answer :
Triggers can be used to tune values for information operations on tables. This is achieved the usage of the vintage and new qualifiers in the trigger code. These clauses assist preserve song of the facts this is being inserted, up to date, or deleted within the table; and therefore, facilitate in application auditing of DML statements. The audit path can be written to a user-described desk and audit facts can be generated for each row-level and assertion-stage triggers.
Question 141. What Are Instead Of Triggers?
Answer :
The INSTEAD OF triggers are utilized in association with views. The popular table-based triggers cannot be used by views. These triggers inform the database of what movements are to be accomplished as opposed to the moves that invoked the trigger. Therefore, the INSTEAD OF triggers may be used to replace the underlying tables, that are a part of the views. They may be used on both relational perspectives and object perspectives. The INSTEAD OF triggers can most effective be described as row-degree triggers and now not as assertion-level triggers.
Question 142. What Is The Difference Between Database Trigger And Stored Procedure?
Answer :
The predominant distinction between database trigger and saved procedure is that the trigger is invoked implicitly and saved method is invoked explicitly.
Transaction Control statements, which includes COMMIT, ROLLBACK, and SAVEPOINT, aren't allowed inside the frame of a trigger; while, those statements can be covered in a saved technique.
Question 143. How Can The Performance Of A Trigger Be Improved?
Answer :
The performance of a trigger can be improved with the aid of using column names in conjunction with the UPDATE clause in the cause. This will make the trigger hearth when that particular column is up to date and therefore, prevents pointless motion of cause whilst other columns are being up to date.
Question a hundred and forty four. What Are The Events On Which A Database Trigger Can Be Based?
Answer :
Database triggers are primarily based on machine events and may be defined at database or schema degree. The diverse activities on which a database cause can be based totally are given as follows:
Data definition assertion on a database or schema item
Logging off or on of a particular consumer
Database shutdown or startup
On any unique errors that happens
Question a hundred forty five. What Is A Call Statement? Explain With An Example ?
Answer :
A CALL statement inside a trigger allows you to name a stored method in the cause instead of writing the Procedural Language/Structured Query Language (PL/SQL) code in it, The manner can be in PL/SQL, C, or Java language. Following is an instance of the CALL assertion:
Question 146. What Is A Mutating Table?
Answer :
A mutating table is a desk, that is within the state of transition. In other phrases, it's miles a table, that's being updated on the time of triggering action. If the cause code queries this table, then a mutating desk errors happens, which reasons the trigger to view the inconsistent statistics.
Question 147. What Are Schema-level Triggers?
Answer :
Schema-degree triggers are created on schema-degree operations, consisting of create desk, adjust table, drop table, rename, truncate, and revoke. These triggers save you DDL statements, offer protection, and monitor the DDL operations.
Question 148. What Is A Database Event Trigger?
Answer :
Trigger that is done when a database occasion, including startup, shutdown, or mistakes, takes place is called a database occasion cause. It can be used to reference the attributes of the event and carry out machine maintenance functions at once after the database startup.
Question 149. In What Condition Is It Good To Disable A Trigger?
Answer :
It is good to disable triggers in the course of facts load operations. This improves the overall performance of the facts loading activities. The statistics change and manipulation that the trigger could have finished has to be finished man
