如何从VB.NET中调用oracle存储过程 [英] How to call the oracle stored procedure from VB.NET

问题描述

hi
我在oracle中创建了一个select选项，它在后端工作正常

本身。但是当我在vb.net中调用它时会显示错误消息这个

 PLS-00306：调用'PLP_MYAPP_SELECT1'ORA-06550时参数的数量或类型错误：第1行第7列：PL / SQL：忽略语句

我的程序是



创建或替换过程plp_myapp_select1（varchar2中的my_flag，user_master.user_id％类型中的p_userid，varchar2中的userid_out，OTC_REGISTRAION.PANNUMBER％类型中的p_panno，sys_refcursor中的panid_out，varchar2中的company_out）

是

v_id varchar2（40）;

开始

如果my_flag ='用户名'那么

select user_id从user_master进入userid_out，其中user_id = p_userid;

dbms_output.put_line（v_id）;

elsif my_flag ='validatepan'然后

打开panid_out从OT中选择pannumber C_REGISTRAION其中pannumber = p_panno;

elsif my_flag ='companyname'然后

从OTC_INS_COMPANY选择companyname到company_out;

结束if;

结束;





和vb.net我正在调用这样的程序





cmd.Connection = cn

cmd.CommandType = Data.CommandType.StoredProcedure



cmd.CommandText =plp_myapp_select1

Dim Flag As New OracleParameter（my_flag，OracleDbType.Varchar2）

Flag.Direction = ParameterDirection.Input

Flag.Size = 50

Flag.Value =username

cmd.Parameters.Add（Flag）

 Dim qrySerial As New OracleParameter（p_userid，OracleDbType.Varchar2）
 qrySerial.Direction = ParameterDirection.Input 
 qrySerial.Value = user_id。 ToUpper 
 cmd.Paramet ers.Add（qrySerial）
 Dim OutPut As New OracleParameter（userid_out，OracleDbType.Varchar2）
 OutPut.Direction = ParameterDirection.Output 
 OutPut.Size = 400 
'cmd1 .Parameters.Add（OutPut）
 cmd.Parameters.Add（OutPut）
 
 Dim result As Integer = cmd.ExecuteNonQuery（）
 ada = New OracleDataAdapter（cmd）
 ada.Fill（ds）
 
我的编码究竟出了什么问题。如果任何人有想法只是给一些输入
 
 我尝试了什么： 
 
i试图从vb.net调用此程序。在后端它工作正常

解决方案

- 首先，尝试正确格式化你的问题，以便人们能够理解你所写的内容。

- 在存储过程定义中，你提供了6个参数，其中3个是IN和3 OUT参数

- 在您的代码中，您只为2 IN参数提供了值rs



再次检查你的代码并根据需要对参数进行更改。



希望，它有助于：）

hi I created a procedure for select in oracle and it will work fine in the backend
itself .but when i call it in vb.net it will display an error message like this

PLS-00306: wrong number or types of arguments in call to 'PLP_MYAPP_SELECT1' ORA-06550: line 1, column 7: PL/SQL: Statement ignored

my procedure is

create or replace procedure plp_myapp_select1(my_flag in varchar2, p_userid in user_master.user_id%type,userid_out out varchar2,p_panno in OTC_REGISTRAION.PANNUMBER%type,panid_out out sys_refcursor,company_out out varchar2)
is
v_id varchar2(40);
begin
if my_flag = 'username' then
select user_id into userid_out from user_master where user_id=p_userid;
dbms_output.put_line(v_id);
elsif my_flag = 'validatepan' then
open panid_out for select pannumber from OTC_REGISTRAION where pannumber =p_panno;
elsif my_flag = 'companyname' then
select companyname into company_out from OTC_INS_COMPANY;
end if;
end;


and in vb.net i am calling the procedure like this


cmd.Connection = cn
cmd.CommandType = Data.CommandType.StoredProcedure

cmd.CommandText = "plp_myapp_select1"
Dim Flag As New OracleParameter("my_flag", OracleDbType.Varchar2)
Flag.Direction = ParameterDirection.Input
Flag.Size = 50
Flag.Value = "username"
cmd.Parameters.Add(Flag)

  Dim qrySerial As New OracleParameter("p_userid", OracleDbType.Varchar2)
        qrySerial.Direction = ParameterDirection.Input
        qrySerial.Value = user_id.ToUpper
        cmd.Parameters.Add(qrySerial)
        Dim OutPut As New OracleParameter("userid_out", OracleDbType.Varchar2)
        OutPut.Direction = ParameterDirection.Output
        OutPut.Size = 400
        'cmd1.Parameters.Add(OutPut)
        cmd.Parameters.Add(OutPut)
       
        Dim result As Integer = cmd.ExecuteNonQuery()
        ada = New OracleDataAdapter(cmd)
        ada.Fill(ds)

what exactly wrong with my coding .if anyaone have ideas just give some inputs

What I have tried:

i tried to call this procedure from vb.net .in the back end it's working fine

解决方案

-- First of all, try to format your question correctly so that people can understand what you have written.
-- In the Stored Procedure definition you have provided 6 parameters, out of which 3 are IN and 3 OUT parameters
-- In your code you have provided value to only 2 IN parameters

Check your code again and do the changes for parameters as required.

Hope, it helps :)

这篇关于如何从VB.NET中调用oracle存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！