Oracle PL-SQL Interview Preparation Guide
Download PDF

Oracle PL-SQL Interview Questions and Answers will help you to face a successful Oracle PL-SQL Interview and get hired, here you can learn PL-SQL of Oracle database also and update your knowledge and get preparation for a better job in Oracle PL SQL, So learn PL-SQL with the help of this Oracle PL-SQL Interview Questions with Answers guide

52 Oracle PL-SQL Questions and Answers:

1 :: What is difference b/w stored procedures and application procedures, stored function and application function?

Stored procedures are subprogrammes stored in the database and can be called &executee multiple times wherein an application procedure is the one being used for a particular application same is the way for function.

Both can be executed any number of times. Only difference is that stored procedures/ functions are stored in database in complied format while the application procedures/functions are not in precomplied format and at run time has to be compiled.

2 :: Explian rowid, rownum?what are the psoducolumns we have?

ROWID - Hexa decimal number each and every row having unique.Used in searching.

ROWNUM - It is a integer number also unique for sorting Normally TOP N Analysys.

Other Psudo Column are

NEXTVAL,CURRVAL Of sequence are some exampls

psudo columns are default columns provided by oracle

3 :: what is the starting oracle error number?
what is meant by forward declaration in functions?

One must declare an identifier before referencing it. Once it is declared it can be referred even before defining it in the PL/SQL. This rule applies to function and procedures also.

4 :: In a Distributed Database System Can we execute two queries simultaneously? Justify?

s Distributed database system based on 2 phase commit,one query is independent of 2 nd query so of course we can run.

5 :: How we can create a table in PL/SQL block. insert records into it? is it possible by some procedure or function? please give example?

CREATE OR REPLACE PROCEDURE ddl_create_proc (p_table_name IN VARCHAR2)

AS

l_stmt VARCHAR2(200);

BEGIN

DBMS_OUTPUT.put_line('STARTING ');

l_stmt := 'create table '|| p_table_name || ' as (select * from emp )';

execute IMMEDIATE l_stmt;

DBMS_OUTPUT.put_line('end ');

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.put_line('exception '||SQLERRM || 'message'||sqlcode);

END;

6 :: How to avoid using cursors? What to use instead of cursor and in what cases to do so?

Just use subquery in for clause

ex:For emprec in (select * from emp)

loop

dbms_output.put_line(emprec.empno);

end loop;

no exit statement needed

implicit open,fetch,close occurs

7 :: State the difference between implict and explict cursors?

Implicit Cursor are declared and used by the oracle internally. whereas the explicit cursors are declared and used by the user. more over implicitly cursors are no need to declare oracle creates and process and closes autometically. the explicit cursor should be declared and closed by the user.

Implicit cursors are used for single row query whereas explicit cursor is used for multiple row query

8 :: How to know the last executed procedure?

Execute procedure name (parameter1,parameter2)

Select timestamps, owner, obj_name, action_name from dba_audit_trail;this statement gives last executed time for procedure , function & package.

9 :: How can a function retun more than one value in oracle with proper example?

yes we can use objects, arrays to return more than one value

10 :: If the application is running very slow? At what points you need to go about the database in order to improve the performance?

For improving performance, we need to check the sql statement blocks , because for every sql satement execution transfor to sql engine and come back to plsq engine that process takes more time to process the plsql block.