Skip to Main Content

Execute immediate connect statement in SqlPlus' Stored Procedure

IrfanAslamJul 17 2019 — edited Jul 17 2019

create or replace PROCEDURE SYNCDB (usrName IN Char, pass IN Char) IS Cursor C1 is

SELECT TABLE_NAME TNAME FROM ALL_TABLES WHERE OWNER = ( select user from dual ) order by 1;

sql_st Varchar2(500);

IP_ADRS Varchar2(50);

INSTANCE Varchar2(50);

BEGIN

sql_st := 'connect usrName/pass@192.168.2.77:1521/orcl';

execute immediate sql_st;

/*

sql_st := 'SELECT sys_context(''' || 'USERENV' || ''',''' || 'IP_ADDRESS' || ''') AS Instance FROM dual';

execute immediate sql_st INTO IP_ADRS;

sql_st := 'SELECT sys_context(''' || 'USERENV' || ''',''' || 'DB_NAME' || ''') AS Instance FROM dual';

execute immediate sql_st INTO INSTANCE;

For X in C1

Loop

sql_st := 'COPY FROM' || usrName || '/' || pass || '@' || IP_ADRS|| ':1521/' || INSTANCE || 'TO uname/pass@192.168.2.82:1521/' || INSTANCE || 'REPLACE X.TNAME USING SELECT * FROM X.TNAME';

execute immediate sql_st;

End Loop;

*/

Screenshot (42).png

End;

/

Comments
Post Details
Added on Jul 17 2019
21 comments
896 views