-
What are the file utilit comands used in PL/SQL procedures?
samareshp
- Aug 3rd, 2009
UTL_FILEThe Oracle supplied package UTL_FILE can be used to read and write files that are located on the server. It cannot be used to access files locally, that is on the computer where the client is ...
g_sidhu
- Feb 6th, 2008
With the Oracle-supplied UTL_FILE package, you can read from and write to operating system files. It Provides security for directories on the server through the init.ora file. UTL_FILE Procedures and ...
-
What is the difference between using IS and AS while creating a procedure, function package and package body?
-
What are the cursor attributes used in PL/SQL ?
%ISOPEN - to check whether cursor is open or not % ROWCOUNT - number of rows fetched/updated/deleted. % FOUND - to check whether cursor has fetched any row. True if rows are fetched. % NOT FOUND - to check whether cursor has fetched any row. True if no rows are featched.These attributes are proceeded with SQL for Implicit Cursors...
Lakshmi84
- Jul 31st, 2009
%ISOPEN - To check whether cursor is open or not.Returns true if cursor?or cursor variable is open or not otherwise?false.%FOUND - To check wheteher cursor is found or not. Returns true if fetch re...
samareshp
- Jul 30th, 2009
There are five cursor attributes: %isopen, %found, %notfound, %rowcount and %bulk_rowcount.%isopenWith %isopen it is possible to test whether a cursor was opened: %found%found returns true when the la...
-
Function return more than one value
Hi,My questions is " Can function return more than one value".I have gone through the all answers relevant to my question was been posted earlier. But all answers are confusing me.Few people said directly that function can't return more than one value and few are telling that function can return more that one value using OUT parameter or ref cursor.My understanding is if out can't use as a parameter...
sen_sam86
- Jul 23rd, 2009
Yes this can be possible by TABLE FUNCTION STEP - 1 CREATE TYPE ex_tabl_fun_obj AS OBJECT (emp_name varchar2(20), emp_id NUMBER(2));STEP - 2 CREATE TYPE ex_tabl_fu...
javedans
- Jul 8th, 2009
Its a universal truth that function can return only one value, but there are some tricks by using that you can get more than one value , while function returning only one value.When you define functio...
-
SQL Query Tuning Considerations
While tuning SQL query what should be taken into consideration?
edarasatish
- Jun 23rd, 2009
During the SQL tuning, there couple of things need to be considered1. If it is below Oracle 9i we need make sure about the driving table concept (Since it uses RBO - Rule Based Optimization), which im...
-
What is the purpose of NVL2?
r.kothandaraman
- Jun 13th, 2009
We can set all the null value columns to some other values after that we can get all the corresponding rows and columns of the table.
mala321
- Dec 24th, 2007
Hello,NVL2 function is used to substitute a value for an expressionNVL2(string, value_if _not_null_value, value_null_value) if the string contains a null vlaue ...
-
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
PL/SQL Interview Questions
Read Best Answer
Ans