calling procedures in oracle with vb

I need to call a procedure in oracle, but dbexecute does not work. can anyone help me
my code is as follows:
OraDatabase.DBExecuteSQL ("@l:\lims_ora\sql\oraprocs\dps")
Cruisin18Asked:
Who is Participating?
 
bperrelloConnect With a Mentor Commented:
I'm assuming from your comments that you are using oracles objects for ole.
Try:
OraDatabase.DbExecuteSQL ("Begin " + procedureName + "; end;")

where procedurename is a string representing the procedure name and any parameters.  An example of such a string for me is:

generate_credits(1,'13-Jun-1999')

There is a means of putting parameters onto the stack, and making it use them (documentation sucks); what I found when I tried to put more than 1 parameter on the stack (using things like:
 OraDatabase.Parameters.Add "SubPeriodId", defaultSubPeriodId, 1
    OraDatabase.Parameters("SubPeriodId").ServerType = ORATYPE_SINT
was that it wouldn't work with 2 parameters; only 1.  When I found it to work by handing it the entire string (parameters and all), I just went with it and never looked back.
0
 
psmith789Commented:
I think you need to create the procedure in the database before you try to call it - I don't think you can run the script from the file system through Visual Basic.
0
 
Cruisin18Author Commented:
It is created in the database and you can run it with a shell comand but it calls sqlplus and I want to get around that
any ideas
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
psmith789Commented:
For an example of doing this with RDO, go to:

http://support.microsoft.com/support/kb/articles/Q177/3/58.ASP

The topic is how to pass a date-type argument, but the syntax for execution is pretty clear.
0
 
Cruisin18Author Commented:
When i enter the program , I log into oracle and i am not using RDO, I can't use RDO  for certain reasons which I can't get into
but they want me to call this procedure which is over 255 char and can't be called as a string, and they don't want sqlplus to run ,
do you know of another way ?

0
 
Cruisin18Author Commented:
("Begin " + procedureName + "; end;")
If you get rid of the parenthesis it will work because the oracle documentation is wrong, cuz it is a method

I will award you the points , but I called oracle with this problem cuz I couldn't wait for an answer
but your correct, the documentation needs major help
Thanks
0
 
bperrelloCommented:
The parenthesis are part of the dbexecute command, not part of the oracle syntax.  I have used it numerous times with success.  dbexecute(mySQL) will execute any sql against the database.  Here is a cut and paste from the help files from objects for ole.
(By the way, it shows you how to deal with parameters; although as I stated earlier, I had a problem with more than one parameter working correctly).  I'm glad I could help.

This example uses the Add and Remove parameter methods, the ServerType parameter property, and the ExecuteSQL database method to call a Stored Procedure and Function (located in ORAEXAMP.SQL). Copy and paste this code into the definition section of a form. Then press F5.


Sub Form_Load ()

'Declare variables as OLE Objects.
 Dim OraSession As Object
 Dim OraDatabase As Object
 Dim OraDynaset As Object

 'Create the OraSession Object.
 Set OraSession = CreateObject("OracleInProcServer.XOraSession")

 'Create the OraDatabase Object.
 Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

 'Add EMPNO as an Input/Output parameter and set its initial value.
 OraDatabase.Parameters.Add "EMPNO", 7369, ORAPARM_INPUT

 OraDatabase.Parameters("EMPNO").ServerType = ORATYPE_NUMBER

 'Add ENAME as an Output parameter and set its initial value.
 OraDatabase.Parameters.Add "ENAME", 0, ORAPARM_OUTPUT
 OraDatabase.Parameters("ENAME").ServerType = ORATYPE_VARCHAR2

 'Add SAL as an Output parameter and set its initial value.
 OraDatabase.Parameters.Add "SAL", 0, ORAPARM_OUTPUT
 OraDatabase.Parameters("SAL").ServerType = ORATYPE_NUMBER

 'Execute the Stored Procedure Employee.GetEmpName to retrieve ENAME.

 ' This Stored Procedure can be found in the file ORAEXAMP.SQL.
 OraDatabase.DbExecuteSQL ("Begin Employee.GetEmpName (:EMPNO, :ENAME); end;")
 'Display the employee number and name.

 'Execute the Stored Function Employee.GetSal to retrieve SAL.
 ' This Stored Function can be found in the file ORAEXAMP.SQL.
 OraDatabase.DbExecuteSQL ("declare SAL number(7,2); Begin :SAL:=Employee.GetEmpSal (:EMPNO); end;")

 'Display the employee name, number and salary.
 MsgBox "Employee " & OraDatabase.Parameters("ENAME").value & ", #" & OraDatabase.Parameters("EMPNO").value & ",Salary=" & OraDatabase.Parameters("SAL").value

 'Remove the Parameters.
 OraDatabase.Parameters.Remove "EMPNO"
 OraDatabase.Parameters.Remove "ENAME"
 OraDatabase.Parameters.Remove "SAL"

End Sub
0
All Courses

From novice to tech pro — start learning today.