Re: help: pass the parameter to the pl/sql

Followup positionned to c.d.o.m !

On Tue, 15 Jan 2002 20:10:04 GMT, Mike F <u518615722_at_spawnkill.ip-mobilphone.net> a écrit:
>we need to pass our parameter into our pl/sql
>
>-----------------
>create or replace procedure error_log_archive
> (SCHEMA in varchar2)
>as
> sensor_rec SCHEMA.sp_error_log%ROWTYPE;
> CURSOR A is
> select s.* from SCHEMA.Col1 s
> where
> bla bla;
>-------------------------------------
>
>but oracle does not allow us to do so, could somebody help me out?

you cannot pass something like that and expect oracle to sort it out by itself.

what you want to do is "dynamic sql" (because, for example, depending on the table you're SELECT'ing everything may differ, like the execution plan).

What you've got to do declare a REF CURSOR, then build an string containing your request e.g. :

my_stmt := 'SELECT s.* FROM '||SCHEMA||'.my_table s where blabla '; (beware, no ';' inside the quotes)
and then open your cursor with it. I'm sorry not to give you out of the box examples, but it's pretty well explained in the doc.

(as for the %rowtype ... I must admit I do not know !)

-- 
Damien