• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

Calling ORACLE package function from VB

I would like to use 2 Oracle package functions in package ("Package1") using Visual basic to send the commands and process the resulting data.  Below is a pseudo sample of PL/SQL code that calls an ("initSys") function and if successful then calls a second command ("Get_XML")until it is no longer successful. I would like the logic to be in VB and have Vb call the package functions......
I guess I'm not too sure on command data types for these PL SQL parameters and how to retrieve the results...  etc...

Thanks in advance....

vASystem varchar2(20) := 'THESYSTEM';
vData varchar2(32000);
boShowOld boolean := True;
boIgnoreNull boolean := False;
iResult integer;

--- an iResult of 0 means success
iResult := DB1.Package1.InitSys ( vASystem, boShowOld, boIgnoreNull);

if iResult=0 then
     --- an iResult of 0 means success... now get the data!!
     iResult := DB1.Package1.Get_XML(vData, null, null);
     if iResult=0 then
           --- Now VB Deals with the vData result string (XML format)
     end if
   End Loop;
end if;
  • 3
  • 2
1 Solution
piershughesAuthor Commented:
First tried doing
Tried different approach using the oracle in process server but don't get a blank string from the output param:
So obviously doing something wrong....  are the params used correctly?

    Set OO4OServer = CreateObject("OracleInProcServer.XOraServer")
    OO4OServer.Open ("DB_ID")
    Set DbSession1 = OO4OServer.OpenDatabase("username/password", 0)  

    sExtSystem = "MYPCNAME"
    bShowOld = True
    bIgnoreNull = False  

    DbSession1.Parameters.Add "VEXTSYSTEM", sExtSystem, ORAPARM_INPUT
    DbSession1.Parameters.Add "SHOWOLD", bShowOld, ORAPARM_INPUT
    DbSession1.Parameters.Add "IGNORENULL", bIgnoreNull, ORAPARM_INPUT
    DbSession1.Parameters.Add "iRESULT", iResult, ORAPARM_OUTPUT
    iResult = 1
    lResult = DbSession1.ExecuteSQL("begin :iRESULT:=DBName.Package_Ext.InitSys(:VEXTSYSTEM, :SHOWOLD, :IGNORENULL); end;", 0&)
    Debug.Print DbSession1.Parameters("iRESULT")
    If DbSession1.Parameters("iRESULT") = 0 Then  'Sucess=0
        'clear old params since the above initSys got this DB session ready for output function
        Call DbSession1.Parameters.Remove("VEXTSYSTEM")
        Call DbSession1.Parameters.Remove("SHOWOLD")
        Call DbSession1.Parameters.Remove("IGNORENULL")
        Call DbSession1.Parameters.Remove("iRESULT")
       'Create the new params for get data function
        DbSession1.Parameters.Add "VEVENT", vEvent, ORAPARM_OUTPUT
        DbSession1.Parameters.Add "iRESULT", iResult, ORAPARM_OUTPUT

        'iResult := V3_HOTEL1.Business_Event_Ext.Get_Event_XML(vEvent, null, null);
        lResult = DbSession1.ExecuteSQL("begin :iRESULT:=DBName.Package_Ext.GetXML(:VEVENT); end;", 0&)

    Debug.Print DbSession1.Parameters("iRESULT")
    Debug.Print DbSession1.Parameters("VEVENT")

VEVENT should be an XML string... but it's empty...
The SQL code above when run against the DB returns an XML string in vData

I would check firsrable WHAT'S vData DATATYPE IN oRACLE.iF IT'S VARCHAR2 then it should work,but it can be as well a clob and then You need a conversion in Your ExecuteSQL statement.I don't think ORAPARM_OUTPUT acn render CLOB as a type.
So You can use DBMS_LOB in Your ExecuteSQL to rectify this problem.
Hope it helps.
piershughesAuthor Commented:
Thanks for the comment...
I checked and the params are Varchar2... so that does not seem to be a problem.

I've searched the net to see if I could learn anything and it seems that Oracle can be difficult when a function in a package has a return value:
The above may be the solution that I need (have not tried it yet)... I'll post back if this is the solution!

Then it looks like oracle does not handle boolean params!! (find this hard to believe!!)

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Not sure if this would help, there is a very good website in asktom
here is a snippet of code from there

example package
   Function Add5 (num in number) RETURN NUMBER;
END Calc;

   Function Add5 (num in number) RETURN NUMBER IS
      Return (num + 5);
   End Add5;
END Calc;

example vb

Dim con As New ADODB.Connection
   Dim cmd As New ADODB.Command
   Dim InputParam As New ADODB.Parameter
   Dim ReturnParam As New ADODB.Parameter

   con.ConnectionString = "DSN=ODBC8; UID=scott; PWD=tiger;"
   cmd.ActiveConnection = con
   cmd.CommandText = "Calc.Add5"
   cmd.CommandType = adCmdStoredProc
   Set InputParam = cmd.CreateParameter("Prm1", adSmallInt, adParamInput, , 30)
   'Using adParamOutPut instead of adParamReturnValue will result in the
   'following error:
   'ORA-24334 - no descriptor for this position
   'Set Prm2 = cmd.CreateParameter("Prm2", adSmallInt, adParamOutput)
   Set ReturnParam = cmd.CreateParameter("Prm2", adSmallInt, adParamReturnValue)
   'You will also get the ORA-24334 error if you don't Append the parameters
   'in the correct order. Make sure to bind the Returning parameter first.
   cmd.Parameters.Append ReturnParam
   cmd.Parameters.Append InputParam
   MsgBox "Input Value = " & cmd.Parameters(1)
   MsgBox "Return Value = " & cmd.Parameters(0)

and the website itself
piershughesAuthor Commented:

Turns out that the XML content in the return param exceeded the size limit for the second function(Get_XML).
I did end up using ADO but having installed Oracle ODP.NET I set up a TNS (rather than DNS) and referred to that in my connection string.  Also the DB that held data for the 2nd function was apparently 'down' which added to the confusion (InitSys worked!!)
It's working now...
Your solution is correct so I owe you the points..... thanks
thanks to tom eh :)
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.

Join & Write a Comment

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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now