Re: Execute Immediate and bind variables

On 10 Aug 2005 15:01:36 -0700, "brightspot" <brightspot5_at_hotmail.com> wrote:

>Thanks Matthias, I see what you are saying. So I can just use the
>parameter as it comes into the stored procedure:
>
>Execute Immediate 'SELECT ' || SCHEMA_NAME || '.theTableName.theField
>> AS THE_FIELD, ' || ... etc
>
>I'm still getting an error testing the SP from SQLPlus:
>BEGIN MY_QRY (schemaName); END;
>
> *
>ERROR at line 1:
>ORA-06550: line 1, column 27:
>PLS-00201: identifier 'schemaName' must be declared
>ORA-06550: line 1, column 7:
>PL/SQL: Statement ignored
>
>Permissions problem, maybe??
>
>Thanks

You are calling the procedure using a variable. You didn't declare that variable.
So either
variable schemaname varchar2(30)
begin
:schemaname := 'Yourschema';
my_qry(schenaName);
end;
/

or
declare
schemaname varchar2(30) := 'YourSchema'; begin
my_qry(schenaName);
end;
/

or just
execute my_qry('Yourschema')

Programming can be so simple providing you peruse the syntax before hacking your way out.

--
Sybrand Bakker, Senior Oracle DBA