Oracle Stored Procedures 
Author Message
 Oracle Stored Procedures

Quote:

> Has anybody used Oracle7 stored procedures through VB4 and ODBC?

> If the procedure only has input parameters, it is easy:

> MyDb.Execute "begin myProc(7); end;", dbSQLPassThrough

> calls the stored procedure myProc with its input parameter set to 7.

> But what if I have output parameters as well? How to I specify
> the variable where the result should be stored?

> Sth like MyDb.Execute "begin getValue(:result); end;", dbSQLPassThrough

> Thanks.

> ===================================================================
> Stephan W. Haller
> Matsushita Electric Works, Ltd.      Tel.    (06) 906 - 8075
> Central Research Laboratory          Fax     (06) 904 - 7104

>         http://www.*-*-*.com/
> -------------------------------------------------------------------
> "   The brain is a wonderful organ.  It starts working the moment "
> "   you get up and does not stop until you get into the office.   "
> "                                               - Robert Frost    "
> ===================================================================

Stephan,

Unfortunately, the current version of PL/SQL used in stored procedures
does not support returning information.  They are meant as a fire-and-forget
solution.  I'm told this is being addressed in the next version of the
product -- so we might see it when Oracle 7.3 rolls out the door.

Respectfully,
JL.



Tue, 01 Sep 1998 03:00:00 GMT  
 Oracle Stored Procedures
Has anybody used Oracle7 stored procedures through VB4 and ODBC?

If the procedure only has input parameters, it is easy:

MyDb.Execute "begin myProc(7); end;", dbSQLPassThrough

calls the stored procedure myProc with its input parameter set to 7.

But what if I have output parameters as well? How to I specify
the variable where the result should be stored?

Sth like MyDb.Execute "begin getValue(:result); end;", dbSQLPassThrough

Thanks.

===================================================================
Stephan W. Haller
Matsushita Electric Works, Ltd.      Tel.    (06) 906 - 8075
Central Research Laboratory          Fax     (06) 904 - 7104

       http://www.lookup.com/Homepages/54541/home.html
-------------------------------------------------------------------
"   The brain is a wonderful organ.  It starts working the moment "
"   you get up and does not stop until you get into the office.   "
"                                          - Robert Frost    "
===================================================================



Tue, 01 Sep 1998 03:00:00 GMT  
 Oracle Stored Procedures


Quote:

>> Has anybody used Oracle7 stored procedures through VB4 and ODBC?

>> If the procedure only has input parameters, it is easy:

>> MyDb.Execute "begin myProc(7); end;", dbSQLPassThrough

>> calls the stored procedure myProc with its input parameter set to 7.

>> But what if I have output parameters as well? How to I specify
>> the variable where the result should be stored?

>> Sth like MyDb.Execute "begin getValue(:result); end;", dbSQLPassThrough

>> Thanks.

>> ===================================================================
>> Stephan W. Haller
>> Matsushita Electric Works, Ltd.      Tel.    (06) 906 - 8075
>> Central Research Laboratory          Fax     (06) 904 - 7104

>>        http://www.lookup.com/Homepages/54541/home.html
>> -------------------------------------------------------------------
>> "   The brain is a wonderful organ.  It starts working the moment "
>> "   you get up and does not stop until you get into the office.   "
>> "                                               - Robert Frost    "
>> ===================================================================

>Stephan,

>Unfortunately, the current version of PL/SQL used in stored procedures

Bzzt-wrong answer. PL/SQL, in version 2.2/Oracle 7.2 supports and has supported
for a few releases IN and OUT parameters. ODBC just doesn't support them.
Being able to pass a literal as an in parameter isn't really IN param support.
Full support would mean being able to change param values and re-execute
with parse/bind/define etc. All of this can be accomplished with the
current version of PLSQL and OCI or Pro*C or Oracle Objects for OLE.

Also, PLSQL currently supports arrays of a single scalar item. ODBC does
not support this either. Basically ODBC is a generic solution which
doesn't work well with Oracle PLSQL-and ODBC wasn't designed to.

Finally, in PLSQL 2.3/Oracle7.3 arrays of records can be returned as
can cursors, so selects can be done from them. Again, ODBC will most
likely not support this since it's too vendor specific.

Anyway, you should read up on PLSQL-it's much more useful in a non-ODBC
environment.

- Show quoted text -

Quote:
>does not support returning information.  They are meant as a fire-and-forget
>solution.  I'm told this is being addressed in the next version of the
>product -- so we might see it when Oracle 7.3 rolls out the door.

>Respectfully,
>JL.



Wed, 02 Sep 1998 03:00:00 GMT  
 Oracle Stored Procedures
A friend of mine gave me the following idea :
- Write stored procedure a a function
- Call it by : select  stored_function( par1, ... ) from sys.dual

I haven't tested it. Hope this helps.

---------------------------- Veikko Visala -------------------------------

    mobile : +358 50 5120 702 ( work ), +358 50 5599 543 ( freetime )
Addr: Mikrolinna Oy, Sibeliuksenkatu 5 A 4 krs, 13100 Hameenlinna, Finland



Fri, 04 Sep 1998 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Calling Oracle Stored Procedure

2. RDO Error while retrieving resultset from ORACLE stored procedure using VB5

3. VB and Oracle Stored Procedures???

4. Help? Accessing oracle stored procedure

5. Running an Oracle Stored Procedure from Access 8.0

6. Executing an oracle stored procedure from access 95

7. call Oracle stored procedure from Excel?

8. does MS support Oracle Stored Procedures?

9. oracle stored procedure

10. Calling an Oracle Stored Procedure

11. Calling Oracle Stored Procedure from VB

 

 
Powered by phpBB® Forum Software