Why do you want to complicate the way if it can be done a lot of other simpler ways. just to show the capability of asktom.oracle.com, I guess.

I dont understand anony's comment but the code is good! But I am confused about the pl/sql table part

May you be put a small example comparing index by bnary integer and one that doesnt and how do we initialize each of them?

Also if index by binary integer gives us unlimited array size shouldnt we use that most of the times? (I guess we have more flexibility)

Thx

Ok, here we are:

ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2          type idx_by_array_type is table of varchar2(30) index by binary_integer;
  3          type array_type is table of varchar2(30);
  4  
  5          idx_by_array   idx_by_array_type;
  6          array          array_type := array_type( 'one', 'two', 'three' );
  7  begin
  8          idx_by_array(1) := 'one';
  9          idx_by_array(2) := 'two';
 10          idx_by_array(3) := 'three';

<b>the above shows the differences in defaulting the values.  a collection type (the non-index by array type) is easier to default...</b>

 11  
 12  
 13          idx_by_array(4) := 'four';
 14          array.extend;
 15          array(4) := 'four';

<b>that shows that adding to the "end" of the index by array is easier, its as if it were already there.  We have to "extend" the other array type...</b>


 16  
 17  
 18          idx_by_array(10000) := 'ten thousand';
 19          array.extend(10000-4);
 20          array(10000) := 'ten thousand';
 21  end;
 22  /

PL/SQL procedure successfully completed.

<b>and that last bit shows that index by arrays are "sparse" in a way.  There are 5 elements in it -- 1,2,3,4,10000 are valid subscripts -- there are no values 5, 6, .... 9999.  The other one however is not sparse.  There are 10,000 elements in that array now.</b>

So, it depends on your needs.  I find myself using the non-index by arrays more and more due to the ease of initialization like this example uses. 

i see in the question, the procedure is declared
"create or replace procedure rdbms_analyze
IS"

in your answer you declared it as
"create or replace procedure rdbms_analyze
aS"

is there any difference if yes what is that???

thanks

This procedure looks good but what happens if we have 1000 users...? How could we define the array in a faster way?

thanx

Tom,

I feel really slow for having such difficulty in grasping this concept, when I really need to. We use subquery inlists VERY extensively to control our PL/SQL DSS code, there may be 100s run in the course of an entire process. The form is

INSERT INTO Target_table
( SELECT *
FROM Some_table
WHERE Some_column IN ( SELECT * FROM Another_table WHERE Another_column = aBind_variable ) );

I have been searching your site, thinking there should be a way to accomplish this with a collection, but I'm not having any luck at getting it to work. When I code a TYPE and a variable based on that type in a test PL/SQL procedure, I get a complaint that says 'ORA-00902: invalid datatype' and it points to the 'AS HID_LIST' in the code, below.

TYPE HID_List IS TABLE OF VARCHAR2(20) NOT NULL;
sHID_List HID_List;

BEGIN

SELECT CAST( MULTISET
( SELECT DISTINCT HG.Hospital_id
FROM T1 c,
T2 hg
WHERE c.CLIENT_DESC = pClient
AND hg.CLIENT_CODE = c.CLIENT_CODE
AND hg.PRODUCT_ID = cProd_ID )
AS HID_List )
INTO sHID_List
FROM DUAL ;

Next, I tried a specific example of yours from this discussion:

sHID_LIST HID_List;

BEGIN
INSERT_LOG( pRun_ID, cSP_Nm, 'Pass', 'BEGIN ' || cSP_Nm );

FOR x IN ( SELECT DISTINCT HG.Hospital_id
FROM CORPORATE.Clients c,
CORPORATE.Hospital_Groups hg
WHERE c.CLIENT_DESC = pClient
AND hg.CLIENT_CODE = c.CLIENT_CODE
AND hg.PRODUCT_ID = pProd_ID )
LOOP
SELECT x.Hospital_ID INTO sHID_LIST
FROM DUAL;
END LOOP;

In this, I got an error that pointed to the x in x.Hospital_ID portion of the code and said 'ORA-00932: inconsistent datatypes: expected UDT got CHAR'.

Also, most all of your examples and your inlist function I saw referenced, in other discussions, work directly on known strings. We want to use a dynamic list through bind variables, like the example above, to drive the list for each schema. I think if I can get the second example to work this will solve that problem, but I don't quite understand why the query in the FOR x and the query inside the LOOP are both needed.

Finally, the last part of this question I have is relative to CREATE TYPE. When I tried to do it in SQL*Plus, it was private to the schema where it was created. Is there no way to make the type globally public, or will I have to create the type in every schema that might reference it? I see an AUTHID CURRENT_USER feature in the syntax doc, but how would you use it. Create a public synonym? Seems odd for a data element, but maybe?

The reason behind this question is that we use AUTHID CURRENT_USER and run our PL/SQL extensively across many schemas.

OK, Tom, that was a much faster response than I expected, thanks, the Schema reference was painfully obvious when you pointed it out.

First, all that I am describing here, I want to do in PL/SQL. We do very little in SQL.

This will be my last question in this thread. Yes, the subquery is OK. The underlying improvement I hoped to uncover was that it strikes me as quite inefficient to keep going out to the buffer cache to retrieve the same small ( < 100, usually ) list of values, over and over and over. We can hold individual values in a PL/SQL program as variables, yet there is seemingly no way to hold and use a list, easily and effectively.

I was thinking that collections could provide me with that kind or form of constant list for the duration of the Stored Procedure, to use instead of the subquery, but I seem to be missing some key point.

Hi Tom,
In the "Create type " statement, Functions and Procedures
can be defined with "static" keyword.What role does "static" play in the function or procedure declaration?
Please do reply.