Re: Oracle Return value

First parameter added in the code.

// add this line
cmd.Parameters.Add("somename", somevariable, ParameterType.ReturnValue;
// before this one..
>cmd.Parameters.Add("AUDIT_DATE", v_audit_date)
> ...

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


"quixote" <quixote@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D6A658D1-AE08-42DE-8A3B-40421193F5A7@xxxxxxxxxxxxxxxx
> Hi bob,
> Thanks for the reply. I'm not sure what you mean by it should be the first
> parameter. Do I need to alter the stored function?
>
> Thanks
>
> "Bob Beauchemin" wrote:
>
>> 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
    ... It should be the first parameter, with a parameter type of ... Bob Beauchemin ... 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