FAQ
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, <DATF> 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, <DATF> 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

Search Discussions

  • Reidy, Ron at Jun 7, 2005 at 4:17 pm
    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, <DATF> 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, <DATF> 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.
  • Job Miller at Jun 7, 2005 at 7:21 pm
    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.

Related Discussions

Discussion Navigation
viewthread | post
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