Re: PL/SQL variable in a cursor as partition name? Oracle 8.1.7

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 27 Jun 2001 13:15:06 -0700
Message-ID: <9hdesa0ktc@drn.newsguy.com>

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 )
 12 /

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;

 18 end;
 19 /

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 Corp 

Received on Wed Jun 27 2001 - 15:15:06 CDT