Call SQL from PL/SQL?

[Helge Moulding]

The application I'm working with includes a series of scripts that
have to be called from the shell, or from SQL*Plus. I'd rewrite
them as procedures, except that the company that provides the
application may provide updates to the scripts, in which case I'll
have to rewrite all those scripts again. I'd like to avoid that.
(I know, I'd like it even better if the company that provides the
application didn't saddle me with this mess of scripts, but that's
a different issue.)

Is there any way I can call these scripts from a procedure?
--
Helge Moulding
hmoulding at gmail dot com Just another guy
http://hmoulding.cjb.net/ with a weird name

[DA Morgan]

Helge Moulding wrote: The application I'm working with includes a series of scripts that have to be called from the shell, or from SQL*Plus. I'd rewrite them as procedures, except that the company that provides the application may provide updates to the scripts, in which case I'll have to rewrite all those scripts again. I'd like to avoid that. (I know, I'd like it even better if the company that provides the application didn't saddle me with this mess of scripts, but that's a different issue.) Is there any way I can call these scripts from a procedure? -- Helge Moulding hmoulding at gmail dot com Just another guy http://hmoulding.cjb.net/ with a weird name

What version of Oracle? If 10g you could use DBMS_SCHEDULER.
If a previous version you could write an external procedure
or read them in using an external table or UTL_FILE and then
put together some way to modify them on-the-fly into anonymous
blocks.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

[Helge Moulding]

DA Morgan wrote: What version of Oracle?

9i, I believe.
If 10g you could use DBMS_SCHEDULER.

OK, not that.
If a previous version you could write an external procedure or read them in using an external table or UTL_FILE and then put together some way to modify them on-the-fly into anonymous blocks.

That sounds like my code would have to know too much about the
scripts. I want to be able to do the equivalent of

@@scriptfile parameter

where someone can make arbitrary changes to scriptfile, like
call other scripts, etc, and the calling program doesn't have
to know about them.
--
Helge Moulding
hmoulding at gmail dot com Just another guy
http://hmoulding.cjb.net/ with a weird name

[Sybrand Bakker]

On 7 Oct 2005 15:31:26 -0700, "Helge Moulding" <hmoulding@gmail.com>
wrote:
That sounds like my code would have to know too much about thescripts.

The only thing your code needs to know is the terminator character.
And you can set that in (so retrieve it from) login.sql/
I fail to see the problem.

--
Sybrand Bakker, Senior Oracle DBA

[Helge Moulding]

Sybrand Bakker wrote: I fail to see the problem.

I'm not really looking forward to writing a command parser. I guess
you are telling me that (prior to 10g, maybe) Oracle has no way to
simply do the equivalent of

@@scriptfile parameter1

in PL/SQL. How about doing the equivalent of shelling out to run
a shell script or command, e.g.

sqlplus name/pw@db @c:\folder\scriptfile parameter1

I don't like that much, because I'd like to not put the db password
out in a text file, but is that at least possible?
--
Helge Moulding
hmoulding at gmail dot com Just another guy
http://hmoulding.cjb.net/ with a weird name

[DA Morgan]

Helge Moulding wrote: Sybrand Bakker wrote:I fail to see the problem. I'm not really looking forward to writing a command parser. I guess you are telling me that (prior to 10g, maybe) Oracle has no way to simply do the equivalent of @@scriptfile parameter1 in PL/SQL. How about doing the equivalent of shelling out to run a shell script or command, e.g. sqlplus name/pw@db @c:\folder\scriptfile parameter1 I don't like that much, because I'd like to not put the db password out in a text file, but is that at least possible? -- Helge Moulding hmoulding at gmail dot com Just another guy http://hmoulding.cjb.net/ with a weird name

Use operating system authentication ... do not put the password into the
file. Look up OPS$ accounts and external authentication at
tahiti.oracle.com.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)