PL/SQL Interview Questions:

Important PL/SQL Questions and Answers Set-1 :

1. What is PL/SQL and what is it used for?
2. What is the difference between SQL and PL/SQL?
3. Should one use PL/SQL or Java to code procedures and triggers?
4. How can one see if somebody modified any code?
5. How can one search PL/SQL code for a string/ key value?
6. How does one keep a history of PL/SQL code changes?
7. How can I protect my PL/SQL source code?
8. How can I know which stored PL/SQL code is wrapped?
9. Can one print to the screen from PL/SQL?
10. Can one read/write files from PL/SQL?
11. Can one call DDL statements from PL/SQL?
12. Can one use dynamic SQL statements from PL/SQL?
13. What is the difference between %TYPE and %ROWTYPE?
14. How does one get the value of a sequence into a PL/SQL variable?
15. Can one execute an operating system command from PL/SQL?
16. How does one loop through tables in PL/SQL?
17. How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?
18. I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?
19. What is a mutating and constraining table?
20. Can one pass an object/table as an argument to a remote procedure?
21. What is the difference between stored procedures and functions?
22. Is there a PL/SQL Engine in SQL*Plus?
23. Is there a limit on the size of a PL/SQL block?
24. What are the PL/SQL compiler limits for block, record, subquery and label nesting?
25. Can one COMMIT/ ROLLBACK from within a trigger?
26. What is ACID property of transaction.

Answers:

1: What is PL/SQL and what is it used for?

SQL is a declarative language that allows database programmers to write a SQL declaration and hand it to

the database for execution. As such, SQL cannot be used to execute procedural code with conditional,

iterative and sequential statements. To overcome this limitation, PL/SQL was created.

PL/SQL is Oracle’s Procedural Language extension to SQL. PL/SQL’s language syntax, structure and data

types are similar to that of .Some of the statements provided by PL/SQL:

Conditional Control Statements:

• IF … THEN … ELSIF … THEN … ELSE … END IF;
• CASE … WHEN … THEN … ELSE … END CASE;

Iterative Statements:

• LOOP … END LOOP;
• WHILE … LOOP … END LOOP; 
• FOR … IN [REVERSE] … LOOP … END LOOP;

Sequential Control Statements:

• GOTO …;
• NULL;

The PL/SQL language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance).

PL/SQL is commonly used to write data-centric programs to manipulate data in an Oracle database.

Example PL/SQL blocks:

/* Remember to SET SERVEROUTPUT ON to see the output */

BEGIN

DBMS_OUTPUT.PUT_LINE(‘Hello World’);

END;

/

BEGIN

— A PL/SQL cursor

FOR cursor1 IN (SELECT * FROM table1) — This is an embedded SQL statement

LOOP

DBMS_OUTPUT.PUT_LINE(‘Column 1 = ‘ || cursor1.column1 ||

‘, Column 2 = ‘ || cursor1.column2);

END LOOP;

END;

/

2: What is the difference between SQL and PL/SQL?

Both SQL and PL/SQL are languages used to access data within Oracle databases. SQL is a limited language that allows you to directly interact with the database. You can write queries (SELECT), manipulate objects (DDL) and data (DML) with SQL. However, SQL doesn’t include all the things that normal programming languages have, such as loops and IF…THEN…ELSE statements.

PL/SQL is a normal programming language that includes all the features of most other programming languages. But, it has one thing that other programming languages don’t have: the ability to easily integrate with SQL.

Some of the differences:

• SQL is executed one statement at a time. PL/SQL is executed as a block of code.
• SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tell the database how to do things (procedural).
• SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages.
• You can embed SQL in a PL/SQL program, but you cannot embed PL/SQL within a SQL statement.
  
  
  

3: Should one use PL/SQL or Java to code procedures and triggers?

Both PL/SQL and Java can be used to create Oracle stored procedures and triggers. This often leads to questions like “Which of the two is the best?” and “Will Oracle ever desupport PL/SQL in favour of Java?”. Many Oracle applications are based on PL/SQL and it would be difficult of Oracle to ever desupport PL/SQL. In fact, all indications are that PL/SQL still has a bright future ahead of it. Many enhancements are still being made to PL/SQL. For example, Oracle 9i supports native compilation of PL/SQL code to binaries.

Not to mention the numerous PL/SQL enhancements made in Oracle 10g and 11g. PL/SQL and Java appeal to different people in different job roles. The following table briefly describes the similarities and difference between these two language environments:

PL/SQL:

• Can be used to create Oracle packages, procedures and triggers
• Data centric and tightly integrated into the database
• Proprietary to Oracle and difficult to port to other database systems
• Data manipulation is slightly faster in PL/SQL than in Java
• PL/SQL is a traditional procedural programming language

Java:

• Can be used to create Oracle packages, procedures and triggers
• Open standard, not proprietary to Oracle
• Incurs some data conversion overhead between the Database and Java type
• Java is an Object Orientated language, and modules are structured into classes
• Java can be used to produce complete applications

PS: Starting with Oracle 10g, .NET procedures can also be stored within the database (Windows only). Nevertheless, unlike PL/SQL and JAVA, .NET code is not usable on non-Windows systems.

PS: In earlier releases of Oracle it was better to put as much code as possible in procedures rather than triggers. At that stage procedures executed faster than triggers as triggers had to be re-compiled every time before executed (unless cached). In more recent releases both triggers and procedures are compiled when created (stored p-code) and one can add as much code as one likes in either procedures or triggers. However, it is still considered a best practice to put as much of your program logic as possible into packages, rather than triggers.

4: How can one see if somebody modified any code?

The source code for stored procedures, functions and packages are stored in the Oracle Data Dictionary. One can detect code changes by looking at the TIMESTAMP and LAST_DDL_TIME column in the

USER_OBJECTS dictionary view. Example:

SELECT OBJECT_NAME,

TO_CHAR(CREATED, ‘DD-Mon-RR HH24:MI’) CREATE_TIME,

TO_CHAR(LAST_DDL_TIME, ‘DD-Mon-RR HH24:MI’) MOD_TIME,

STATUS

FROM USER_OBJECTS

WHERE LAST_DDL_TIME > ‘&CHECK_FROM_DATE’;

Note: If you recompile an object, the LAST_DDL_TIME column is updated, but the TIMESTAMP column is not updated. If you modified the code, both the TIMESTAMP and LAST_DDL_TIME columns are updated. 

5: How can one search PL/SQL code for a string/ key value?

The following query is handy if you want to know where certain tables, columns and expressions are referenced in your PL/SQL source code.

SELECT type, name, line

FROM user_source

WHERE UPPER(text) LIKE UPPER(‘%&KEYWORD%’);

If you run the above query from SQL*Plus, enter the string you are searching for when prompted for

KEYWORD. If not, replace &KEYWORD with the string you are searching for.

6: How does one keep a history of PL/SQL code changes?

One can build a history of PL/SQL code changes by setting up an AFTER CREATE schema (or database) level trigger (available from Oracle 8.1.7). This will allow you to easily revert to previous code should someone make any catastrophic changes. Look at this example:

CREATE TABLE SOURCE_HIST — Create history table

AS SELECT SYSDATE CHANGE_DATE, ALL_SOURCE.*

FROM ALL_SOURCE WHERE 1=2;

CREATE OR REPLACE TRIGGER change_hist — Store code in hist table

AFTER CREATE ON SCOTT.SCHEMA — Change SCOTT to your schema name

DECLARE

BEGIN

IF ORA_DICT_OBJ_TYPE in (‘PROCEDURE’, ‘FUNCTION’,

‘PACKAGE’, ‘PACKAGE BODY’,

‘TYPE’, ‘TYPE BODY’)

THEN

— Store old code in SOURCE_HIST table

INSERT INTO SOURCE_HIST

SELECT sysdate, all_source.* FROM ALL_SOURCE

WHERE TYPE = ORA_DICT_OBJ_TYPE — DICTIONARY_OBJ_TYPE IN 8i

AND NAME = ORA_DICT_OBJ_NAME; — DICTIONARY_OBJ_NAME IN 8i

END IF;

EXCEPTION

WHEN OTHERS THEN

raise_application_error(-20000, SQLERRM);

END;

/

show errors

A better approach is to create an external CVS or SVN repository for the scripts that install the PL/SQL code. The canonical version of what’s in the database must match the latest CVS/SVN version or else someone would be cheating.

7: How can I protect my PL/SQL source code?

Oracle provides a binary wrapper utility that can be used to scramble PL/SQL source code. This utility was introduced in Oracle7.2 (PL/SQL V2.2) and is located in the ORACLE_HOME/bin directory. The utility use human-readable PL/SQL source code as input, and writes out portable binary object code (somewhat larger than the original). The binary code can be distributed without fear of exposing your proprietary algorithms and methods. Oracle will still understand and know how to execute the code. Just be

careful, there is no “decode” command available. So, don’t lose your source!

The syntax is:

wrap iname=myscript.pls oname=xxxx.plb

Please note: there is no legal way to unwrap a *.plb binary file. You are supposed to backup and keep your *.pls source files after wrapping them. However it is possible for skilled hackers to unwrap your wrapped Oracle PL/SQL code.

8: How can I know which stored PL/SQL code is wrapped?

The following query gives the list of all wrapped PL/SQL code:

select owner, name, type

from dba_source

where line = 1

and instr(text, ‘ wrapped’||chr(10))+instr(text, ‘ wrapped ‘||chr(10)) > 0

order by 1, 2, 3

/

9: Can one print to the screen from PL/SQL?

One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be

displayed on the screen from SQL*Plus if you issue the SET SERVEROUTPUT ON; command. For example:

set serveroutput on

begin

dbms_output.put_line(‘Look Ma, I can print from PL/SQL!!!’);

end;

/

DBMS_OUTPUT is useful for debugging PL/SQL programs. However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000 If you forget to set serveroutput on type SET SERVEROUTPUT ON once you remember, and then EXEC NULL;. If you haven’t cleared the DBMS_OUTPUT buffer with the disable or enable procedure, SQL*Plus will display the entire contents of the buffer when it executes this dummy PL/SQL block.

To display an empty line, it is better to use new_line procedure than put_line with an empty string.

10: Can one read/write files from PL/SQL?

The UTL_FILE database package can be used to read and write operating system files.

A DBA user needs to grant you access to read from/ write to a specific directory before using this package.

Here is an example:

CONNECT / AS SYSDBA

CREATE OR REPLACE DIRECTORY mydir AS ‘/tmp’;

GRANT read, write ON DIRECTORY mydir TO scott;

Provide user access to the UTL_FILE package (created by catproc.sql):

GRANT EXECUTE ON UTL_FILE TO scott;

Copy and paste these examples to get you started:

Write File

DECLARE

fHandler UTL_FILE.FILE_TYPE;

BEGIN

fHandler := UTL_FILE.FOPEN(‘MYDIR’, ‘myfile’, ‘w’);

UTL_FILE.PUTF(fHandler, ‘Look ma, Im writing to a file!!!\n’);

UTL_FILE.FCLOSE(fHandler);

EXCEPTION

WHEN utl_file.invalid_path THEN

raise_application_error(-20000, ‘Invalid path. Create directory or set

UTL_FILE_DIR.’);

END;

/

Read File

DECLARE

fHandler UTL_FILE.FILE_TYPE;

buf varchar2(4000);

BEGIN

fHandler := UTL_FILE.FOPEN(‘MYDIR’, ‘myfile’, ‘r’);

UTL_FILE.GET_LINE(fHandler, buf);

dbms_output.put_line(‘DATA FROM FILE: ‘||buf);

UTL_FILE.FCLOSE(fHandler);

EXCEPTION

WHEN utl_file.invalid_path THEN

raise_application_error(-20000, ‘Invalid path. Create directory or set

UTL_FILE_DIR.’);

END;

/

NOTE: UTL_FILE was introduced with Oracle 7.3. Before Oracle 7.3 the only means of writing a file was to use DBMS_OUTPUT with the SQL*Plus SPOOL command.

11: Can one call DDL statements from PL/SQL?

One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the

“EXECUTE IMMEDIATE” statement (native SQL). Examples:

begin

EXECUTE IMMEDIATE ‘CREATE TABLE X(A DATE)’;

end;

begin execute Immediate ‘TRUNCATE TABLE emp’; end;

DECLARE

var VARCHAR2(100);

BEGIN

var := ‘CREATE TABLE temp1(col1 NUMBER(2))’;

EXECUTE IMMEDIATE var;

END;

NOTE: The DDL statement in quotes should not be terminated with a semicolon.

Users running Oracle versions below Oracle 8i can look at the DBMS_SQL package (see FAQ about

Dynamic SQL).

12: Can one use dynamic SQL statements from PL/SQL?

Starting from Oracle8i one can use the “EXECUTE IMMEDIATE” statement to execute dynamic SQL and PL/SQL statements (statements created at run-time). Look at these examples. Note that the statements within quotes are NOT semicolon terminated:

EXECUTE IMMEDIATE ‘CREATE TABLE x (a NUMBER)’;

— Using bind variables…’

sql_stmt := ‘INSERT INTO dept VALUES (:1, :2, :3)’;

EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;

— Returning a cursor…

sql_stmt := ‘SELECT * FROM emp WHERE empno = :id’;

EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;

One can also use the older DBMS_SQL package (V2.1 and above) to execute dynamic statements. Look at

these examples:

CREATE OR REPLACE PROCEDURE DYNSQL AS

cur integer;

rc integer;

BEGIN

cur := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(cur, ‘CREATE TABLE X (Y DATE)’, DBMS_SQL.NATIVE);

rc := DBMS_SQL.EXECUTE(cur);

DBMS_SQL.CLOSE_CURSOR(cur);

END;

/

More complex DBMS_SQL example using bind variables:

CREATE OR REPLACE PROCEDURE DEPARTMENTS(NO IN DEPT.DEPTNO%TYPE) AS

v_cursor integer;

v_dname char(20);

v_rows integer;

BEGIN

v_cursor := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(v_cursor, ‘select dname from dept where deptno > :x’, DBMS_SQL.V7);

DBMS_SQL.BIND_VARIABLE(v_cursor, ‘:x’, no);

DBMS_SQL.DEFINE_COLUMN_CHAR(v_cursor, 1, v_dname, 20);

v_rows := DBMS_SQL.EXECUTE(v_cursor);

loop

if DBMS_SQL.FETCH_ROWS(v_cursor) = 0 then

exit;

end if;

DBMS_SQL.COLUMN_VALUE_CHAR(v_cursor, 1, v_dname);

DBMS_OUTPUT.PUT_LINE(‘Deptartment name: ‘||v_dname);

end loop;

DBMS_SQL.CLOSE_CURSOR(v_cursor);

EXCEPTION

when others then

DBMS_SQL.CLOSE_CURSOR(v_cursor);

raise_application_error(-20000, ‘Unknown Exception Raised: ‘||sqlcode||’ ‘||

sqlerrm);

END;

/

13: What is the difference between %TYPE and %ROWTYPE?

Both %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the database.

If the datatype or precision of a column changes, the program automatically picks up the new definition from the database without having to make any code changes. The %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the database changes to meet new business needs.

%TYPE

%TYPE is used to declare a field with the same type as that of a specified table’s column. Example:

DECLARE

v_EmpName emp.ename%TYPE;

BEGIN

SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;

DBMS_OUTPUT.PUT_LINE(‘Name = ‘ || v_EmpName);

END;

/

%ROWTYPE

%ROWTYPE is used to declare a record with the same types as found in the specified database table, view

or cursor. Examples:

DECLARE

v_emp emp%ROWTYPE;

BEGIN

v_emp.empno := 10;

v_emp.ename := ‘XXXXXXX’;

END;

/

14: How does one get the value of a sequence into a PL/SQL variable?

As you might know, one cannot use sequences directly from PL/SQL; Oracle prohibits this:

i := sq_sequence.NEXTVAL;

However, one can use embedded SQL statements to obtain sequence values:

select sq_sequence.NEXTVAL into :i from dual;

Note: This restriction has been removed in oracle 11g and the former syntax (direct assignment) can be used.

15: Can one execute an operating system command from PL/SQL?

There is no direct way to execute operating system commands from PL/SQL. PL/SQL doesn’t have a “host” command, as with SQL*Plus, that allows users to call OS commands. Nevertheless, the following workarounds can be used:

Database Pipes

Write an external program (using one of the precompiler languages, OCI or Perl with Oracle access modules) to act as a listener on a database pipe (SYS.DBMS_PIPE). Your PL/SQL program then put requests to run commands in the pipe, the listener picks it up and run the requests. Results are passed back on a different database pipe. For a Pro*C example, see chapter 8 of the Oracle Application Developers Guide.

CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 )

RETURN INTEGER IS

status NUMBER;

errormsg VARCHAR2(80);

pipe_name VARCHAR2(30);

BEGIN

pipe_name := ‘HOST_PIPE’;

dbms_pipe.pack_message( cmd );

status := dbms_pipe.send_message(pipe_name);

RETURN status;

END;

/

External Procedure Listeners:

From Oracle 8 one can call external 3GL code in a dynamically linked library (DLL or shared object). One can write a library in C/ C++ to do whatever is required. Defining this C/C++ function to PL/SQL makes it executable. Look at this External Procedure example.

DBMS_SCHEDULER

In Oracle 10g and above, one can execute OS commands via the DBMS_SCHEDULER package. Look at

this example:

BEGIN

dbms_scheduler.create_job(job_name => ‘myjob’,

job_type => ‘executable’,

job_action => ‘/app/oracle/x.sh’,

enabled => TRUE,

auto_drop => TRUE);

END;

/

exec dbms_scheduler.run_job(‘myjob’);

16: How does one loop through tables in PL/SQL?

One can make use of cursors to loop through data within tables. Look at the following nested loops code

example.

DECLARE

CURSOR dept_cur IS

SELECT deptno

FROM dept

ORDER BY deptno;

— Employee cursor all employees for a dept number

CURSOR emp_cur (v_dept_no DEPT.DEPTNO%TYPE) IS

SELECT ename

FROM emp

WHERE deptno = v_dept_no;

BEGIN

FOR dept_rec IN dept_cur LOOP

dbms_output.put_line(‘Employees in Department ‘||TO_CHAR(dept_rec.deptno));

FOR emp_rec in emp_cur(dept_rec.deptno) LOOP

dbms_output.put_line(‘…Employee is ‘||emp_rec.ename);

END LOOP;

END LOOP;

END;

/

17: How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?

Contrary to popular belief, one should COMMIT less frequently within a PL/SQL loop to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit, the sooner the extents in the undo/ rollback segments will be cleared for new transactions, causing ORA-1555 errors.

To fix this problem one can easily rewrite code like this:

FOR records IN my_cursor LOOP

…do some stuff…

COMMIT;

END LOOP;

COMMIT;

… to …

FOR records IN my_cursor LOOP

…do some stuff…

i := i+1;

IF mod(i, 10000) = 0 THEN — Commit every 10000 records

COMMIT;

END IF;

END LOOP;

COMMIT;

If you still get ORA-1555 errors, contact your DBA to increase the undo/ rollback segments.

NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard. Issuing frequent commits is bad, bad, BAD! It’s the WORST thing you can do… just don’t do it! In the following example I will create around 7 million rows and then attempt to update a portion of them serially.

In addition, I will issue a commit every thousandth row.

Example 1.1: Creating a somewhat large table

SQL> create table big_employee_table

2 as

3 select rownum as eid

4 , e.*

5 from hr.employees e

6 , dba_objects do;

Table created.

Elapsed: 00:00:12.23

SQL> select count(*)

2 from big_employee_table;

COUNT(*)

———-

7838713

Elapsed: 00:00:08.11

Before I go on, notice that Oracle’s “Create Table As” (CTAS) method blazed thru table creation. That’s 7.84 Million rows in 12.23 seconds. Sometimes, this is the very best method of updating large data sets. The following block updates 100,000 rows, serially, committing every 1000 rows:

Example 1.2: Updating serially

SQL> declare

2 cursor c is

3 select *

4 from big_employee_table

5 where rownum <= 100000;

6 begin

7 for r in c loop

8 update big_employee_table

9 set salary = salary * 1.03

10 where eid = r.eid;

11

12 if mod ( r.eid, 1000 ) = 0 then

13 commit;

14 end if;

15 end loop;

16 end;

17 /

Observe that the update took more time than I have patience for ;). At 20 minutes I killed the session. It is

painfully slow and should never be done. Moreover, it chewed up an entire CPU core for the duration. If

you’re only updating a few rows, why do it in PL/SQL at all? I like Tom Kyte’s approach (paraphrasing):

1. Do it in SQL.
2. If SQL can’t do it, do it in PL/SQL.
3. If PL/SQL can’t do it, do it in Java.
4. If Java can’t do it ask yourself if it needs to be done.

The following block does the same work in bulk:

Example 1.3: Updating in bulk and committing at the end

SQL> declare

2 type obj_rowid is table of rowid

3 index by pls_integer;

4

5 lr_rowid obj_rowid;

6 lr_salary dbms_sql.number_table;

7

8 cursor c is

9 select rowid rid

10 , salary

11 from big_employee_table

12 where rownum <= 100000;

13 begin

14 open c;

15 loop

16 fetch c bulk collect

17 into lr_rowid

18 , lr_salary

19 limit 500;

20

21 for a in 1 .. lr_rowid.count loop

22 lr_salary ( a ) := lr_salary ( a ) * 1.03;

23 end loop;

24

25 forall b in 1 .. lr_rowid.count

26 update big_employee_table

27 set salary = lr_salary ( b )

28 where rowid in ( lr_rowid ( b ));

29

30 exit when c%notfound;

31 end loop;

32 close c;

33 commit; — there! not in the loop

34 exception

35 when others then

36 rollback;

37 dbms_output.put_line ( sqlerrm );

38 end;

39 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.11

SQL>

Notice that the update completed in 2 seconds! I’ve seen faster but my two-gerbil sandbox machine doesn’t

have the power that our newer servers do. The point is that the update was incredibly fast and chewed up

only 10% of one core. So, in answer to the question of “how often should I commit?” I say don’t until you

absolutely have to.

18: I can SELECT from SQL*Plus but not from PL/SQL. What is wrong?

PL/SQL respect object privileges given directly to the user, but does not observe privileges given through roles. The consequence is that a SQL statement can work in SQL*Plus, but will give an error in PL/SQL.

Choose one of the following solutions:

• Grant direct access on the tables to your user. Do not use roles!

GRANT select ON scott.emp TO my_user;

• Define your procedures with invoker rights (Oracle 8i and higher);create or replace procedure procauthid current_user is begin

…

• Move all the tables to one user/schema.

19: What is a mutating and constraining table?

“Mutating” means “changing”. A mutating table is a table that is currently being modified by an update,delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered “mutating” and raises an error since Oracle should not return data that has not yet reached its final state.

Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints,  the workaround is to enforce the referential integrity through triggers as well.

There are several restrictions in Oracle regarding triggers:

• A row-level trigger cannot query or modify a mutating table. (Of course, NEW and OLD still can be accessed by the trigger).

• A statement-level trigger cannot query or modify a mutating table if the trigger is fired as the result of a CASCADE delete.Etc.

20: Can one pass an object/table as an argument to a remote procedure?

The only way to reference an object type between databases is via a database link. Note that it is not enough to just use “similar” type definitions. Look at this example:

— Database A: receives a PL/SQL table from database B

CREATE OR REPLACE PROCEDURE pcalled(TabX DBMS_SQL.VARCHAR2S) IS

BEGIN

— do something with TabX from database B

null;

END;

/

— Database B: sends a PL/SQL table to database A

CREATE OR REPLACE PROCEDURE pcalling IS

TabX DBMS_SQL.VARCHAR2S@DBLINK2;

BEGIN

pcalled@DBLINK2(TabX);

END;

/

21: What is the difference between stored procedures and functions?

• Functions MUST return a value, procedures don’t need to.You can have DML (insert,update, delete) statements in a function. But, you cannot call such a function in a SQL query. (However an autonomous transaction function can.) 
• You cannot call a procedure in a SQL query.

22: Is there a PL/SQL Engine in SQL*Plus?

No. Unlike Oracle Forms, SQL*Plus does not have an embedded PL/SQL engine. Thus, all your PL/SQL code is sent directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and sent to the database individually.

23: Is there a limit on the size of a PL/SQL block?

Yes, the max size is not an explicit byte limit, but related to the parse tree that is created when you compile the code. 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’;

24: What are the PL/SQL compiler limits for block, record, subquery and label nesting?

The following limits apply:

Level of Block Nesting: 255

Level of Record Nesting: 32

Level of Subquery Nesting: 254

Level of Label Nesting: 98

25: Can one COMMIT/ ROLLBACK from within a trigger?

A commit inside a trigger would defeat the basic definition of an atomic transaction ( See ACID Property) .

Trigger logic is by definition an extension of the original DML operation. Changes made within triggers should thus be committed or rolled back as part of the transaction in which they execute. For this reason, triggers are NOT allowed to execute COMMIT or ROLLBACK statements (with the exception of autonomous triggers). Here is an example of what will happen when they do:

SQL> CREATE TABLE tab1 (col1 NUMBER);

Table created.

SQL> CREATE TABLE log (timestamp DATE, operation VARCHAR2(2000));

Table created.

SQL> CREATE TRIGGER tab1_trig

2 AFTER insert ON tab1

3 BEGIN

4 INSERT INTO log VALUES (SYSDATE, ‘Insert on TAB1’);

5 COMMIT;

6 END;

7 /

Trigger created.

SQL> INSERT INTO tab1 VALUES (1);

INSERT INTO tab1 VALUES (1)

*

ERROR at line 1:

ORA-04092: cannot COMMIT in a trigger

ORA-06512: at “SCOTT.TAB1_TRIG”, line 3

ORA-04088: error during execution of trigger ‘SCOTT.TAB1_TRIG’

Autonomous transactions:

As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the

current transaction.

Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements. Example:

SQL> CREATE OR REPLACE TRIGGER tab1_trig

2 AFTER insert ON tab1

3 DECLARE

4 PRAGMA AUTONOMOUS_TRANSACTION;

5 BEGIN

6 INSERT INTO log VALUES (SYSDATE, ‘Insert on TAB1’);

7 COMMIT; — only allowed in autonomous triggers

8 END;

9 /

Trigger created.

SQL> INSERT INTO tab1 VALUES (1);

1 row created.

Note that with the above example will insert and commit log entries – even if the main transaction is rolled

back!

Remember that an “autonomous_transaction” procedure/function/trigger is a whole transaction in itself and

so it must end with a commit or a rollback statement.

26: What is ACID Property of Transaction?

1: Atomicity

The entire sequence of actions must be either completed or aborted. The transaction cannot be partially successful.

2: Consistency

The transaction takes the resources from one consistent state to another.

3: Isolation

A transaction’s effect is not visible to other transactions until the transaction is committed.

4: Durability

Changes made by the committed transaction are permanent and must survive system failure.+6

PL/SQL Frequently Asked Questions

1.What is a PL/SQL table? Its purpose and Advantages?

A PL/SQL table is one dimensional, indexed, unbounded sparse collection of homogeneous

Data. PLSQL tables are used to move data into and out of the database and between client side

applications and stored sub-programs. They have attributes such as exits, prior, first, last, delete , next .

These attributes make PLSQL tables easier to use and applications easier to maintain.

Advantages :

i: PL\SQL tables give you the ability to hold multiple values in a structure in memory so that a PL\SQL

block does not have to go to the database every time it needs to retrieve one of these values – it can

retrieve it directly from the PL\SQL table in memory.

ii: Global temporary tables act as performance enhancers when compared to standard tables as they

greatly reduce the disk IO.

iii: They also offer the ease-of-use of standard tables, since standard SQL can be used with them; no

special array-processing syntax is required

2.What is a Cursor? How many types of Cursor are there?

A) Cursor is an identifier/name to a work area that we can interact with to access its information. A cursor points to the current row in the result set fetched. There are three types of cursors. They are

i: Implicit cursors : created automatically by PL/SQL for all SQL-DML statements such as

Insert Update, delete and Select

ii: Explicit cursors : Created explicitly. They create a storage area where the set of rows

Returned by a query are placed.

iii: Dynamic Cursors : Ref Cursors( used for the runtime modification of the select querry).

Declaring the cursor, Opening the cursor, Fetching data , Closing the cursor(Releasing the work area) are the steps involved when using explicit cursors. It requires two IN parameters for a PL/SQL procedure that’s registered as a concurrent program in Apps.

They are

1. Errcode IN VARCHAR2

      2.Errbuff IN VARCHAR2

3.What are the modes for passing parameters to Oracle?

There are three modes for passing parameters to subprograms

i: IN – An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like

a constant and cannot be assigned a value.

ii: OUT ? An out-parameter lets you return values to the caller of the subprogram. It acts like an

initialized variable its value cannot be assigned to another variable or to itself.

iii: INOUT ? An in-out parameter lets you pass initial values to the subprogram being called and returns

updated values to the caller.

4.What is the difference between Truncate and Delete Statement?

Truncate : Data truncated by using truncate statement is lost permanently and cannot be retrieved even by

rollback. Truncate command does not use rollback segment during its execution, hence it is fast.

Delete : Data deleted by using the delete statement can be retrieved back by Rollback. Delete statement

does not free up the table object allocated space.

5.What are Exceptions? How many types of Exceptions are there?

Exceptions are conditions that cause the termination of a block. There are two types of exceptions

i: Pre-Defined : Predefined by PL/SQL and are associated with specific error codes.

ii: User-Defined : Declared by the users and are rose on deliberate request. (Breaking a condition etc.)

Exception handlers are used to handle the exceptions that are raised. They prevent exceptions from

propagating out of the block and define actions to be performed when exception is raised.

6.What is a Pragma Exception_Init? Explain its usage?

Pragma Exception_Init is used to handle undefined exceptions. It issues a directive to the compiler asking

it to associate an exception to the oracle error. There by displaying a specific error message pertaining to

the error occurred.

Pragma Exception_Init (exception_name, oracle_error_name).

7.What is a Raise and Raise Application Error?

8. A) Raise statement is used to raise a user defined exception.
9. B) A raise application error is a procedure belonging to dbms_standard package. It allows to display a

user defined error message from a stored subprogram.

9. How do you make a Function and Procedure as a Private?
10. A) Functions and Procedures can be made private to a package by not mentioning their declaration in the

package specification and by just mentioning them in the package body.

10. What is an Anonymous block?
11. A) Anonymous Block is a block of instructions in PL/SQL and SQL which is not saved under a name as

an object in database schema. It is also not compiled and saved in server storage, so it needs to be parsed

and executed each time it is run. However, this simple form of program can use variables, can have flow

of control logic, can return query results into variables and can prompt the user for input using the

SQL*Plus ‘&’ feature as any stored procedure.

11. How do you kick a Concurrent program from PL/SQL?

Using FND_SUBMIT.SUBMIT_REQUEST.

12. How to display messages in Log file and Output file?

Using FND_FILE.PUT_LINE

13. What is a Trigger ? How many types of Triggers are there?

Trigger is a procedure that gets implicitly executed when an insert/update/delete statement is issued

against an associated table. Triggers can only be defined on tables not on views, how ever triggers on the

base table of a view are fired if an insert/update/delete statement is issued against a view.

There are two types of triggers, Statement level trigger and Row level trigger.

Insert After / For each row Trigger is fired / Update / Before / For Each statement Delete

14. Can we use Commit in a Database Trigger, if ?No? then why?

No. Committing in a trigger will violate the integrity of the transaction.

15. What are the two basic parameters that we have to pass while registering PL/SQL procedure?

Error code and Error Buffer.

16. What is the difference between DDL, DML and DCL structures?

DDL statements are used for defining data. Ex: Create, Alter, Drop.

DML statements are used for manipulating data. Ex: Insert, update, truncate, delete, select.

DCL statements are used for to control the access of data. Ex; Grant, Revoke.

17. How can u create a table in PL/SQL procedure?

By using execute immediate statement we can create a table in PLSQL.

Begin Execute immediate ?create table amit as select * from emp?; End;

All DDL,DML,DCL commands can be performed by using this command.

18. How do we Tune the Queries?

Queries can be tuned by Checking the logic (table joins), by creating Indexes on objects in the where

clause, by avoiding full table scans. Finally use the trace utility to generate the trace file, use the TK-Prof

utility to generate a statistical analysis about the query using which appropriate actions can be taken.

19. What is a TK-PROF and its usage?
20. A) Tk-Prof is a utility that reads the trace files and generates more readable data that gives the statistics

about the performance of the query on a line to line basis.

20. What is Optimization? How many types of Optimization are there?
21. A) Rule based Optimization and Cost Based Optimization.
22. What is the default optimization chosen by Oracle?
23. A) Cost based Optimization.
24. What is the difference between When no data Found and cursor attribute % DATA FOUND?
25. A) When no Data Found is a predefined internal exception in PLSQL. Where as % Data found is a cursor

attribute that returns YES when zero rows are retrieved and returns NO when at least one row is

retrieved.

23. What is the difference between the snapshot and synonym?

A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can

be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to

reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.

A synonym is an alias for table, view, sequence or program unit. They are of two types private and

public.

24. What is the difference between Package, Procedure and Functions?

A package is a database objects that logically groups related PL/SQL types, objects, and

Subprograms.

Procedure is a sub program written to perform a set of actions and can return multiple values.

Function is a subprogram written to perform certain computations and return a single value.

Unlike subprograms packages cannot be called, passed parameters or nested.

25. What is the difference between data types char and varchar2?
26. A) Char reserves the number of memory locations mentioned in the variable declarations, even though

not used (it can store a maximum of 2000 bytes). Where as Varchar2 does not reserve any memory

locations when the variable is declared, it stores the values only after they are assigned (it can store a

maximum of 4000 bytes).

26. What is Explain Plan? How do u use Explain Plan in TOAD?
27. A) It is a utility provided by toad that gives the statistics about the performance of the query. It gives

information such as number of full table scans occurred, cost, and usage of indexes

27. When do you use Ref Cursors?

We base a query on a ref cursor when you want to:

1. i) More easily administer SQL
2. ii) Avoid the use of lexical parameters in your reports

iii) Share data sources with other applications, such as Form Builder

1. iv) Increase control and security
2. v) Encapsulate logic within a subprogram
3. what is a trace file?

when ever an internal error is detected by a process in oracle it dumps the information about the error into

a trace file.

Alter session set sql_trace=TRUE scope=spfile;

29) How can the duplicate records be from the table?

SQL> create table table_name2 as select distinct * from table_name1;

SQL> drop table_name1;

SQL> rename table_name2 to table_name1;

30) What is the significance of _all tables?

_all tables are multi-org tables which are associated with the company as a whole. Multiple Organizations

is enabled in Oracle Applications by partitioning some database tables by the Operating Unit. Other tables

are shared across Operating Units (and therefore across set of books). Examples of Applications with

partitioned tables are Oracle Payables, Oracle Purchasing, Oracle Receivables, Oracle Projects, Oracle

Sales & Marketing etc. The name of each corresponding partitioned table is the view name appended by

‘_ALL’

Q1. What is PL/SQL?

Answer : PL/SQL is a procedural language that has both interactive SQL and procedural programming language

constructs such as iteration, conditional branching.

Q2. What is the basic structure of PL/SQL?

Answer : PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in

PL/SQL.

Q3. What are the components of a PL/SQL block?

Answer : A set of related declarations and procedural statements is called block.

Q4. What are the components of a PL/SQL Block?

Answer : Declarative part, Executable part and Execption part.

Q5. What are the datatypes a available in PL/SQL?

Answer : Some scalar data types such as

NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN.

Some composite data types such as RECORD & TABLE.

Q6. What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?

Answer : % TYPE provides the data type of a variable or a database column to that variable.

% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in

the cursor.The advantages are: I. need not know about variable’s data type

1. If the database definition of a column in a table changes, the data type of a variable changes accordingly.

Q7. What is difference between % ROWTYPE and TYPE RECORD ?

Answer : % ROWTYPE is to be used whenever query returns a entire row of a table or view.

TYPE rec RECORD is to be used whenever query returns columns of different table or views and variables.

E.g. TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type );

e_rec emp% ROWTYPE

Cursor c1 is select empno,deptno from emp;

e_rec c1 %ROWTYPE.

Q8. What is PL/SQL table?

Answer : Objects of type TABLE are called “PL/SQL tables”, which are modelled as (but not the same as)

database tables, PL/SQL tables use a primary PL/SQL tables can have one column and a primary key.

Q9. What is a cursor? Why Cursor is required?

Answer : Cursor is a named private SQL area from where information can be accessed.

Cursors are required to process rows individually for queries returning multiple rows.

Q10. Explain the two types of Cursors?

Answer : There are two types of cursors, Implict Cursor and Explicit Cursor.

PL/SQL uses Implict Cursors for queries.

User defined cursors are called Explicit Cursors. They can be declared and used.

Q11. What are the PL/SQL Statements used in cursor processing?

Answer : DECLARE CURSOR cursor name, OPEN cursor name, FETCH cursor name INTO <variable list> or

Record types, CLOSE cursor name.

Q12. What are the cursor attributes used in PL/SQL?

Answer : %ISOPEN – to check whether cursor is open or not 

% ROWCOUNT – number of rows featched/updated/deleted.

% FOUND – to check whether cursor has fetched any row. True if rows are featched.

% NOT FOUND – to check whether cursor has featched any row. True if no rows are featched.

These attributes are proceded with SQL for Implict Cursors and with Cursor name for Explict Cursors.

Q13. What is a cursor for loop?

Answer : Cursor for loop implicitly declares %ROWTYPE as loop index,opens a cursor, fetches rows of values

from active set into fields in the record and closes when all the records have been processed.

1. FOR emp_rec IN C1 LOOP

salary_total := salary_total +emp_rec sal;

END LOOP;

Q14. What will happen after commit statement ?

Answer : Cursor C1 is

Select empno,

ename from emp;

Begin

open C1; loop

Fetch C1 into

eno.ename;

Exit When

C1 %notfound;—–

commit;

end loop;

end;

The cursor having query as SELECT …. FOR UPDATE gets closed after COMMIT/ROLLBACK.

The cursor having query as SELECT…. does not get closed even after COMMIT/ROLLBACK.

Q 15. Explain the usage of WHERE CURRENT OF clause in cursors ?

Answer : WHERE CURRENT OF clause in an UPDATE,DELETE statement refers to the latest row fetched from a

cursor.

Q 16. What is a database trigger ? Name some usages of database trigger ?

Answer : Database trigger is stored PL/SQL program unit associated with a specific database table. Usages are

Audit data modificateions, Log events transparently, Enforce complex business rules Derive column values

automatically, Implement complex security authorizations. Maintain replicate tables.

Q 17. How many types of database triggers can be specified on a table? What are they?

Answer :

Insert Update Delete

Before Row o.k. o.k. o.k.

After Row o.k. o.k. o.k.

Before Statement o.k. o.k. o.k.

After Statement o.k. o.k. o.k.

If FOR EACH ROW clause is specified, then the trigger for each Row affected by the statement. 

If WHEN clause is specified, the trigger fires according to the retruned boolean value.

Q 18. Is it possible to use Transaction control Statements such a ROLLBACK or COMMIT in Database

Trigger? Why?

Answer : It is not possible. As triggers are defined for each table, if you use COMMIT of ROLLBACK in a trigger,

it affects logical transaction processing.

Q 19. What are two virtual tables available during database trigger execution?

Answer : The table columns are referred as OLD.column_name and NEW.column_name.

For triggers related to INSERT only NEW.column_name values only available.

For triggers related to UPDATE only OLD.column_name NEW.column_name values only available.

For triggers related to DELETE only OLD.column_name values only available.

Q 20. What happens if a procedure that updates a column of table X is called in a database trigger of the

same table?

Answer : Mutation of table occurs.

Q 21. Write the order of precedence for validation of a column in a table ?

Answer :

1. done using Database triggers.
2. done using Integarity Constraints.

Q 22. What is an Exception? What are types of Exception?

Answer : Exception is the error handling part of PL/SQL block. The types are Predefined and user_defined.

Some of Predefined execptions are.

CURSOR_ALREADY_OPEN

DUP_VAL_ON_INDEX

NO_DATA_FOUND

TOO_MANY_ROWS

INVALID_CURSOR

INVALID_NUMBER

LOGON_DENIED

NOT_LOGGED_ON

PROGRAM-ERROR

STORAGE_ERROR

TIMEOUT_ON_RESOURCE

VALUE_ERROR

ZERO_DIVIDE

OTHERS.

Q 23. What is Pragma EXECPTION_INIT? Explain the usage?

Answer : The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error. To

get an error message of a specific oracle error.

e.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)

Q 24. What is Raise_application_error?

Answer : Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue an

user_defined error messages from stored sub-program or database trigger. 

Q 25. What are the return values of functions SQLCODE and SQLERRM?

Answer : SQLCODE returns the latest code of the error that has occured.

SQLERRM returns the relevant error message of the SQLCODE.

Q 26. Where the Pre_defined_exceptions are stored?

Answer : In the standard package.

Procedures, Functions & Packages;

Q 27. What is a stored procedure?

Answer : A stored procedure is a sequence of statements that perform specific function.

Q28. What is difference between a PROCEDURE & FUNCTION?

Answer : A FUNCTION is alway returns a value using the return statement.

A PROCEDURE may return one or more values through parameters or may not return at all.

Q29. What are advantages of Stored Procedures?

Answer : Extensibility,Modularity, Reusability, Maintainability and one time compilation.

Q30. What are the modes of parameters that can be passed to a procedure?

Answer : IN,OUT,IN-OUT parameters.

Q 31. What are the two parts of a procedure?

Answer : Procedure Specification and Procedure Body.

Q 32. Give the structure of the procedure?

Answer : PROCEDURE name (parameter list…..)

is

local variable declarations

BEGIN

Executable statements.

Exception.

exception handlers

end;

Q 33. Give the structure of the function?

Answer : FUNCTION name (argument list …..) Return datatype is

local variable declarations

Begin

executable statements

Exception

execution handlers

End;

Q 34. Explain how procedures and functions are called in a PL/SQL block ?

Answer : Function is called as part of an expression.

sal := calculate_sal (‘a822’);

procedure is called as a PL/SQL statement

calculate_bonus (‘A822’); 

Q 35. What is Overloading of procedures?

Answer : The Same procedure name is repeated with parameters of different datatypes and parameters in

different positions, varying number of parameters is called overloading of procedures.

e.g. DBMS_OUTPUT put_line

Q 36. What is a package? What are the advantages of packages?

Answer : Package is a database object that groups logically related procedures.

The advantages of packages are Modularity, Easier Applicaton Design, and Information.

Hiding,. Reusability and Better Performance.

Q 37. What are two parts of package?

Answer : The two parts of package are PACKAGE SPECIFICATION & PACKAGE BODY.

Package Specification contains declarations that are global to the packages and local to the schema.

Package Body contains actual procedures and local declaration of the procedures and cursor declarations.

Q 38. What is difference between a Cursor declared in a procedure and Cursor declared in a package

specification?

Answer : A cursor declared in a package specification is global and can be accessed by other procedures or

procedures in a package.

A cursor declared in a procedure is local to the procedure that can not be accessed by other procedures.

Q 39. How packaged procedures and functions are called from the following ?

Answer : a. Stored procedure or anonymous block

1. an application program such a PRC *C, PRO* COBOL
2. SQL *PLUS
3. PACKAGE NAME.PROCEDURE NAME (parameters);

variable := PACKAGE NAME.FUNCTION NAME (arguments);

EXEC SQL EXECUTE

BEGIN

PACKAGE NAME.PROCEDURE NAME (parameters)

variable := PACKAGE NAME.FUNCTION NAME (arguments);

END;

END EXEC;

1. EXECUTE PACKAGE NAME.PROCEDURE if the procedures does not have any out/in-out parameters. A

function can not be called.

Q 40. Name the tables where characteristics of Package, procedure and functions are stored?

Answer : User_objects, User_Source and User_error.

31. What are mutating tables? And what is mutating error?

A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a

table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity

constraint.

A mutating error occurs when a trigger which fires when updation/deletion/insertion is done on a table A performs

insertion/updation/deletion on the same table A. This error results in an infinite loop which is termed as a mutating

error.

32.What is Data cleaning and testing.

1. A) Data Cleaning: Transformation of data in its current state to a pre-defined, standardized format using packaged

software or program modules.

1. B) Data Testing: The agreed upon conversion deliverables should be approved by the client representatives who are

responsible for the success of the conversion. In addition, three levels of conversion testing have been identified and

described in the prepare conversion test plans deliverables.

Eg: for Summary Balances in GL we set Test Criteria as Record Counts, Hash Totals, Balances, Journal Debit and

Credit.

33. What is a mutating table error and how can you get around it?

Ans: 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.

34. What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

Ans: 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.

35. What are Transactional Triggers ? Give the uses of Transational Trigger ?

Ans: Transactional Triggers fire in response to transaction processing events. These events represent points during

application processing at which Oracle Forms needs to interact with the data source. Examples of such events include

updating records, rolling back to savepoints, and committing transactions. By default, Oracle Forms assumes that the

data source is an ORACLE database, and issues the appropriate SQL statements to optimize transaction processing

accordingly. However, by defining

transactional triggers and user exits, you can build a form to interact with virtually any data source, including even

non-relational databases and flat files. Calling User Exits When you define transactional triggers to interact with a

non-ORACLE data source, you will usually include a call to a user exit in the appropriate triggers. The code in your

user exit interacts with the non-ORACLE data source. Once the user exit has performed the appropriate function (as

indicated by the trigger from which it was called), it returns control to Oracle Forms for subsequent processing. For

example, a user exit called from an On-Fetch trigger might be responsible for retrieving the appropriate number of

records from the non-ORACLE data source. Once the records are retrieved, Oracle Forms takes over the display and

management of those records in the form interface, just as it would if the records had been fetched from an ORACLE

database. Uses for Transactional Triggers • Transactional triggers, except for the commit triggers, are primarily

intended to access certain data sources other than Oracle. • The logon and logoff transactional triggers can also be

used with Oracle databases to change connections at run time.

36. What is Autonomous transaction ? Where do we use it?

Ans: In Oracle’s database products, an autonomous transaction is an independent transaction that is initiated by

another transaction. It must contain at least one Structured Query Language (SQL) statement. Autonomous

transactions allow a single transaction to be subdivided into multiple commit/rollback transactions, each of which will

be tracked for auditing purposes. When an autonomous transaction is called, the original transaction (calling 

transaction) is temporarily suspended. The autonomous transaction must commit or roll back before it returns control

to the calling transaction. Once changes have been made by an autonomous transaction, those changes are visible to

other transactions in the database. Autonomous transactions can be nested. That is, an autonomous transaction can

operate as a calling transaction, initializing other autonomous transactions within itself.

37. What is a package, procedure and function?

Ans: Package : A package is a group of related program objects stored together as a unit in the database. A package is

an encapsulated collection of related program objects stored together in the database. Program objects are: procedures,

functions, variables, constants, cursors, exceptions. Procedure/Function : A procedure or function is a set of SQL and

PL/SQL statements grouped together as an executable unit to perform a specific task. The main difference between a

procedure and function is functions return a single variable by value whereas procedures do not return any variable by

value. Rather they return multiple variables by passing variables by reference through their OUT parameter.

38. What do u mean by overloading?

Ans: Function Overloading : Packages allow you to overload procedures or functions. Overloading a procedure

means creating multiple procedures with the same name in the same package, each taking arguments of different

number or datatype.

39. What are the constructs of a procedure, function or a package ?

Ans: The constructs of a procedure, function or a package are : • variables and constants • cursors • exceptions

40. What are cascading triggers? What is the maximum no of cascading triggers at a time?

Ans: When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.

Max = 32

41. What is the significance of the & and && operators in PL/SQL ?

Ans: The & operator means that the PL SQL block requires user input for a variable. The && operator means that the

value of this variable should be the same as inputted by the user previously for this same variable.

42. If all the values from a cursor have been fetched and another fetch is issued, the output will be?

Ans: Last Record

43. What is a forward declaration ? What is its use ?

Ans: PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before

calling it. This declaration at the start of a subprogram is called forward declaration. A forward declaration consists of

a subprogram specification terminated by a semicolon.

44. Any three PL/SQL Exceptions?

Ans: Too_many_rows, No_Data_Found, Value_Error, Zero_Error, Others

45. Describe the use of %ROWTYPE and %TYPE in PL/SQL?

Ans: %ROWTYPE allows you to associate a variable with an entire table row. The %TYPE associates a variable with

a single column type.

46. How can you call a PL/SQL procedure from SQL?

Ans: By use of the EXECUTE (short form EXEC) command.

47. What are the various types of Exceptions ?

Ans: User defined and Predefined Exceptions.

48. What is RAISE_APPLICATION_ERROR ?

Ans: DBMS_STANDARD provides a procedure named raise_application_error, which lets you issue user-defined

error messages. That way, you can report errors to an application and avoid returning unhandled exceptions. The

calling syntax is : raise_application_error(error_number, error_message); where error_number is a negative integer in

the range -20000…-20999 and error_message is a character string up to 2048 bytes in length. An application can call

raise_application_error only from an executing stored subprogram. When called, raise_application_error ends the

subprogram, rolls back any database changes it made, and returns a user-defined error number and message to the

application. The error number and message can be trapped like any ORACLE error. The calling application gets a

PL/SQL exception, which it can process using the error-reporting functions SQLCODE and SQLERRM in an

OTHERS handler. • The statement Raise_Application_Error can be called either from a procedure body or from an

exception handler. • Irrespective of whether an error occurred or not, a raise_application_error command always

raises an exception in the calling program (eg a forms trigger). If an exception handler is not written in that forms

trigger, then a forms error occurs.

49. What is Ref Cursor?

Ans: A REF CURSOR is basically a data type. A variable created based on such a data type is generally called a

cursor variable. A cursor variable can be associated with different queries at run-time. The primary advantage of using

cursor variables is their capability to pass result sets between sub programs (like stored procedures, functions,

packages etc.).

50. What is row chaining, how does it happen?

Ans: Row chaining occurs when a VARCHAR2 value is updated and the length of the new value is longer than the

old value and won’t fit in the remaining block space. This results in the row chaining to another block. It can be

reduced by setting the storage parameters on the table to appropriate values. It can be corrected by export and import

of the effected table.

51. What do u mean by EXCEPTION_INIT Pragma ?

Ans: EXCEPTION_INIT Pragma : To handle unnamed internal exceptions, you must use the OTHERS handler or the

pragma EXCEPTION_INIT. A “pragma” is a compiler directive, which can be thought of as a parenthetical remark to

the compiler. Pragmas (also called “pseudoinstructions”) are processed at compile time, not at run time. They do not

affect the meaning of a program; they simply convey information to the compiler. The predefined pragma

EXCEPTION_INIT tells the PL/SQL compiler to associate an exception name with an Oracle error number. That

allows you to refer to any internal exception by name and to write a specific handler for it. You code the pragma

EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package

using the syntax PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number); where “exception_name” is

the name of a previously declared exception. For internal exceptions, SQLCODE returns the number of the associated

Oracle error. The number that SQLCODE returns is negative unless the Oracle error is “no data found,” in which case

SQLCODE returns +100. SQLERRM returns the message associated with the Oracle error that occurred. The

message begins with the Oracle error code. For user-defined exceptions, SQLCODE returns +1 and SQLERRM

returns the message “User-Defined Exception” unless you used the pragma EXCEPTION_INIT to associate the

exception name with an Oracle error number, in which case SQLCODE returns that error number and SQLERRM

returns the corresponding error message. The maximum length of an Oracle error message is 512 characters including

the error code, nested messages, and message inserts such as table and column names.

52. What do u mean by JSP query?

Ans: JSP Query : The JSP Query is a standard query for number to words conversion, used especially for converting

amount in number into equivalent amount in words. The query is as follows : Select to_char ( to_date ( ‘&no’, ‘J’ ),

‘JSP’ ) words from dual; For eg : Select to_char ( to_date ( ‘23949’,’j’ ), ‘JSP’ ) “words” from dual; The value that can

pass to &no cannot exceed 7 digits.

53. Do we use commit in triggers.

Ans: No

54. What are triggers and its types?

Ans: A trigger is a piece of code attached to a table that is executed after specified DML statements executed on that

table. There are 12 types of triggers in PL/SQL that consist of combinations of the BEFORE, AFTER, ROW,

STATEMENT, TABLE, INSERT, UPDATE, DELETE and ALL key words: For eg: BEFORE ALL ROW INSERT

AFTER ALL ROW INSERT BEFORE INSERT AFTER INSERT

55. What is the maximum number of triggers, can apply to a single table?

Ans: 12 triggers(Oracle).

56. Can a trigger written for a view ?

Ans: No

57. What is cursor?

Cursor is private sql area which is used to execute sql statements and store processing information

58. What is a cursor its attribute and types?

Ans: The Oracle Engine uses a work area for its internal processing in order to execute an SQL statement. This work

area is private to SQL operation and is called Cursor.

Types of Cursor:

Implicit Cursor: If the Oracle engine opened a cursor for its internal processing then it is know as implicit cursor. It

is invoked implicitly.

Explicit Cursor: A cursor which is opened for processing data through a PL/SQL block is know as Explicit Cursor.

Attributes Of a Implicit Cursor:

%ISOPEN —returns TRUE if cursor is open else FALSE.

Syntax is SQL%ISOPEN

%ROWCOUNT— returns number of records processed from cursor syntax is SQL %ROWCOUNT %FOUND—-

returns TRUE if record is fetched successfully else FALSE, syntax is SQL%FOUND %NOTFOUND– returns TRUE

if record is not fetched successfully else FALSE syntax is SQL%NOTFOUND Attributes Of a Explicit Cursor

%ISOPEN— returns TRUE if cursor is open else FALSE. Syntax is cursorname%ISOPEN %ROWCOUNT—

returns number of records processed from cursor syntax is cursorname %ROWCOUNT %FOUND—- returns TRUE

if record is fetched successfully else FALSE, syntax is cursorname %FOUND %NOTFOUND– returns TRUE if

record is not fetched successfully else FALSE syntax is cursorname %NOTFOUND

59.What is explicit and implicit cursor and examples?

The implicit cursor is automatically declared by oracle every time an sql statement is executed whenever you issue a

sql statement, the oracle server opens an area of memory in which the command is parsed and executed. Every

implicit cursor attribute start with sql%.

An explicit cursor is created and managed by the user. And used for multi row select statement.