Posted by 'jaykumar' on June 29, 2001 at 10:03:45 EST:
In Reply to: PL/SQL varible into Cursor? Oracle 8.1.7 posted by 'Tim Lindsey' on June 27, 2001 at 12:30:00 EST:
: We are using Oracle 8.1.7 and I would like to pass a variable to a PL/SQL procedure and have that variable used in a cursor. The cursor is extracting from a partitioned dataset.
: CREATE OR REPLACE PROCEDURE PR_TEST (vPartitionName in VARCHAR2)
: IS
: CURSOR c_SmallTable
: IS
: SELECT model,
: pn_id
: FROM BAMB.TA_TABLE1 partition (vPartitionName);
: When I run this in my procedure it doesn't replace vPartition name with MODEL_757 that I pass in when I execute the procedure. What am I doing wrong?
: I have Steven Feuerstein's "Oacle PL/SQL" book and in the section on cursor variables he seems to be doing the same thing except the variable is being used as a selected field or on the right of the equal sign on the WHERE clause. Why won't it work for a partition name?
Instead of using explicit normal cusor you can use ref cursor or
execute immediate.
if you are using ref cursor the statement is like this
1. first you declare ref cursor.
2. open the ref cursor dynamically.
vl_sql_stmt := 'SELECT model,pn_id
FROM BAMB.TA_TABLE1 partition ('||vPartitionName||')';
open cu_tname for vl_sql_stmt ;
use fetch and other your processing steps.
If the above statement contains one row you can use execute immediate statement.