Execute Immediate and bind variables

I'm translated SQL Server stored procedures into Oracle. We used
sp_executesql before and I'm trying to use Execute Immediate to mimic
the previous code. Here's my problem:

The schema name is passed into the stored procedure. We concatenate the
schema name to the table name and then do some selects and updates to
the table based on the particular schema we're working on. For example:

Execute Immediate 'SELECT ' || SCHEMA_NAME || '.theTableName.theField
AS THE_FIELD, ' || ... etc

So I need to do the concatenation of the schema name (which is
variable) to the table name. I was thinking something like this:

Execute Immediate 'SELECT ' || :1 || '.theTableName_theField AS
THE_FIELDS, ' || ... etc
using SCHEMA_NAME

The error I get is invalid table. Can I use bind variables in this
case? Anyone know of a way to make this work?

Thanks,
Chris

.

Relevant Pages

  • Re: Problem with Stored Procedure that conatins several select statements
    ... If you test the stored procedures from the SQL Analyser, ... The Problem is that everytime I create a generated schema there is only ... one element in the response node called "Success" and not the two columns ... the final SELECT then the response elements are created as I expected. ...
    (microsoft.public.biztalk.general)
  • Re: SQL Best Practices Analyzer Rule: Use of Schema Qualified Tables/Views
    ... > How important is to specify the schame (dbo. ... > application may be improved by specifying schema names." ...
    (comp.databases.ms-sqlserver)
  • Re: One message should result into inserts into several sql-tables
    ... But I'll have to use several stored procedures it seems. ... Since there is a header-line structure ... (even without using transaction in the SP) ... You need to design you schema in the way you capture all the required data. ...
    (microsoft.public.biztalk.general)
  • Re: A philosophical question about inserts
    ... Stored Procedures are the ideal -for all the benefits mentioned above. ... schema from them and have them call stored procedures or views to get the ... DBI/DBD) would prepare/execute: ... The main goal here is to isolate the developer from knowing the database ...
    (comp.databases.ms-sqlserver)
  • Re: Execute Immediate and bind variables
    ... sp_executesql before and I'm trying to use Execute Immediate to mimic ... The schema name is passed into the stored procedure. ... We concatenate the ...
    (comp.databases.oracle.misc)