PL/SQL (MOSC)

MOSC Banner

Wrapping DBMS_METADATA.GET_DLL in a Stored Proc

edited May 26, 2013 2:06AM in PL/SQL (MOSC) 4 commentsAnswered
Hi,I'm trying to create stored procs that manages partitions automatically every months. 

 I'm trying to get the DDL of tablespaces so I can create the new ones for the next partition.


CREATE OR REPLACE FUNCTION DBATABLES.GENERERTABLESPACESCRIPT(P_EXISTING_TBSPACE IN VARCHAR2, P_TBSP_PREFIX IN VARCHAR2, P_TBSPTABLE IN VARCHAR2)
RETURN VARCHAR2 
IS
V_TBSP VARCHAR2(2000);
BEGIN 

    V_TBSP := REPLACE(UPPER(DBMS_METADATA.GET_DDL('TABLESPACE',P_EXISTING_TBSPACE)),'"','');
    V_TBSP := SUBSTR(V_TBSP,1,INSTR(V_TBSP,'ALTER')-1);
    V_TBSP:= REPLACE(V_TBSP,P_EXISTING_TBSPACE,P_TBSPTABLE);

    RETURN V_TBSP;
END;
/

This fail with ORA-36103 erreur saying that tablespace something is not in schema something... Which is true but tablespaces don't have Schema.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center