Solved

How to execute and see results of Oracle Stored Procedure from VB.NET

Posted on 2004-09-13
6
445 Views
Last Modified: 2012-08-14
I created a simple SP in oracle like this:
 create or replace procedure TEST_VM(prm_1 IN varchar2,prm_2 IN varchar2,sp_cursor OUT sys_refcursor
)
 is
 begin
 open sp_cursor for select * from nm_Test where ptcpt_cd = prm_1 and ptcpt_nme = prm_2;
 end;
 /

It created and I can run in sql*plus perfectly and I have this code in VB.NET and I'm not able to run the procedure
myCMD.CommandText = "TEST_VJ"
        myCMD.CommandType = System.Data.CommandType.StoredProcedure
 
myCMD.Parameters.Add(New OracleParameter("prm_1", OracleType.VarChar)).Value = "'PARENT'"
        myCMD.Parameters.Add(New OracleParameter("prm_2", OracleType.VarChar)).Value = "'PARENT'"
        myCMD.Parameters.Add(New OracleParameter("sys_refcursor", OracleType.Cursor)).Direction = ParameterDirection.Output

myCMD.ExecuteNonQuery()

What is wrong am I doing

Since I don't have many points that I'm giving only 30 points right now.
Thanks
vm
0
Comment
Question by:vmandem
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 7

Accepted Solution

by:
natloz earned 30 total points
ID: 12049021
does it run if you comment out this line?
 
myCMD.Parameters.Add(New OracleParameter("sys_refcursor", OracleType.Cursor)).Direction = ParameterDirection.Output
0
 
LVL 7

Expert Comment

by:natloz
ID: 12049024
You should not have to pass in a return value as far as I know...
0
 
LVL 7

Expert Comment

by:natloz
ID: 12049054
Hold the phone...you may need it actually...are you getting an error?
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 10

Expert Comment

by:123654789987
ID: 12051389
Try changing it to

myCMD.Parameters.Add(New OracleParameter("@prm_1", OracleType.VarChar)).Value = "'PARENT'"
myCMD.Parameters.Add(New OracleParameter("@prm_2", OracleType.VarChar)).Value = "'PARENT'"
        myCMD.Parameters.Add(New OracleParameter("@sys_refcursor", OracleType.Cursor)).Direction = ParameterDirection.Output
0
 

Author Comment

by:vmandem
ID: 12054235
natloz

I get the error as:
ORA-06550: line1, column7;
PLS-00306: wrong number or types of arguments
ORA-06550: line1, column7;
PL/SQL: Statment ignored
0
 

Author Comment

by:vmandem
ID: 12054257
123654...

I get this error if I use @:

ORA-01036: illegal variable name/number.

I don't know where excatly it is doing. Can you tell me based on my stored procedure where it is causing.

Thanks
vm
0

Featured Post

Industry Leaders: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

734 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