Solved

Call a PLSQL Procedure with VB

Posted on 1998-10-13
16
498 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA to copy paste columns form one file to other 20 91
How does CurrentUser work? 10 39
Hide vba in gp 7 106
adding "ungroup sheets" to existing vbs code 5 31
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

828 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