Easy DDL extractor:
CREATE TABLE "my_ddl"
( "OBJECT_TYPE" VARCHAR2(30 CHAR),
"OBJECT_NAME" VARCHAR2(30 CHAR),
"EXTRACTED_ON" DATE,
"DDL" CLOB,
"OSUSER" VARCHAR2(100 CHAR)
);
CREATE TABLE "my_ddl_CONF_TABLE_DATA_EXTRACT"
( "TABLE_NAME" VARCHAR2(30 CHAR)
)
/
create or replace
PACKAGE "DDL_EXTRACTOR" AS
/*------------------
Package Version: 0.01
-------------------*/
c_object_order varchar(30000 char) := 'select ''DATABASE LINK'' as object_type, ''01'' as order_id from dual union all
select ''SYNONYM'' as object_type, ''02'' as order_id from dual union all
select ''FUNCTION'' as object_type, ''09'' as order_id from dual union all
select ''INDEX'' as object_type, ''06'' as order_id from dual union all
select ''PACKAGE'' as object_type, ''11'' as order_id from dual union all
select ''PACKAGE BODY'' as object_type, ''12'' as order_id from dual union all
select ''PROCEDURE'' as object_type, ''10'' as order_id from dual union all
select ''SEQUENCE'' as object_type, ''03'' as order_id from dual union all
select ''TABLE'' as object_type, ''05'' as order_id from dual union all
select ''TRIGGER'' as object_type, ''07'' as order_id from dual union all
select ''TYPE'' as object_type, ''04'' as order_id from dual union all
select ''VIEW'' as object_type, ''08'' as order_id from dual ';
c_list_objects varchar(30000 char) := 'select object_name, object_type from user_objects where
(object_type in (''TABLE'', ''DATABASE LINK'', ''FUNCTION'', ''PROCEDURE'', ''PACKAGE'', ''SEQUENCE'', ''VIEW''))
and
(object_type != ''TABLE'' or (object_name not like ''STA%'' and object_name not like ''TMP%''))
union all
SELECT object_name, object_type FROM all_objects WHERE object_type = ''DIRECTORY''
';
procedure export_object(p_object_type in varchar2, p_object_name in varchar2);
procedure INSERT_SCRIPT(p_table_name varchar2) ;
function get_insert_script_select(p_table_name varchar2) return varchar2 ;
PROCEDURE Main;
PROCEDURE writelog(
P_PROCEDURE_NAME IN VARCHAR2,
P_ACTIVITY IN VARCHAR2,
P_LOGTEXT IN VARCHAR2,
P_LOG_LEVEL IN NUMBER DEFAULT 0,
P_SQL_TEXT IN VARCHAR2 DEFAULT NULL,
P_ERROR_CODE IN VARCHAR2 DEFAULT NULL,
P_EXTRA_INFO IN VARCHAR2 DEFAULT NULL
);
end DDL_EXTRACTOR;
/
create or replace
PACKAGE BODY "DDL_EXTRACTOR" AS
/*------------------
Package Version: 0.01
-------------------*/
/* This package will mainly need these two tables:
--This table contains the generated DDL and data
create table my_ddl(
object_type varchar2(30 char),
object_name varchar2(30 char),
extracted_on date,
ddl clob
);
--This table contains the configuration of which tables to extract data from it.
create table my_ddl_conf_table_data_extract(
table_name varchar2(30 char)
);
*/
PROCEDURE Main AS
V_Procedure_Name Varchar2(100) := 'Main';
V_Parameters Varchar2(32000);
v_return number;
v_message varchar2(4000 char);
rec_object_type varchar2(30 char);
rec_object_name varchar2(30 char);
v_sql_loop varchar2(32000 char);
TYPE t_refcrs IS REF CURSOR;
c_exttable t_refcrs;
BEGIN
--get immediately the min log level (this will reduce automatically the number of entries in the log)
g_min_log_level := 10; -- TODO 13-11-2012: calculate the log_level from the config_variables table
execute immediate 'truncate table my_ddl drop all storage'; commit;
v_sql_loop := 'with extraction_order as ( ' || c_object_order || ' )
select list.object_type, list.object_name
from ( ' || c_list_objects || ' ) list join extraction_order on (extraction_order.object_type = list.object_type)
order by order_id, object_name
';
Writelog(V_Procedure_Name, 'main loop query', null, c_log_level_debug, v_sql_loop);
OPEN c_exttable FOR v_sql_loop;
loop
FETCH c_exttable INTO rec_object_type, rec_object_name;
exit when c_exttable%notfound;
export_object(rec_object_type, rec_object_name);
end loop;
end;
procedure export_object(p_object_type in varchar2, p_object_name in varchar2) as
v_procedure_name VARCHAR2(30) := 'export_object' ;
v_sql varchar2(32000);
V_Parameters Varchar2(32000);
v_object_type varchar2(30);
V_PRESENT NUMBER;
V_OSUSER VARCHAR2(100 CHAR);
begin
SELECT OSUSER
INTO V_OSUSER
FROM V$SESSION
where audsid=userenv('SESSIONID');
if p_object_type = 'DATABASE LINK' then
v_object_type := 'DB_LINK';
else
v_object_type := p_object_type;
END IF;
insert into my_ddl(osuser, object_type, object_name, extracted_on, ddl)
values(V_OSUSER, p_object_type, p_object_name, sysdate, dbms_metadata.get_ddl(v_object_type, p_object_name));
commit;
if v_object_type = 'TABLE' then
for rec in (select * from user_indexes where table_name = p_object_name) loop
export_object ('INDEX', rec.index_name);
end loop;
for rec in (select * from user_triggers where table_name = p_object_name) loop
export_object ('TRIGGER', rec.trigger_name);
end loop;
v_present := 0;
v_sql := 'select case when count(*) > 0 then 1 else 0 end as vpresent from my_ddl_conf_table_data_extract where table_name = ''' || p_object_name || '''';
execute immediate v_sql into v_present;
if v_present = 1 then
INSERT_SCRIPT(p_object_name);
end if;
END IF;
if v_object_type = 'PACKAGE' then
export_object ('PACKAGE_BODY', p_object_name);
end if;
end;
procedure INSERT_SCRIPT(P_TABLE_NAME VARCHAR2) AS
v_procedure_name VARCHAR2(30) := 'INSERT_SCRIPT' ;
v_sql varchar2(32000);
v_sql_loop varchar2(32000 char);
TYPE t_refcrs IS REF CURSOR;
c_exttable t_refcrs;
v_parameters varchar2(32000);
rec_script varchar2(32000);
v_object_type varchar2(30);
V_NON_EMPTY NUMBER;
v_date date;
V_OSUSER VARCHAR2(100 CHAR);
begin
v_sql := 'select case when count(*) > 0 then 1 else 0 end from ' || p_table_name;
execute immediate v_sql into v_non_empty;
v_sql := ' ';
if v_non_empty = 1 then
SELECT OSUSER
INTO V_OSUSER
FROM V$SESSION
WHERE AUDSID=USERENV('SESSIONID');
v_date := sysdate;
insert into my_ddl(osuser, object_type, object_name, extracted_on) values( V_OSUSER, 'TABLE_DATA', p_table_name , v_date);
v_sql_loop := get_insert_script_select(p_table_name);
OPEN c_exttable FOR v_sql_loop;
loop
FETCH c_exttable INTO rec_script;
EXIT WHEN C_EXTTABLE%NOTFOUND;
IF LENGTH(V_SQL) + LENGTH(REC_SCRIPT) > 32000 THEN
UPDATE my_ddl
SET DDL = DDL || V_SQL
WHERE EXTRACTED_ON = V_DATE AND OBJECT_TYPE = 'TABLE_DATA' AND OBJECT_NAME = P_TABLE_NAME;
V_SQL := ' ';
ELSE
V_SQL := V_SQL || REC_SCRIPT || CHR(10);
end if;
end loop;
end if;
commit;
END;
FUNCTION GET_INSERT_SCRIPT_SELECT(P_TABLE_NAME VARCHAR2) RETURN VARCHAR2 AS
B_FOUND BOOLEAN := FALSE;
V_TEMPA VARCHAR2 (8000);
V_TEMPB VARCHAR2 (8000);
V_TEMPC VARCHAR2 (255);
v_procedure_name VARCHAR2(30) := 'GET_INSERT_SCRIPT_SELECT' ;
v_sql varchar2(32000);
V_Parameters Varchar2(32000);
v_object_type varchar2(30);
begin
v_parameters := ' P_TABLE_NAME: ' || p_table_name;
writelog(v_procedure_name, 'START', 'Parameters: ' || v_parameters, c_log_level_info);
FOR TAB_REC IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME = UPPER (P_TABLE_NAME)) LOOP
b_found := true;
V_TEMPA := 'select ''insert into ' || TAB_REC.TABLE_NAME || ' (';
FOR COL_REC IN (
select *
FROM user_tab_cols
WHERE TABLE_NAME = TAB_REC.TABLE_NAME
order by column_id
) LOOP
if col_rec.column_id = 1 then
V_TEMPA := V_TEMPA;
else
v_tempa := v_tempa || ', ';
V_TEMPB := V_TEMPB || ', ';
END IF;
V_TEMPA := V_TEMPA || COL_REC.COLUMN_NAME;
if instr (col_rec.data_type, 'VARCHAR2') > 0 then
V_TEMPC := '''''''''||' || COL_REC.COLUMN_NAME || '||''''''''';
ELSIF INSTR (COL_REC.DATA_TYPE, 'DATE') > 0 THEN
V_TEMPC := '''to_date(''''''||to_char(' || COL_REC.COLUMN_NAME || ',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')''';
ELSE
V_TEMPC := COL_REC.COLUMN_NAME;
end if;
V_TEMPB := V_TEMPB || '''||decode(' || COL_REC.COLUMN_NAME || ',Null,''Null'',' || V_TEMPC || ')||''';
END LOOP;
V_TEMPA := V_TEMPA || ') values (' || V_TEMPB || ');'' as dml from ' || TAB_REC.TABLE_NAME || '';
END LOOP;
return v_tempa;
end;
END DDL_EXTRACTOR;
/
Nessun commento:
Posta un commento