Pl sql Interview Questions
Where is the WHERE CURRENT OF clause required in cursors?
Where do we store the features of package, procedure and functions?
Discuss the cursor attributes available in pl/sql.
Explain forward declaration used in functions.
If we call a column updating procedure in the database trigger of the same table?
Explain the two virutal tables which are used while execution of database.
Explain the difference between Function and Procedure.
What do you understand by a Raise_application_error?
Explain the various types of database triggers possible on a table.
What do you mean by ref cursor?
Describe the method of calling functions and procedures in a PL/SQL block.
What do you understand by Trigger, function, cursor in pl-sql?
Are the Rollback or Commit Transaction Control Statements useful in Database Trigger?
Explain the various uses and limitations of cursor.
What are the constituents of a package?
Describe the basic structure of PL/SQL.
Method of dubugging the procedure.
Method for insertion of records in a table. How do we make a table? Provide suitable examples.
Describe a cursor for loop.
Where in memory are the Pre_defined_exceptions stored?
What do you understand by Pragma EXCEPTION_INIT?
Describe the various types of cursors.
Describe the difference between application procedure and stored procedure.
What do you understand by PL/SQL?
Explain the various restrictions on cursor variables.
Explain the use of procedure to make a table.
What do you understand by database trigger? What are its uses?
Describe the difference between application function and stored function.
How can we use commit, savepoint and rollback in database triggers?
How is a cursor different in a procedure and package specification?
What are the advantages of using PL/SQL?
What do you know about rowid and rownum?
Which pl/sql statements are required in cursor processing?
What do you understand by a stored procedure?
Define the %TYPE and % ROWTYPE. What is the benefit of these datatypes?
Explain the meaning of “Overloading of Procedures”.
Why do we use OUT Parameter in pl/sql while we have the return statement?
What are the results of using commit statement?
What do you mean by an Exception?
Various advantages of using stored procedures.
Explain the structure of a procedure.
Describe a function’s structure.
Why do we need a cursor?
Explain the various types of exceptions.
What does the function SQLCODE return?
What does the function SQLERRM return?
Explain the difference between implicit and explicit cursors.
Can you queries be run simultaneously in a distributed database system?
List the various components found in a PL/SQL Block.
Describe the points of difference between % ROWTYPE and TYPE RECORD.
Discuss the various components of a procedure.
When should we not use cursors?
Give an example of a PL SQL Table.
Describe the modes of parameters which a procedure can have?
How can we disable multiple triggers of a table at a time?
Which datatypes can be found in PL/SQL?
Showing posts with label pl/sql faqs. Show all posts
Showing posts with label pl/sql faqs. Show all posts
How we difine user defined Exceptions in PL/SQL(Oracle)?
How we difine user defined Exceptions in PL/SQL(Oracle)?
It's very simple to define,for that i am giving one simple examle..
DECLARE
a NUMBER;
b VARCHAR2(10);
BEGIN
EXCEPTION
WHEN No_data_found THEN
dbms_output.put_line('Data not found');
WHEN Dul_value_on_index THEN
dbms_output.put_line('Inserting duplicate record');
WHEN Too_many_rows THEN
dbms_output.put_line('Too many values selected');
WHEN zero_devide
dbms_output.put_line('Deviding with ZERO');
WHEN Others THEN
dbms_output.put_line('Othere Error'||SQLERRM);
END;
It's very simple to define,for that i am giving one simple examle..
DECLARE
a NUMBER;
b VARCHAR2(10);
BEGIN
EXCEPTION
WHEN No_data_found THEN
dbms_output.put_line('Data not found');
WHEN Dul_value_on_index THEN
dbms_output.put_line('Inserting duplicate record');
WHEN Too_many_rows THEN
dbms_output.put_line('Too many values selected');
WHEN zero_devide
dbms_output.put_line('Deviding with ZERO');
WHEN Others THEN
dbms_output.put_line('Othere Error'||SQLERRM);
END;
How we difine pre defined Exceptions in PL/SQL(Oracle)?
How we difine pre defined Exceptions in PL/SQL(Oracle)?
It's very simple to define,for that i am giving one simple examle..
DECLARE
a NUMBER;
b VARCHAR2(10);
BEGIN
EXCEPTION
WHEN No_data_found THEN
dbms_output.put_line('Data not found');
WHEN Dul_value_on_index THEN
dbms_output.put_line('Inserting duplicate record');
WHEN Too_many_rows THEN
dbms_output.put_line('Too many values selected');
WHEN zero_devide
dbms_output.put_line('Deviding with ZERO');
WHEN Others THEN
dbms_output.put_line('Othere Error'||SQLERRM);
END;
It's very simple to define,for that i am giving one simple examle..
DECLARE
a NUMBER;
b VARCHAR2(10);
BEGIN
EXCEPTION
WHEN No_data_found THEN
dbms_output.put_line('Data not found');
WHEN Dul_value_on_index THEN
dbms_output.put_line('Inserting duplicate record');
WHEN Too_many_rows THEN
dbms_output.put_line('Too many values selected');
WHEN zero_devide
dbms_output.put_line('Deviding with ZERO');
WHEN Others THEN
dbms_output.put_line('Othere Error'||SQLERRM);
END;
How we difine user defined Exceptions in PL/SQL(Oracle)?
How we difine user defined Exceptions in PL/SQL(Oracle)?
User defined Exception
DECLARE
a NUMBER;
b VARCHAR2(10);
user_ex EXCEPTION;
BEGIN
if a > b THE
RAISE user_ex;
end if;
EXCEPTION
WHEN user_ex TEHN
dbms_output.put_line('User Defined Exception');
END;
/
User defined Exception
DECLARE
a NUMBER;
b VARCHAR2(10);
user_ex EXCEPTION;
BEGIN
if a > b THE
RAISE user_ex;
end if;
EXCEPTION
WHEN user_ex TEHN
dbms_output.put_line('User Defined Exception');
END;
/
How we find User defined error message for Oracle Error?
How we find User defined error message for Oracle Error?
DECLARE
a NUMBER;
b VARCHAR2(10);
deadlock_detected EXCEPTION
pragma EXCEPTION_INIT(deadlock_detected,-0060);
BEGIN
select count(*) INTO a FROM emp;
EXCEPTION
WHEN deadlock_detected THEN
dbms_output.put_line('deadlock_detected');
WHEN OTHERS THEN
dbms_output.put_line('Other error');
END;
DECLARE
a NUMBER;
b VARCHAR2(10);
deadlock_detected EXCEPTION
pragma EXCEPTION_INIT(deadlock_detected,-0060);
BEGIN
select count(*) INTO a FROM emp;
EXCEPTION
WHEN deadlock_detected THEN
dbms_output.put_line('deadlock_detected');
WHEN OTHERS THEN
dbms_output.put_line('Other error');
END;
How to we use Raise_application_error(Oracle PL/SQL)?
How to we use Raise_application_error(Oracle PL/SQL)?
DECLARE
a NUMBER;
b VARCHAR2(10);
BEGIN
IF a > b THEN
Raise_application_error('A sal is greater than b');
END IF;
END;
/
DECLARE
a NUMBER;
b VARCHAR2(10);
BEGIN
IF a > b THEN
Raise_application_error('A sal is greater than b');
END IF;
END;
/
Why do stored procedures reduce network traffic ?
Why do stored procedures reduce network traffic ?
When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.
When a stored procedure is called, only the procedure call is sent to the server and not the statements that the procedure contains.
What are Database Triggers and Stored Procedures
What are Database Triggers and Stored Procedures
Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
eg. operations insert,update ,delete 3 before ,after 3*2 A total of 6 combinatons
At statment level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12. Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures : Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.
Database Triggers :: Database Triggers are Procedures that are automatically executed as a result of insert in, update to, or delete from table. Database triggers have the values old and new to denote the old value in the table before it is deleted and the new indicated the new value that will be used. DT are useful for implementing complex business rules which cannot be enforced using the integrity rules.We can have the trigger as Before trigger or After Trigger and at Statement or Row level.
eg. operations insert,update ,delete 3 before ,after 3*2 A total of 6 combinatons
At statment level(once for the trigger) or row level( for every execution ) 6 * 2 A total of 12. Thus a total of 12 combinations are there and the restriction of usage of 12 triggers has been lifted from Oracle 7.3 Onwards.
Stored Procedures : Stored Procedures are Procedures that are stored in Compiled form in the database.The advantage of using the stored procedures is that many users can use the same procedure in compiled and ready to use format.
How many types of Exceptions are there in Pl/sql?
How many types of Exceptions are there in Pl/sql?
There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then
There are 2 types of exceptions. They are
a) System Exceptions
e.g. When no_data_found, When too_many_rows
b) User Defined Exceptions
e.g. My_exception exception
When My_exception then
Procedure to Reverse a String
Procedure to Reverse a String
The following tip uses a procedure that reverses a string provided as an argument.
SQL> create or replace procedure rev(x in varchar2) as
2 c char(1);
3 i number;
4 begin
5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(c);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /
Procedure created.
SQL> set serverout on
SQL> exec rev('Java')
avaJ
PL/SQL procedure successfully completed.
SQL> exec rev('Oracle')
elcarO
PL/SQL procedure successfully completed.
The following tip uses a procedure that reverses a string provided as an argument.
SQL> create or replace procedure rev(x in varchar2) as
2 c char(1);
3 i number;
4 begin
5 for i in 1..length(x) loop
6 select substr(x,length(x)-i+1,1) into c from dual;
7 dbms_output.put(c);
8 end loop;
9 dbms_output.put_line(' ');
10 end;
11 /
Procedure created.
SQL> set serverout on
SQL> exec rev('Java')
avaJ
PL/SQL procedure successfully completed.
SQL> exec rev('Oracle')
elcarO
PL/SQL procedure successfully completed.
How can I count the number of characters in a LONG data type field?
How can I count the number of characters in a LONG data type field?
Answer: It doesn't appear that you can find the length of a LONG field in SQL. However, you can use PLSQL code to determine the length of a LONG field.
Here is an example of a function that returns the length of the LONG field called SEARCH_CONDITION. This function accepts the primary key values (owner and constraint_name fields) and returns the length of the SEARCH_CONDITION field for the selected record.
CREATE or REPLACE function Find_Length
( av_owner varchar2, av_cname varchar2)
RETURN number
IS
long_var LONG;
BEGIN
SELECT SEARCH_CONDITION INTO long_var
FROM ALL_CONSTRAINTS
WHERE owner = av_owner
AND constraint_name = av_cname;
return length(long_var);
END;
Answer: It doesn't appear that you can find the length of a LONG field in SQL. However, you can use PLSQL code to determine the length of a LONG field.
Here is an example of a function that returns the length of the LONG field called SEARCH_CONDITION. This function accepts the primary key values (owner and constraint_name fields) and returns the length of the SEARCH_CONDITION field for the selected record.
CREATE or REPLACE function Find_Length
( av_owner varchar2, av_cname varchar2)
RETURN number
IS
long_var LONG;
BEGIN
SELECT SEARCH_CONDITION INTO long_var
FROM ALL_CONSTRAINTS
WHERE owner = av_owner
AND constraint_name = av_cname;
return length(long_var);
END;
What is the difference between a procedure and a function ?
What is the difference between a procedure and a function ?
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.
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.
What are the various types of parameter modes in a procedure ?
What are the various types of parameter modes in a procedure ?
IN, OUT AND INOUT
IN, OUT AND INOUT
What are the values of :new and :old in Insert/Delete/Update Triggers ?
What are the values of :new and :old in Insert/Delete/Update Triggers ?
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value
What is the significance of the & and && operators in PL SQL ?
What is the significance of the & and && operators in PL SQL ?
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.
If a transaction is very large, and the rollback segment is not able to hold
the rollback information, then will the transaction span across different
rollback segments or will it terminate ?
It will terminate (Please check ).
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.
If a transaction is very large, and the rollback segment is not able to hold
the rollback information, then will the transaction span across different
rollback segments or will it terminate ?
It will terminate (Please check ).
How to pass a parameter to a cursor ?
How to pass a parameter to a cursor ?
Explicit cursors can take parameters, as the example below shows. A cursor
parameter can appear in a query wherever a constant can appear.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
Explicit cursors can take parameters, as the example below shows. A cursor
parameter can appear in a query wherever a constant can appear.
CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;
What is the difference between a view and a synonym?
What is the difference between a view and a synonym?
Synonym is just a second name of table used for multiple link of database.
View can be created with many tables, and with virtual columns and with
conditions. But synonym can be on view.
Synonym is just a second name of table used for multiple link of database.
View can be created with many tables, and with virtual columns and with
conditions. But synonym can be on view.
What is PL/SQL?
What is PL/SQL?
PL/SQL is Oracle's Procedural Language extension to SQL. The language
includes object oriented programming techniques such as encapsulation,
function overloading, information hiding (all but inheritance), and so,
brings state-of-the-art programming to the Oracle database server and a
variety of Oracle tools.
PL/SQL is Oracle's Procedural Language extension to SQL. The language
includes object oriented programming techniques such as encapsulation,
function overloading, information hiding (all but inheritance), and so,
brings state-of-the-art programming to the Oracle database server and a
variety of Oracle tools.
Is there a limit on the size of a PL/SQL block?
Is there a limit on the size of a PL/SQL block?
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the
maximum code size is 100K. You can run the following select statement to
query the size of an existing package or procedure
Currently, the maximum parsed/compiled size of a PL/SQL block is 64K and the
maximum code size is 100K. You can run the following select statement to
query the size of an existing package or procedure
How can I protect my PL/SQL source code?
How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for
PL/SQL programs to protect the source code. This is done via a standalone
utility that transforms the PL/SQL source code into portable binary object
code (somewhat larger than the original). This way you can distribute
software without having to worry about exposing your proprietary algorithms
and methods. SQL*Plus and SQL*DBA will still understand and know how to
execute such scripts. Just be careful, there is no "decode" command
available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.yyy
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for
PL/SQL programs to protect the source code. This is done via a standalone
utility that transforms the PL/SQL source code into portable binary object
code (somewhat larger than the original). This way you can distribute
software without having to worry about exposing your proprietary algorithms
and methods. SQL*Plus and SQL*DBA will still understand and know how to
execute such scripts. Just be careful, there is no "decode" command
available.
The syntax is:
wrap iname=myscript.sql oname=xxxx.yyy
Subscribe to:
Posts (Atom)
