Oracle PL-SQL Interview Preparation Guide
Elevate your Oracle PL-SQL interview readiness with our detailed compilation of 52 questions. Each question is crafted to challenge your understanding and proficiency in Oracle PL-SQL. Suitable for all skill levels, these questions are essential for effective preparation. Download the free PDF now to get all 52 questions and ensure youre well-prepared for your Oracle PL-SQL interview. This resource is perfect for in-depth preparation and boosting your confidence.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.
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
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;
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
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
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.
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.