-
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 ...
-
Find null value in a table
What are all the different ways available to find the null value in a table
-
What is a stored procedure ?
A stored procedure is a sequence of statements that perform specific function.
g_sidhu
- Jan 31st, 2008
A procedure is a named PL/SQL block that can accept parameters (sometimes referred to as arguments), and be invoked. Generally speaking, you use a procedure to perform an action. A procedure has ...
ramesh_resides
- Jun 23rd, 2007
Stored procedure is a sub program and stored in memory for repated execution
-
What are the modes of parameters that can be passed to a procedure ?
IN,OUT,IN-OUT parameters.
KSPRADEEP
- Oct 17th, 2012
Two modes of parameters are available to pass the parameter In and Out, the default parameter is IN. In : Lets you pass a value to subprogram being called.It can not be changed inside the parameter(...
g_sidhu
- Jan 31st, 2008
IN (default): Passes a constant value from the calling environment into the procedureOUT :Passes a value from the procedure to the calling environmentIN OUT :Pa...
-
What is difference between DESCRIBE and DESC please define
ashishdixit
- May 29th, 2008
desc has two meanings and depends on the usage.when used in sql plus as in the below stt.desc <tablename>;it is taken as a sqlplus keyword and describes the table, dispalying column, constraints...
Sebastian Thomas
- Aug 17th, 2007
DESC will display the values in DESCending order when used in an ORDER BY clause.For example:SELECT * FROM empORDER BY empno DESC;
-
FLASHBACK concept ? What is it?
Can any one help me on FLASHBACK concept ? What is it?
-
Can any one explain Perforance Tuning in PL/SQL
neerajalatha
- Jul 5th, 2010
Performance tuning can be improved by using FOR ALL and BULK COLLECT clauses in place of FOR LOOP and CURSORS which makes the application faster and better.
ORA_CRAZY
- Nov 19th, 2009
Using Bulk binding concepts, we can tune the PL/SQL job that takes long time due to millions of records.
-
-
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;
-
What is difference between varray and nested table.can u explain in brief and clear my these concepts.also give a small and sweet example of both these.
krishnaindia2007
- May 7th, 2008
The following are the main differences between nested tables and varray1. Nested tables are unbounded where as varray has maximun limit.2. Nested tables are dense intially but can be sparse latte...
g_sidhu
- Feb 18th, 2008
Nested tables are unbounded. Initially dense but can become sparse through deletions. Order is not preservedCan be indexed VARRAYs are always bounded(varying arrays have a limited number of entr...
-
-
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 pragma? can any one give me the example of autonomous Transaction ?can we change the order of procedure parameter while calling procedure?
g_sidhu
- Feb 18th, 2008
A pragma is compiler directive. Pragmas are processed at compile time, not at run time. They pass information to the compiler.
g_sidhu
- Feb 2nd, 2008
Using Named & Combination Parameter passing Methods you can change the order of parameter in a procedureNamed: List actual parameters in arbitrary order by associating each with its corresponding ...
-
What is the difference between granting and creating a view?
amahanit
- Dec 1st, 2007
grand is to provide privilege on a specific object to a usereg. grand select on emp to uma;well view is a database object which is made from column of one or more tables.if u don't have ...
-
What is difference b/w stored procedures and application procedures,stored function and application function..
g_sidhu
- Feb 4th, 2008
Application procedures or functions: Named PL/SQL blocks stored in an Oracle Forms Developer application or shared library; can accept parameters and can be invoked repeatedly by name and are availabl...
Nishant Patel
- Jun 13th, 2007
The difference between the two can be categorised via: Stored subprograms ans Application subprograms1) Stored subprograms: These are coded and compiled from?within the Oracle database via SQL Plus e...
-
Hi,We have to select some rows from a table using many conditions in the where clause. Then we have to write those selected rows to one flat file. I know that we can use UTL_FILE package to do this. Is there any better approach to do this??
rajivgupta780184
- Aug 12th, 2008
YOU CAN USE TEXT_IO.PUTLINE INBUILTSOR YOU CAN USE THE SPOOL FUNCTION BOTH ARE APPLICABLE
hindol_saha
- Mar 27th, 2007
Spool c:filename.txtselect * from tab where ................;spool off;goto c: and check it.i think it is the easyest way to store some data from oracle to flat file...u can save it in any extension like .csv.xls.txt ect............
-
Hi Friends!! Can anybody answer what are the constraints on Mutating tables? How to remove the mutating errors in triggers? Urgent plzzzzzzzzzzzzzzzzThanks in Advance..Thanks&RegdsRamki,TCS
amahanit
- Oct 13th, 2007
1) make the trigger as a statement level trigger.2) if u want to use :old and :new qualifer then create 2 trigger ,1st before insert trigger and use PL/SQL table to select the values,2nd after insert trigger and insert the values from PL/SQL tables.
-
What is the diff between %Rowtype and %type?
manoj_lanser
- Jul 20th, 2011
%type is used when we have to define the datatype of a variable similar to another existing column data-type. eg- v_id employess.employee_id%type. In this example we have make a variable v_id similar...
Radha
- Jul 14th, 2011
We use %type and %rowtype in PL/SQL programming part. %Type: The variable will assign the datatype which contains in the column of that particular table we mentioned. Ex: v_eno employee.eno%type; ...
-
Hi,Can anyone tell me the difference between instead of trigger, database trigger, and schema trigger?Thanks.
g_sidhu
- Feb 4th, 2008
Triggers on system events can be defined at the database or schema level. A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only whe...
Nasim
- Dec 17th, 2006
Hiif we have created a view that is based on join codition then its not possibe to apply dml operations like insert, update and delete on that view. So what we can do is we can create instead off trig...
-
PL/SQL Interview Questions
Read Best Answer
Ans