Question OracleParameter.ParameterName

theAsocialApe

Member
Joined
Mar 21, 2008
Messages
13
Programming Experience
5-10
does anyone know how to call an oracle procedure without the parametername?

----

We've got some oracle packages that take like 15 parameters. I'm sick of doing this

VB.NET:
Dim pc As New OracleParameterCollection
and then typing

VB.NET:
        Dim a As New OracleParameter("p1", OracleType.Number)
        a.Direction = Data.ParameterDirection.Input
        a.Value = tb1.Text.Trim
        pc.Add(a)

        Dim b As New OracleParameter("p2", OracleType.Number)
        b.Direction = Data.ParameterDirection.Input
        b.Value = tb2.Text.Trim
        pc.Add(a)
etc for fifteen values.


I'm trying to make an oracleparameter maker function like
VB.NET:
    Private Function tb2OraParam(ByVal tb As TextBox) As OracleParameter
        Dim a As New OracleParameter
        Dim theInt As Integer
        Dim theDate As Date

        a.Direction = Data.ParameterDirection.Input

        If Integer.TryParse(tb.Text.Trim, theInt) Then
            a.OracleType = OracleType.Number
            a.Value = theInt
        ElseIf Date.TryParse(tb.Text.Trim, theDate) Then
            a.OracleType = OracleType.DateTime
            a.Value = tb.Text.Trim
        Else
            a.OracleType = OracleType.VarChar
            a.Value = tb.Text.Trim
        End If
        Return a
    End Function
where I can just give a textbox like
VB.NET:
 pc.Add(tb2OraParam(tb1))

But it's failing on calling the procedure or package because that function doesn't add the OracleParameter.ParameterName, either in the constructor or just explicitly when it's adding the value and type and stuff.


I know that the f(x) needs a lot more work to do it's job, but I'm just wanting to get a flimsy little thing working so I can see if there's any point in continuing.
 

MattP

Well-known member
Joined
Feb 29, 2008
Messages
1,206
Location
WY, USA
Programming Experience
5-10
Your function is returning an OracleType, Direction, and Value. You'll need to have a.ParameterName returned somewhere or you'll keep getting the error.
 
Last edited:

theAsocialApe

Member
Joined
Mar 21, 2008
Messages
13
Programming Experience
5-10
jah. that's what I thought. I'm just so sick of typing like the same freaking four lines a thousand times (of course, in the time I spent trying not to have to do it, I could have just finished it by typing them all in :D)
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Functions and procedures that are NOT in an oracle package can be interrogated by the designer, and it will write the code for you. Functions within packages, you must do this yourself, i'm afraid but I do have a cool trick I will show you in the latter half of this post

Here is how you connect a function or procedure (not packaged):

On the DataSet Designer Surface, right click and choose Add.. Query
QueriesTableAdapter is generated (or right click this/choose add query, if it already exists)
Type in the SQL text SELECT 1 FROM dual
Press finish
Call your query something sensible
Change the query type to StoredProcedure
Choose the proc name from the drop down list in the CommandText
The wizard creates a parameters collection for you

Note: if youre using a function and you want the return value then you should:
Edit the parameters collection and remove one of the RETURN_VALUE parameters, i forget which, but delete whichever one has the ParameterDirection = ReturnValue, and then make the OTHER one have a ParameterDirection = ReturnValue (the rest of the info re datatype etc is retrieved, but for some reason it doesnt get this one right 100%)
Make sure the query is ExecuteNonQuery, not ExecuteScalar mode


-
Now, for the one inside packages.. Well I dont have the exact view to hand because it's at work, but take a look at this:
http://www.codeguru.com/forum/archive/index.php/t-393934.html

It should spark your imagination as to how we get oracle to write code for us. I'll see if I can retrieve the similar one I wrote for the guys at work that you basically say:SELECT * FROM dotnet_param_assist WHERE object_name = 'myFuncName'
And it writes the relevant code for you to just paste in, tweak a bit and you got the parameters..
I think the view is based on user_args, user_arguemnts, user_procedures or some similar name.. you can probably guess it from "Select * from all_views where view_name like '%ARG%'"

Note2: you can also take the header spec of the package function, and create it as an ordinary function, make it reutnr null, follow the procedure above to get the wizard to make a load of code for you, then copy it and paste it
It is definitely longer winded than getting Oracle to write the code

Note3: OracleParameter has enough constructor overloads to be able to do all this on one line of code!
-

Maybe one day i'll bung all this in one EXE.. Till then I just keep plugging away with the dataset designer :)
 

theAsocialApe

Member
Joined
Mar 21, 2008
Messages
13
Programming Experience
5-10
this looks intriguing. i'm going to check it out in more depth at lunchtime. thank you.

-aApe
 

theAsocialApe

Member
Joined
Mar 21, 2008
Messages
13
Programming Experience
5-10
Wow. Just. Wow

Just got the chance to check out the gen_pl_sql_assist and all I can say is

:O and thanks. Not sure what I'm going to do with it yet, but that view is going to be WAY useful I can tell. Thanks!!!!
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
Here you go:

VB.NET:
create view gen_dotnet_paramassist as
select
        --parameter work in vb.net
        ua.*,
      'cmd.Parameters.Add(New OracleParameter("'||COALESCE(argument_name, 'RETURN_VALUE')||
      '", OracleType.'||decode(data_type,'NUMBER','Number','DATE','DateTime','VARCHAR2','VarChar')||
      ', '||COALESCE(data_length, 4000)||
      ', ParameterDirection.'||  NVL2(argument_name, decode(in_out, 'IN', 'Input', 'OUT', 'Output', 'InputOutput'), 'ReturnValue') ||
      ', '||NVL2(argument_name, '"'||argument_name||'_DT_COLNAME"', ' Nothing') ||
      ', DataRowVersion.Current, True, VALUE))' as vbnet_dt_column_mapped,

      'cmd.Parameters.Add(New OracleParameter("'||COALESCE(argument_name, 'RETURN_VALUE')||
      '", OracleType.'||decode(data_type,'NUMBER','Number','DATE','DateTime','VARCHAR2','VarChar')||
      ', '||COALESCE(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("'||COALESCE(argument_name, 'RETURN_VALUE')||
      '", OracleType.'||decode(data_type,'NUMBER','Number','DATE','DateTime','VARCHAR2','VarChar')||
      ', '||COALESCE(data_length, 4000)||
      ', ParameterDirection.'||  NVL2(argument_name, decode(in_out, 'IN', 'Input', 'OUT', 'Output', 'InputOutput'), 'ReturnValue') ||
      ', '||NVL2(argument_name, '"'||argument_name||'"', ' null') ||
      ', DataRowVersion.Current, true, VALUE));' as csharp_with_mapping,

      'cmd.Parameters.Add(new OracleParameter("'||COALESCE(argument_name, 'RETURN_VALUE')||
      '", OracleType.'||decode(data_type,'NUMBER','Number','DATE','DateTime','VARCHAR2','VarChar')||
      ', '||COALESCE(data_length, 4000)||
      ', ParameterDirection.'||  NVL2(argument_name, decode(in_out, 'IN', 'Input', 'OUT', 'Output', 'InputOutput'), 'ReturnValue') ||
      ',  null' ||
      ', DataRowVersion.Current, false, VALUE));' as csharp_without_mapping

from
  user_arguments ua;
Use like:

SELECT * FROM gen_dotnet_param_assist WHERE object_name = 'MY_FUNCTION'

here is what it produces if youre using your function:

VB.NET:
cmd.Parameters.Add(New OracleParameter("RETURN_VALUE", OracleType., 4000, ParameterDirection.ReturnValue,  Nothing, DataRowVersion.Current, False, VALUE))
cmd.Parameters.Add(New OracleParameter("USERNAME", OracleType.VarChar, 4000, ParameterDirection.Input,  Nothing, DataRowVersion.Current, False, VALUE))
cmd.Parameters.Add(New OracleParameter("PASSWORD", OracleType.VarChar, 4000, ParameterDirection.Input,  Nothing, DataRowVersion.Current, False, VALUE))
or you would use the WITH_MAPPING if you are using a procedure and it will e.g. update your datatable into the database:

VB.NET:
cmd.Parameters.Add(New OracleParameter("IN_PREF_GROUP", OracleType.VarChar, 4000, ParameterDirection.Input, "IN_PREF_GROUP_DT_COLNAME", DataRowVersion.Current, True, VALUE))
cmd.Parameters.Add(New OracleParameter("IN_PREF_NAME", OracleType.VarChar, 4000, ParameterDirection.Input, "IN_PREF_NAME_DT_COLNAME", DataRowVersion.Current, True, VALUE))
cmd.Parameters.Add(New OracleParameter("IN_PREF_VALUE", OracleType.VarChar, 4000, ParameterDirection.Input, "IN_PREF_VALUE_DT_COLNAME", DataRowVersion.Current, True, VALUE))
cmd.Parameters.Add(New OracleParameter("IN_PREF_COMMENT", OracleType.VarChar, 4000, ParameterDirection.Input, "IN_PREF_COMMENT_DT_COLNAME", DataRowVersion.Current, True, VALUE))
You obviousnt need to replace e.g. IN_PREF_COMMENT_DT_COLNAME with the name of the datatable column so that vb can get the data automatically out of the column. when you do that you can say:
adapter.UpdateCommand = cmd
adfapter.Update(datateable)

rather than:
foreach ro as MyDataRow in MyDataTable
adapter.MyUpdate(ro.col1, ro.col2, ro.col3 ...)

but you can also do that. anyway, have a play, see what you think
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
and here is the latest known version of my plsql assist. It helps with actually writing plsqls

If you want the .NET tool that writes MERGE statements, well.. I might just go ahead and create the whole thing into one app and release it..

VB.NET:
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","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,

      --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)
    from
      all_tables,
      (select 0 as flop from dual union select 1 from dual) flip
)
order by
  owner, table_name;
 

theAsocialApe

Member
Joined
Mar 21, 2008
Messages
13
Programming Experience
5-10
astounding!

awesome. you've saved me lots of frustration from the typing, and the inevitable copy/paste/change var names but forget to change one errors.

plus, you've really piqued my interest in the oracle side. i generally view the db work as the chore, and the vb/c#/whatever work as the reward, but i'm really interested now in delving into your sql code, and seeing how you're doing this.

thank you very much!
 

theAsocialApe

Member
Joined
Mar 21, 2008
Messages
13
Programming Experience
5-10
ORA-01652. heh. I better talk to the DBA before I alter the tablespace. :p
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,052
Programming Experience
10+
we've been having that quite a bit recently.. seems some idiots have been using really badly written queries which have used massive amounts of temp space to keep track of all the hash tables used in the joins.. As a suggestion, i'd tell you, after you make the view, to really definitely dont try and get it to write code for the entire contents of the database.. Just ask if for the objects you need.. ;)
You can also SELECT * FROM v$session and see if anyone has any huge jobs (last_call_et will be high.. its the numbe rof seconds since the job started). I can give you an sql to see what the code is they are running.. We use it reasonaly often to see if the database is busy; users frequently start badly written reports with stupid parameters and the jobs can take hours to finish.. They havent learned yet to be more choosy or else they get a "Your session has been killed" error

Heh, thinking about it, i could give you the monitor component that does that too.. every hour, it looks for long running jobs and sends an email.. We then make a judgement as to whether to kill the user .In most cases it helps, but some stubborn jobs have needed orakill ;)
 
Top Bottom