Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Re^2: Calling Oracle StoredProc using Win32:ODBC

by ranijoseph (Initiate)
on Jan 11, 2013 at 20:30 UTC ( #1012972=note: print w/replies, xml ) Need Help??

in reply to Re: Calling Oracle StoredProc using Win32:ODBC
in thread Calling Oracle StoredProc using Win32:ODBC

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.

Replies are listed 'Best First'.
Re^3: Calling Oracle StoredProc using Win32:ODBC
by roboticus (Chancellor) on Jan 12, 2013 at 03:56 UTC


    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.


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

Re^3: Calling Oracle StoredProc using Win32:ODBC
by Anonymous Monk on Jan 11, 2013 at 21:28 UTC
    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...


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://1012972]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2017-03-29 03:51 GMT
Find Nodes?
    Voting Booth?
    Should Pluto Get Its Planethood Back?

    Results (343 votes). Check out past polls.