Solved

Call a PLSQL Procedure with VB

Posted on 1998-10-13
16
495 Views
Last Modified: 2013-12-25
How to call a PLSQL-Procedure with IN-OUT-Parameters.
0
Comment
Question by:mario_ba
  • 8
  • 5
  • 2
  • +1
16 Comments
 
LVL 3

Expert Comment

by:altena
ID: 1496868
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
 

Author Comment

by:mario_ba
ID: 1496869
I want to connect with rdo
0
 
LVL 3

Expert Comment

by:vmano
ID: 1496870
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
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 

Author Comment

by:mario_ba
ID: 1496871
It's not the problem with one parameter, just with more than 10 parameters
0
 
LVL 3

Expert Comment

by:vmano
ID: 1496872
i think your question never said mentioned about number of parameters!!!!! please try to be more clear, when you ask the question itself.
0
 
LVL 2

Expert Comment

by:vspeter
ID: 1496873
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
 

Author Comment

by:mario_ba
ID: 1496874
vspeter: I know this, it works up to 10 Parameters but above VB crashs totaly
0
 
LVL 2

Expert Comment

by:vspeter
ID: 1496875
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
 

Author Comment

by:mario_ba
ID: 1496876

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
 

Author Comment

by:mario_ba
ID: 1496877

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
 
LVL 2

Expert Comment

by:vspeter
ID: 1496878
So what is the problem ?.
0
 

Author Comment

by:mario_ba
ID: 1496879
vspeter:

How to say the procedure to send a returnvalue(SQL-Side)?
0
 

Author Comment

by:mario_ba
ID: 1496880
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
 
LVL 2

Accepted Solution

by:
vspeter earned 150 total points
ID: 1496881
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
 
LVL 2

Expert Comment

by:vspeter
ID: 1496882
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
 

Author Comment

by:mario_ba
ID: 1496883
Thanx vspeter great it works
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel - Save a copy of work book 13 84
Add and format columns in vb6 7 57
Problem to With line 4 57
Sub or Function is not defined 6 28
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question