?
Solved

Calling ORACLE package function from VB

Posted on 2006-11-28
6
Medium Priority
?
654 Views
Last Modified: 2013-12-25
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;
begin

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

if iResult=0 then
   loop
     --- 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)
     else
           Exit;
     end if
   End Loop;
end if;
end;
0
Comment
Question by:piershughes
  • 3
  • 2
6 Comments
 

Author Comment

by:piershughes
ID: 18056628
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


0
 
LVL 21

Expert Comment

by:oleggold
ID: 18061800
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.
0
 

Author Comment

by:piershughes
ID: 18066740
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:
http://www.orafaq.com/forum/t/52567/0/
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!!)
http://forums.oracle.com/forums/thread.jspa?messageID=451528񮏈

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 65

Accepted Solution

by:
rockiroads earned 1500 total points
ID: 18094657
Not sure if this would help, there is a very good website in asktom
here is a snippet of code from there

example package
CREATE OR REPLACE PACKAGE Calc AS
   Function Add5 (num in number) RETURN NUMBER;
END Calc;
/

CREATE OR REPLACE PACKAGE BODY Calc AS
   Function Add5 (num in number) RETURN NUMBER IS
      Begin
      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;"
   con.Open
   
   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
 
   cmd.Execute
   MsgBox "Input Value = " & cmd.Parameters(1)
   MsgBox "Return Value = " & cmd.Parameters(0)



and the website itself
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:498045385849
0
 

Author Comment

by:piershughes
ID: 18095588

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
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 18095652
thanks to tom eh :)
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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
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…
Suggested Courses
Course of the Month17 days, 8 hours left to enroll

831 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