Access a varray type defined inside a package using oci_new_collection

Question

Hello I am trying to pass in varrays from PHP to Oracle. I am using OCI8 and have earlier worked with varrays as arguments in stored procedures, and on compilation the types of those varrays are created. So while making collection instance on the PHP end, we can directly mention the collection name.

Ex:

$my_coll = oci_new_collection($c, 'MY_ARRAY');

where MY_ARRAY would be the varray type I had declared in the Oracle instance.

create or replace type MY_ARRAY as varray(100) of varchar2(20);

So when I create them outside a package, the type is compiled and would be ready during execution.

If I do that from packages, I am getting back the error

PHP Warning: oci_new_collection() [function.oci-new-collection]: OCI-22303: type ""."my_pack.my_array_type" not found

My package header would look like this

create or replace
PACKAGE my_pack
AS
   TYPE my_array_type is VARRAY(200) of varchar2(20);
    my_arr my_array_type;

    function my_func(
    in_id number,
    in_arr my_array_type    
    )
    return number;

end my_pack;

Now when I make a call from PHP to create an instance of collection, this is the way I do

$my_collection = oci_new_collection($connect,'my_pack.my_array_type');

Now I get the warning type not found.

My question is, how would I have to call the varray type that is in the package??? I am doing it as package.type_name, but I am getting the warning that says type not found.

score 0 · Answer 1 · edited Oct 06 '12 at 03:14

This works for me:

$in_arr = array('1','2','3');    
$s = ociparse($database, "BEGIN my_pack.my_func(:in_id, :in_arr); END;");
oci_bind_by_name($s, ':in_id', $in_id, 32 );
oci_bind_array_by_name($s, ':in_arr', $in_arr, 250, -1, SQLT_VCS);

If you need out:

$out_arr = array(); //OUT   
$s = ociparse($database, "BEGIN my_pack.my_func(:in_id, :out_arr); END;") ;
oci_bind_by_name($s, ':in_id', $in_id, 32);
oci_bind_array_by_name($s,':out_arr', $out_arr, 250, 250, SQLT_VCS);
                                                     // change -1 for 250

Lev Khomich · Accepted Answer · 2011-02-04T19:09:06.070

0

Try to pass schema and typename in uppercase

oci_new_collection($connect,'MY_PACK.MY_ARRAY_TYPE', 'MY_SCHEMA')

Upd.

I've found no limitations in OCI Reference, but PL\SQL Reference was more informative:

A PL/SQL composite type defined in a package specification is incompatible with an identically defined local or standalone stored type

Also from PL\SQL Reference (Table 5-1) all sorts of collections have restrictions. For example VARRAY declared at package level:

Can Be ADT Attribute Data Type only if defined at schema level

edited Feb 04 '11 at 19:09

answered Feb 04 '11 at 17:48

Lev Khomich

2,247
14
18

done that, tried passing schema in upper and typename in lowercase too, but it still gives me the same error – macha Feb 04 '11 at 17:50
well the schema would be mentioned only if the package and type do not exist in that schema. In this case, they do. :( – macha Feb 04 '11 at 19:06
So looks like there's no way to do this without schema-level definition – Lev Khomich Feb 04 '11 at 19:13
Yes, seems so, do you have any idea of how to pass a date from php to a package expecting dates in oracle? – macha Feb 04 '11 at 19:48
some variants at first blush: using of CLOB with parsing on db size, SYS_REFCURSOR as input param in package function and array to refcursor PL/SQL implementation, and, in the last, using of ANYDATA – Lev Khomich Feb 04 '11 at 20:17

Access a varray type defined inside a package using oci_new_collection

2 Answers2

Linked