HELP: Retrieve a resultset from ORACLE stored procedures 
Author Message
 HELP: Retrieve a resultset from ORACLE stored procedures

Dear all,

I desperately need a solution on how to retrieve resultset from oracle
stored procedure. I know that there is an article discussing about this
(Q221189), but when I tried to execute the sample program in the KB, I got
an error message.
Is there any special treatment in order to retrieve the result set?
I am using VFP 6, and ORACLE 8.0.5.

Have you ever tried the sample in Q221189? It didn't work in my system.

Regards,
Gunawan S.



Tue, 25 Feb 2003 15:37:48 GMT  
 HELP: Retrieve a resultset from ORACLE stored procedures

Gunawan,

It helps if you post the code you're using and also the text of the error
message.

Also, scroll down a little and see if there's anything in the "SQLEXEC()
syntax" thread that will help you.

--

Cindy Winegarden
Microsoft Certified Professional, Visual FoxPro

Duke Children's Information Systems
Duke University Medical Center


| Dear all,
|
| I desperately need a solution on how to retrieve resultset from oracle
| stored procedure. I know that there is an article discussing about this
| (Q221189), but when I tried to execute the sample program in the KB, I got
| an error message.
| Is there any special treatment in order to retrieve the result set?
| I am using VFP 6, and ORACLE 8.0.5.
|
| Have you ever tried the sample in Q221189? It didn't work in my system.
|
| Regards,
| Gunawan S.
|
|
|



Wed, 26 Feb 2003 08:11:37 GMT  
 HELP: Retrieve a resultset from ORACLE stored procedures

Hi Cindy,

thanks! Your suggestion to see the thread lead me to the right answer. Yes,
it is now solved. I just don't understand how come microsoft didn't check
the sample in KB Q221189 before post it there. Or, maybe it works only with
Oracle 7.x? But, surely it won't work with ORACLE 8.x and VFP 6.

In my case, I have a table named mtgoods in ORACLE 8.x. There are 2 columns
there, godgoodsno and goddesc. Both are in varchar2() type. Somehow, I need
to query the result via stored procedure (yes we can do it via simple select
statement, but there are another code that I don't show it here). So,
according to kb q221189 above, we need to create a package in oracle. Here
are the PL/SQL code:

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

create or replace package pkgcoba is

  -- Type declarations
  type goodscur is ref cursor ;

  -- Function and procedure declarations
  procedure getgoods(hasil out goodscur) ;

end pkgcoba;
/

create or replace package body pkgcoba is

  procedure  getgoods(hasil out goodscur) is

  begin
    OPEN hasil for SELECT godgoodsno, goddesc from mtgoods;
  end;

end;
/

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

The problem comes from the sqlexec() syntax. How can we retrieve the result
set? After reading the thread, I realize that I didn't put ? before the
parameter. After I put it, then everything is ok. Here is the sample code:

Proc_String3 = "{call pkgcoba.getgoods(?coba)}"
coba = ''
gnConnHandle=SQLCONNECT(mydatabase,myuserid,mypassword)

Get_One=SQLEXEC(gnConnHandle,Proc_String3,'sqlresult')
=SQLDISCONN(gnConnHandle)
CLOSE ALL

So, we need to put ? in front of coba, which is a dummy variable.

Thanks to you Cindy, and also Anders who post the thread

Regards,
Gunawan


Quote:
> Gunawan,

> It helps if you post the code you're using and also the text of the error
> message.

> Also, scroll down a little and see if there's anything in the "SQLEXEC()
> syntax" thread that will help you.

> --

> Cindy Winegarden
> Microsoft Certified Professional, Visual FoxPro

> Duke Children's Information Systems
> Duke University Medical Center



> | Dear all,
> |
> | I desperately need a solution on how to retrieve resultset from oracle
> | stored procedure. I know that there is an article discussing about this
> | (Q221189), but when I tried to execute the sample program in the KB, I
got
> | an error message.
> | Is there any special treatment in order to retrieve the result set?
> | I am using VFP 6, and ORACLE 8.0.5.
> |
> | Have you ever tried the sample in Q221189? It didn't work in my system.
> |
> | Regards,
> | Gunawan S.
> |
> |
> |



Wed, 26 Feb 2003 14:39:35 GMT  
 HELP: Retrieve a resultset from ORACLE stored procedures

Gunawan,

I'm so glad you've got it working now.

There's a lot to be learned here by reading the questions other people post
and the answers to them.

--

Cindy Winegarden
Microsoft Certified Professional, Visual FoxPro

Duke Children's Information Systems
Duke University Medical Center


| Hi Cindy,
|
| thanks! Your suggestion to see the thread lead me to the right answer.
Yes,
| it is now solved. I just don't understand how come microsoft didn't check
| the sample in KB Q221189 before post it there. Or, maybe it works only
with
| Oracle 7.x? But, surely it won't work with ORACLE 8.x and VFP 6.
|
| In my case, I have a table named mtgoods in ORACLE 8.x. There are 2
columns
| there, godgoodsno and goddesc. Both are in varchar2() type. Somehow, I
need
| to query the result via stored procedure (yes we can do it via simple
select
| statement, but there are another code that I don't show it here). So,
| according to kb q221189 above, we need to create a package in oracle. Here
| are the PL/SQL code:
|
| ------------------------
|
| create or replace package pkgcoba is
|
|   -- Type declarations
|   type goodscur is ref cursor ;
|
|   -- Function and procedure declarations
|   procedure getgoods(hasil out goodscur) ;
|
| end pkgcoba;
| /
|
|
| create or replace package body pkgcoba is
|
|   procedure  getgoods(hasil out goodscur) is
|
|   begin
|     OPEN hasil for SELECT godgoodsno, goddesc from mtgoods;
|   end;
|
| end;
| /
|
| ----------------
|
| The problem comes from the sqlexec() syntax. How can we retrieve the
result
| set? After reading the thread, I realize that I didn't put ? before the
| parameter. After I put it, then everything is ok. Here is the sample code:
|
| Proc_String3 = "{call pkgcoba.getgoods(?coba)}"
| coba = ''
| gnConnHandle=SQLCONNECT(mydatabase,myuserid,mypassword)
|
| Get_One=SQLEXEC(gnConnHandle,Proc_String3,'sqlresult')
| =SQLDISCONN(gnConnHandle)
| CLOSE ALL
|
| So, we need to put ? in front of coba, which is a dummy variable.
|
|
| Thanks to you Cindy, and also Anders who post the thread
|
| Regards,
| Gunawan
|
|


| > Gunawan,
| >
| > It helps if you post the code you're using and also the text of the
error
| > message.
| >
| > Also, scroll down a little and see if there's anything in the "SQLEXEC()
| > syntax" thread that will help you.
| >
| >
| > --
| >
| >
| > Cindy Winegarden
| > Microsoft Certified Professional, Visual FoxPro
| >
| > Duke Children's Information Systems
| > Duke University Medical Center

| >
| >
| >


| > | Dear all,
| > |
| > | I desperately need a solution on how to retrieve resultset from oracle
| > | stored procedure. I know that there is an article discussing about
this
| > | (Q221189), but when I tried to execute the sample program in the KB, I
| got
| > | an error message.
| > | Is there any special treatment in order to retrieve the result set?
| > | I am using VFP 6, and ORACLE 8.0.5.
| > |
| > | Have you ever tried the sample in Q221189? It didn't work in my
system.
| > |
| > | Regards,
| > | Gunawan S.
| > |
| > |
| > |
| >
| >
|
|



Wed, 26 Feb 2003 17:56:41 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Retrive a resultset from Oracle stored procedure

2. Returning values from Oracle Stored Procedure or Stored function to VFP 5

3. calling oracle stored procedure

4. Oracle Stored Procedures

5. Running Oracle Stored Procedures from FoxPro

6. Oracle Stored Procedure

7. Oracle Stored procedures

8. Stored Procedure Oracle - Vfp 6.0

9. Retuning values from ORACLE Stored Procedure to VFP 5

10. Invoking Oracle Stored Procedures

11. Accessing Oracle stored procedures

12. oracle stored procedures

 

 
Powered by phpBB® Forum Software