-
Is the order in which the conditions are given in the 'WHERE' clause are important?
Mean I heard from Oracle 8i onwards the order of where clause condition does not matter which oracle creates the explain plan...and execute..Is it true?
manoj_behera82
- Aug 7th, 2008
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...
gtomar
- Aug 7th, 2008
Hi, Where Clause conditions execution are independent of order in explain plan but it reads top to bottom.Thanks,Gunjan
-
Call PL/SQL function
How to call a PL/SQL function in another PL/SQL function? Is it possible? Give example?
Jirange
- Nov 7th, 2008
Eg,create or replace fun_1 return varchar2asddmmyy varchar2;beginselect sysdate into ddmmyy from dual;end fun1;in above example function fun_1 calling another function (i.e. sysdate) thro' select statement. this way you can call any function in pl/sql function
-
Suppose, I've created a new database DB1 n i've created a table DB1.T1.Now, DESC T1 --> desplaying the table structure butselect * from DB1.T1 ---->giving--> table or view does not exist.. Can any one explain possible reason behind this.
todeepakmalhotra
- Nov 10th, 2008
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...
oozaifa
- Nov 6th, 2008
Type schema name before table name or your id dont have the access to view the data from that table
-
-
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: ...
-
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 :...
-
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...
-
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...
-
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
-
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
-
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..
-
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
-
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.
-
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.
-
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...
-
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 ...
-
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...
-
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...
-
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.
-
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...
PL/SQL Interview Questions
Read Best Answer
Ans