Dynamic Delete using EXECUTE IMMEDIATE 1

[indie75]

Hi I writting a procedure that deletes selected log tables from the database.
I am trying to use a string which depending on the else-if statment concatanates a different where clause to it. I am trying to write a dynamic procedure because The screen works on 52 different log screens. These logs are on serate tab pages, so I can easly creat a string to delete from the correct log with the correct where clause, 1-4 possibillities on the where, all passed into sql_statement_ varchar2(250) However when I try

EXECUTE IMMEDIATE sql_statement_ ;

I get an error message on keyword IMMEDIATE, this is my first time with this statment so am at abit of a loss as to know why?

Do I have to turn sql_statement_ into a procedure somehow, before I can EXECUTE IMMEDIATE

I have hit abit of a wall on this, so as always your advice and tips are greatly recieved.
Cheers,
Indie

 

[sem]

What tool do you use? This command doesn't work on too old PL/SQL engines.

Regards, Dima

http://seminihin.narod.ru

 

[indie75]

Hi, Im using Form Builder in Oralce Developer 6.0 with Oracle 8 Drivers I think.

 

[sem]

If you call this code from Forms trigger, it shouldn't work beacause it's executed by client-side pl/sql engine, that is quite old. Try FORMS_DDL built-in instead (or DBMS_SQL package if you need more advanced features).

Regards, Dima

http://seminihin.narod.ru

 

[indie75]

Cheers Sem, I will give this a try, no-doubt I will be back. Because I am writing reusable componants I have created more development time for myself
We have Oracle 9i knocking about on the network but getting hold of it is not that easy. So just have to work with the tools ive got I suppose. Cheers again Sem
Indie

 

[indie75]

Is anything else I need to do. As I can compile ok now. But it wont delete. This is my phrasing..

FORMS_DDL('BEGIN DELETE FROM ' ||sql_statement_ ||'WHERE BATCH_LD_ID = '||v_batch_id|| '; END;');
Commit;

sql_statment_ is full table name, which I have checked with message('Table in statment is:'||sql_statement_ );
And v_batch_id is also correct in this instance it = E

The code is in a procedure which is called for a when-button-pressed trigger. I also tryed putting the code directly into the button-pressed-trigger all sadly to no avail...

 

[sem]

Check the command you're trying to execute. Your statement contains no space between table name and WHERE. Besides if v_batch_id is not a NUMBER, it probably should be quoted.

Regards, Dima

http://seminihin.narod.ru

 

[indie75]

I noticed that, when tryed experimenting with:

cursor_hndlr := DBMS_SQL.OPEN_CURSOR;
sql_statement_try := 'Delete from '|| sql_statement_ ||' Where log_status = '||v_log_status;
message(sql_statement_try);
DBMS_SQL.PARSE(cursor_hndlr, sql_statement_try, 1);
update_applied := DBMS_SQL.EXECUTE(cursor_hndlr);

..But still no joy on that or Forms_DDL. I couldn't work out how to put qoutes into a string, as its not hard coded, its taken from the form directly. But I am designing it to work on two fields, one being the char of log status, but the other is a number, and I get the same message of no changes to save, after commit.

I dont understand. using Forms_ddl i get the correct statment going into the string, which when statment put into toad executes correctly, and deletes from the database.

 

[indie75]

Forget that last bit, I have got Forms_DDL to delete on Number, Thanks alot sem for all your help. I didnt notice because I forgot to excute_statment to refresh my screen, opps!
I still need to work out how to put quotation marks into a string so I can delete on log_status. I think you can escape the metecharacters with a backslash in unix, but that doesn't work in forms. Ar well Thanks again.

Si

 

[sem]

To embed quotes into string literal you should double them:

SQL> select 'O''Henry' from dual;

'O''HEN
-------
O'Henry

Or, if your log_status is a string:

...' Where log_status = '''||v_log_status||'''';


With DBMS_SQL as well as with EXECUTE IMMEDIATE you may BIND variables instead of generating "disposable" statements, so FORMS_DDL is quite restrictive.

Regards, Dima

http://seminihin.narod.ru

 

[indie75]

Cheers sem,
I had to play around with it abit, but your advice put me on the right track.
Solution:

Declare
..
Quote_ varchar2(10);
v_log_status VARCHAR2(1);

BEGIN

Quote_ := '''';
v_log_status := ELETE_BLK.ls;

FORMS_DDL('BEGIN DELETE FROM ' ||sql_statement_ ||' WHERE log_status = '||Quote_||v_log_status||Quote_||'; END;');
Commit;
..

END

Thanks for all your help.
Regards,
Si