Array parameter to oracle stored procedure

Dear friends,

 I want to pass array parameter to following oracle  stored procedure from visual studio 2005 using vb.net my is question is it possible or not

  create or replace package demo_pkg
    as
        type charArray is table of varchar2(255) index by binary_integer;
       procedure emp_report( pin  in charArray,
                             charge_acct in charArray);
  end;
/



  1   create or replace package body demo_pkg
  2      as
  3         procedure emp_report( pin  in charArray,
  4                              charge_acct in charArray)
  5         is
  6         begin
  7            for i in 1 .. pin.count loop
  8  insert into test values(pin(i),charge_acct(i));
  9             end loop;
 10         end;
 11*    end;
 

 

thank you.

regards,

asad 

 

 


regards,
Asad Mahmood
0
asad_dba
8/8/2008 11:05:08 AM
asp.net.oracle 2269 articles. 0 followers. Follow

3 Replies
1841 Views

Similar Articles

[PageSpeed] 31

Hi,

  Oracle's ODP.NET (oracle.dataaccess.client) supports Associative Arrays (aka Index-by Tables).  Theres examples in the docs and also that install with odp.net.

  Microsoft's ODP (system.data.oracleclient) doesnt support them though as far as I know.

 Hope it helps.
Greg

0
greg
8/8/2008 4:13:00 PM

dear ,

 from where I can download opd.net and also it will work with oracle 8.1.7 database or not.

thank you

regards,

asad


regards,
Asad Mahmood
0
asad_dba
8/8/2008 9:35:41 PM

I dont think the 10g odp will work with a 817 database, but you can stil get the 92 version at

http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

 Hope it helps,
Greg

0
greg
8/13/2008 4:40:57 PM
Reply:

Similar Artilces:

Oracle store procedure problem using Powerbuilder and Oracle 8.0.4.0 Net
Powerbuilder version 6.0.00 / 6.5 Build 444 Oracle Net 8.0.4.0 I found there is problem with Oracle store procedure having more than 6 in parameters. I got GPF error on Window 95 and Dr. Watson error on NT. Also, I found it works find in Oracle Net 8.0.3.0. Is this a bug? Is there any new patch for this if it is a bug. Any recommendation about What version Oracle Net or SQL Net should I used for Oracle server version 8.0.4.0 (I don't think I am using any Oracle 8 new features)? Thanks in advance, Sharon Li If you use PB 6.5, you can try the new Oracle 8 driver: ftp...

is there any way to call oracle stored function, not stored procedure using .net?
I am just wondering if I can call oracle stored function instead of stored procedure.Thank in advance! you call a function just like s procedure.except you need to add a parameter for the return value.myparam ... ParameterDirection.ReturnValue;--dweezilSELECT * FROM users WHERE clue = 'yes';Records found: 0...

Arrays to Oracle Stored Procedures
------_=_NextPart_001_01C9D899.4F1828BA Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Is it possible with Latest DBI modules to pass an array [string or integer] to Oracle stored procedure which accepts an array? Procedure specification is like this: =20 Create or replace procedure p_getarray_perl(p_arr_value dbms_sql.varchar2_table) -- String array. =20 How do I pass an array to this from PERL? My main requirement is to read a file and send this file contents as an array [since file is comma separated, procedure...

Parameters of an Oracle stored procedure
I'm using PowerBuilder 9.0 with Oracle Net8. Is it possible to make parameters of an Oracle stored procedure into bind variables? Thanks. ...

Oracle stored procedure with .net
Dear all I wrote a following stored procedure and program coding. ------------------------------------------------------------------------------------------ Stored procedure ------------------------------------------------------------------------------------------- create or replace procedure Delete_emp (p_empno  number) is begin     delete from emp      where empno = p_empno; end delete_emp; ---------------------------------------------------------------------------------------------- program coding ---------------------------------...

MS Oracle client and stored procedures-oracle hell
i'm using asp.net 2, oracle client latest version (9..), oracle database 8i. i'm trying to execute oracle stored procedures via MS oracle client (system.data.oracleclient) and nothing seems to work. all of them failed with oracle error: line 1, column 7: PLS-00306: wrong number or types of arguments the same procedures ran by MS OLEDB provider for oracle with the same code (except offcourse the object types) are running perfectly. i tried the procedures with ODP.NET and they worked too. it happenes to all procedures even the small ones. it seems that oracle client denies MS oracle c...

Invoking Oracle Stored-Procedure from Sybase Stored-Procedure
I have a peculiar design constraint. I need to invoke a Oracle Stored-Procedure pass it 1 parameter and use the result-set returned by the oracle stored procedure in my Sybase stored procedure. In affect I need to invoke a Oracle Stored-Procedure returning a result-set from a Sybase Stored-Procedure. I checked up and found Direct-Connect could be one option where-in I could invoke a rpc. Has any-one tried using Direct-Connect ? Also I remember Sybase also had a product\technology called Omni-Connect which could be used to connect to Oracle database. Is Omni-Connect still ...

Invoking Oracle Stored-Procedure from Sybase Stored-Procedure
I have a peculiar design constraint. I need to invoke a Oracle Stored-Procedure pass it 1 parameter and use the result-set returned by the oracle stored procedure in my Sybase stored procedure. In affect I need to invoke a Oracle Stored-Procedure returning a result-set from a Sybase Stored-Procedure. I checked up and found Direct-Connect could be one option where-in I could invoke a rpc. Has any-one tried using Direct-Connect ? Also I remember Sybase also had a product\technology called Omni-Connect which could be used to connect to Oracle database. Is Omni-Connect still ...

Invoking Oracle Stored-Procedure from Sybase Stored-Procedure
I have a peculiar design constraint. I need to invoke a Oracle Stored-Procedure pass it 1 parameter and use the result-set returned by the oracle stored procedure in my Sybase stored procedure. In affect I need to invoke a Oracle Stored-Procedure returning a result-set from a Sybase Stored-Procedure. I checked up and found Direct-Connect could be one option where-in I could invoke a rpc. Has any-one tried using Direct-Connect ? Also I remember Sybase also had a product\technology called Omni-Connect which could be used to connect to Oracle database. Is Omni-Connect still ...

Problem with Oracle Stored Procedures using ODP.NET in VB.NET (VS 2003)
This is the stored procedure,Procedure RetrieveReservations(pProperty In Varchar2,pResHeaderId In Varchar2,pEmail In Varchar2,pPasswd In Varchar2,ResultSet In Out ResCursor);This is the old .asp code (part of it)objConn.ConnectionString = Application("BackEndConnection_ConnectionString")objConn.Open objComm.ActiveConnection = objConn objComm.CommandText = "internet_package.RetrieveReservations"objComm.CommandType = adCmdStoredProc objComm.Parameters.Append objComm.CreateParameter("pProperty", adVarChar , adParamInput, 8, Request.QueryString("propid"))objComm.Parameters.Append objComm.Crea...

PLS-00221: is not a procedure or is undefined FUNCTIONS VS. STORED PROCEDURES
PLS-00221: is not a procedure or is undefined FUNCTIONS VS. STORED PROCEDURES - ADO.NET TO ORACLE FUNCTION   I have a very simple oracle function I'm trying to call from ado.net. The code works fine for a stored procedure but I get the error  PLS-00221: is not a procedure or is undefined  when I try to call the function. here's my code:  I've manually tested both the function and stored procedure and they are both working as expected.  here's my  vb.net code Protected Overrides Sub OnLoad(ByVal e As EventArgs) Dim cnn As OracleConnection Dim sConnString As String = "Data Source=XXXXXXXXXXXXXXXXXXXXXXXXXXXX" cnn = New OracleConnection(sConnString) Dim cmd As New OracleCommand With cmd .CommandType = CommandType.StoredProcedure .CommandText = "FN_1"     ' THIS DOES NOT WORK '.CommandText = "SP_1"    ' THIS WORKS .CommandTimeout = 0 .Connection = cnn .Parameters.Add(New OracleParameter("iprocname", OracleType.VarChar)).Value = "P1" End With cnn.Open() If cnn.State = ConnectionState.Open Then Response.Write("connected") End If Dim job As String 'job = cmd.ExecuteScalar().ToString job = cmd.ExecuteNonQuery().ToString TextBox1.Text = job 'cmd.ExecuteNonQuery() cmd.Dispose() cnn.Close() End Sub I figured it out. I needed to specify param direction....

How to convert Sql Server Stored Procedures into Oracle Stored Procedures
Hi All, I am migrating from sql server2000 to oracle. I have moved all table into oracle manually. Now I need to move stored procedure. I don't know how to convert sql server stored procedure into oracle stored procedure form. Is there any tool which will convert directly. Please some one help me. Thank you.    Hi there,Please use the tool bellow, which does the job you want. http://www.swissql.com/products/sqlserver-to-oracle/sql-server-to-oracle.html thanks sharp guy...

Passing array parameter to Oracle Procedure
I am trying to pass a paramater as ARRAY to Oracle Stored Procedure from ASP.NET page using OracleClient. oo4o and ASP classic have a method called parameters.AddTable which is not there in OracleClient ans ASP.NET. In VB 6.0 with oo4o it is possible like following: pconnection.Parameters.Add "ov_tseqno", 0, 2 pconnection.Parameters.AddTable "pv_arr", ORAPARM_INPUT, ORATYPE_VARCHAR2, 20, 100 Set arry1 = pconnection.Parameters("pv_arr") arry1(0) = "Angel Pabon " arry1(1) = "Nawed Khan " arry1(2) = "FUL ...

Passing Array to Oracle Stored Procedure
I have requirement where in I need to pass a array from Powerbuilder to Oracle Stored procedure and also I want to know how to access the returned array from the Oracle stored procedure. Any help in this regard is appreciated. Thanks -Sanjay ---== Posted via the PFCGuide Web Newsreader ==--- http://www.pfcguide.com/_newsgroups/group_list.asp Make it a ref in the RPCFUNC declaration. It will be an INOUT argument with a TABLE OF type in the procedure or function. You will need to pre-fill the array [to maximum length if it is type string] *before* the call. On return the values wil...

Oracle: No stored procedure parameters during design.
I have a simple test stored procedure. I setup a TAdoStoredProcedure and a TAdoDataSet to call the procedure. I set the name (The name is present in the drop down.), but no parameters show up after setting the name procedure's name. What is missing here? Here is the procedure's code. create or replace PROCEDURE ns_foo( aInt1 in INTEGER, aInt2 in INTEGER, aResult out INTEGER) IS BEGIN aResult := aInt1 + aInt2; END ns_foo; My database is a local database with Oracle 10g Express. I think I might have found the problem. I created a new user and created the objects in the ...

Number parameters in Oracle Stored Procedures
This is an FYI. I am running Mobilink Server 10.0.0.2465 as a service under Windows XP SP2. I am using an Oracle 10G consolidated database with the Oracle ODBC drivers provided by Mobilink. When calling an Oracle Stored Procedure from a Mobilink event, if an arg is set to type NUMBER(15,5) and you pass in a decimal number, 10.56 for example, the ODBC driver appears to be rounding the number to 11. Now, if I change the arg to using the tablename.columname%TYPE syntax in the Oracle stored procedure, then the number is not being rounded. I know this was not an issue in Mobil...

vb.net function and Oracle procedure to get an Oracle Dataset??
I have similar code (a procedure and function) working perfectly with a sql server data source. I;'m trying to do the same things with Oracle, but have run into the problems.    I'm not an oracle expert, so I'm not sure if producing a cursor is my only and best  option. create or replace package body PK_RETAILER isPROCEDURE sp_Get_Order_History(v_ani IN number,output_cursor out PK_CURSORES.generic_cursor) ASBEGINopen output_cursor forselect o.account_number, p.phone, o.amount, o.name, o.init_datefrom orders ojoin users_phones p on p.account_number...

Oracle stored procedure with BLOB parameter
I have two simple Oracle7 stored procedures that will store LONG RAW data into and retrieve from an Oracle table. They are declared as transaction object's local external function - something like put_raw( long id, BLOB b, REF int returncode) and get_raw(long id, REF BLOB b, REF int returncode) The problem I am having is that put_raw() works just fine. Get_raw() seems to work, and to verify , I write the BLOB variable into a file. It is supposed to be a JPG file, but the written file is not a JPG. Secondly, before invoking get_raw(), I must do: b = BLOB(space(32000)) ...

Oracle Output Parameters from Stored Procedure
Powerbuilder 8.0 Executable compiled in Windows 2000 is causing GPF while executing on Windows NT workstation. This problem is occurring frequently while accessing the application from windows NT workstation and same one is working perfectly in windows 2000 workstation. After investigating the GPF we found out that this problem is occuring while executing the stored procedure with more than 20 parameters from powerbuilder executable on windows NT workstation. The same part of the code is executing successfully on windows 2000 workstation. we are suspecting that the problem is occuring...

Binding parameters of an Oracle stored procedure
I'm using PowerBuilder 9.0 and Oracle Net8. When a DBParm parameter DisableBind is set to 0(default), PowerBuilder binds input parameters to a compiled SQL statement. I wonder if this applies to parameters of an Oracle stored procedure. Thanks. Since you don't put a : on the arguments to an RPCFUNC, I don't think so. <Nobuo Saito> wrote in message news:429eb1fe.38ca.1681692777@sybase.com... > I'm using PowerBuilder 9.0 and Oracle Net8. > When a DBParm parameter DisableBind is set to 0(default), > PowerBuilder binds input parameters to a compiled SQL...

How to use Oracle Store Procedure with out parameters?
experts, Could you give me a hand to let me go out of my problem? I create a package acc.MS_Load.remove_(info_ out varchar2, contract_ in varchar2), then I using the following script to use it as that: string info_, contract_ info_ = space(2000) contract = 'NN' DECLARE myproc procedure for acc.MS_load.remove_(:info_,:contract_); Execute myproc; if sqlca.sqlcode .... When it runs, it said that "PLS-00306:wrong numnber or types of arguments in call to 'acc.ms_load.remove_'" Why? Thank you and best regards! Venn Try using INOUT instead...

FOR XML and OPENXML using Oracle / using xml in ORACLE stored procedure
Hi, I need a help with respect to xml operations in ORACLE.For example, Need to send multiple rows at once into stored procedure.orNeed to  pass data (a single entity) to stored procedure as a xml from Data Layer ( Instead of individual params)In case of sql server, There is an approach like,     From Front End,   Serialize the entity collection or single entity as xml and send to the db.   Pass xml to stored procedure from Data Layer     sql server stored proc in turn,  &n...

Pass string array to oracle stored procedure.
Hi there, Am trying to pass a string array to an ORACLE 8i stored procedure, I have tested the procedure on the server and know it works, PB7 is giving me any error though when trying to save ; 'variable reference in database statement has unsupported data type' My PB code is; dec{0} ld_sys_ed_key STRING lsa_inp_key[],lsa_inp_val[] //Initialise lsa_inp_key[1] = '1900000137' lsa_inp_key[2] = '1900000139' lsa_inp_val[1] = 'ORCLEVAL' lsa_inp_val[2] = 'YES' ld_sys_ed_key = 0123456789 DECLARE insert_into_system_inputs PROCEDURE FOR ...

Passing array argument to Oracle stored procedure
I have PB 7.0.3 build 10047 and O84 driver running on Windows 2000 with Oracle 8i on a DEC Alpha. Is this the driver we should be using? If not what should we be using and where do I get it? I am getting an error message while passing an array argument to a stored procedure: Select error: ORA-06550: line 1, column 16: PLS-00306: wrong number or types of arguments in call to ...ORA-06550: line 1, column 16: PL/SQL statement ignored. And I do have SQLCA.dbparm ="pbdbms=0" It works fine if I pass a value that is not an array. Here is my pl/sql program: CREATE OR REPLACE PACKAGE pkg_plsql_table_test IS TYPE emp_id_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE my_result_set IS REF CURSOR; PROCEDURE sp_plsql_table_test(arg_emp_id IN emp_id_tbl, arg_emp_name IN OUT my_result_set); END pkg_plsql_table_test; / CREATE OR REPLACE PACKAGE BODY pkg_plsql_table_test IS PROCEDURE sp_plsql_table_test(arg_emp_id IN emp_id_tbl, arg_emp_name IN OUT my_result_set) IS BEGIN OPEN arg_emp_name FOR SELECT RTRIM(ENAME) INTO arg_emp_name FROM emp where empno = arg_emp_id(1); --where empno in(arg_emp_id(1),arg_emp_id(2), arg_emp_id(3)); END sp_plsql_table_test; END pkg_plsql_table_test; / Thanks. ...

Web resources about - Array parameter to oracle stored procedure - asp.net.oracle

Parameter - Wikipedia, the free encyclopedia
A parameter (from the Ancient Greek παρά , "para", meaning "beside, subsidiary" and μέτρον , "metron", meaning "measure"), in its common meaning, ...

fb_source Parameter - Facebook-Entwickler
This document lists the complete list of values for the fb_source parameter that is returned with th...

Google / Firefox Parameter - Flickr - Photo Sharing!
HS what? www.seroundtable.com/archives/017105.html

Cat 8: TIA performance standards committee chairman previews parameters of forthcoming twisted-pair cable ...
Category 8, the next-generation twisted-pair cabling specification, is still in development, but the outlook looks quite positive that it will ...

Philippine president asks for 'parameters' of Mayon Volcano tours
Philippine president asks for 'parameters' of Mayon Volcano tours People's Daily Online ... 26, 27, 28, 29, 30, 31. Jan, Feb, Mar, Apr, May, ...

Alternatives to boolean parameters
... ) { PrintLine ( item . Name ); if ( printDetails ) { PrintLine ( item . Description ); } } } Aha! Now can see that the boolean parameter ...

Exporting Error (Error in Parameter...: Apple Support Communities
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability ...

Apple tech monitors device aging, adjusts operating parameters to ensure optimal performance
... describing a method of monitoring the aging of a device's electronics, comparing the data with a global average and modifying operating parameters ...

Is it poor programming practice to pass parameters as objects?
... a free, community-powered network of 100+ Q&A sites . Risser asked: So, we've got a guy who likes to write methods that take Objects as parameters, ...

Parameter Short Film Competition sponsored by Grolsch
Grolsch presents Parameter The A.V. Club Short Film Competition Please Enter Your Date of Birth Ineligible entry Enter For people over the age ...

Resources last updated: 3/11/2016 5:28:00 PM