c# Oracle Table Output Parameter with returning statement

Question

It looks like it's possible to have an Oracle command in C# which has an output parameter, however if it is I'm not sure how to wire it up.

The command:

declare
    type new_rows is table of Table1%rowtype;
    newRows new_rows;

    type newKeys_rec is record (col1 number, col2 number);
    type newKeys_type is table of newKeys_rec;
    newKeys newKeys_type;

begin
    select *
    bulk collect into newRows
    from Table2;

    forall idx in 1..newRows.count()
        insert into Table1
        values newRows(idx)
        returning Table1.col1, Table1.col2 bulk collect into newKeys;
    end;

The command parameter in sql:

Parameters.Add(new OracleParameter
                {
                    ParameterName = "newKeys",
                    ObjectTypeName = "newKeys_type",
                    OracleDbType = OracleDbType.Object,
                    Direction = ParameterDirection.Output
                });

The error:

OCI-22303: type ""."NEWKEYS_TYPE" not found

UPDATE: Following upon the answers below:

1) Declare the type on the schema:

Create type Schema.newKeys_object as object (col1 number, Col2 number)
Create type Schema.newKeys_type as table of Schema.type1_object

2) In the OracleParameter:

Parameters.Add(new OracleParameter
                {
                    ParameterName = "newKeys",
                    ObjectTypeName = "newKeys_type",
                    OracleDbType = OracleDbType.Table,
                    Direction = ParameterDirection.ReturnValue
                });

Try remove the package-level UDT definition and use CREATE TYPE with schema level: CREATE TYPE new_rows IS table OF Table1%rowtype; — Tetsuya Yamamoto
– Tetsuya Yamamoto, Commented Feb 14, 2019 at 3:58
Thanks @TetsuyaYamamoto, I tried this however it failed with: Error: PLS-00329: schema-level type has illegal reference to T13.TABLE1 — user3167162
– user3167162, Commented Feb 14, 2019 at 4:02

Abra · Accepted Answer · 2019-02-14 06:06:25Z

1

In order for the PL/SQL types to be accessible from C# you need to define them as database types using the CREATE TYPE statement. See this Web page for more details on that DDL statement. Note also that a database type belongs to a schema and has access permissions just like a database table has, so when accessing the database type from C# code, you may need to prepend the schema name to the type name, as in...

SCOTT.NEWKEYS_TYPE

answered Feb 14, 2019 at 6:06

Abra

21k14 gold badges101 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

c# Oracle Table Output Parameter with returning statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related