denzil_cactus has asked for the wisdom of the Perl Monks concerning the following question:

Hi, I am trying to pass a variable to a PL/SQL block like this
$dbh->func( 1000000, 'dbms_output_enable' ); $user_location = "NYC"; $sth = $dbh->prepare(q{ DECLARE name VARCHAR2(50); address1 VARCHAR2(50); address2 VARCHAR2(50); address3 VARCHAR2(50); phone VARCHAR2(50); fax VARCHAR2(50); fax1 VARCHAR2(50); BEGIN select name, address1, address2, address3 || ' ' || state || ' ' || zip_cnty, 'PHONE: ' || telephone, 'FAX: ' || +fax, fax || '.' into name, address1, address2, address3, phone, fax, fax1 from custdata2 where custdata2.code = $user_location; dbms_output.put_line('The value is '||name); END; }); $sth->execute; $dbh->func( $date_string, 'dbms_output_put' ); $values = $dbh->func('dbms_output_get' ); $sth->finish; print "Returned $values \n";
------------------------------- but its giving me the error
DBD::Oracle::st execute failed: ORA-06550: line 43, column 33: PL/SQL: ORA-00911: invalid character
Please suggest

Replies are listed 'Best First'.
Re: Problem in passing value to a PL/SQL block
by almut (Canon) on Jul 11, 2007 at 09:28 UTC

    To have $user_location be interpolated, you want qq{ ... }  (q{} is functionally the same as single quotes).

      Yeah Thanks !!!! It works now
Re: Problem in passing value to a PL/SQL block
by wind (Priest) on Jul 11, 2007 at 10:05 UTC
    Use placeholders. The new sql snippet
    where custdata2.code = ?;
    And the execute statement
    $sth->execute($user_location) or die $dbh->errstr;
    - Miller