Oracle Return value

I am wondering how to get a return value from and Oracle function. 1) My
function may be written improperly 2) If it's not, I still don't know how to
use oleDBCommand to retrieve the value I want. Below is the function and
code. any help would be appreciated.

Thanks.

-----------------------------------------------------
CREATE OR REPLACE FUNCTION PA_INSERT_AUDIT_DATA

(
v_audit_date IN PA_AUDIT_DATA.AUDIT_DATE%TYPE,
v_product_id IN PA_AUDIT_DATA.PRODUCT_ID%TYPE,
v_process_area_id IN PA_AUDIT_DATA.PROCESS_AREA_ID%TYPE,
v_user_id IN PA_AUDIT_DATA.USER_ID%TYPE,
v_location_id IN PA_AUDIT_DATA.LOCATION_ID%TYPE,
v_team_members IN PA_AUDIT_DATA.TEAM_MEMBERS%TYPE,
v_comments IN PA_AUDIT_DATA.COMMENTS%TYPE,
v_car_number IN PA_AUDIT_DATA.CAR_NUMBER%TYPE)

RETURN NUMBER


IS

v_audit_data_id NUMBER;


BEGIN

select PA_AUDIT_DATA_SEQ.NEXTVAL into v_audit_data_id FROM dual;

INSERT INTO PA_AUDIT_DATA
values(v_audit_data_id,
v_audit_date,v_product_id,v_process_area_id,v_user_id,v_location_id,v_team_members,v_comments,v_car_number);

RETURN v_audit_data_id;
END PA_INSERT_AUDIT_DATA;
/


VB CODE BELOW
------------------------------------------------
Dim cmd As OleDb.OleDbCommand = New
OleDb.OleDbCommand("PA_INSERT_AUDIT_DATA", objConn)
cmd.CommandType = CommandType.StoredProcedure


Dim v_audit_date As Date
Dim v_car_number As String
Dim v_product_id As Integer
Dim v_process_area_id As Integer
Dim v_user_id As Integer
Dim v_location_id As Integer
Dim v_team_members As String
Dim v_comments As String


v_audit_date = Me.txtDate.Text
v_car_number = Me.txtCAR.Text
v_product_id = Me.cboProducts.SelectedValue
v_process_area_id = Me.cboProcessArea.SelectedValue
v_user_id = Me.cboInitiator.SelectedValue
v_location_id = Me.cboLocation.SelectedValue
v_team_members = Me.txtTeamMembers.Text
v_comments = Me.txtComments.Text

objConn.Open()

cmd.Parameters.Add("AUDIT_DATE", v_audit_date)
cmd.Parameters.Add("PRODUCT_ID", v_product_id)
cmd.Parameters.Add("PROCESS_AREA_ID", v_process_area_id)
cmd.Parameters.Add("USER_ID", v_user_id)
cmd.Parameters.Add("LOCATION_ID", v_location_id)
cmd.Parameters.Add("TEAM_MEMBERS", v_team_members)
cmd.Parameters.Add("COMMENTS", v_comments)
cmd.Parameters.Add("CAR_NUMBER", v_car_number)

Dim intreturn

cmd.ExecuteNonQuery()

objConn.Close()






.

Relevant Pages

  • DBI custom functions
    ... I must retrieve a particular field from an Oracle DB with perl/DBI. ... oracle function to retrieve that field in a correct way, ... Is there a way to define custom functions to be used in a dbi session? ...
    (perl.dbi.users)
  • How to retrieve value from an Oracle function?
    ... We need to call a Oracle function and get the returned value. ... I searched MSDN. ... Any respone or or suggestion will ... Prev by Date: ...
    (microsoft.public.scripting.vbscript)
  • how to retrieve values from a user defined oracle type ( )
    ... I have an oracle function which return an oracle user defined table type. ... How to retrieve values from this type ... Here is my sample code. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: how can i call an oracle function
    ... > how can i call an oracle function? ... procedures, but use named parameters, where the returning result is named ... // Bjorn A ... Prev by Date: ...
    (microsoft.public.dotnet.languages.csharp)
Loading