OraFAQ Forum: SQL & PL/SQL » EXECUTE IMMEDIATE question

Home » SQL & PL/SQL » SQL & PL/SQL » EXECUTE IMMEDIATE question

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

EXECUTE IMMEDIATE question [message #7443]

Fri, 13 June 2003 08:03

Christophe MAILHE
Messages: 2
Registered: June 2003

Junior Member

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.

Report message to a moderator

Re: EXECUTE IMMEDIATE question [message #7447 is a reply to message #7443]

Fri, 13 June 2003 11:23

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

execute immediate 
  'insert into h21admin.h21_rc_watch_dat' ||
  ' select :mnew_seq, :mytable, count(*) from ' || mytable
  using mnew_seq, mytable;

Report message to a moderator

Re: EXECUTE IMMEDIATE question [message #7459 is a reply to message #7447]

Mon, 16 June 2003 04:39

Christophe MAILHE
Messages: 2
Registered: June 2003

Junior Member

Hi Barry,

I have already tried something similar, but without any success. Oracle returns an Error message : Columns do not exist because the Select statment interprets :mnew_seq and :mytable as columns name instead of values.

To solve the issue I have had to add the record using 2 statements (1 insert and 1 update) :

INSERT INTO H21ADMIN.H21_RC_WATCH_DAT
VALUES (mnew_seq, mytable, NULL);

sql_stmt := 'UPDATE H21ADMIN.H21_RC_WATCH_DAT SET ROW_COUNT = '
|| ' (SELECT COUNT(*) FROM '|| mytable ||' ) WHERE SEQ_NUM = '
|| mnew_seq || ' AND TABLE_NAME = ''' || mytable || ''' ';

EXECUTE IMMEDIATE sql_stmt;

If someone have a better solution.... Ideas are welcome :)

Christophe.

Report message to a moderator

Re: EXECUTE IMMEDIATE question [message #7465 is a reply to message #7459]

Mon, 16 June 2003 10:48

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Christophe, you must have done something differently. Note how the bind variable is not interpreted as a column name - it is treated correctly as a literal value:

sql>create table t (table_name varchar2(30), num_rows int);
 
Table created.
 
sql>declare
  2    v_table  varchar2(30) := 'ALL_OBJECTS';
  3  begin
  4    execute immediate 'insert into t select :tn, count(*) from ' || v_table using v_table;
  5  end;
  6  /
 
PL/SQL procedure successfully completed.
 
sql>select * from t;
 
TABLE_NAME                      NUM_ROWS
------------------------------ ---------
ALL_OBJECTS                        26060
 
1 row selected.

Report message to a moderator

Previous Topic:	Help Me Out if you will....
Next Topic:	query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 04:24:22 CDT 2024