GeekInterview.com
  I am new, Sign me up!
 
GeekInterview.com  >  Tech FAQs  >  PL/SQL
Go To First  |  Previous Question  |  Next Question 
 PL/SQL  |  Question 83 of 166    Print  
Oracle referance cursor code
Hi,
1. Iam looking for sample program for ref cursor. ref cursor can be associated with many select statements and we can use the ref cursor to return the multiple value from the procedure or function. how to use the ref cursor in a program to associate with many select statements.

2. we can't use the %rowtype attribut to return the multiple from the procedure or function instead of ref cursor.
if we can use the %rowtype attribute then what is difference between ref cursor and %row type attribute.


  
Total Answers and Comments: 1 Last Update: March 25, 2007     Asked by: ily_saravanan 
  
 Sponsored Links

 
 Best Rated Answer
—

No best answer available. Please pick the good answer available or submit your answer.
March 25, 2007 13:45:50   #1  
krishnamohan_geek Member Since: March 2007   Contribution: 2    
RE: Oracle referance cursor code
Hi
Let me answer your second questin first. Why ref cursor instead of ROWTYPE.
ROWTYPE is used for fetches record/row of a single table.
OR in case of columns of different tables then you have to create a RECORD and then used <recordname> ROWTYPE.

But if you have a cursor which gets data dynamically based on some input parameters and you would like to fetch the record/row then you cannot go for rowtype instead you need a REF CURSOR.
REF CURSORS are used when the query in the cursor needs to be changed dynamically.
Say in a cursor query
CURSOR cur_emp IS
SELECT emp_id emp_name emp_address
FROM <tablename>;

say if the logged in used is a employee then in the above query <tablename> EMP and if the logged in user is a admin then <tablename> ADMIN

this can be acheived only through REF CURSOR.

SO in the proc DECLARE section use

TYPE cur_ref IS REF CURSOR cur_emp ROWTYPE;

v_refcur cur_ref TYPE;

BEGIN

text_query : 'SELECT emp_id emp_name emp_address'
IF (logged_user 'EMP') THEN
text_from : 'FROM EMP';
ELSE
text_from : 'FROM ADMIN';
END IF;

OPEN cur_emp FOR (text_query || ' ' || text_from)
LOOP
{
write the code
};
END LOOP;

For futher clarification on the syntax and examples of the code please refer to any PL/SQL text book.

Rgds
Krishna


 
Is this answer useful? Yes | No

 Related Questions

Latest Answer : Cursor is a private Sql area which is used to execute sql statements and store Processing information, Where as Refcursor is a data structure which points to an object which inturn points to memmory LocationThe advantage Refcursor over Normal Cursor is1.We ...
Latest Answer : By using cursor parameters we can pass the value dynamically. So that cursor can fetch data as per parameter ...
Read Answers (4) | Asked by : Kishorebabukm
What is cursor? Explain the types of cursor and write one implicit cursor program to check how many records deleted from table?
Read Answers (6) | Asked by : indranil
Latest Answer : After compiling procedures,functions and triggers(in new versions only),Those will be stored in the form of p-code(after parsing) in SGA(System Global Area).The advantage of p-code is Since it was there in SGA which can be shared by all,Whenever ...
Read Answers (1) | Asked by : richa
Hi, in Cursors this is the Scenario.if i am manipulating the data of a table which has 10 records,i need to go to the 5th record.how is it possible?What is the Exact usage of Ref Cursor?
Read Answers (1) | Asked by : murthy
Latest Answer : %type will use for to define at column level.%rowtype is used to define for one or more than one columns ( like row or record or tuple ) means its user define type which hold one or more than one columns along with it. and generally it used with Cursor. ...
Latest Answer : Ref cursor is like a pointer to the cursor variable which is opened at the servecr side and pass the data as a whole and not one by one . ...
Latest Answer : Yes you can pass the whole select statement of the cursor dynamicaly, see ref_cursors.There is also a system package sys.dbms_sql which allows everything to make dynamically. ...
Read Answers (5) | Asked by : sweety
Latest Answer : Yes we can relate two tables from 2 diff usersE.g. if user A has table Ta  and User B has Table Tb  and Table Tb is child table and needs foreign key on A.Ta  then we need to grant "references" object privillege to User B on table ...
Read Answers (1) | Asked by : Shankar
Latest Answer : Two types of trigger are there1. Row level trigger2. Statement level trigger ...
Read Answers (7) | Asked by : GIRISH H.R.

 Sponsored Links

 
Related Articles

Querying Data with Oracle XQuery

Querying Data with Oracle XQuery Starting with Oracle Database 10g Release 2 you can take advantage of a full featured native XQuery engine integrated with the database With Oracle XQuery you can accomplish various tasks involved in developing PHP Oracle XML applications operating on any kind of dat
 

Using Oracle XML DB Repository

Using Oracle XML DB Repository Another variation on accessing and manipulating XML content stored in Oracle database is provided by Oracle XML DB repository which is an essential component of Oracle XML DB mosgoogle NOTE Oracle XML DB repository also known as XML repository is a hierarchically organ
 

Using Oracle Database for Storing, Modifying, and Retrieving XML Data

Using Oracle Database for Storing Modifying and Retrieving XML Data With Oracle XML DB you have various XML storage and XML processing options allowing you to achieve the required level of performance and scalability One of the most interesting things about Oracle XML DB is that it allows you to per
 

XML Processing in PHP and Oracle Applications

Processing XML in PHP Oracle Applications As mentioned there are two alternatives when it comes to performing XML processing in your PHP Oracle application You can perform any required XML processing using either PHP s XML extensions or PEAR XML packages or Oracle s XML features mosgoogle In the fol
 

PHP Oracle Web Development

PHP Oracle Web Development Data processing Security Caching XML Web Services and Ajax The book is written by Yuli Vaseliev a well known author of different web development and programming books PHP Oracle Web Development Data processing Security Caching XML Web Services and Ajax is a good starting b
 

Getting Started with Oracle and ODP.NET

ODP NET Developer&rsquo; s Guide by Jagadish Chatarji Pulakhandam Sunitha Paruchuri A practical guide for developers working with the Oracle Data Provider for NET and the Oracle Developer Tools for Visual Studio 2005 Application development with ODP NET Dealing with XML DB using ODP NET Oracle
 

PHP Oracle Web Development Review

PHP Oracle Web Development Data processing Security Caching XML Web Services and Ajax The book is written by Yuli Vaseliev a well known author of different web development and programming books The author is also an expert in open source technologies and SOA Service Oriented Architecture But besides
 

jQuery Table Row Finished Code

jQuery Table Row Finished Code The Finished Code Our second example page has demonstrated table row striping highlighting tooltips collapsing expanding and filtering Taken together the JavaScript code for this page is mosgoogle geshibot lang php&quot; document ready function var highlighted
 

jQuery Interacting with Other Code

jQuery Interacting with Other Code We learned with our sorting and paging code that we can t treat the various features we write as islands The behaviors we build can interact in sometimes surprising ways; for this reason it is worth revisiting our earlier efforts to examine how they coexist with t
 

jQuery Completed sorting and paging code

Learning jQuery The Finished Code The completed sorting and paging code in its entirety follows mosgoogle geshibot lang php&quot; fn alternateRowColors function tbody tr odd this removeClass even addClass odd ; tbody tr even this removeClass odd addClass even ; return this; ; document
 

About Us -  Privacy Policy -  Terms and Conditions -  Contact -  Ask Question -  Propose Category -  Site Updates 

Copyright © 2005 - 2009 GeekInterview.com. All Rights Reserved

Page copy protected against web site content infringement by Copyscape