-
What is p-code and sourcecode ?
grajeshkumar82
- Jun 9th, 2009
PCode is the parsed Code of the Procedure which is ready forexecution... This is the code that is loaded into the shared pool andexecuted for you.
g_sidhu
- Feb 1st, 2008
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
What is the rollforward in pl/sql
goelumesh
- Jul 1st, 2008
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...
-
Return statement and OUT Parameters
What are Return Statement and OUT Parameter in PLSQL Function?
Database Steve
- May 20th, 2009
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.
KD09714
- Jun 28th, 2008
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...
-
Why Functions are used in oracle ?Can Functions Return more than 1 values?Why Procedures are used in oracle ?What are the Disadvantages of packages?What are the Global Variables in Packages?
ravivenkat1234
- Apr 28th, 2009
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...
krishnaindia2007
- May 7th, 2008
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...
-
Explain, Is it possible to have same name for package and the procedure in that package.
bhushan_nemade
- Feb 26th, 2007
Yes, its possible to have same name for package and the procedure in that package.
-
What is an Exception ? What are types of Exception ?
Exception is the error handling part of PL/SQL block. The types are Predefined and user defined. Some of Predefined exceptions are. CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX NO_DATA_FOUND TOO_MANY_ROWS INVALID_CURSOR ...
samareshp
- Apr 17th, 2009
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...
Lavanya Chowdary
- May 6th, 2007
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...
-
Procedure Parameters
What is call by value and call by reference in parameters (IN, OUT, INOUT)?
Read Best Answer
Editorial / Best Answer
lisha.varghese- Member Since Nov-2008 | Nov 17th, 2008
Three types of parameter modes
1) 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 mode -This mode is used to return a value to the main block.The value of OUT parameter can change anywhere in the program.
3)IN OUT parameter mode-This mode is used to pass values to the calling module and return a value to the main block.The value of IN OUT parameter can change anywhere in the program.
In Call By value ,the copy of actual parameter is passed to the formal parameter,So any changes to the formal parameter doesn't affect the actual parameter.
In Call By reference,the address of actual parameter is passed to the formal parameter,so any changes to the formal parameter will change the actual parameter also,because both of them are pointing to the same memory location.
Here no copying is required.
The IN parameter is passe by reference,so we can't change the value of IN parameter inside the module,It acts as a constant,But the OUT and IN OUT parameters are passed by value,we can change the values of OUT & IN OUT paremeterssamareshp
- Apr 17th, 2009
In Parameter:: call by reference OUT Parameter: call by valueIN OUT Parameter: call by valueif you use the hint NOCOPY with OUT Parameter and IN OUT Parameter then ::: call by reference
lisha.varghese
- Nov 17th, 2008
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...
-
Can i write plsql block inside expection
Rupjit Roy
- Feb 23rd, 2006
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 ...
-
What are the restrictions on Functions ?
samareshp
- Apr 15th, 2009
Function having a return type as Boolean ,can't be used in select statement.
krishnaindia2007
- May 7th, 2008
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...
-
Auto Commit
When we insert new values into the table using loops. Is it going to be auto commit or do we need to commit manually?
chaitra murthy
- Mar 19th, 2009
All the DML statement requires manual commit where as DDL statements are autocommitted.
-
Without closing the cursor, If you want to open it what will happen. If error, get what is the error?
ratna82
- Oct 10th, 2008
CURSOR_ALREADY_OPEN ORA-06511 An attempt was made to open a cursor that was already open.
-
Debug PL SQL
How can we generate debugging output from PL/SQL?
KD09714
- Jun 28th, 2008
Stshekhar is right, oracle supplied package DBMS_OUTPUT with procedures PUT, PUT_LINE, NEW_LINE etc in case of debugging pl/sql
-
About dbms_output.put_line( ) package
What is the maximum size of the message that we can give in dbms_output.putline();
jp_valapad
- Aug 5th, 2008
255 bytes was the size limits of earlier version of oracle..ie prior to 10 g ..in 10g 32767 byte is the limit..
-
Passing the parameter to TOP keyword
Hi All,I want create a stored procedure by passing the parameter to top nth recordfor Excreate proc test1(@x integer)asbeginselect top @x empid from emp order by empid descendwhen iam executing iam getting error .Please let me know any other alternative way
shruti.potdar
- Dec 10th, 2008
Solution to your problem:Create Procedure TEST1( p_num number)isv_eid number;begin?? select empid into v_eid from EMPwhere empid=(select A.empid from (SELECT empid, rank() over (order by empid desc) RNK FROM emp) A where A.RNK=p_num);dbms_output.put_line('Top'|| p_num || 'emp is ::'|| v_eid);End;/
xambrose
- Jul 19th, 2008
create proc test1(@x integer)asbeginSELECT empid FROM (SELECT empid FROM emp ORDER BY empid ) WHERE ROWNUM = @x;end
-
What is PRAGMA RESTRICT_REFERENCES:
saishradha
- Dec 4th, 2008
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.ThanksJai Sainath
Kullai Naidu
- Mar 5th, 2007
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
-
What is the difference between In, Out, InOut Parameters. Can we pass value or reference or both to the In Out Parameter.
saishradha
- Nov 27th, 2008
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...
raghav_sy
- Jan 15th, 2007
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...
-
BulkBind
What Is BulkBind? When it is used?
saishradha
- Nov 27th, 2008
Bulk Bind is the facilty through which we can INSERT, UPDATE, DELETE or SELECT from records from a table in bulk rather than multiple individual statments..Suppose you have 10 inserts to be made so yo...
-
How do i write a function that returnsmore than 1 records from a tablefor example in sql server there is a solution as create function f1() return table asselect * from emphow do i do the same in oracle. plz help
rajivgupta780184
- Nov 24th, 2008
If we want to return multiple row from a function then we can use ref cursor function .Example is given below: create or replace function f1 return sys_refcursor as r1 sys_refcursor;begin open r1 for ...
pardhu323
- Jul 20th, 2008
create or replace function f1 return sys_refcursor as r1 sys_refcursor;begin open r1 for select * from emp; return(r1); end;call the funtion as :select f1 from dual;then it returns :CURSOR STATEMENT :...
-
Send Email from Oracle Procedure
How to send Email via PL/SQL Procedure?
rajivgupta780184
- Nov 24th, 2008
rem -----------------------------------------------------------------------rem Filename: smtp.sqlrem Purpose: Send e-mail messages from PL/SQLrem Notes: ...
-
PL/SQL Interview Questions
Read Best Answer
Ans