Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Partition cursor parameter

732311Apr 11 2011 — edited Apr 12 2011
Hi expert,

I have a problem with a custom procedure
create or replace
PROCEDURE        PRC_PARTITION
AS
	STRFLUSSO	VARCHAR2(50) := 'PRC_PARTITION';
	STRERR		VARCHAR2(255);
	V_PARTITION	VARCHAR2(255);
  V_PARTITION_TRANSACTION VARCHAR2(255);

CURSOR V_CUR (V_PARTITION_CUR VARCHAR2)
  IS
	SELECT NAME FROM CITY PARTITION (V_PARTITION_CUR);

BEGIN
  SELECT max(partition_name) into V_PARTITION FROM user_tab_partitions WHERE table_name = 'CITY';
  
  DBMS_OUTPUT.Put_Line (V_PARTITION);
  FOR I IN V_CUR(V_PARTITION)
    LOOP
    EXIT WHEN V_CUR%NOTFOUND; 
        DBMS_OUTPUT.Put_Line (I.NAME);
    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        STRERR := SUBSTR (SQLERRM, 1, 254);
        dbms_output.put_line('Errore:'||STRERR);       
        rollback;
END PRC_CARING_TRANSACTION;
/


Connecting to the database DBTEST.
SYS_P6209
Errore:ORA-02149: Specified partition does not exist
Process exited.
Disconnecting from the database DBTEST.
But the partition SYS_P6209 exists and I can run this:
SELECT NAME FROM CITY PARTITION (SYS_P6209);
.... what I wrong

cheers,
Lain
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 10 2011
Added on Apr 11 2011
10 comments
1,027 views