Stopping code check and or variable assigns

[Bill P]

I have some code that I would like to just generate some scripts to rescript existing triggers. I try and execute this and it seems like it wants to code check the text I would like to assign to the variable. How do I stop the check? Any suggestions? I have tried single/double/both quotes.

Thanks!

declare myvar varchar2(2000);
Begin
myvar :="'CREATE OR REPLACE TRIGGER xxxx BEFORE INSERT ON xxxx FOR EACH ROW BEGIN SELECT whatever_seq.nextval into :NEW.whatever_id from dual; end;'";

select replace(myvar,'xxxx',trigger_name)
from dba_triggers
where owner='whateverowner'
End;

 

[Marco Kalter]

There are some errors in the PL/SQL Block:

• There should not be any double quotes for the myvar string assignment.
• In PL/SQL you need to process a result set of more than 1 row from a select statement in a select loop.

Wouldn't it be better to execute a query like this, instead of using a PL/SQL Block?

select 'CREATE OR REPLACE TRIGGER ' || t.trigger_name || ...
from all_triggers t
where owner = '...'

 

[William Robertson]

Also in your generated trigger, why the 'select from dual' cursor? I would just assign the value using the := assignment operator.