PL/SQL Interview Questions

Spaces will be padded with y variable,its means y will store y='sudhir              ' . while x only store 6 six character...

ans is both values are not equal  char(20)='name' covered in memorey areea is 20 but varchar2(20)='name' covered in memorey areea is 4 becoze char is fixed lenth char data type but varchar2 is variable lenth char data type

PCode is the parsed Code of the Procedure which is ready for
execution... This is the code that is loaded into the shared pool and
executed for you.

Source code is the Text of the procedure.It is accessed from USER_SOURCE data dictionary view.

P-code is Compiled object code which is not accessible.

Rollforward is to Redo, as Rollback is to undo.

Roll forward refers to the process Oracle goes through to apply changes contained in the redo log files (both online and archive). The database clock (as measured by the system change number) is moved...

Functions always return a value.

Procedures do not.

The use of IN, OUT, and IN_OUT parameters as scalar values or ref cursors is dependent on your need and overall performance not on a pseudo-standard as answered above.

Function must have return statement by which it returns one value.Though we can use out parameter in function(function not getting called from select statement or DML), it is not good programming prac...

Functions can have multiple out Parameters one with Return Parameter and others can be IN OUT Parameter to the function. this way a function can return multiple out parameters.Eg : Function ( a in num...

1.Functions are used to computer a value.2.Function can return more than one value using out parameter. But it is not a good practice to return more than one value in functions.3.Proced...

yes its possible.

Yes, its possible to have same name for package and the procedure in that package.

Hi all, Exception is nothing but Error. Exception can serve as an ALERT message also. (using RAISE_APPLICATION_ERROR)There are two types of exceptions: 1> Pre-defineddefine exception (2&g...

Exception is nothing but error in the PL/SQL program. If any error occured in the PL/SQL program that terminates from the program. To handle that exceptions we are using exception handling part in the...

In Parameter:: call by reference
OUT Parameter: call by value
IN OUT Parameter: call by value


if you use the hint NOCOPY  with OUT Parameter and IN OUT Parameter then ::: call by reference

Three types of parameter modes1) IN parameter mode- This mode is used to pass values to the calling module when invoked.The value of IN parameter can't be changed in the module.2) OUT parameter mo...

Sure, you can write plsql block inside exception.

Yes you can write PL/SQL block inside exception section. Suppose you want to insert the exception detail into your error log table, that time you can write insert into statement in exception part. To ...

Function  having a return type as Boolean ,can't be used in select statement.

In functions we can use all three types of parametershere is a simple exampleCREATE OR REPLACE FUNCTION TEST_FUNC(A IN NUMBER, B IN OUT NUMBER,C  OUT NUMBER)  RETURN NUMBER IS   &n...

Error will be like "Cursor already open"

CURSOR_ALREADY_OPEN ORA-06511 An attempt was made to open a cursor that was already open.

Use "SHOW ERRORS" command.

Regards,
Karthik

stshekhar is right,

oracle supplied package DBMS_OUTPUT with procedures PUT, PUT_LINE, NEW_LINE etc in case of debugging pl/sql

The maximum size of package is 32k
We can't give  more than that

255 bytes was the size limits of earlier version of oracle..ie prior to 10 g ..in 10g 32767 byte is the limit..

Pragma RESTRICT_REFERENCES is a compiler directive.
It makes sure that the function maintains the purity rules and code in fuction creation satisfy the purity rules.
There are 4 values for it.
WNDS,RNDS,WNPS,RNPS.

Thanks
Jai Sainath

By using pragma_restrict_references we can  give the different status to functions,Like WNDB(WRITE NO DATA BASE),RNDB(read no data base),Write no package state,read no packge state.W

Here is the answer in 1 lineIN-- value is used in a program.       It cannot be a variable .COuld be literal,expression,value .OUT-- value is returned back from the progr...

hi friends,To make it simple, keep in mind,IN : It is a CONSTANT in the sub-program and u can not modify its value in sub-      program. if its value is modified in the s...

Ans : A & D

A and D

You cannot use the database name before the table name like db1.t1.we can only use a user name and then table name. eg. let we have a user "deepak" then we can write select * from deepak.t1 .  We ca...

Type schema name before table name or your id dont have the access to view the data from that table

Yes the order is important. But it is important for the performance of the query and not for the final result. The output of the query will be the same irrespective of the order in which the join con...

Hi,

Where Clause conditions execution are independent of order in explain plan but it reads top to bottom.

Thanks,
Gunjan

First, let us understand why these errors cropup.1) NO_DATA_FOUND - is raised only for "select into" statements when  the where clause of the query does not match any rows.2)TOO_MANY_rows - is raised...

If you don't want an excpetion to be raised on a SELECT statement, you can avoid executing the statement by having a count just before the SELECT as shown belowSELECT count(rowid)into v_count from...

Unlike tables ref cursors do not store data. They act like mold to hold data during the execution of the piece of code so proper coding and use of bind variables can help decrease the burden of proces...