Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL variable in a cursor as partition name? Oracle 8.1.7
In article <GFLsIo.600_at_news.boeing.com>, "Tim says...
>
>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 table.
>
>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 vPartitionName with
>MODEL_757 that I pass in when I execute the procedure. What am I doing
>wrong? If I hardcode the partition name it works fine.
>
>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?
>
>Thanks in advance! Timothy.D.Lindsey_at_Boeing.com
>
>
You need to use dynamic sql when you want to change an identifier in the query at runtime. It would look like this:
ops$tkyte_at_ORA815> CREATE TABLE t
2 ( id int,
3 data varchar2(25)
4 )
5 PARTITION BY RANGE (id)
6 (
7 PARTITION part_1 VALUES LESS THAN(2), 8 PARTITION part_2 VALUES LESS THAN(3), 9 PARTITION part_3 VALUES LESS THAN(4), 10 PARTITION part_4 VALUES LESS THAN(5)11 )
Table created.
ops$tkyte_at_ORA815> insert into t values ( 1, 'hello' );
1 row created.
ops$tkyte_at_ORA815> insert into t values ( 2, 'world' );
1 row created.
ops$tkyte_at_ORA815> insert into t values ( 3, 'good' );
1 row created.
ops$tkyte_at_ORA815> insert into t values ( 4, 'bye' );
1 row created.
ops$tkyte_at_ORA815>
ops$tkyte_at_ORA815> create or replace procedure p( p_part_name in varchar2 )
2 as
3 type rc is ref cursor;
4
5 l_cursor rc; 6 l_rec t%rowtype; 7 begin 8 open l_cursor for 9 'select * 10 from t partition ( ' || p_part_name || ' )'; 11 12 loop 13 fetch l_cursor into l_rec; 14 exit when l_cursor%notfound; 15 dbms_output.put_line( l_rec.id || ',' || l_rec.data ); 16 end loop; 17 close l_cursor;
Procedure created.
ops$tkyte_at_ORA815>
ops$tkyte_at_ORA815> exec p('part_1')
1,hello
PL/SQL procedure successfully completed.
ops$tkyte_at_ORA815> exec p('part_2')
2,world
PL/SQL procedure successfully completed.
ops$tkyte_at_ORA815> exec p('part_3')
3,good
PL/SQL procedure successfully completed.
ops$tkyte_at_ORA815> exec p('part_4')
4,bye
PL/SQL procedure successfully completed.
-- Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/ Expert one on one Oracle, programming techniques and solutions for Oracle. http://www.amazon.com/exec/obidos/ASIN/1861004826/ Opinions are mine and do not necessarily reflect those of Oracle CorpReceived on Wed Jun 27 2001 - 15:15:06 CDT