Help.....Stored Procs. and Oracle (Return values???)

Help.....Stored Procs. and Oracle (Return values???)

Post by Ian Abrams » Thu, 09 Feb 1995 00:13:51



        I need some more help with stored procedures....

        I can get the procedure working ..... but I can not get Oracle to
pass the return values to PB.

        What am I doing wrong here????

        1) I DECLARE the procedure

                In the declare I note the 3 parms. 2 IN, and 1 OUT, but I
                get and Oracle error from that...6550? It says that I have
                the wrong number or types od arguments. The types seem
                OK.

        2) When I work with only IN parms fine.....

        Can someone out there please let me know how to use Stored procs
to RETURN a value (number or string) from the stored proc.......

        Please help save a young man from abandoning the correct
application path, because features of PB are so poorly documented.

Thanks,

Ian Abramson
--
===========================================================================
Ian Abramson                            ||   I think I am losing touch    |

===========================================================================

 
 
 

Help.....Stored Procs. and Oracle (Return values???)

Post by Ewan Humphr » Thu, 09 Feb 1995 06:10:06



>    I need some more help with stored procedures....
>    I can get the procedure working ..... but I can not get Oracle to
> pass the return values to PB.
>    What am I doing wrong here????
>    1) I DECLARE the procedure
>            In the declare I note the 3 parms. 2 IN, and 1 OUT, but I
>            get and Oracle error from that...6550? It says that I have
>            the wrong number or types od arguments. The types seem
>            OK.
>    2) When I work with only IN parms fine.....
>    Can someone out there please let me know how to use Stored procs
> to RETURN a value (number or string) from the stored proc.......

I had the same problems when I first implemented SP's. The solution is
partly shown in the Oracle Interface guide, pages 34-36.
The way to do it correctly is:

DECLARE proc_name PROCEDURE FOR oracle_name(In_params_only)

EXECUTE proc_name (in_parms)

FETCH proc_name INTO out_params  // this is the only place for out_params

CLOSE proc_name

We check for SQL errors after each step, so we can confirm whether the
data inputted was not valid, or an actual DB error occurred.

regards,
Ewan

>    Please help save a young man from abandoning the correct
> application path, because features of PB are so poorly documented.
> Thanks,
> Ian Abramson
> --
> ===========================================================================
> Ian Abramson                               ||   I think I am losing touch    |

> ===========================================================================

--
--------------------------------------------------
Ewan Humphrey,  BComp(InfoSys), AACS, PCP
Telecom Australia,                          Voice: +61-3-868-9477


"Any opinions are mine, No one else wanted them" - Unknown

 
 
 

Help.....Stored Procs. and Oracle (Return values???)

Post by Stevi G » Sat, 18 Feb 1995 11:56:28


Hi,

Assume - stored procedure SP_FOO_GET has two input params s_foo_1 and
s_foo_2 - and one output param s_bla (all of type string)

string   s_foo_1, s_foo_2, s_bla

// declare procedure - give it a valid powerbuilder name (sp_foo)

DECLARE sp_foo PROCEDURE FOR SP_FOO_GET
      (:s_foo_1, :s_foo_2) ;

EXECUTE sp_foo ;

// to get the values passed back issue a FETCH statement

FETCH sp_foo into :s_bla ;

// if return params - must close procedure

CLOSE sp_foo ;

-- must make sure that the params you are using in Powerbuilder are of the
same or compatable data types as ORACLE.

Hope this helps.

 
 
 

1. HELP - Capturing Stored Procedures Return Values

I am calling a stored procedure from a Window, there is no DBERROR event,
my (SQL) stored procedure if it does not work properly will rollback the
transaction and raise an error code, I need to test in my window if the
stored procedure worked or not,  I am not sure how to do this! Where will
this error code be stored and how do I trap for it in a Window?


Thanks!

2. GNU CC

3. Stored Proc return codes from Sybase

4. HMP IX 4400 experiences

5. stored procs - returning control during exec

6. Minolta Scan Dual II - colour correction

7. Problem running Store Proc which returns selects

8. Quicken under OS/2 - HELP

9. Return value from an SQL stored procedure

10. SQL - return value from stored procedure

11. Checking return value from a stored procedure

12. Get a returned value from Watcom stored procedures?

13. STORED PROCEDURE RETURNING A VALUE