Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Calling Oracle StoredProc using Win32:ODBC

by ranijoseph (Initiate)
on Jan 11, 2013 at 17:22 UTC ( #1012929=perlquestion: print w/ replies, xml ) Need Help??
ranijoseph has asked for the wisdom of the Perl Monks concerning the following question:

Hi

Im using Perl 5, version 14.The Win32::ODBC is VERSION = '0.034'; and Oracle as database. Im able to reteive information from the database using quries like "emp_id, emp_name from emp" by the following code

use Win32::ODBC; $db= new Win32::ODBC("DSN=datasourcename;UID=username;PWD=passwrd") || + die "Error: " . Win32::ODBC::Error(); $db->Sql("SELECT emp_Id, emp_name, salary FROM Sample.Emp"); while($db->FetchRow()) { @values = $db->Data; print @values; } $db->Close();

Instead of using quries in the perl program, I like to use stored procedures. I have created a storedproc called sp_rank.

PROCEDURE sp_rank(p_cursorVar out CursorType) is begin open p_cursorVar for select emp_id, emp_name from emp; End sp_rank;

I would like to know how to pass storedproc name in the perl and retrieve the data.

Comment on Calling Oracle StoredProc using Win32:ODBC
Select or Download Code
Re: Calling Oracle StoredProc using Win32:ODBC
by roboticus (Canon) on Jan 11, 2013 at 18:31 UTC

    ranijoseph:

    For calling Oracle stored procs (which I do *all the time*), I do it like this:

    ... my $ST=$DB->prepare("call apps.package_name.proc(?, ?, ?)"); my ($ST_result, $arg1, $arg2); ... $ST->bind_param(1, $arg1); $ST->bind_param(2, $arg2); $ST->bind_param_inout(3, \$ST_result, 0, { ora_type=>ORA_RSET } ); $ST->execute(); while (my $hr = $ST_result.fetchrow_hashref) { ... process data ... }

    This is for a stored procedure that looks something like:

    procedure proc( arg1 in varchar(32), arg2 in number, result out sys_cursor ) as begin open result for select col1, col2, 'FOO' col3 from tableFoo where col7=arg1 or col8=arg2 ; end;

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

      Thank you so much for the reply. I didn't understand the code very well.

      my $ST=$DB->prepare("call apps.package_name.proc(?, ?, ?)");

      what is apps?

      If we don't want to pass parameter, are we declare like my $ST=$DB->prepare("call apps.package_name.proc"). Could you please give me the code for calling the following procedure.

      PROCEDURE sp_rank(p_cursorVar out CursorType) is begin open p_cursorVar for select emp_id, emp_name from emp; End pension_rank;
      Thank you so much for your time.
        First I'd guess he's using DBI w/ DBD::ODBC or DBD::Oracle, I know I haven't used Win32::ODBC in quite some time.

        I'd also guess that 'apps.package_name' is related to the name space that the stored proc is in. For those details you'll want to talk to your DBA.

        Also the 'bind' statements are related to the parameters. Since you don't want to use parameters, you can safely ignore the binds...

        TJD

        ranijoseph:

        As the Anonymous Monk states above, it's the namespace the package resides in. I'm using DBI with DBD::Oracle, so the code may not be exactly what Win32::ODBC would use.

        The code would be something like:

        ... my $ST=$DB->prepare("call apps.package_name.sp_rank(?)"); my ($ST_result); ... $ST->bind_param_inout(1, \$ST_result, 0, { ora_type=>ORA_RSET } ); $ST->execute(); while (my $hr = $ST_result.fetchrow_hashref) { ... process data ... }

        But in any case, to call the procedure, you just use a "call SPROC(args...)" statement as you would normally use "select * from table". The difficulty may be in returning a recordset. For DBD::Oracle we bind another variable ($ST_result above) to the output cursor, and then we can treat it as a statement handle and retrieve the results from it. You'll have to fiddle with that to make it work in Win32::ODBC.

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

      You may already know this and not care but I'd be careful using "call" as it gobbles up NO_DATA_FOUND errors. I've been bitten by this myself in the past and it is documented by Oracle but I cannot remember where now. We switched to use "begin package.proc(?); end;" syntax ages ago which does not suffer the same problem.

Re: Calling Oracle StoredProc using Win32:ODBC
by sundialsvc4 (Monsignor) on Jan 11, 2013 at 22:14 UTC

    I assume that apps. is his database binding ... and, for what it may be worth, I strongly recommend that you do use bound parameters.   Basically, I advise that you replicate that example exactly as-given.   Here we have the voice of a very-experienced Roman ... and you are in Rome, so do what they do.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1012929]
Front-paged by Arunbear
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (9)
As of 2014-07-28 06:38 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (190 votes), past polls