PL/SQL EXECUTE IMMEDIATE question

[Christophe MAILHE]

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.

[Yogeeraj_1 · Accepted solution]

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

[Yogeeraj_1]

hi again,

sorry for the garbled output,

please find attached the above example.

regards
Yogeeraj

[Christophe MAILHE]

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.