Hi, can we execute within a script another script in a varchar?

Example:

declare myscript varchar;

set myscript=
   'script()'+#13+
   'begin'+#13+
      'Execute immediate ''do something'' '+#13+
   'end';

execute immediate myscript;

We want to store some scripts for execution in the night in a table and
have a job running these scripts at the given time, possible?

--
--
Yusuf Zorlu | MicrotronX

Yusuf


I have no idea - what happens if you try it?

Roy Lambert

Roy Lambert wrote:

> I have no idea - what happens if you try it?

Hi Roy, it is not working with my example:

ElevateDB Error #700 An error was found in the statement at line 11 and
column 19 (Expected SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER,
DROP, GRANT, REVOKE, BACKUP, RESTORE, COMPARE, SET BACKUPS, MIGRATE,
REPAIR, VERIFY, OPTIMIZE, IMPORT, EXPORT, DISCONNECT, REMOVE, PUBLISH,
UNPUBLISH, SAVE, LOAD, SET UPDATES, COPY FILE, RENAME FILE, DELETE FILE
but instead found script)


--
--
Yusuf Zorlu | MicrotronX

Yusuf ,

No, that's not possible.
However, you can create stored procedures instead of scripts and call them from another script, procedure or job with the CALL statement.

--
Fernando Dias
[Team Elevate]

Fernando Dias wrote:

> No, that's not possible.
> However, you can create stored procedures instead of scripts and call
> them from another script, procedure or job with the CALL statement.

Ok but how can i execute a script which is stored in a VARIABLE?
(or in a CLOB field which i load into a VARIABLE)

is there a EXECUTE SCRIPT ? I don't see it in the help.

--
--
Yusuf Zorlu | MicrotronX

Yusuf,

You can't do that, there is no EXECUTE SCRIPT.
If you want to execute statements in a variable or CLOB, you will have to execute them one at a time with EXECUTE IMMEDIATE but that excludes prepared statements or, as I have already said, CALL stored procedures.

--
Fernando Dias
[Team Elevate]

Yusuf


As Fernando says - you can't as you found out by trying

However, I think you're trying to do the wrong thing <VBG>

EXECUTE IMMEDIATE will execute or at least try to execute a random string. You're want to store these in a table and run them overnight. Why not simply store only the bit of SQL that you want to run and have a job which goes


declare fred varchar;
open the execute immediate table
read in fred
EXECUTE IMMEDIATE fred
move on to next bit

you can make the script carrying out your wishes pretty complex, loading and running arbitrary sql strings in various sequence at various times.

If you can supply a real life example that couldn't be solved by this we can have a look and see if we can help.

Roy Lambert

Roy Lambert wrote:

> EXECUTE IMMEDIATE will execute or at least try to execute a random
> string. You're want to store these in a table and run them overnight.
> Why not simply store only the bit of SQL that you want to run and
> have a job which goes

That's the point "ONLY ONE SQL per Execute Immediate", if we want to be
able to do something complex in the night (without a delphi app and
without tedbquery) and want to do this, we have to create a procedure,
but then we are not as dynamic as we want.

Thanks for your inputs.

--
--
Yusuf Zorlu | MicrotronX

Yusuf


I am really baffled by what you're trying to achieve.

Roy Lambert

Roy Lambert wrote:

> I am really baffled by what you're trying to achieve.
>
> Roy Lambert


I try to describe it:

* we have a script (20 liner) which i call in onafterpost

* but I want to have a these 20 lines not in the onafterpost-trigger!

* I need to be dynamic and so i want the script stored in another
table-field

* onafterpost-trigger reads the script from a this field with i.e.
  select myscript from scriptstable where tablename='mytabletest'
  and type='onafterpost'


understood?

--
--
Yusuf Zorlu | MicrotronX