AspAlliance.com LogoASPAlliance: Articles, reviews, and samples for .NET Developers
URL:
http://aspalliance.com/articleViewer.aspx?aId=1644&pId=-1
CodeSnip: Using Dynamic Cursor in a Procedure Using Oracle 9i
page
by Deepankar Sarangi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 27904/ 117

Introduction

When the table to be used inside a cursor is not known to the user while creating the procedure, a ref cursor can be used to pass the name of the table dynamically as the input parameter to the procedure. The procedure also serves the purpose of being used again and again for many different tables of similar type as per the business requirement.

Requirements

Oracle database 9i

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;
Analysis

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.

Conclusion

By following the mentioned approach the user can use same procedure multiple times for a different set of tables again and again. The user also does not have to bother about the table name while declaring the cursor.



©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-12-07 1:31:14 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search