Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

PL/SQL EXECUTE IMMEDIATE question

SOLVED
Go to solution
Christophe MAILHE
Frequent Advisor

PL/SQL EXECUTE IMMEDIATE question

Hi all.
I am quite new in PL/SQL and I need to run the following statment in a PL/SQL procedure :

EXECUTE IMMEDIATE "INSERT INTO H21ADMIN.H21_RC_WATCH_DAT ( SELECT '"|| mnew_seq ||"', '"|| mytable ||"', COUNT(*) FROM "|| mytable || ")";

mnew_seq and mytable are PL/SQL variables and not columns. These columns don't exist in the table.

I need to find the equivalent to :

INSERT INTO H21ADMIN.H21_RC_WATCH_DAT ( SELECT '1', 'H21ADMIN.THISISATABLE', COUNT(*) FROM H21ADMIN.THISISATABLE);

Could someone help ?

Cheers,

Christophe.
3 REPLIES
Yogeeraj_1
Honored Contributor
Solution

Re: PL/SQL EXECUTE IMMEDIATE question

hi Christophe,

play with the single quotes.

anyway, below an example:
===============================================
yd@MYDB.MU> create table h21_rc_watch_dat(
2 mseq number(5),
3 tabname varchar2(100),
4 tabcount number(5)
5 );

Table created.

Elapsed: 00:00:00.03

yd@MYDB.MU> declare
2 l_str1 varchar2(200) ;
3 begin
4 for r1 in (select rownum rnum, table_name tb from user_tables where rownum < 4) loop
5 l_str1 := ''''||r1.rnum||''','''||r1.tb||''', count(*) from '||r1.tb;
6 execute immediate 'insert into h21_rc_watch_dat (select '||l_str1||')';
7 dbms_output.put_line( l_str1 );
8 end loop;
9* end;

'1','CMTSTORESTATS', count(*) from CMTSTORESTATS
'2','DEMO', count(*) from DEMO
'3','DEPT', count(*) from DEPT

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
yd@MYDB.MU> select count(*) from h21_rc_watch_dat;

COUNT(*)
__________
3

Elapsed: 00:00:00.01
yd@MYDB.MU>

===============================================

hope this helps!

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: PL/SQL EXECUTE IMMEDIATE question

hi again,

sorry for the garbled output,

please find attached the above example.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Christophe MAILHE
Frequent Advisor

Re: PL/SQL EXECUTE IMMEDIATE question

Hi Yogeeraj.

Thanks for your help !

Here is the script I have implemented :

FOR tables_to_watch_rec IN tables_to_watch_cursor LOOP

mytable := tables_to_watch_rec.TABLE_NAME;

sql_stmt := 'INSERT INTO H21ADMIN.H21_RC_WATCH_DAT '
|| ' (SELECT ''' || mnew_seq || ''', ''' || mytable || ''', '
|| ' COUNT(*) FROM '|| mytable ||' )';

EXECUTE IMMEDIATE sql_stmt;

END LOOP;

Christophe.