Solved

Calling ORACLE package function from VB

Posted on 2006-11-28
6
637 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&#451528

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 65

Accepted Solution

by:
rockiroads earned 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Public Sub, Empty Cell 7 57
Window placement 17 65
VBA loop through headers using value 3 48
VBA color chart bars 12 66
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now