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 Received on Wed Aug 10 2005 - 16:17:01 CDT