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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
bperrelloCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.