This forum is in READ-ONLY mode.
You can look around, but if you want to ask a new question, please use Stack Overflow.

How to get Oracle output from function/stored procedure?

Notes and reports about symfony 1.0.x stable

How to get Oracle output from function/stored procedure?

by armyofda12mnkeys » Wed Dec 16, 2009 6:15 pm

Hey, I had a question today about Oracle (11)/symfony 1.0.21...

I'm trying to get output params from an Oracle stored procedure into symfony...

In Java, I think it would be something like:

Code: Select all
stmt = con.prepareCall("begin ? := pkg_foo.fn_bar(?,?); end;");
stmt.registerOutParameter(1, OracleTypes.VARCHAR);
stmt.setString(1, class_id);
stmt.setString(2, student_id);
stmt.execute();
output_result = stmt.getObject(1).toString();



In symfony, it would probably start off looking something like:

Code: Select all
$connection->prepareStatement("begin ? := pkg_foo.fn_bar(?,?); end;");
$statement->setString(1,$class_id);
$statement->setString(2,$student_id);
$statement->executeQuery();
// ??? How to get the Oracle output
armyofda12mnkeys
Junior Member
 
Posts: 10
Joined: Tue Nov 10, 2009 6:47 pm

Re: How to get Oracle output from function/stored procedure?

by halfer » Wed Dec 16, 2009 7:21 pm

Someone else asked the very same question today! Do a search? :roll:
halfer
Faithful Member
 
Posts: 10148
Joined: Mon Jan 30, 2006 1:16 pm
Location: West Midlands, UK

Re: How to get Oracle output from function/stored procedure?

by armyofda12mnkeys » Wed Dec 16, 2009 7:28 pm

I know i saw that and was wondering if i should post on that thread. I wasnt sure if his was slightly different. he seemed to have it integrated with an object class, i just want the lines of code to do it in my actions. I'll take a look again to see if it helps.

EDIT: guess that was yer code to make output params work?
It returns a cursor?

What if returns just a string value (like mine returns "Error: blah" and i wanted to check that in my app).

Should I drop your classes into lib/ and then use addOutputParameter and getOutputParameter as needed?
armyofda12mnkeys
Junior Member
 
Posts: 10
Joined: Tue Nov 10, 2009 6:47 pm

Re: How to get Oracle output from function/stored procedure?

by armyofda12mnkeys » Wed Dec 16, 2009 7:43 pm

Curious, can you post an example on how to use the class without extending another class, like if I was just using it standalone?

Trying to figure out how to work in into my action
armyofda12mnkeys
Junior Member
 
Posts: 10
Joined: Tue Nov 10, 2009 6:47 pm

Re: How to get Oracle output from function/stored procedure?

by halfer » Thu Dec 17, 2009 11:44 am

Should be quite easy. Something like:

<span name="php"><code><span style="color: #000000">
<span style="color: #0000BB"><?php $sp </span><span style="color: #007700">= new </span><span style="color: #0000BB">StoredProcedure</span><span style="color: #007700">(</span><span style="color: #DD0000">'PACKAGE.HelloWorld'</span><span style="color: #007700">, </span><span style="color: #0000BB">$conn</span><span style="color: #007700">); </span><span style="color: #0000BB">$sp</span><span style="color: #007700">-></span><span style="color: #0000BB">addParameter</span><span style="color: #007700">(</span><span style="color: #DD0000">'name'</span><span style="color: #007700">, </span><span style="color: #DD0000">'halfer'</span><span style="color: #007700">, </span><span style="color: #0000BB">SQLT_CHR</span><span style="color: #007700">); </span><span style="color: #0000BB">$sp</span><span style="color: #007700">-></span><span style="color: #0000BB">addOutputParameter</span><span style="color: #007700">(</span><span style="color: #0000BB">$outAll </span><span style="color: #007700">= </span><span style="color: #DD0000">'OutAll'</span><span style="color: #007700">, </span><span style="color: #0000BB">SQLT_INT</span><span style="color: #007700">); </span><span style="color: #0000BB">$sp</span><span style="color: #007700">-></span><span style="color: #0000BB">addOutputCursor</span><span style="color: #007700">(</span><span style="color: #0000BB">$cName </span><span style="color: #007700">= </span><span style="color: #DD0000">'OutResults'</span><span style="color: #007700">); try {     </span><span style="color: #0000BB">$sp</span><span style="color: #007700">-></span><span style="color: #0000BB">execute</span><span style="color: #007700">(); } catch (</span><span style="color: #0000BB">StoredProcedureException $e</span><span style="color: #007700">) {     </span><span style="color: #FF8000">// ... your handling code here </span><span style="color: #007700">} echo </span><span style="color: #0000BB">getOutputParameter</span><span style="color: #007700">(</span><span style="color: #0000BB">$outAll</span><span style="color: #007700">); </span><span style="color: #FF8000">// Use $objectify = true if you want to populate a  CursorObject </span><span style="color: #0000BB">print_r</span><span style="color: #007700">(</span><span style="color: #0000BB">$sp</span><span style="color: #007700">-></span><span style="color: #0000BB">hydrateResults</span><span style="color: #007700">(</span><span style="color: #0000BB">$cName</span><span style="color: #007700">, </span><span style="color: #0000BB">$objectify </span><span style="color: #007700">= </span><span style="color: #0000BB">false</span><span style="color: #007700">)); </span><span style="color: #0000BB">?></span>
</span>
</code></span>
halfer
Faithful Member
 
Posts: 10148
Joined: Mon Jan 30, 2006 1:16 pm
Location: West Midlands, UK

Re: How to get Oracle output from function/stored procedure?

by halfer » Thu Dec 17, 2009 11:45 am

For other readers, the thread in question is here:

http://forum.symfony-project.org/index.php/t/24386/
halfer
Faithful Member
 
Posts: 10148
Joined: Mon Jan 30, 2006 1:16 pm
Location: West Midlands, UK

Re: How to get Oracle output from function/stored procedure?

by dorjsuren » Fri May 16, 2014 4:11 am

Thanks, It's really helpful for me.

But cannot download attachment files from oldforum.
StoredProcedure.class.php
CursorObject.class.php
StoredProcedureException.class.php

How i download them.

5 years later. :cry:
dorjsuren
Junior Member
 
Posts: 1
Joined: Thu May 15, 2014 12:04 pm