Skip to Main Content
  • Questions
  • Assoicative array of Record type in OPD .net

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Muhammad Masood.

Asked: August 06, 2009 - 3:43 am UTC

Last updated: August 06, 2009 - 11:07 am UTC

Version: OPD .net

Viewed 1000+ times

You Asked

Dear Sir,

How can I use an Associative Array of record type using OPD .net (ORACLE DATA PROVIDER FOR .NET 11.1.0.6.20 PRODUCTION). e.g. I have a package
TYPE my_rec IS RECORD(val_1 number (3), val_2 VARCHAR2(1),val_3 date);

TYPE my_tab IS TABLE OF my_rec INDEX BY BINARY_INTEGER;

FUNCTION my_plan(pt_tab my_tab)  RETURN VARCHAR2;



I want to call this function with this associative array as a parameter from .Net. Kindly Guide me how can I do this.

Thanks

Masood

and Tom said...

I believe you mean "ODP"...

index by tables that are indexed by anything OTHER THAN binary_integers (number) are not accessible outside of PLSQL

But, what you show is not an associative array, it is indexed by binary_integer (1,2,3,4,...... are the indices)


However, what you have is an index by table of records - and PLSQL records are not accessible outside of PLSQL.


You would have to use an object type if you wanted to do this. eg:

create type myscalarType as object (val_1 number, val_2 varchar2(1), val_3 date )
/
create type myarrayType as table of myscalarType
/

and use those SQL types in your plsql code.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ODP .Net

Muhammad Masood Qaisar, August 06, 2009 - 9:40 am UTC

Subject: 'Array is not a one-dimensional' Error When Passing Table of Records as Input Parameter Using PL/SQL Associative Arrays
Doc ID: 286001.1 Type: PROBLEM
Modified Date: 30-NOV-2004 Status: PUBLISHED


The information in this document applies to:
Oracle Data Provider for .NET - Version: 9.2.0.4
Microsoft Windows 2000

Symptoms
A C# application is passing a PL/SQL table of records as an input parameter to a stored procedure using the Oracle Data Provider for .NET (ODP.NET) PL/SQL Associative Arrays feature. Execution of the code results in the following error:

'Array is not a one-dimensional'
Cause
ODP.NET does not support binding a table of records (multidimensional array) as this is a limitation of the Oracle Call Interface API.


Fix
Fix 1: ODP.NET does support PL/SQL Associative arrays in which you can pass an array of single scalar types.

For example, you can declare and pass the following for each column in your table.


TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER;
Tom Kyte
August 06, 2009 - 10:45 am UTC


I do see that you, well, had already answered your question using support and already knew the answer was "it cannot be done" - so thanks for asking me to verify that "it cannot be done". brilliant.


Muhammad Masood Qaisar, August 06, 2009 - 11:00 am UTC

sir it is a 30-NOV-2004 post (Oracle Data Provider for .NET - Version: 9.2.0.4
). i want to confirm that it is still unsportted in newer version of ODP .net.
Tom Kyte
August 06, 2009 - 11:07 am UTC

It is unsupported in every language - I said "However, what you have is an index by table of records - and PLSQL records are not accessible outside of PLSQL. "

OO4O

A reader, March 23, 2011 - 9:18 pm UTC

Hi Tom,

I am using oo4o from a DotNet application and want to record over 100 records to a table containing 20 columns, what is the best way to do this.

Thanks

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library