-
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...
-
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...
-
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.
-
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 ...
-
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...
-
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...
-
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...
-
What is the difference between using IS and AS while creating a procedure, function package and package body?
-
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 ...
-
Handle Multiple Rows Without Loops
How can we handle multiple rows without using loops in PL SQL?
sen_sam86
- Aug 18th, 2009
This can be done by a function called TABLE FUNCTION by means of this you can return more number of rows and columns this is more effective than using ref cursorregards sen-prakash
rajivgupta780184
- Oct 31st, 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 fo...
-
Retriving Multiple Records
Why PL/SQL does not support retriving multiple records?
Read Best Answer
Editorial / Best Answer
promisinganuj- Member Since Sep-2009 | Sep 13th, 2009
Retriving multiple records is a basic feature of SQL where we fetch the data using SELECT statement.
SELECT *
FROM emp;
This itself gives us multiple records. There is no additional feature required to do the same in PL/SQL.
At the same time, PL/SQL goes one step further and enable us to process these fetched rows one by one (unlike the update statements which updates all the rows selected).
There are several options for fethcing data in different PL/SQL constructs:
1. Using Ordinary Cursor
2. Using Ref Cursor
3. Using PL/SQL bulk collect
4. Using PL/SQL arraysBut the important thing here is that irrespective of the way we fetch the data, we act upon them row-by-row.
promisinganuj
- Sep 13th, 2009
Retriving multiple records is a basic feature of SQL where we fetch the data using SELECT statement.SELECT * FROM emp;This itself gives us multiple records. There is no additional feature requir...
Mad Hatter
- Nov 4th, 2008
Multiple records at a time could be retreved in PL/SQL using BULK COLLECT.To do that you define PL/SQL table, and load it using SELECT ... BULK COLLECT INTO <pl/sql table>FROM ..Optionaly could ...
-
Indexed Select Statement
How can we find the select statement is indexed or not?
Read Best Answer
Editorial / Best Answer
promisinganuj- Member Since Sep-2009 | Sep 13th, 2009
1. using AUTOTRACE
SQL> SET AUTOTRACE ON TRACEONLY
SELECT *
FROM emp
WHERE emp_name = 'ABC';
2. Using EXPLAIN PLAN
SQL> EXPLAIN PLAN for
SELECT *
FROM emp
WHERE emp_name = 'ABC';
The advantage of using EXPLAIN PLAN over the AUTOTRACE is that former does not require the query to be actually run. The TRACEONLY option in AUTOTRACE just supress the query ouput but the query still runs before the explain plan is displayed.promisinganuj
- Sep 13th, 2009
1. using AUTOTRACESQL> SET AUTOTRACE ON TRACEONLYSELECT * FROM emp WHERE emp_name = 'ABC';2. Using EXPLAIN PLANSQL> EXPLAIN PLAN forSELECT * FROM...
-
What is PL/SQL ?
PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.
surya72446
- Sep 30th, 2009
PL/SQL stands for procedural languages extensions to SQL and it's theme is to hold a bunch of SQL statements as a single function and return a single output.PL/SQL is advantageous compared to other pr...
g_sidhu
- Feb 16th, 2008
PL/SQL is the procedural extension to SQL with design features of programming languages. Data manipulation and query statements of SQL are included within procedural units of code. PL/SQL offers moder...
-
Referenced and Dependent objects
How oracle manages dependency between referenced and dependent objects ?
johnjerry
- Oct 7th, 2009
We can set REMOTE_DEPENDENCIES_MODE inside init.ora parameter file to TIMESTAMP or SIGNATURE.ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = [TIMESTAMP|SIGNATURE]
YugundharK
- Oct 13th, 2008
Oracle manages dependency between referenced and dependent objects by using 2 modes1. Timestamp mode2. Signature mode
-
Oracle extract records from temporary table
Assume that I am using a temporary table in a procedure and I am inserting records and updating another set of records through Merge statement. If I use cursor in that temporary table, How can I extract all of the records from temporary table at the end of the stored procedure. Can you please give coding for this.
johnjerry
- Oct 7th, 2009
Make sure you do not COMMIT* in the middle of the procedure. You can do any number of insertion into and deletion from temporary table and use "Select * from temp_table" to fetch a...
priyanka jolly
- Nov 24th, 2007
Create Global temporary tables and say on commit preserve rows. The global temporary tables have data available till the session is active. So you if you insert it at one procedure and want to access it somewhere else you can do that very easily till the session is activeThanks Priyanka
-
What is the difference between all_ and user_ tables ?
pawan_kr2007
- Oct 11th, 2009
ALL_OBJECTS: Objects accessible to the user.USER_OBJECTS: Describes all objects owned by the current user.DBA_OBJECTS: Describes all objects in the database.
krishnaindia2007
- May 7th, 2008
The information available in data dictionary tables is very difficult to understand. So it provides views in a form that is easily? understood by users. All these views are owned by sys.Oracle provid...
-
What can be the Maximum size of a plsql block?
jabir.mkk
- Oct 16th, 2009
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. SQL> select * from dba_object_size where name = 'procedure_name'
-
When we need to use USING clause in the sql?For example in this below:SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id) JOIN locations l USING (location_id) WHERE salary > 10000; Can anyone explain what is mean of USING in this sql statement? or USING concept in brief? Thank you.
hmounir
- Nov 17th, 2009
In natural joins USING clause can be used to specify only those culomns that should be used for an equijion
ravikumarzee@gmail.com
- Mar 9th, 2007
USING is also used while executing Dynamic SQL.e.g.,EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id;
-
Why we use instead of trigger. what is the basic structure of the instead of trigger. Explain specific business reason of it's use
berly.george
- Dec 1st, 2009
Instead of triggers are used:1) To control the DML operations on Views(Insert/Update/Delete)2) Makes the non updatable views updatable
hindol_saha
- Apr 9th, 2007
Conceptually, INSTEAD OF triggers are very simple. You write code that the Oracle server will execute when a program performs a DML operation on the view. Unlike a conventional BEFORE or AFTER trigger...
-
What will be the impact of replacing an API call with a stored PL/SQL call?
godsrikanth
- Dec 3rd, 2009
Difference is in performance. API is a package call which will process faster since its already compiled. It can be called globally. Stored PL/SQL call is not compiled into memory and may take more time.
PL/SQL Interview Questions
Read Best Answer
Ans