Muhammad Masood -- Thanks for the question regarding "Assoicative array of Record type in OPD .net", version OPD .net
Submitted on 6-Aug-2009 3:43 Central time zone
Last updated 6-Aug-2009 11:07
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 we 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.
ODP .Net
August 6, 2009 - 9am Central time zone
Reviewer: Muhammad Masood Qaisar
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; Followup August 6, 2009 - 10am Central time zone:
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.
August 6, 2009 - 11am Central time zone
Reviewer: Muhammad Masood Qaisar
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. Followup August 6, 2009 - 11am Central time zone:
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
March 23, 2011 - 9pm Central time zone
Reviewer: A reader
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