Pl/Sql interview question

1.            Difference b/w procedure and function?  A procedure may return (one or more values using OUT & INOUT Parameters) or may not return a value. But a function has to return a single value and has the return clause in its definition. Function can be called in select statements but procedure can only be called in a pl/sql block.  Procedure's parameters can have IN or OUT or INOUT parameters. But function's parameters can only have IN parameters.

2.            Difference b/w ROWID and ROWNUM? ROWID : It gives the hexadecimal string representing the address of a row.It gives the location in database where row is physically stored. ROWNUM: It gives a sequence number in which rows are retrieved from the database.

3.            Give some examples of pseudo columns? NEXTVAL, CURRVAL, LEVEL, SYSDATE

4.            Difference b/w implicit cursor and explicit cursor? Implicit cursors are automatically created by oracle for all its DML stmts. Examples of implicit cursors: SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN; Explicit cursors are created by the users for multi row select stmts.

5.            How to create a table in a procedure or function? See the below piece of code:  Since create stmt can be used only at the sql prompt, we have used dynamic sql to create a table.           

DECLARE

                    L_STMT VARCHAR2(100);

BEGIN

                    DBMS_OUTPUT.PUT_LINE('STARTING ');

                    L_STMT := 'create table dummy1 (X VARCHAR2(10) , Y NUMBER)';

                    EXECUTE IMMEDIATE L_STMT;

                    DBMS_OUTPUT.PUT_LINE('end ');

END;

The above piece of code can be written In procedure and function DDL's can be used in function provided that function should be invoked in Begin-End block not from Select statement.

6.            Explain the usage of WHERE CURRENT OF clause in cursors ? Look at the following pl/sql code:

DECLARE

                   CURSOR wip_cur IS

                   SELECT acct_no, enter_date

                   FROM wip

WHERE enter_date < SYSDATE -7

FOR UPDATE;

BEGIN

FOR wip_rec IN wip_cur

LOOP

                   INSERT INTO acct_log (acct_no, order_date)

                   VALUES (wip_rec.acct_no, wip_rec.enter_date);

                   DELETE FROM wip

WHERE CURRENT OF wip_cur;

          END LOOP;

END;

"WHERE CURRENT OF" has to be used in concurrence with "FOR UPDATE"  in the cursor select stmt.

"WHERE CURRENT OF" used in delete or update stmts means, delete/update the current record specified by the cursor.

By using WHERE CURRENT OF, you do not have to repeat the WHERE clause in the SELECT statement.

7.            What is the purpose of FORUPDATE? Selecting in FOR UPDATE mode locks the result set of rows in update mode, which means that row cannot be updated or deleted until a commit or rollback is issued which will release the row(s). If you plan on updating or deleting records that have been referenced by a Select For Update statement, you can use the Where Current Of statement.                                                                                               

8.            What is RAISE_APPLICATION_ERROR? The RAISE_APPLICATION_ERROR is a procedure defined by Oracle that allows the developer to raise an exception and associate an error number and message with the procedure other than just Oracle errors. Raising an Application Error With raise_application_error

9.            

DECLARE

   num_tables NUMBER;

BEGIN

   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;

   IF num_tables < 1000 THEN

      /* Issue your own error code (ORA-20101) with your own error message.

         Note that you do not need to qualify raise_application_error with

         DBMS_STANDARD */

      raise_application_error(-20101, 'Expecting at least 1000 tables');

   ELSE

      NULL; -- Do the rest of the processing (for the non-error case).

   END IF;

END;

/

The procedure RAISE_APPLICATION_ERROR lets you issue user-defined ORA- error messages from stored subprograms. That way, you can report errors to your application and

avoid returning unhandled exceptions.

9.            What is mutating error? Mutating error occurs in the following scenario:

WHEN WE ARE UPDATING A TABLE (TRIGGER WRITTEN ON A TABLE FOR UPDATE) AND AT THE SAME TIME TRYING TO RETRIEVE DATA FROM THAT TABLE. IT WILL RESULT INTO MUTATING TABLE AND IT WILL RESULT INTO MUTATING ERROR.

10.         Can we have commit/rollback in DB triggers? Having Commit / Rollback inside a trigger defeats the standard of whole transaction's commit / rollback all together. Once trigger execution is complete then only a transaction can be said as complete and then only commit should take place. If we still want to carry out some action which should be initiated from trigger but should be committed irrespective of trigger completion / failure we can have AUTONOMUS TRANSACTION. Inside Autonomous transaction block we can have Commit and it will act as actual commit.                                                                                                

11.         Can we make the trigger an autonomous transaction? This makes all the difference because within the autonomous transaction (the trigger), Oracle will view the triggering table as it was before any changes occurred—that is to say that any changes are uncommitted and the autonomous transaction doesn’t see them. So the potential confusion Oracle normally experiences in a mutating table conflict doesn’t exist.

12.         What is autonomous transaction? Autonomous transaction means a transaction that is embedded in some other transaction, but functions independently.

13.         What is a REF Cursor? The REF CURSOR is a data type in the Oracle PL/SQL language. It represents a cursor or a result set in Oracle Database.

14.         What is the difference between ref cursors and normal pl/sql cursors?

   Declare

          type rc is ref cursor;

          cursor c is

          select * from dual;

          l_cursor rc;

       begin

          if ( to_char(sysdate,'dd') = 30 ) then

                   open l_cursor

                             for select * from emp;

          elsif ( to_char(sysdate,'dd') = 29 ) then

                   open l_cursor

                             for select * from dept;

          else

                   open l_cursor

                             for select * from dual;

          end if;

          open c;

      end;                                                                                                                    

Given that block of code you see perhaps the most "salient" difference, no matter how many times you run that block The cursor C will always be select * from dual.  The ref cursor can be anything.

15.         Is Truncate a DDL or DML statement? And why? Truncate is a DDL statement. Check the LAST_DDL_TIME on USER_OBJECTS after truncating your table. TRUNCATE will automatically commit, and it's not rollback able. This changes the storage definition of the object. That's why it is a DDL.

16.         What are the actions you have to perform when you drop a package? If you rename a package, the other packages that use it will have to be MODIFIED. A simple compilation of the new renamed package won't do. If you have toad, go to the "used by" tab that will show you the packages that call the package being renamed.

17.         What is cascading triggers? When a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers.

18.         What are materialized views? A materialized view is a database object that stores the results of a query (possibly from a remote database). Materialized views are sometimes referred to as snapshots.