Extracting SQL from an EXPORT file

TIP: Click help for a detailed explanation of this page.
Bookmark Go to End

Subject: Unix Script: IMPSHOW2SQL - Extracting SQL from an EXPORT file
Doc ID: Note:29765.1 Type: SCRIPT
Last Revision Date: 25-APR-2001 Status: PUBLISHED


This is a short script that allows you to extract the SQL from an export
file. It is intended for use on UNIX machines. Instructions are in the
header of the script.

When entering the script ensure:

A> The first line in the file should be a single colon (:)

Please note this is a proto-type script so there may be some statements
in the resultant file that need manual editting.

:
# impshow2sql Tries to convert output of an IMP SHOW=Y command into a
# usage SQL script.
#
# To use:
# Start a Unix script session and import with show=Y thus:
#
# $ imp user/password file=exportfile show=Y log=/tmp/showfile
#
# You now have the SHOW=Y output in /tmp/showfile .
# Run this script against this file thus:
#
# $ ./impshow2sql /tmp/showfile > /tmp/imp.sql
#
# The file /tmp/imp.sql should now contain the main SQL for
# the IMPORT.
# You can edit this as required.
# Note: This script may split lines incorrectly for some statements
# so it is best to check the output.
#
# CONSTRAINT "" problem:
# You can use this script to help get the SQL from an export
# then correct it if it includes bad SQL such as CONSTRAINT "".
# Eg:
# Use the steps above to get a SQL script and then
# $ sed -e 's/CONSTRAINT ""//' infile > outfile
# Now precreate all the objects and import the export file.
#
# Extracting Specific Statements only:
# It is fairly easy to change the script to extract certain statements
# only. For statements you do NOT want to extract change N=1 to N=0
# Eg: To extract CREATE TRIGGER statements only:
# a) Change all lines to set N=0.
# Eg: / \"CREATE / { N=0; }
# This stops CREATE statements being output.
#
# b) Add a line (After the general CREATE line above):
# / \"CREATE TRIGGER/ { N=1; }
# This flags that we SHOULD output CREATE TRIGGER statements.
#
# c) Run the script as described to get CREATE TRIGGER statements.
#

awk ' BEGIN { prev=";" }
/ \"CREATE / { N=1; }
/ \"ALTER / { N=1; }
/ \"ANALYZE / { N=1; }
/ \"GRANT / { N=1; }
/ \"COMMENT / { N=1; }
/ \"AUDIT / { N=1; }
N==1 { printf "\n/\n\n"; N++ }
/\"$/ { prev=""
if (N==0) next;
s=index( $0, "\"" );
if ( s!=0 ) {
printf "%s",substr( $0,s+1,length( substr($0,s+1))-1 )
prev=substr($0,length($0)-1,1 );
}
if (length($0)<78) printf( "\n" );
}' $*

To restrict sqlplus / Toad to connect Oracle

create or replace trigger restrict_login
after logon on database
declare
flag number := 0;
begin
select 1 into flag
from sys.v_$session
where lower(program) like '%sqlplus%' or
lower(program) like '%toad%' and
rownum=1 ;
if flag = 1 then
raise_application_error(-20000, 'You are not Authorised to Access from TOAD/SQLPLUS');
end if;
end;
***********************************
One can check Module_hash /Module from V$session instead of checking the program name. The Module_hash /Module wont change by renaming the exe

Cascade Update

Prviledge to User Creating Package=
• CREATE TRIGGER
• CREATE PROCEDURE
********************************************
@ create package
SQL> update emp set empno = 8000 where empno = 7698;
update emp set empno = 8000 where empno = 7698
*
ERROR at line 1:
ORA-02292: integrity constraint (UCDEMO.SYS_C005186) violated - child record
found


SQL> exec update_cascade.on_table('emp')

PL/SQL procedure successfully completed.

SQL> update emp set empno = 8000 where empno = 7698;
*********************************************


create or replace package update_cascade
as

procedure on_table( p_table_name in varchar2,
p_preserve_rowid in boolean default TRUE,
p_use_dbms_output in boolean default FALSE );

end update_cascade;
/

create or replace package body update_cascade
as

type cnameArray is table of user_cons_columns.column_name%type
index by binary_integer;

sql_stmt varchar2(32000);
use_dbms_output boolean default FALSE;
preserve_rowid boolean default TRUE;

function q( s in varchar2 ) return varchar2
is
begin
return '"'||s||'"';
end q;

function pkg_name( s in varchar2 ) return varchar2
is
begin
return q( 'u' || s || 'p' );
end pkg_name;


function view_name( s in varchar2 ) return varchar2
is
begin
return q( 'u' || s || 'v' );
end view_name;

function trigger_name( s in varchar2, s2 in varchar2 ) return varchar2
is
begin
return q( 'u' || s || s2 );
end trigger_name;


function strip( s in varchar2 ) return varchar2
is
begin
return ltrim(rtrim(s));
end strip;

procedure add( s in varchar2 )
is
begin
if ( use_dbms_output ) then
dbms_output.put_line( chr(9) || s );
else
sql_stmt := sql_stmt || chr(10) || s;
end if;
end add;



procedure execute_immediate
as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
if ( use_dbms_output ) then
dbms_output.put_line( '/' );
else
dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_sql.close_cursor( exec_cursor );
dbms_output.put_line(
substr( sql_stmt, 2, instr( sql_stmt,chr(10),2)-2 ) );
sql_stmt := NULL;
end if;
exception
when others then
if dbms_sql.is_open(exec_cursor) then
dbms_sql.close_cursor(exec_cursor);
end if;
raise;
end;

procedure get_pkey_names
( p_table_name in out user_constraints.table_name%type,
p_pkey_names out cnameArray,
p_constraint_name in out user_constraints.constraint_name%type )
is
begin
select table_name, constraint_name
into p_table_name, p_constraint_name
from user_constraints
where ( table_name = p_table_name
or table_name = upper(p_table_name) )
and constraint_type = 'P' ;

for x in ( select column_name , position
from user_cons_columns
where constraint_name = p_constraint_name
order by position ) loop
p_pkey_names( x.position ) := x.column_name;
end loop;
end get_pkey_names;


procedure write_spec( p_table_name in user_constraints.table_name%type,
p_pkey_names in cnameArray )
is
l_comma char(1) default ' ';
begin
add( 'create or replace package ' || pkg_name(p_table_name) );
add( 'as' );
add( '--' );
add( ' rowCnt number default 0;' );
add( ' inTrigger boolean default FALSE;' );
add( '--' );

for i in 1 .. 16 loop
begin
add( ' type C' || strip(i) || '_type is table of ' ||
q(p_table_name) || '.' || q(p_pkey_names(i)) ||
'%type index by binary_integer;' );
add( '--' );
add( ' empty_C' || strip(i) || ' C' || strip(i) || '_type;' );
add( ' old_C' || strip(i) || ' C' || strip(i) || '_type;' );
add( ' new_C' || strip(i) || ' C' || strip(i) || '_type;' );
add( '--' );
exception
when no_data_found then exit;
end;
end loop;

add( '--' );
add( ' procedure reset;' );
add( '--' );
add( ' procedure do_cascade;' );
add( '--' );

add( ' procedure add_entry' );
add( ' ( ' );
for i in 1 .. 16 loop
begin
add( ' ' || l_comma || 'p_old_C' || strip(i) || ' in ' ||
q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type ' );
l_comma := ',';
add( ' ,p_new_C' || strip(i) || ' in out ' ||
q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type ' );
exception
when no_data_found then exit;
end;
end loop;
add( ' );' );
add( '--' );
add( 'end ' || pkg_name(p_table_name) || ';' );

end write_spec;


procedure write_body
( p_table_name in user_constraints.table_name%type,
p_pkey_names in cnameArray,
p_constraint_name in user_constraints.constraint_name%type )
is
l_col_cnt number default 0;
l_comma char(1) default ' ';
l_pkey_str varchar2(2000);
l_pkey_name_str varchar2(2000);
l_other_col_str varchar2(2000);
begin
add( 'create or replace package body ' || pkg_name(p_table_name) );
add( 'as' );
add( '--' );

add( ' procedure reset ' );
add( ' is' );
add( ' begin' );
add( '--' );
add( ' if ( inTrigger ) then return; end if;' );
add( '--' );
add( ' rowCnt := 0;' );
for i in 1 .. 16 loop
begin
if (p_pkey_names(i) = p_pkey_names(i)) then
l_col_cnt := l_col_cnt+1;
end if;
add( ' old_C' || strip(i) || ' := empty_C' || strip(i) || ';' );
add( ' new_C' || strip(i) || ' := empty_C' || strip(i) || ';' );
exception
when no_data_found then exit;
end;
end loop;
add( ' end reset;' );
add( '--' );

add( ' procedure add_entry ' );
add( ' ( ' );
for i in 1 .. 16 loop
begin
add( ' ' || l_comma || 'p_old_C' || strip(i) || ' in ' ||
q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type ' );
l_comma := ',';
add( ' ,p_new_C' || strip(i) || ' in out ' ||
q(p_table_name) || '.' || q(p_pkey_names(i)) || '%type ' );
exception
when no_data_found then exit;
end;
end loop;
add( ' )' );
add( ' is' );
add( ' begin' );
add( '--' );
add( ' if ( inTrigger ) then return; end if;' );
add( '--' );
add( ' if ( ' );
for i in 1 .. l_col_cnt loop
if ( i <> 1 ) then
add( ' OR' );
end if;
add( ' p_old_C' || strip(i) || ' <> ' ||
'p_new_C' || strip(i) );
end loop;
add( ' ) then ' );
add( ' rowCnt := rowCnt + 1;' );

for i in 1 .. l_col_cnt loop
add( ' old_C' || strip(i) ||
'( rowCnt ) := p_old_C' || strip(i) || ';' );
add( ' new_C' || strip(i) ||
'( rowCnt ) := p_new_C' || strip(i) || ';' );
add( ' p_new_C' || strip(i) ||
' := p_old_C' || strip(i) || ';' );
end loop;

add( ' end if;' );
add( ' end add_entry;' );


add( '--' );

l_comma := ' ';
for i in 1 .. l_col_cnt loop
l_pkey_str := l_pkey_str||l_comma||'$$_C' || strip(i) || '(i)';
l_pkey_name_str := l_pkey_name_str || l_comma || q(p_pkey_names(i));
l_comma := ',';
end loop;

for x in ( select column_name
from user_tab_columns
where table_name = p_table_name
and column_name not in
( select column_name
from user_cons_columns
where constraint_name = p_constraint_name )
order by column_id )
loop
l_other_col_str := l_other_col_str || ',' || q(x.column_name);
end loop;

add( ' procedure do_cascade' );
add( ' is' );
add( ' begin' );
add( '--' );
add( ' if ( inTrigger ) then return; end if;' );
add( ' inTrigger := TRUE;' );
add( '--' );
add( ' for i in 1 .. rowCnt loop' );
add( ' insert into ' || p_table_name || ' ( ' );
add( ' ' || l_pkey_name_str );
add( ' ' || l_other_col_str || ') select ');
add( ' ' || replace( l_pkey_str, '$$', 'new' ) );
add( ' ' || l_other_col_str );

add( ' from ' || q(p_table_name) || ' a' );
add( ' where (' || l_pkey_name_str || ' ) = ' );
add( ' ( select ' || replace(l_pkey_str,'$$','old') );
add( ' from dual );' );
add( '--' );
if ( preserve_rowid ) then
add( ' update ' || q(p_table_name) || ' set ' );
add( ' ( ' || l_pkey_name_str || ' ) = ' );
add( ' ( select ' );
for i in 1 .. l_col_cnt loop
if ( i <> 1 ) then add( ' ,' ); end if;
add( ' decode( ' || q(p_pkey_names(i)) ||
replace(', old_c$$(i), new_c$$(i), old_c$$(i) )', '$$',strip(i)) );
end loop;
add( ' from dual )' );
add( ' where ( ' || l_pkey_name_str || ' ) =' );
add( ' ( select ' || replace(l_pkey_str,'$$','new') );
add( ' from dual )' );
add( ' OR ( ' || l_pkey_name_str || ' ) =' );
add( ' ( select ' || replace(l_pkey_str,'$$','old') );
add( ' from dual );' );
end if;

for x in ( select table_name, constraint_name
from user_constraints
where r_constraint_name = p_constraint_name
and constraint_type = 'R' ) loop

l_comma := ' ';
l_other_col_str := '';
for y in ( select column_name
from user_cons_columns
where constraint_name = x.constraint_name
order by position ) loop
l_other_col_str := l_other_col_str || l_comma || q(y.column_name);
l_comma := ',';
end loop;

add( '--' );
add( ' update ' || q( x.table_name ) || ' set ');
add( ' ( ' || l_other_col_str || ' ) = ' );
add( ' ( select ' || replace( l_pkey_str, '$$', 'new' ) );
add( ' from dual )' );
add( ' where ( ' || l_other_col_str || ' ) = ' );
add( ' ( select ' ||
replace( l_pkey_str, '$$', 'old' ) );
add( ' from dual );' );

end loop;

add( '--' );
add( ' delete from ' || q(p_table_name) );
add( ' where ( ' || l_pkey_name_str || ' ) = ' );
add( ' ( select ' ||
replace( l_pkey_str, '$$', 'old' ) );
add( ' from dual);' );
add( ' end loop;' );

add( '--' );
add( ' inTrigger := FALSE;' );
add( ' reset;' );
add( ' exception' );
add( ' when others then' );
add( ' inTrigger := FALSE;' );
add( ' reset;' );
add( ' raise;' );
add( ' end do_cascade;' );
add( '--' );
add( 'end ' || pkg_name( p_table_name ) || ';' );

end write_body;


procedure write_bu_trigger( p_table_name in user_constraints.table_name%type,
p_pkey_names in cnameArray )
is
l_comma char(1) default ' ';
begin
add( 'create or replace trigger ' || trigger_name( p_table_name, '1' ) );
add( 'before update of ' );
for i in 1 .. 16 loop
begin
add( ' ' || l_comma || q(p_pkey_names(i)) );
l_comma := ',';
exception
when no_data_found then exit;
end;
end loop;
add( 'on ' || q(p_table_name) );
add( 'begin ' || pkg_name(p_table_name) || '.reset; end;' );
end write_bu_trigger;


procedure write_bufer_trigger
( p_table_name in user_constraints.table_name%type,
p_pkey_names in cnameArray )
is
l_comma char(1) default ' ';
begin
add( 'create or replace trigger '||trigger_name( p_table_name, '2' ) );
add( 'before update of ' );

for i in 1 .. 16 loop
begin
add( ' ' || l_comma || q(p_pkey_names(i)) );
l_comma := ',';
exception
when no_data_found then exit;
end;
end loop;
add( 'on ' || q(p_table_name) );
add( 'for each row' );
add( 'begin ' );
add( ' ' || pkg_name(p_table_name) || '.add_entry(' );

l_comma := ' ';
for i in 1 .. 16 loop
begin
add( ' ' || l_comma || ':old.' || q(p_pkey_names(i)) );
add( ' ,:new.' || q(p_pkey_names(i)) );
l_comma := ',';
exception
when no_data_found then exit;
end;
end loop;
add( ' );' );
add( 'end;' );
end write_bufer_trigger;

procedure write_au_trigger( p_table_name in user_constraints.table_name%type,
p_pkey_names in cnameArray )
is
l_comma char(1) default ' ';
begin
add( 'create or replace trigger ' || trigger_name( p_table_name, '3' ) );
add( 'after update of ' );
for i in 1 .. 16 loop
begin
add( ' ' || l_comma || q(p_pkey_names(i)) );
l_comma := ',';
exception
when no_data_found then exit;
end;
end loop;
add( 'on ' || q(p_table_name) );
add( 'begin ' || pkg_name(p_table_name) || '.do_cascade; end;' );
end write_au_trigger;

procedure on_table( p_table_name in varchar2,
p_preserve_rowid in boolean default TRUE,
p_use_dbms_output in boolean default FALSE )
is
l_table_name user_constraints.table_name%type default p_table_name;
l_constraint_name user_constraints.constraint_name%type;
l_pkey_names cnameArray;
l_comma char(1) default ' ';
begin
use_dbms_output := p_use_dbms_output;
preserve_rowid := p_preserve_rowid;

get_pkey_names( l_table_name, l_pkey_names, l_constraint_name );

sql_stmt := NULL;
write_spec( l_table_name, l_pkey_names );
execute_immediate;
write_body( l_table_name, l_pkey_names, l_constraint_name );
execute_immediate;
write_bu_trigger( l_table_name, l_pkey_names );
execute_immediate;
write_bufer_trigger( l_table_name, l_pkey_names );
execute_immediate;
write_au_trigger( l_table_name, l_pkey_names );
execute_immediate;


end on_table;

end update_cascade;
/
show errors