dbms_sql ... please help! 2

[lel75]

Hello everyone,

I am sorry but I have another problem with dbms_sql. I have the following program:

DECLARE
stmt INTEGER;

BEGIN
--
--Read in user values
v_start_dt := '&START';
v_end_dt := '&END';
stmt := dbms_sql.open_cursor;

dbms_sql.parse(stmt, 'CREATE TABLE test AS
(SELECT *
FROM employee
WHERE hire_dt NOT
BETWEEN '''||v_start_dt||'''AND'''||v_end_dt||''')', DBMS_SQL.NATIVE);

dbms_sql.parse(stmt, 'TRUNCATE TABLE employee', DBMS_SQL.NATIVE);

dbms_sql.parse(stmt, 'INSERT INTO employee
SELECT *
FROM test', DBMS_SQL.NATIVE);


When I run the program, I get no error. However it doesn't insert into the employee table. Do you know if I need dbms_sql for the insert statement? I tried it w/and w/o but it still doesn't work.

Thanks for your help in advance!

lel75

 

[ddrillich]

Please look at:
thread186-727279 - I think you miss the dbms_sql.execute_and_fetch statement.

Regards,
Dan

 

[sem]

I suppose that you might have misinterpreted my advice. Only DDL (i.e. CREATE, DROP, TRUNCATE etc.) statements are executed during parse time. All DML's need execute at least.

Regards, Dima

http://seminihin.narod.ru

 

[lel75]

Thanks for the quick response Dan & Dima... You guys are great! I was missing the execute statement.