Oracle CREATE TYPE and PL/SQL

Question

We have an Oracle type that's declared as such:

CREATE OR REPLACE TYPE its_accountarray;

There is no base type. This type is then used in Procedures and packages (and in Java Stored procedures as an array descriptor). In the procedure/package it uses extend function to add to array.

<declare section>
    v_account latax.ITS_ACCOUNTARRAY := new ITS_accountArray();
...
BEGIN
    ...
    ...
    v_account.extend(1);

and in Java, it's used as: oracle.sql.ARRAY ls_accountARRAY, ls_periodARRAY;

oracle.sql.ArrayDescriptor ad =   ArrayDescriptor.createDescriptor(**"ITS_ACCOUNTARRAY"**, oconn);

ls_accountARRAY = new ARRAY(ad, oconn, arg_accounts);
ocs.setARRAY(2, ls_accountARRAY);

I am curious as to how this works. Even though the name has Array in it, it is not defined as an array or table type, like I typically see. It works, but is this a legal usage or should I declare the type to be of some array type explicitly?

Thanks

Sam

Update: I just want to correct this for anyone stumbling on this question: This turned out to be just a display problem in the Sqltools version I was using. When I edited the type, it was actually displaying the source partially as CREATE OR REPLACE TYPE its_accountarray That's what got me. The body portion "AS TABLE OF..." was not displayed; apparently it put a lot of spaces in between, that body didn't show in view. As I mentioned below, SQL*Plus did show correctly. New version of the tool does better, still with lot of spaces embedded, but at least within the view. — svaratech
– svaratech, Commented Sep 18, 2015 at 19:19

Dave Costa · Accepted Answer · 2011-11-07 21:04:56Z

2

I tried to reproduce what you say is happening, and got an error:

CREATE OR REPLACE TYPE its_accountarray;

DECLARE
  v its_accountarray := its_accountarray();
BEGIN
  null;
END;
/

PLS-00311: the declaration of "ITS_ACCOUNTARRAY" is incomplete or malformed

So, you're probably looking at the wrong type definition (e.g. you're looking at it in the wrong schema), or not looking at the entire type definition (e.g. there is a TYPE BODY that provides the implementation). Since this seems to be acting like a nested table type, the former sounds more likely.

answered Nov 7, 2011 at 21:04

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

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

2 Comments

svaratech Over a year ago

Hi Dave, Thanks for responding so quickly.

svaratech Over a year ago

Hi Dave, I think I pressed ENTER too quick last time. You are right!! That declaration was incorrect. I am wondering if it's a bug in the tool I was using (SqlTools 1.5). When I opened the type in SQLTools, it only showed that portion and it did not have a body! That's why I was so confused. I just tried Describe ITS_ACCOUNTARRAY in sqlplus it shows as expected: SQL> describe ITS_ACCOUNTARRAY ITS_ACCOUNTARRAY TABLE OF VARCHAR2(24) I've been using SQLTools for a few years and this is the first time, it really got me. Thanks a lot again. Sam

Collectives™ on Stack Overflow

Oracle CREATE TYPE and PL/SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related