I want to pass/bind an array of (key1, key2) to an Oracle PL/SQL stored procedure using PHP. I'm able to bind primitive types and arrays of primitive types, but haven't found a way to pass complex datatypes back and forth.
Is this unsupported? So far I've been having to pass along multiple arrays -- one for each subtype in my complex type -- and then depend on their indexes to reconstitute them in the procedure.
Does not seem supported. oci_bind_array_by_name() and oci_bind_by_name() seems to only be able to pass primitive types. The documentation does not show any other possibility.
Sharing object/structures/complex types is not easy, as the representation of an object in different languages can be far from each other. For example memcached allow to store objects from any language that has a library that convert objects from its own representation to memcached one and vice versa.
You could do it if oci8 had a converter between PHP hashes/objects to complex types in PL/SQL but that's not the case.
You just can make several calls to oci_bind_by_name() and so call your procedure with as many arguments as needed for each key of your hash/complex type.
Why don't you serialize the object's state as XML?
Currently it's pretty common practice to searilize objects as XML so they can be passed across the web in a language/platform agnostic manner. Why not do the same and store it as a varchar in the DB.
Note: The glaring deficiency of this method is you won't be able to select fields in the database by attributes of the object in a clean manner because it mixes multiple attributes of the object in one field of the database.
If you do need the attributes of the object to be searchable, you'll need to break out the data from each of the object's attributes into their own respective fields.
Further to Evans's suggestion, I'd recommend using JSON. Both PHP and Oracle can encode/decode JSON and it's substantially less verbose than XML.
Whichever you go for, serialization is probably the way to go. That said, I would advise against storing the raw JSON in a varchar field - Oracle is able (with the tool linked above) to both interpret JSON and generate JSON responses.