pl/sql stored procedure 
Author Message
 pl/sql stored procedure

Hi,

I'm trying to run an Oracle stored procedure via script.

This works:
   $csr = $dbh->prepare ('begin insert_content (:p_AD_CREATIVE_ID,
:p_CONTENT_TYPE, :p_BITRATE_ID, :p_FORMAT_ID,
:p_CONTENT_ID,:p_AD_CREATIVE_CONTENT_ID, :p_EXE_STATUS, :p_SQLCODE,
:p_SQLERRMSG); end;');
   $csr->bind_param (":p_AD_CREATIVE_ID", $ACID);
   $csr->bind_param (":p_CONTENT_TYPE", $TYPE);
   $csr->bind_param (":p_BITRATE_ID", $p_bitrate);
   $csr->bind_param (":p_FORMAT_ID", $p_format);
   $csr->bind_param_inout (":p_CONTENT_ID", \$content_id, 1);
   $csr->bind_param_inout (":p_AD_CREATIVE_CONTENT_ID",
\$ad_creative_content_id, 1);
   $csr->bind_param_inout (":p_EXE_STATUS", \$exe_status, 1);
   $csr->bind_param_inout (":p_SQLCODE", \$sqlcode, 1);
   $csr->bind_param_inout (":p_SQLERRMSG", \$sqlerrmsg, 80);
   $csr->execute;

This doesn't:
   $dbh->do ("begin insert_content (?,?,?,?,?,?,?,?,?); end;",
             {},
             $ACID, 'ad', $p_bitrate, $p_format,
             $content_id, $ad_creative_content_id, $exe_status,
             $sqlcode, $sqlerrmsg);

Anyone see any obvious flaw?  Or know of any useful documentation?

TIA,
-Jack

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Wed, 18 Jun 1902 08:00:00 GMT  
 pl/sql stored procedure

Quote:

> This works:
>    $csr = $dbh->prepare ('begin insert_content (:p_AD_CREATIVE_ID,
> :p_CONTENT_TYPE, :p_BITRATE_ID, :p_FORMAT_ID,
> :p_CONTENT_ID,:p_AD_CREATIVE_CONTENT_ID, :p_EXE_STATUS, :p_SQLCODE,
> :p_SQLERRMSG); end;');
>    $csr->bind_param (":p_AD_CREATIVE_ID", $ACID);
>    $csr->bind_param (":p_CONTENT_TYPE", $TYPE);
>    $csr->bind_param (":p_BITRATE_ID", $p_bitrate);
>    $csr->bind_param (":p_FORMAT_ID", $p_format);
>    $csr->bind_param_inout (":p_CONTENT_ID", \$content_id, 1);
>    $csr->bind_param_inout (":p_AD_CREATIVE_CONTENT_ID",
> \$ad_creative_content_id, 1);
>    $csr->bind_param_inout (":p_EXE_STATUS", \$exe_status, 1);
>    $csr->bind_param_inout (":p_SQLCODE", \$sqlcode, 1);
>    $csr->bind_param_inout (":p_SQLERRMSG", \$sqlerrmsg, 80);
>    $csr->execute;

> This doesn't:
>    $dbh->do ("begin insert_content (?,?,?,?,?,?,?,?,?); end;",
>              {},
>              $ACID, 'ad', $p_bitrate, $p_format,
>              $content_id, $ad_creative_content_id, $exe_status,
>              $sqlcode, $sqlerrmsg);

> Anyone see any obvious flaw?  Or know of any useful documentation?

The obvious flaw is that you didn't include

        or warn $dbh->errstr;
or
        $dbh->{'RaiseError'} = 1;

or forgot to send us the result or didn't tell use, what exactly went
wrong (where your expectations started to differ from reality). ;-)

But anyway, I don't think the variables get binded as INOUT in the
second call, and also you're sending them as scalars, not as
references to scalars.

I'd say that you cannot shortcut the command much if you want to use
INOUT parameters. You could bind_param_inout only those that need it
and provide the other ones in the execute call, but you cannot avoid
the prepare step.

Yours,

--
------------------------------------------------------------------------

   .project: Perl, DBI, Oracle, MySQL, auth. WWW servers, MTB, Spain.
Petition for a Software Patent Free Europe http://petition.eurolinux.org
------------------------------------------------------------------------



Wed, 18 Jun 1902 08:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Executing DEL and UPDT PL/SQL stored procedures from PERL

2. Executing DEL and UPDT PL/SQL stored procedures from PERL

3. Binding Cursors returned from PL/SQL stored procedure

4. pl/sql stored procedure with Table input

5. OraPerl and PL/SQL Stored Procedure

6. Does anyone know Perl call PL/SQL store procedure URL links?

7. LONG: Req: Help in retrieving values from SQL Server stored procedure using Win32::ODBC

8. ODBC, SQL Server, Stored Procedures

9. Perl Library to execute stored procedures in MS-SQL Server

10. SQL 2000 Stored procedure called from PERL not responding

11. Using Embedded SQL vs Stored procedures in Perl DBI?DBD for Oracle

12. ODBC, SQL Server, Stored Procedures

 

 
Powered by phpBB® Forum Software