Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of anonymous TABLE/VARRAY types in sql's Oracle. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Preface

For information about sql Oracle anonymous TABLE/VARRAY types, I sometimes create structures like this in Oracle

SELECT * FROM TABLE (STRINGS ('averse,' baked,'c')) SELECT * FROM TABLE (NUMBERS (1,2,3))

Obviously, I can declare my own type for the above. I can choose between TABLE and VARRAY. For example:

CREATE TYPE STRINGS AS TABLE OF VARCHAR2 (100); CREATE TYPE NUMBERS AS VARRAY (10) OF NUMBER

In this special case, another solution is to write.

SELECT'a 'FROM DUAL UNION ALLSELECT 'b'FROM DUAL UNION ALLSELECT 'c'FROM DUAL

But I may have a more complex example, I really need a TABLE / VARRAY type. So if my SQL is running on an unknown system, I can't create a type because I may not have the necessary funding?

So my question is: does Oracle know about the "anonymous" TABLE / VARRAY types available on any Oracle instance? Simple ARRAY type similar to Postgres / H2 / HSQLDB?

Update: I mainly run this SQL from Java, if this is relevant. You don't need to explain PL / SQL to me, I'm just looking for anonymous SQL array types (that is, "anonymous" isolated storage types). If it doesn't exist, the answer is no.

Best answer

There are several SYS schemas that offer you not to be afraid to explicitly quote. Here are some I often use (odcivarchar2list is not very good because it chews a lot of memory: for me

Dbms_debug_vc2coll string) .SQL > desc sys.odcinumberlist sys.odcinumberlist VARRAY (32767) OF NUMBERSQL > desc sys.odcivarchar2list sys.odcivarchar2list VARRAY (32767) OF VARCHAR2 (4000) SQL > desc sys.ODCIDATELIST sys.ODCIDATELIST VARRAY (32767) OF DATESQL > desc sys.dbms_debug_vc2coll sys.dbms_debug_vc2coll TABLE OF VARCHAR2 (1000) SQL >

However, if these are not enough to meet your needs, run this query to find out more:

Select type_name, ownerfrom all_typeswhere typecode = 'COLLECTION'and owner! = user/

Of course, this result will vary from database to database. For example, many of the relationships in my database are owned by XDB and not every system installs it. Although this answer is not always recorded in earlier versions, after 9iR2 (maybe earlier versions), I can see the top four of this answer on each database.

"Note that ALL_COLL_TYPES seems to be an even better dictionary view

To find appropriate types "

That's a good point of view. We can also filter COLL_TYPE in order to understand VARRAY. The view is introduced to 10g, while ALL_TYPES is available on 9i. Like most Oracle, the later the version, the more features it has.

This is the end of this article on "sample analysis of anonymous TABLE/VARRAY types in sql's Oracle". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.