DDL and bind variables don't mix.
You have to use:
BEGIN
EXECUTE IMMEDIATE 'alter user :user identified by blarg' using 'DW'
END;
where you bind/substitute :x via the pl/sql block.
I believe you can substitute 'DW' with :user and than bind_param to the execute immediate statement. I *think* this should work. or create a proc that takes a parameter. bind the user to the parameter and use the parameter in the "using :input_param"
Job
"Reidy, Ron" wrote:
From metalink note 1007844.6:
Problem Description:
====================
Getting ora-01036: illegal variable name/number at runtime on the EXECUTE of a
dynamic SQL statement.
Solution Description:
====================
This is often caused by improper error checking on the PREPARE statement.
Solution Explanation:
====================
If the statement is not parsed correctly (the prepare statement fails) and this
is not detected properly by the program, the EXECUTE can fail with the ORA-01036
error or with similar errors. Make sure that you check sqlca.sqlcode after the
PREPARE explicitly or implicitly with a WHENEVER SQLERROR statement.
In practice, you should always check the return from all prepare() and execute() statements, or set RaiseError => 1.
-----------------
Ron Reidy
Lead DBA
Array BioPharma, Inc.
303.386.1480
-----Original Message-----
From: Vergara, Michael (TEM)
Sent: Tuesday, June 07, 2005 9:34 AM
To:
[email protected]Subject: SQL Parsing question
After yesterday's dummy question, I'm afraid to reveal more of my
ignorance, but here goes.
I think that this code:
$SQLstmt =
q{ alter user :DBUser identified by blarg };
print "1 Executing ",$SQLstmt, "\nfor ${pwUSR}\@${pwSID} - newpw is $pwNEWPW\n";
$SQLh = $DBh->prepare( $SQLstmt,{ora_check_sql => 0} );
print "2 Executing ",$SQLstmt, "\nfor ${pwUSR}\@${pwSID} - newpw is $pwNEWPW\n";
$SQLh->bind_param( ":DBUser", uc($pwUSR) );
print "3 Executing ",$SQLstmt, "\nfor ${pwUSR}\@${pwSID} - newpw is $pwNEWPW\n";
...ought to work. However, I get these errors...
1 Executing alter user :DBUser identified by blarg
for
[email protected] - newpw is atari800
2 Executing alter user :DBUser identified by blarg
for
[email protected] - newpw is atari800
DBD::Oracle::st bind_param failed: ORA-01036: illegal variable name/number (DBD ERROR: OCIBindByName) [for Statement " alter user :DBUser identified by blarg " with ParamValues: :dbuser='DW'] at ./cpw1.pl line 273, line 54.
DBD::Oracle::st bind_param failed: ORA-01036: illegal variable name/number (DBD ERROR: OCIBindByName) [for Statement " alter user :DBUser identified by blarg " with ParamValues: :dbuser='DW'] at ./cpw1.pl line 273, line 54.
I can execute the command 'alter user DW identified by blarg' without
any errors, and I've never had any problems with parameter replacement
before. I have tried both named parameters (:DBUser) and blind
parameters ('?') to no avail.
What am I not seeing?
Thanks,
Mike
This electronic message transmission is a PRIVATE communication which contains
information which may be confidential or privileged. The information is intended
to be for the use of the individual or entity named above. If you are not the
intended recipient, please be aware that any disclosure, copying, distribution
or use of the contents of this information is prohibited. Please notify the
sender of the delivery error by replying to this message, or notify us by
telephone (877-633-2436, ext. 0), and then delete it from your system.
---------------------------------
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
