Oracle PL-SQL Interview 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.

Is This Answer Correct?    7 Yes 0 No

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

Is This Answer Correct?    3 Yes 1 No

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.

Is This Answer Correct?    2 Yes 2 No

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.

Is This Answer Correct?    4 Yes 1 No

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;

Is This Answer Correct?    5 Yes 1 No