CodeSnip: Using Dynamic Cursor in a Procedure Using Oracle 9i
page 3 of 4
by Deepankar Sarangi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 22981/ 35
Article Contents:


Here in the code section we have tried to select records from a table called EMP through a ref cursor. The string that selects the records for the cursor has been dynamically built up as per the choice of table name and the selection criteria.

This kind of cursor declaration is not possible with the knowledge of simple cursor where especially the table name is either not known or cannot be hard coded due to business requirements.

Also, this kind of cursor declaration makes the code reusable and more efficient.

Execution output

Figure 1

By applying a different table name with the kind of selection criteria we can fetch a different set of records using the same procedure.

View Entire Article

User Comments

Title: Auther   
Name: Deepankar Sarangi
Date: 2009-06-13 2:45:20 AM
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
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.

Title: Passing Table from .Net   
Name: Jegan
Date: 2008-05-30 1:42:32 AM
Hi Deepankar Sarangi

This is goood,now i wanted to pass datatable from .Net to oracle stored procedure.
Pls reply


Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2024  |  Page Processed at 2024-02-22 10:12:06 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search