|
Home > Archive > Other Oracle database topics > August 2005 > Execute Immediate and bind variables
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Execute Immediate and bind variables
|
|
| brightspot 2005-08-10, 8:23 pm |
| 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_theFiel
d 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
| |
| Matthias Kleinicke 2005-08-10, 8:23 pm |
| Hi Chris,
brightspot schrieb:
> 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
>
whats the point in replacing the parameter with anythig else?
a bind variable is a placeholder for a value. so
you can only replace constants. tablenames and keyword can not substituted.
greetings
Matthias
| |
| brightspot 2005-08-10, 8:23 pm |
| 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
| |
| Sybrand Bakker 2005-08-10, 8:23 pm |
| On 10 Aug 2005 15:01:36 -0700, "brightspot" <brightspot5@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
>
>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
| |
| brightspot 2005-08-10, 8:23 pm |
| Oops. I forgot the tick marks in execute my_qry('Yourschema')
.
Thanks for your help.
| |
| Chris ( Val ) 2005-08-10, 8:23 pm |
|
brightspot wrote:
> Oops. I forgot the tick marks in execute my_qry('Yourschema')
.
Tick marks ???
I cannot ever recall having heard of them as being
called that before :-)
Cheers,
Chris Val
| |
| Jim Kennedy 2005-08-11, 3:24 am |
|
"brightspot" <brightspot5@hotmail.com> wrote in message
news:1123711296.563366.284890@g47g2000cwa.googlegroups.com...
> 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
>
> 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
>
This is usually a bad idea. direct ports of SQL Server practices to Oracle
usually turn out to be a big mistake.
SQL Server != Oracle
Jim
| |
|
| On Wed, 10 Aug 2005 14:17:01 -0700, brightspot interested us by writing:
> 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.
If you have not done so, PLEASE get and read a copy of the following books
before you proceed too much further:
By Thomas Kyte:
Effective Oracle by Design, and
Expert One on One Oracle
By McDonald, et. al
Mastering Oracle PL/SQL
By Jonathan Lewis
Practical Oracle8i
Regardless of the version, these books will give you very important
information about the conversion you are attempting, as well as how to
avoid pitfalls in some of the more common conversion techniques due to the
differences in the internal implementation of the various SQL engines.
The above is not intended to put any judgement on your skills, but to
provide additional, very significant, resources you may not have
encountered to this point.
--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***
| |
| brightspot 2005-08-11, 1:24 pm |
| Hans,
I'll look into the books, thanks.
We are newbies at this sort of translation. We used the Oracle
Migration Workbench to do the bulk of the translation and we're now
wading though the views and procedures that didn't translate properly.
Do you know if the Workbench does a good job? We were hoping to keep
the SQL Server and Oracle implementations as close to each other as
possible because we have to maintain both. From your comments above,
that doesn't really sound like the best idea.
Any thoughts from anyone would be appreciated.
Thanks,
Chris
| |
|
| On Thu, 11 Aug 2005 06:22:42 -0700, brightspot interested us by writing:
> We were hoping to keep
> the SQL Server and Oracle implementations as close to each other as
> possible because we have to maintain both.
Think of it this way ...
You have a Wankel rotary engine, an inline engine and a diesel engine.
All accomplish the same job of propelling the vehicle, but they do it
differently. Are you going to use the same tools and maintenance
techniques for all three engines?
Bottom line - learn your tool and you will do the best job for your
client. Individual SQL statements will be the same, or at least very
similar, but the way they are strung together in an application may be
very different. The books I indicated describe what is needed.
--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***
| |
| Frank van Bortel 2005-08-11, 1:24 pm |
| HansF wrote:
> On Thu, 11 Aug 2005 06:22:42 -0700, brightspot interested us by writing:
>
>
>
>
> Think of it this way ...
>
> You have a Wankel rotary engine, an inline engine and a diesel engine.
> All accomplish the same job of propelling the vehicle, but they do it
> differently. Are you going to use the same tools and maintenance
> techniques for all three engines?
>
> Bottom line - learn your tool and you will do the best job for your
> client. Individual SQL statements will be the same, or at least very
> similar, but the way they are strung together in an application may be
> very different. The books I indicated describe what is needed.
>
Like the analogy - ever tried to change the spark plugs on the latter?
Now... would that be SS2K? - Flame war! :evil:
--
Regards,
Frank van Bortel
| |
| Matthias Kleinicke 2005-08-11, 8:23 pm |
| hi,
brightspot schrieb:
> 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
>
>
>
> I'm still getting an error testing the SP from SQLPlus:
> BEGIN MY_QRY (schemaName); END;
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
>
greetings
|
|
|
|
|