FAQ
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: dbi-users@perl.org
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 dw@DWT - newpw is atari800
2 Executing alter user :DBUser identified by blarg
for dw@DWT - 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.

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 3 of 3 | next ›
Discussion Overview
groupdbi-users @
categoriesperl
postedJun 7, '05 at 3:35p
activeJun 7, '05 at 7:21p
posts3
users3
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase