i am trying to make a procedure that takes table name as an IN PARAMETER, and then returns me the DDL creation code, i have couple of issues i am trying to tackle:
1- how to bind an IN PARAMETER TO a table name from ALL_TAB_COLS
2- how to specify the DDL object of ONLY the create table syntax (e.g Create table emps;)
3- how to return the code into an OUT parameter, and how to specify the length size
p.s i am working with TOAD for oracle
and this is the code i worked with
CREATE OR REPLACE PROCEDURE GET_TABLE_DDL
(P_TABLE_NAME VARCHAR2,
P_DDL_CODE OUT VARCHAR2 )
AS
CURSOR C1 IS SELECT (DBMS_METADATA.GET_DDL('TABLE',P_TABLE_NAME,'APPS')) FROM ALL_TAB_COLS WHERE TABLE_NAME=P_TABLE_NAME;
BEGIN
FOR I IN C1
LOOP
SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','TABLE_NAME','APPS')) into P_DDL_CODE FROM all_tab_cols ;
P_DDL_CODE:= ((TO_CHAR ((DBMS_METADATA.GET_DDL('TABLE',P_TABLE_NAME,'APPS')))) || I.TABLE_NAME );
END LOOP;
DBMS_OUTPUT.PUT_LINE(P_DDL_CODE);
END;
DECLARE
V_DDL_CODE VARCHAR2(10000);
BEGIN
GET_TABLE_DDL('EMPLOYEES',v_ddl_code);
END;
--here i was testing if the code works outside the procedure , but it gives me HUGE CLOB DATA, and i need it to return the full output
SELECT TO_CHAR(DBMS_METADATA.GET_DDL('TABLE','EMPLOYEES','APPS')) AS DDL_EMPLOYEES FROM all_tab_cols;
SELECT DBMS_LOB.SUBSTR('EMPLOYEES', LENGTH ( 4000)) FROM DUAL;
select dbms_metadata.get_ddl('FUNCTION','RETURN_EMPLOYEES','APPS') from dual;
SELECT * FROM ALL_TAB_COLS;