SEO

#1 (**permalink**) 02-23-2008, 09:47 AM

Hallo everyone,

I would like you to consider the following case:
Procedure 'Batch' is calling as 2.nd Procedure 'Importer(xy varchar2)'
and this creates at runtime a Procedure xy || '_prc' .
This works fine !
Cause the Name of the Procedure that 'Importer(xy)' creates is not
fixed, it
can't be called in Procedure 'Batch' by simply calling the Name.
Execute Immediate xy || '_prc'; won't work
So how must I reference this Procedure?

Besides I tried to solve it by creating a Procedure 'CallIt' which is
dynamically generated by an Execute Immediate Command in 'Batch',
CallIt calles xy || '_prc'.
'CallIt' itself will be called in 'Batch' .
The Error occurs : could not find program unit being called
If I call 'CallIt' then manually in Sql Plus it works properly.
Even if I try to disable the Execute Immediate that dynamically
creates
'CallIt' the Execution would fail in 'Batch' with this errormessage.

It would be a great Effort , if I have to code it all the dynamically
way,
so please help.

Greetings B. Kochs

#2 (**permalink**) 02-23-2008, 09:47 AM

Answers embedded

On 1 Jun 2004 07:47:27 -0700, bernhard.kochs@t-com.net (BKochs) wrote:

>Hallo everyone,
>
>I would like you to consider the following case:
>Procedure 'Batch' is calling as 2.nd Procedure 'Importer(xy varchar2)'
>and this creates at runtime a Procedure xy || '_prc' .
>This works fine !
>Cause the Name of the Procedure that 'Importer(xy)' creates is not
>fixed, it
>can't be called in Procedure 'Batch' by simply calling the Name.
>Execute Immediate xy || '_prc'; won't work
>So how must I reference this Procedure?

The procedure name needs to be wrapped in a begin end;
pair.
>
>Besides I tried to solve it by creating a Procedure 'CallIt' which is
>dynamically generated by an Execute Immediate Command in 'Batch',
>CallIt calles xy || '_prc'.
>'CallIt' itself will be called in 'Batch' .
>The Error occurs : could not find program unit being called
>If I call 'CallIt' then manually in Sql Plus it works properly.
>Even if I try to disable the Execute Immediate that dynamically
>creates
>'CallIt' the Execution would fail in 'Batch' with this errormessage.
>
>It would be a great Effort , if I have to code it all the dynamically
>way,

More importantly: it is unscalable.

>so please help.
>
>Greetings B. Kochs

--
Sybrand Bakker, Senior Oracle DBA

#3 (**permalink**) 02-23-2008, 09:49 AM

Thank you!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Execute a Procedure (Name only known at runtime) from another Procedure