Wrapping DBMS_METADATA.GET_DLL in a Stored Proc
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.
0