Re: partitions and cursors

I answered my own question. The answer is yes you do have to use dynamic sql.

In article <96embj0ihg_at_drn.newsguy.com>, Dan says...
>
>Hello,
>
>
>I have a cursor that I need to dynamically pull the last partition from a
>large(270million rows) table m,y test procedure looks like the following
>do I have to use dynamic sql for this ?? i'm getting the following error
>
>error: ORA-02149: Specified partition does not exist
>
>
>
>PROCEDURE TEST3
>IS
>CURSOR cur_test_part(vc_partition IN varchar2)
>IS
>SELECT *
> FROM pclk_src.session_detail PARTITION (vc_partition)
> WHERE ROWNUM < 10;
>
>vc_partition_1 varchar2(50);
>BEGIN
>SELECT MAX(partition_name)
> INTO vc_partition_1
> FROM dba_tab_partitions
> WHERE table_name ='SESSION_DETAIL';
>dbms_output.put_line(vc_partition_1);
>
>FOR rec_test_part IN cur_test_part(vc_partition_1) LOOP
> dbms_output.put_line('xxx');
>END LOOP;
>EXCEPTION
>WHEN OTHERS THEN
> DBMS_OUTPUT.PUT_LINE('ERROR: '||SQLERRM);
>END;
>
>thanks in advance
>
>Dan White
>
>Dan White
>programmer/analyst
>

Dan White
programmer/analyst Received on Tue Feb 20 2001 - 15:05:45 CST