 Print
 Add To Favorites
 Email To Friend
 Rate This Article
|
CodeSnip: Using Dynamic Cursor in a Procedure Using Oracle 9i
|
by Deepankar Sarangi
Feedback
|
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days):
22439/
39
|
|
|
|
| Code |
Listing 1
CREATE OR REPLACE
PROCEDURE referenced_cursor_p (in_table_nm IN VARCHAR2, in_job_name IN VARCHAR2)
as
TYPE CUR_TYPE IS REF CURSOR;
managers CUR_TYPE;
manager_name VARCHAR2 (20);
sql_stmt VARCHAR2 (200);
job_name VARCHAR2 (20):=in_job_name;
BEGIN
/*Use of cursor in selecting the SALES Guys*/
sql_stmt :=
'SELECT distinct ENAME FROM '||in_table_nm||' WHERE JOB ='''||job_name||'''';
dbms_output.put_line('Use of cursor in selecting the Managers');
/*Opened the ref cursor where the table name is passed dynamically through a string*/
Open managers for sql_stmt;
loop
FETCH managers INTO manager_name;
EXIT WHEN managers%NOTFOUND;
dbms_output.put_line(manager_name||' is a Manager');
end loop;
END referenced_cursor_p; |
|
|
|
User Comments
Title:
Auther
Name:
Deepankar Sarangi
Date:
2009-06-13 2:45:20 AM
Comment:
Hi Andrew,
In case you do not know the column(s) name in advance and wish to pass the column name dynamically just like the table name and job name you have to pass another in parameter carrying the column name.
Then your dynamic query would look as given below.
sql_stmt := 'SELECT distinct '||in_column_nm||' FROM '||in_table_nm||' WHERE JOB ='''||job_name||'''';
plz let me know in case any issue.....
|
Title:
listing Table Contents
Name:
Andrew Snowball
Date:
2009-06-11 7:01:29 PM
Comment:
But what if you don't know the columns in the table(s).
This example only works if you know that whatever table you use will always contain a column named "ename"...
I need to extract data without knowing the columns.
~AS.
|
Title:
Passing Table from .Net
Name:
Jegan
Date:
2008-05-30 1:42:32 AM
Comment:
Hi Deepankar Sarangi
This is goood,now i wanted to pass datatable from .Net to oracle stored procedure.
Pls reply
Jegan
|
|
|
|
|
|
