Question need help in using stored proc for insert command

[adshocker]

hi all,

first of, here's what i'm using.

Visual Basic 2005 Express Edition
Oracle Database 10g Express Edition

i created a procedure stored in the oracle database create_user and accepts a few parameters...

procedure create_user 
( p_username         in tbl_users.username%type
 ,p_password         in tbl_users.encrypted_password%type
 ,p_title            in tbl_users.title%type
 ,p_last_name        in tbl_users.last_name%type
 ,p_first_name       in tbl_users.first_name%type
 ,p_middle_name      in tbl_users.middle_name%type
 ,p_email_address    in tbl_users.email_address%type
 ,p_gender           in tbl_users.gender%type
 ,p_date_of_birth    in tbl_users.date_of_birth%type
 ,p_description      in tbl_users.description%type);

in my visual basic project, i manually created a dataset and a datatable for the tbl_users table using the dataset designer since express edition doesn't support oracle for its dataset wizard.

anyways, in my form i have a BindingNavigator and a BindingSource..

and the following codes:

    Private da As New OracleDataAdapter
    Private selCmd As New OracleCommand
    Private updCmd As New OracleCommand
    Private delCmd As New OracleCommand
    Private insCmd As New OracleCommand
    Private dbconn As New OracleConnection

    Private Sub Initialize()
        With selCmd
            .CommandText = "select * from tbl_users where 1 = 1"
            .CommandType = CommandType.Text
            .Connection = dbconn
        End With

        With insCmd
            .CommandText = "create_user"
            .CommandType = CommandType.StoredProcedure
            .Connection = dbconn
            .Parameters.Clear()
            .Parameters.Add("p_username", OracleType.VarChar, 30, "p_username")
            .Parameters.Add("p_password", OracleType.VarChar, 30, "p_password")
            .Parameters.Add("p_title", OracleType.VarChar, 10, "p_title")
            .Parameters.Add("p_last_name", OracleType.VarChar, 30, "p_last_name")
            .Parameters.Add("p_first_name", OracleType.VarChar, 30, "p_first_name")
            .Parameters.Add("p_middle_name", OracleType.VarChar, 30, "p_middle_name")
            .Parameters.Add("p_email_address", OracleType.VarChar, 100, "p_email_address")
            .Parameters.Add("p_gender", OracleType.VarChar, 1, "p_gender")
            .Parameters.Add("p_date_of_birth", OracleType.DateTime, 30, "p_date_of_birth")
            .Parameters.Add("p_description", OracleType.VarChar, 250, "p_description")

        End With

        With da
            .SelectCommand = selCmd
            .InsertCommand = insCmd
        End With

    End Sub

    Private Sub SaveToolStripButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripButton.Click
        Me.Validate()
        Me.Tbl_usersBindingSource.EndEdit()
        Me.da.Update(Me.DsUser.tbl_users)
    End Sub

    Private Sub frmUser_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Dim dbstring As New OracleConnectionStringBuilder
        With dbstring
            .DataSource = "xe"
            .UserID = "dev"
            .Password = "password"
        End With
        dbconn.ConnectionString = dbstring.ConnectionString
        dbconn.Open()

        Initialize()
    End Sub

i'm getting an error "PLS-00306: wrong number or types of arguments in call to 'create_user'".. the error seems to say that i'm not giving enough values..

i think the problem might be the srcColumn i'm passing when adding the parameter... not sure what to put there.

can someone help me?

thanks

 

[cjard]

If you give me a create script for the tbl_users table (most apps like TOAD or the free SQLDeveloper from oracle will make a script to create a table with all indexes and PK etc) I'll create it in my oracle instance, create this proc, link to it in my VS, and give you the code

I'll also give you the codes for a view I wrote to make this kind of stuff much easier; i'll just check that it it up to date

Sorry for the slow response.. I havent been in Data Access for a while, not sure why!

 

[adshocker]

hi,

i've been able to fix this issue... sorry forgot to update the thread.

thanks.

 

[cjard]

i think the problem might be the srcColumn i'm passing when adding the parameter... not sure what to put there.

ps; if you intend to use the stored procedure with datatable data (i.e. you want the sproc to update data based on datatable content) then the source column is the name in the datatable of the datacolumn that will provide data for this parameter..

If your dt has ColA, ColB, ColC and your proc has P1, P2, P3, and you wanted data colA-> p3, ColB -> p1, ColC -> p2 you'd have:

Paramters.Add("P1" ... "ColB")

etc

 

[adshocker]

ps; if you intend to use the stored procedure with datatable data (i.e. you want the sproc to update data based on datatable content) then the source column is the name in the datatable of the datacolumn that will provide data for this parameter..

If your dt has ColA, ColB, ColC and your proc has P1, P2, P3, and you wanted data colA-> p3, ColB -> p1, ColC -> p2 you'd have:

Paramters.Add("P1" ... "ColB")

etc

thanks...

actually, that was the error i had when it wasn't working... it was because the srcColumn i was putting was not the one in the datatable. then i had to do a lot of trial and error with it and finally got it working when i entered the columnname of the datatable.

 

[cjard]

CREATE OR REPLACE VIEW GEN_PL_SQL_ASSIST (OWNER, TABLE_NAME, PROCEDURE_DECLARE_LIST, PROCEDURE_TYPE_LIST, COLUMN_NAME_LIST, PROCEDURE_ARG_LIST, MERGE_SELECT_LIST, MERGE_UPD_LIST, MERGE_INS_LIST, ERROR_CHECK, TRIG_ERROR_CHECK, FOR_MERGE_PROC_WRITER) AS
select "OWNER","TABLE_NAME","PROCEDURE_DECLARE_LIST","PROCEDURE_TYPE_LIST","COLUMN_NAME_LIST","PROCEDURE_ARG_LIST","MERGE_SELECT_LIST","MERGE_UPD_LIST","MERGE_INS_LIST","ERROR_CHECK","TRIG_ERROR_CHECK","FOR_MERGE_PROC_WRITER" from
(
  select * from
  (
    select
      owner,
      --table name mainly for ID purposes. space added to allow sort order to be correct for header and footer rows
      table_name || ' ' as table_name,

      --stored procedure argument list, deafults to in only. space after IN provided so addition of word OUT possible without
      --breaking indentation. same applies for pad_width + 4; extra 4 chars allows even longest name to be amended to in_out_name
      --without upsetting indentation and columnar formatting
      '  in_'||rpad(column_name, pad_width+4,' ')||' IN     '||RPAD(table_and_column_name||'%TYPE, ',64,' ')||'--'||data_type||'('||data_length||')' as procedure_declare_list,

      --simpler version of above, if you want to call the params your own names
      lower(table_name)||'.'||column_name||'%TYPE,' as procedure_type_list,

      --used whenever you need to write a select and a bunch of column names rather than SELECT *
      column_name||',' as column_name_list,

      --used whenever you need to refer to a list of the params in a stored proc, i.e. INSERT INTO tbl(list of params)
      --indented 4 spaces because I use 2 spaces per nest level, INSERT statement is nest 1 (inside proc), VALUES list is nest 2
      '    '||in_column_name||',' as procedure_arg_list,

      /*
        The following lines assist writing a MERGE statement of the form:
        MERGE INTO
          destination_table dst
        USING
          (SELECT
            in_plsql_argument AS column_name ...    --get from MERGE_SELECT_LIST
          FROM
            dual) src
        ON
          (src.id_column_name = dst.id_column_name) --get from your brain
        WHEN MATCHED THEN UPDATE SET
          dst.column_names = src.column_names       --get from the MERGE_UPD_LIST, note you cannot do this on any fields used in the ON clause!!
        WHEN UNMATCHED THEN INSERT(
          columns                                   --get from COLUMN_NAME_LIST
        )VALUES(
          src.columns                               --get from MERGE_INS_LIST
        );
      */

      --used in the (SELECT in_column as column ... FROM dual) src  section of a MERGE statement
      in_column_name||' as '||column_name||',' as merge_select_list,

      --for the WHEN MATCHED THEN UPDATE SET dst.column = src.column part of a merge statement
      decode(nullable, 'N', 'PK? ')|| --prefix column name with ?PK if col might be PK, hence used for ON clause of MERGE hence cannot appear in UPDATE SET clause
        'dst.'||column_name||' = ' ||
        decode(data_type, 'DATE', 'trunc(src.'||column_name||'),', 'src.'||column_name||',') as merge_upd_list,

       --for use in the WHEN UNMATCHED THEN INSERT, in combination with the
      case
        when data_type = 'DATE' then
          'trunc(src.'||column_name||'),'
        else
          'src.'||column_name||','
      end as merge_ins_list,

      --rudimentary error checking for pasting into the start of a stored procedure. can be modified as desired in the stored proc
      case
        when data_type in ('DATE', 'NUMBER') then
          REPLACE(REPLACE('  IF NOT :column_name BETWEEN -1 AND +1 THEN raise_application_error(-20000, ''in_:column_name value of ''||in_:column_name||'' is outside the allowed range of -1 to +1'', TRUE); END IF;',
          ':data_length', data_length), ':column_name', column_name)
        when data_type like '%CHAR%' then
          REPLACE(REPLACE('  IF LENGTH(in_:column_name) > :data_length THEN raise_application_error(-20000, ''in_:column_name value has a length of ''||LENGTH(in_:column_name)||'', max is :data_length'', TRUE); END IF;',
          ':data_length', data_length), ':column_name', column_name)
      end as error_check,

      case
        when data_type in ('DATE', 'NUMBER') then
          REPLACE(REPLACE('  IF NOT :new.:column_name BETWEEN -1 AND +1 THEN ddh_pr_debug_write('':new.:column_name value of ''||:new.:column_name||'' is outside the allowed range of -1 to +1''); END IF;',
          ':data_length', data_length), ':column_name', column_name)
        when data_type like '%CHAR%' then
          REPLACE(REPLACE('  IF LENGTH(:new.:column_name) > :data_length THEN ddh_pr_debug_write('':new.:column_name value has a length of ''||LENGTH(:new.:column_name)||'', max is :data_length''); END IF;',
          ':data_length', data_length), ':column_name', column_name)
      end as trig_error_check,

      --special method used in an app i wrote specifically for writing MERGE statements, takes a field list in and spits out a merge statement
      column_name||','||data_length||decode(nullable, 'N', ',p') as for_merge_proc_writer

    from
      (
        select
          owner,
          max(length(column_name)) over(partition by table_name) as pad_width,
          lower(column_name) as column_name,
          'in_'||lower(column_name) as in_column_name,
          lower(table_name||'.'||column_name) as table_and_column_name,
          data_type,
          table_name,
          nullable,
          data_length,
          column_id
        from
          all_tab_columns c
      )
    order by
      table_name, column_id
  )


  UNION ALL
    --this pseudo table is used to provide headers and footers interspersing the rows
    select
      owner,
      decode(flip.flop, 0, table_name , table_name ||' END') as table_name,
      decode(flip.flop, 0, table_name , null),
      decode(flip.flop, 0, table_name , null),
      decode(flip.flop, 0, table_name , null),
      decode(flip.flop, 0, table_name , null),
      decode(flip.flop, 0, table_name , null),
      decode(flip.flop, 0, table_name , null),
      decode(flip.flop, 0, table_name , null),
      decode(flip.flop, 0, table_name , null),
      decode(flip.flop, 0, table_name , null),
      decode(flip.flop, 0, table_name , null)
    from
      all_tables,
      (select 0 as flop from dual union select 1 from dual) flip
)
order by
  owner, table_name

CREATE OR REPLACE VIEW GEN_DOTNET_PARAMASSIST (OBJECT_NAME, PACKAGE_NAME, OBJECT_ID, OVERLOAD, ARGUMENT_NAME, POSITION, SEQUENCE, DATA_LEVEL, DATA_TYPE, DEFAULT_VALUE, DEFAULT_LENGTH, IN_OUT, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, RADIX, CHARACTER_SET_NAME, TYPE_OWNER, TYPE_NAME, TYPE_SUBNAME, TYPE_LINK, PLS_TYPE, CHAR_LENGTH, CHAR_USED, VB_NET_WITH_MAPPING, VB_NET_WITHOUT_MAPPING, CSHARP_WITH_MAPPING, CSHARP_WITHOUT_MAPPING) AS
select
        --parameter work in vb.net
        ua.*,
      'cmd.Parameters.Add(New OracleParameter("'||NVL2(argument_name, REPLACE(REPLACE(REPLACE(argument_name,'IN_',''),'OUT_',''),'INOUT_',''), 'RETURN_VALUE')||
      '", OracleType.'||decode(data_type,'NUMBER','Number','DATE','DateTime','VARCHAR2','VarChar')||
      ', '||NVL(data_length, '4000')||
      ', ParameterDirection.'||  NVL2(argument_name, decode(in_out, 'IN', 'Input', 'OUT', 'Output', 'InputOutput'), 'ReturnValue') ||
      ', '||NVL2(argument_name, '"'||argument_name||'"', ' Nothing') ||
      ', DataRowVersion.Current, True, VALUE))' as vb_net_with_mapping,
      'cmd.Parameters.Add(New OracleParameter("'||NVL2(argument_name, REPLACE(REPLACE(REPLACE(argument_name,'IN_',''),'OUT_',''),'INOUT_',''), 'RETURN_VALUE')||
      '", OracleType.'||decode(data_type,'NUMBER','Number','DATE','DateTime','VARCHAR2','VarChar')||
      ', '||NVL(data_length, '4000')||
      ', ParameterDirection.'||  NVL2(argument_name, decode(in_out, 'IN', 'Input', 'OUT', 'Output', 'InputOutput'), 'ReturnValue') ||
      ',  Nothing' ||
      ', DataRowVersion.Current, False, VALUE))' as vb_net_without_mapping,
            'cmd.Parameters.Add(new OracleParameter("'||NVL2(argument_name, REPLACE(REPLACE(REPLACE(argument_name,'IN_',''),'OUT_',''),'INOUT_',''), 'RETURN_VALUE')||
      '", OracleType.'||decode(data_type,'NUMBER','Number','DATE','DateTime','VARCHAR2','VarChar')||
      ', '||NVL(data_length, '4000')||
      ', ParameterDirection.'||  NVL2(argument_name, decode(in_out, 'IN', 'Input', 'OUT', 'Output', 'InputOutput'), 'ReturnValue') ||
      ', '||NVL2(argument_name, '"'||argument_name||'"', ' Nothing') ||
      ', DataRowVersion.Current, true, VALUE));' as csharp_with_mapping,
      'cmd.Parameters.Add(new OracleParameter("'||NVL2(argument_name, REPLACE(REPLACE(REPLACE(argument_name,'IN_',''),'OUT_',''),'INOUT_',''), 'RETURN_VALUE')||
      '", OracleType.'||decode(data_type,'NUMBER','Number','DATE','DateTime','VARCHAR2','VarChar')||
      ', '||NVL(data_length, '4000')||
      ', ParameterDirection.'||  NVL2(argument_name, decode(in_out, 'IN', 'Input', 'OUT', 'Output', 'InputOutput'), 'ReturnValue') ||
      ',  Nothing' ||
      ', DataRowVersion.Current, false, VALUE));' as csharp_without_mapping

from
  user_arguments ua;

Here are some old views I made to help with things like writing stored procedures and linking them to .net

I don't code some things that way any more, I just havent had time to update them, but feel free to create them, look at their output and edit them

Note, i would genuinely advise against doing a select * from either of them without using a where clause.. it will take a long time to complete

Instead, use like:

select * from gen_plsql_assist where table_name like 'YOURTABLE%'

The % is mandatory because of trailing text




select * from gen_dotnet_paramassist where object_name like 'MY_PROC'


-

I may one day get round to building an app that generates better working code than this - something more like the dataset designer.. thing is, I jsut havent the time!

Note that these use my own conventions of naming parameters like:

create proc abc(
inout_column_name IN OUT tablename.column_name%TYPE,
out_arg_name OUT VARCHAR2
...
)

You use p_ to prefix your args; i don't because I use IN_ out_ or inout_ (hence knowing that a) its a parameter and b) it may/not be used as assignment target)

 

[adshocker]

thanks.. i'll take a look at it.