Re: Oracle Return value

It should be the first parameter, with a parameter type of
ParameterType.ReturnValue.

Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb


"quixote" <quixote@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:FB29841C-1E8C-4F84-9011-0F552EF73F27@xxxxxxxxxxxxxxxx
>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

  • Re: Oracle Return value
    ... First parameter added in the code. ... >> Bob Beauchemin ... >>> use oleDBCommand to retrieve the value I want. ... Prev by Date: ...
    (microsoft.public.data.oledb)
  • Re: Splitting a filename out of a filepath
    ... >If you apply reverse to the first parameter in the charindex you could ... >search for multiple chars as well. ... Prev by Date: ...
    (comp.databases.ms-sqlserver)
  • Re: Excel hyperlink to another worksheet
    ... The key for me was the first parameter is required even if ... it is empty. ... > failed to guess the syntax by modifying recorded VBA ... Prev by Date: ...
    (microsoft.public.scripting.vbscript)
  • Re: Creating events in VC++ / ATL, receiving in VB
    ... The Invoke() method should have 0x0a as its first parameter if it is ... Regards ... Prev by Date: ...
    (microsoft.public.vc.atl)
  • Re: How to convert a vector type into string?
    ... > You can't legally pass NULL as the first parameter, ... > second parameter is 0 ... Even on a weird architecture that traps bad pointers NULL ... Prev by Date: ...
    (microsoft.public.vc.language)
Loading