Call a PLSQL Procedure with VB

How to call a PLSQL-Procedure with IN-OUT-Parameters.
mario_baAsked:
Who is Participating?
 
vspeterConnect With a Mentor Commented:
Okie,

1. I guess, You've set all parameters (from VB) correctly in term of Direction, Type and Value (if as IN, IN-OUT).

2. You've set all parameter (from Oracle PL/SQL) correctly in term of Type and Direction. (Keep in mind, If you would to refer to my first suggestion, there are 3 parameters from VB, but you only have to define 2 parameters in Oracle PL/SQL. Parameter(0) will hold the value of 1, IF AND WHEN PL/SQL (from Oracle) executes the statement RETRUN 1 or whatever number you want it to be.

3. For any other parameters you defined as IN, IN-OUT from VB, you would retrieve just like parameter(0) (Refer to my first suguestion). From Oracle PL/SQL side, you would have to do a SELECT statement, for example, the parameter name is '@RewardPoint' and field name is 'Point' from table name 'Reward' then

   SELECT @RewardPoint = Point From Reward

The suggestion above (1,2,3) is only apply if you want to return 1-n value (OUTPUT). But if you want to return 1-n RECORDS, then you are talking about different thing.

Below is a piece of code cut out from our system to retrieve 1-n RECORDS from database

With lrdoQry
 .SQL = "{? = Call cms_GetCodeTypeList (?)}"
 .CursorType = rdUseServer
 .LockType = rdConcurReadOnly
 Set .ActiveConnection = gRDOCon
 .rdoParameters(0).Direction = rdParamReturnValue

 .rdoParameters(1).Direction = rdParamInput
 .rdoParameters(1).Type = rdTypeVARCHAR
 .rdoParameters(1).Value = rsParameterValue
End With

Set lrdoRSet = lrdoQry.OpenResultset(rdOpenForwardOnly)
   
miNumbRow = 0

While Not lrdoRSet.EOF
 miNumbRow = miNumbRow + 1
       
 maSumOptItemList_00(miNumbRow) = lrdoRSet(0)
 maSumOptItemList_01(miNumbRow) = lrdoRSet(1)
 maSumOptItemList_02(miNumbRow) = lrdoRSet(2)
 lrdoRSet.MoveNext
Wend

Now again the number of parameters not the issues. We know that, we want to retrieve 3 columns from within stored procedure 'cms_GetCodeTypeList'.
0
 
altenaCommented:
Use OO4O (Ole Objects for Oracle)
There is a sample in the manual.

(There was when I needed it, long time ago I did it.
Sorry, can't halp you more than this)
0
 
mario_baAuthor Commented:
I want to connect with rdo
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
vmanoCommented:
check out the MSDN article Q167225 at
http://support.microsoft.com/support/kb/articles/q167/2/25.asp

let me know if this helps
vmano
0
 
mario_baAuthor Commented:
It's not the problem with one parameter, just with more than 10 parameters
0
 
vmanoCommented:
i think your question never said mentioned about number of parameters!!!!! please try to be more clear, when you ask the question itself.
0
 
vspeterCommented:
This method requires a pre-defined number of parameters (NOT dynamic)

The stored procedure list here does not use IN-OUT parameter, but there 3 parameters,

1st         - OUT
2nd and 3rd - IN

If your stored procedure has a need to use IN-OUT parameter than just replace the appropriate on (direcion) with 'rdParamInputOutput '

Assuming that you have all the connection in place.

With lrdoQry
  .SQL = "{? = Call cms_InsertAddressType(?, ?)}"
  .CursorType = rdUseServer
  .LockType = rdConcurLock
  Set .ActiveConnection = gRDOCon

  .rdoParameters(0).Direction = rdParamReturnValue
  .rdoParameters(1).Direction = rdParamInput
  .rdoParameters(2).Direction = rdParamInput

  .rdoParameters(1).Type = rdTypeVARCHAR
  .rdoParameters(2).Type = rdTypeVARCHAR

  .rdoParameters(1).Value = msAddressDesc                
  .rdoParameters(2).Value = gsCMSAppSrvUserId            
End With

lrdoQry.Execute

' Check the return value
If lrdoQry.rdoParameters(0).Value = 0 Then

Cheers
0
 
mario_baAuthor Commented:
vspeter: I know this, it works up to 10 Parameters but above VB crashs totaly
0
 
vspeterCommented:
Mario

What VB version are you using ?. Is there any document saying that VB crashs if there are more than 10 parameters (what type, IN, OUT, IN-OUT ?) ?.

Below is one of our stored procedure currently use. Environment, VB, SYBASE 11.XXX

With lrdoQry
       .SQL = "{ ? = Call cms_GetFranAssoSales (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}"

        .LockType = rdConcurReadOnly
        .CursorType = rdUseServer

        Set .ActiveConnection = gRDOCon

        .rdoParameters(0).Direction = rdParamReturnValue
        .rdoParameters(1).Direction = rdParamInput
        .rdoParameters(2).Direction = rdParamInput
        .rdoParameters(3).Direction = rdParamInput
       
        .rdoParameters(4).Direction = rdParamOutput
        .rdoParameters(5).Direction = rdParamOutput
        .rdoParameters(6).Direction = rdParamOutput
        .rdoParameters(7).Direction = rdParamOutput
        .rdoParameters(8).Direction = rdParamOutput
        .rdoParameters(9).Direction = rdParamOutput
        .rdoParameters(10).Direction = rdParamOutput
        .rdoParameters(11).Direction = rdParamOutput
        .rdoParameters(12).Direction = rdParamOutput
        .rdoParameters(13).Direction = rdParamOutput
        .rdoParameters(14).Direction = rdParamOutput
        .rdoParameters(15).Direction = rdParamOutput
        .rdoParameters(16).Direction = rdParamOutput
        .rdoParameters(17).Direction = rdParamOutput
        .rdoParameters(18).Direction = rdParamOutput
        .rdoParameters(19).Direction = rdParamOutput
        .rdoParameters(20).Direction = rdParamOutput
        .rdoParameters(21).Direction = rdParamOutput
        .rdoParameters(22).Direction = rdParamOutput
        .rdoParameters(23).Direction = rdParamOutput
        .rdoParameters(24).Direction = rdParamOutput
        .rdoParameters(25).Direction = rdParamOutput
        .rdoParameters(26).Direction = rdParamOutput
        .rdoParameters(27).Direction = rdParamOutput
        .rdoParameters(28).Direction = rdParamOutput
        .rdoParameters(29).Direction = rdParamOutput
        .rdoParameters(30).Direction = rdParamOutput
        .rdoParameters(31).Direction = rdParamOutput
        .rdoParameters(32).Direction = rdParamOutput
        .rdoParameters(33).Direction = rdParamOutput
       
        .rdoParameters(1).Type = rdTypeVARCHAR
        .rdoParameters(2).Type = rdTypeINTEGER
        .rdoParameters(3).Type = rdTypeINTEGER
        .rdoParameters(4).Type = rdTypeFLOAT
        .rdoParameters(5).Type = rdTypeFLOAT
        .rdoParameters(6).Type = rdTypeFLOAT
        .rdoParameters(7).Type = rdTypeFLOAT
        .rdoParameters(8).Type = rdTypeFLOAT
        .rdoParameters(9).Type = rdTypeFLOAT
        .rdoParameters(10).Type = rdTypeFLOAT
        .rdoParameters(11).Type = rdTypeFLOAT
        .rdoParameters(12).Type = rdTypeFLOAT
        .rdoParameters(13).Type = rdTypeFLOAT
        .rdoParameters(14).Type = rdTypeFLOAT
        .rdoParameters(15).Type = rdTypeFLOAT
        .rdoParameters(16).Type = rdTypeFLOAT
        .rdoParameters(17).Type = rdTypeFLOAT
        .rdoParameters(18).Type = rdTypeFLOAT
        .rdoParameters(19).Type = rdTypeFLOAT
        .rdoParameters(20).Type = rdTypeFLOAT
        .rdoParameters(21).Type = rdTypeFLOAT
        .rdoParameters(22).Type = rdTypeFLOAT
        .rdoParameters(23).Type = rdTypeFLOAT
        .rdoParameters(24).Type = rdTypeFLOAT
        .rdoParameters(25).Type = rdTypeFLOAT
        .rdoParameters(26).Type = rdTypeFLOAT
        .rdoParameters(27).Type = rdTypeFLOAT
        .rdoParameters(28).Type = rdTypeFLOAT
        .rdoParameters(29).Type = rdTypeFLOAT
        .rdoParameters(30).Type = rdTypeFLOAT
        .rdoParameters(31).Type = rdTypeFLOAT
        .rdoParameters(32).Type = rdTypeFLOAT
        .rdoParameters(33).Type = rdTypeINTEGER

        .rdoParameters(1).Value =  rCFinSumOptions.OptionTypeKey         'AssociationKey
        .rdoParameters(2).Value = rCFinSumOptions.FinCalendarMonth      'AnsettMonth
        .rdoParameters(3).Value = CInt(rCFinSumOptions.SumYear)         'AnsettYear
   
    End With
   
               
    lrdoQry.Execute

As you can see, there are 33 parameters, and it works beautifull. Could it be something else like the way you set up your rdoConnection, query ...etc.

Cheer,
0
 
mario_baAuthor Commented:

I use VB 6 and Oracle 8, ADO 2.0 or RDO 2.0.
I search the hole MSDN-Library but I found nothing.
I will use 1 IN, 1 IN/OUT and n OUT Parameters.

I don't think that the connection or the query doesn't works. Why should it works up to 10 params?
0
 
mario_baAuthor Commented:

I use VB 6 and Oracle 8, ADO 2.0 or RDO 2.0.
I search the hole MSDN-Library but I found nothing.
I will use 1 IN, 1 IN/OUT and n OUT Parameters.

I don't think that the connection or the query doesn't works. Why should it works up to 10 params?
0
 
vspeterCommented:
So what is the problem ?.
0
 
mario_baAuthor Commented:
vspeter:

How to say the procedure to send a returnvalue(SQL-Side)?
0
 
mario_baAuthor Commented:
First it doesn't generates any parmeters, when I used {} then it works, up to 10 parameters, now I use the sample from u.

Now I can set the parameters, but now I get an error message ("invalide sql-statment") when I execute the procedure. (I get no return value)

Are the "{}" and the return value necessary?
How can I tell the proc to return a value?

0
 
vspeterCommented:
One more thing, within the stored procedure, you would just have to do a SELECT statement including those columns you want to return, as the last statement

Let me know how you go !

Cheers,

0
 
mario_baAuthor Commented:
Thanx vspeter great it works
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.