Retrieving a value from an Oracle stored procedure. 1

[dja1]

I have the following Oracle Stored Procedure;

 Create or Replace Procedure CHECK_ACCOUNT_NO
                (SortCode      In   Char,
                 Account_Check In   Char,
                 Check_Ind     Out  Number)
As
Begin
    .
    (Code which sets Check_Ind to 0 or 1) 
    ;
End;

I have the following VB6 code;

 Dim LNGRETURN   As Long

   strSQL = "BEGIN  CHECK_ACCOUNT_NO _
                     ('SORTCD', 'ABCDEFGH', LNGRETURN); End;"
   Db.Execute strSQL, dbSQLPassThrough

When execute this code, I get the following error;
Error Message: [Microsoft][ODBC driver for Oracle][Oracle]ORA-06550: line 1, column 46:
PLS-00201: identifier 'LNGRETURN' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ODBC--call failed.

I have to use the DAO Object Model - it would be too expensive to use ADO instead.

How do I get the value for the returned parameter in my application ?

 

[vladk]

If you have just one returned value, why you don't use the function? It would seem more logical.

 

[dja1]

What function ?

 

[vladk]

The stored function. Instead of stored procedure. Why do you use procedure instead of the function? And if you cannot transform one into another, try IN OUT instead of OUT.

 

[vladk]

And in any case, I would investigate possible use of DAO Parameters collection and Parameter property (but I never did it with DAO).

 

[vladk]

http://msdn.microsoft.com/archive/d...e/en-us/office97/html/output/F1/D2/S5A29A.asp

 

[dja1]

vladk

Thanks very much for taking the time to reply.

As for using a function, or changing the parameter definition, I have been told that I have to use the present Oracle stored procedure, there simply is no "budget" to change anything on the Oracle database, (I'm sure most of us have been in a similar situation).

 

[vladk]

dja1,

Did you solve your problem? I never used DAO with ORACLE. Instead, I used ORACLE client. And I just attached parameters to the parameters collection in VB(the stored procedure arguments), specifying the properties (type, direction). I assume, DAO might offer similar technique.

vladk

 

[BobRodes]

You're just trying to pass your variable name as part of the string. Here's what Oracle sees when you send the string:

BEGIN  CHECK_ACCOUNT_NO ('SORTCD', 'ABCDEFGH', LNGRETURN); End;

Obviously, it's barfing on the LNGRETURN, because you're really trying to pass whatever value LNGRETURN is set to. So, try this:

Dim LNGRETURN   As Long

   strSQL = "BEGIN  CHECK_ACCOUNT_NO _
                     ('SORTCD', 'ABCDEFGH', [COLOR=red]" & LNGRETURN & "[/color]); End;"
   Db.Execute strSQL, dbSQLPassThrough

I'd be surprised if this weren't your problem.

HTH

Bob

 

[dja1]

Thanks for the continuing replies. Not back in the office until 23/11/2006 - will try it then

 

[dja1]

Vladk
Can't see an equivalent to the ADO Parameters collection in DAO, (except for QDF, which I assume is/are apply only to Access databases).

Bob
Tried your suggestion, as follows;

Dim intCheckInd     As Integer
Dim strsql          As String

    strsql = "BEGIN CHECK_ACCOUNT_NO('090126', '07449544', "
    strsql = strsql & intCheckInd & "); END;"
    Db.Execute strsql, dbSQLPassThrough

The result in strSQL;
debug.Print strsql
BEGIN CHECK_ACCOUNT_NO('090126', '07449544', 0); END;
[/color blue]
Result of executing the SQL;

Error Message: [Microsoft][ODBC driver for Oracle][Oracle]ORA-06550: line 1, column 46:
PLS-00363: expression '0' cannot be used as an assignment target
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ODBC--call failed.
[/color blue]
Then tried;

Dim intCheckInd     As Integer
Dim strsql          As String
    strsql = "BEGIN CHECK_ACCOUNT_NO('090126', '07449544', intcheckind); END; "
    Db.Execute strsql, dbSQLPassThrough

The result in strSQL;
debug.Print strsql
BEGIN CHECK_ACCOUNT_NO('090126', '07449544', intcheckind); END;[/color blue]
Result of executing the SQL;

Error Message: [Microsoft][ODBC driver for Oracle][Oracle]ORA-06550: line 1, column 46:
PLS-00201: identifier 'INTCHECKIND' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ODBC--call failed.
[/color blue]
Then tried;

Dim Check_Ind       As Integer
Dim strsql          As String
    strsql = "BEGIN CHECK_ACCOUNT_NO('090126', '07449544', Check_Ind); END; "
    Db.Execute strsql, dbSQLPassThrough

Result of executing the SQL;

Error Message: [Microsoft][ODBC driver for Oracle][Oracle]ORA-06550: line 1, column 46:
PLS-00201: identifier 'CHECK_IND' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ODBC--call failed.
[/color blue]

I think I carried out your suggestion - any more ideas ??

 

[BobRodes]

Well, yes, you did. I thought that you were sending LNGRETURN as a variable, and failing to isolate it from inside the string. Which of course you were, but as it turns out that isn't really the problem.

You do not assume correctly that QueryDef objects only apply to Access databases. They apply to any database that can provide data to an ODBC connection, and I'm pretty confident that Oracle is one of those. I would take a look at the link that vladk sent; it has an example of how to get data from the pubs database in SQL server. It shouldn't be too difficult to modify it for Oracle.

HTH

Bob