Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, zaheer .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: August 02, 2013 - 5:44 pm UTC

Version:

Viewed 50K+ times! This question is

You Asked

I am using Oracle Report Builder 3.0.5.8.0 . While
using bind refernce i have created a dynamic list of values to
give input for that particular bind reference , actually i want
multi selection from LOV for that bind refernce is it possible
and may i use IN Operator over bind reference variable.



and Tom said...



You can use a bind variable in an IN but I don't think it'll do what you want. I believe you want a string that has the value:

1, 2, 3

for example (a single variable) to be used in a query like:

select * from t where x in ( :that_variable );

and have all rows returned such that x is in the set of 1, 2, 3. What would happen though is that every row in T such that x = "1, 2, 3" would be returned (if it worked the other way -- you would never be able to IN on something with commas in it).

One 'trick' that works very well on Oracle8.0 and up is to use an object type that is a nested table type and a simple parse routine that returns this nested table type given a string input. What I mean is best explained via an example:

ops$tkyte@8i> create or replace type myTableType as table of number;
2 /
Type created.

ops$tkyte@8i> create or replace function str2tbl( p_str in varchar2 ) return myTableType
2 as
3 l_str long default p_str || ',';
4 l_n number;
5 l_data myTableType := myTabletype();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15 end;
16 /

Function created.

ops$tkyte@8i>
ops$tkyte@8i> select * from all_users
2 where user_id in ( select *
3 from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )
4 /

USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM 5 20-APR-99



Rating

  (138 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

cursors with in sql

Munz, February 24, 2002 - 10:58 pm UTC

TOm:

1. just to clarify things up. how do you implement this soultion in a stored procedure that creates html

ket us say we have a web page that prompts user to enter up to three states and then we want to pull all records from table that match these three states.

would you define cursor like

create or replace procedure test (p_state)
as
cursor find_records(p_state) is
select * from table where state in (select ( from THE (select cast (str2tbl(p_state) as mytabletype) from dual

you also create the str2table function separately.

am i correct.

what does cast do?

Thanks,



Tom Kyte
February 25, 2002 - 8:10 am UTC

1) is it no different then in a stored procedure that does NOT create HTML.


You will create the table type (myTableType).
You will create a function str2table.
You will create your procedure that generates HTML.

If p_state is a variable that has a comma delimited string like 'PA, VA, MD' -- then yes, what you have there is OK.

the cast just tells the SQL layer what type to expect back - mytabletype

Lexical

Steve, February 25, 2002 - 8:45 am UTC

Couldn't you just use a lexical parameter within your report


select * from t where x in ( &that_variable );

e.g.

Declare you user parameter as normal give it a default value lets say ('x') just so it will compile, and then in a After parameter form trigger , just assign the dynamic value you talked about to the lexical parameter. The only difference in the SQL would be to use the &that_variable instead of the :that_variable.

Tom Kyte
February 25, 2002 - 10:31 am UTC

does not use bind variables. I despise code that does not use bind variables as the is the best way to limit your scalability, flood your shared pool, kill your repsonse time and fail.

Use bind variables!

Steve, February 25, 2002 - 11:09 am UTC

Is this because of soft and hard parsing ??? i.e. the bind vaiable approach would be soft parsed because its sql as already been checked i.e. rights to tables etc... but the lexical approach means the db can't gurantee that the lexical bit won't referrence another table and thus as to hard parse.

Tom Kyte
February 25, 2002 - 11:16 am UTC

It is soft vs hard -- but not for the reason you specify.

If they code:

select * from t where x in ( &blah )

they will generate queries like:

select * from t where x in ( 1 );
select * from t where x in ( 1, 2 );
select * from t where x in ( 2, 1 );
select * from t where x in ( 1, 2, 3 );
....

virtually EVERY query that uses this approach will be hard parsed. That will KILL performance. You want to avoid hard parses.


Great

Alla, February 25, 2002 - 6:52 pm UTC

Very useful info and examples.

IN statement

munz, February 25, 2002 - 8:35 pm UTC

Tom:

as a followp to my example above, p_state is whatever the user selects which means is either VA, or MD or PA without any commas.

Does this mean I have to make it comma delimited string by

FOR i in 1..p_state.count
v_state:=p_state(i)
If i > 1 then
v_state:='||v_state||,||p_state(i)||'
else
v_state:='||p_state(1)||'
end if;

BY THE WAY, would not this give the same result:

CURSOR find_state IS
SELECT * from table where state=p_state1 or state=p_state1
or state=p_state3;

If p_state2 or p_state3 are null then nothing will be found for those states unless i have other records with a null state then it will not work.

Thanks,

Thanks,


Tom Kyte
February 25, 2002 - 8:50 pm UTC

If you can have AT MOST three items, by all means code:

select * from t where state in ( p_state(1), p_state(2), p_state(3) );

If you know the (reasonable) upper bound on the number of elements -- go for it, just code the explicit binds.

Else, use the str2table trick (in which case -- yes, you would have to "string up" the elements of the array into a single string)

bind variables

Munz, February 26, 2002 - 8:57 pm UTC

Tom:

If you do define the cursor

select * from t where state in (p_state(1),p_state(2),p_state(3))

is there a way to avoid records with null states in case user select 1 or 2 states only?

Thank you,

Tom Kyte
February 27, 2002 - 7:52 am UTC

NULLS will never come out from that.


scott@ORA817DEV.US.ORACLE.COM> select ename from emp where comm in ( 500, NULL, NULL );

ENAME
----------
WARD

scott@ORA817DEV.US.ORACLE.COM> select count(*) from emp where comm is null;

COUNT(*)
----------
10


just set the last N array elements to NULL and they are effectively "ignored"

Ref cursor and collections

Ranjan, April 15, 2002 - 6:34 pm UTC

I am getting the following error pls suggest.


1 select * from dual
2 where
3 exists ( select *
4 from THE ( select cast( danka_pkg.in_list( '1, 3, 5, 7, 99' ) as mytab ) from
5* dual ) )
6 /
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel


Tom Kyte
April 16, 2002 - 7:44 am UTC

that error equals "call support".

How about with dynamic SQL?

James, April 16, 2002 - 2:20 pm UTC

Could you suggest an approach to get a list of a varying number of elements to behave as bind variables using NDS? I'm applying the SYS_CONTEXT() technique you described recently to an application that builds dynamic queries based on user selected criteria (up to 45 or so of these). Some of the criteria are lists of things, US states for example.

One way, I suppose would be to use the technique you've described here and pass the nested table type object to the NDS statement via 'OPEN CURSOR v_cursor (v_stmt) USING v_bindvar'. But, for the same reason that static queries would get unwieldy when dealing with all the possible combinations of criteria, figuring out which variables and in which order to list them in the USING clause becomes unmanagable.

Suggestions? (Thanks in advance)

Tom Kyte
April 16, 2002 - 9:42 pm UTC

right above IS THE APPROACH!!!

str2tbl.

Most helpful example

Andreas Faafeng, May 02, 2002 - 8:54 am UTC

Once again, I'm impressed by your wonderfully simple and straight-forward solution to what initially was an intricate question.

I'm working in a group of 4 people, of which 3 has bought your book.

Your mission to teach us all about latches, bind variables and parsing certainly helps!

Best regards,
Andreas Faafeng


Hmm. What about this?

dre, May 06, 2002 - 5:51 pm UTC

I'm not very clear about binding and nested object table types and latches but does this fall into the 'despised' unbinded category or is it acceptable?

SELECT thisid
FROM thistable
WHERE INSTR(stringOnumbers,','|| thisid ||',') != 0;

tks. DRE


Tom Kyte
May 06, 2002 - 8:58 pm UTC

that looks just dandy -- I see no constants that would change from query execution to query execution in there...

Won't use any indexes on thisid -- hope that is not a problem.

Hmm. What about this?

DRE, May 07, 2002 - 1:46 pm UTC

Hmm, my query plan says its using the index, not a full table scan. . . ?

Tom Kyte
May 07, 2002 - 2:11 pm UTC

It is using a FAST FULL INDEX scan (using the table as an index).

So, it is full scanning a "skinny" version of your table. It'll not be able to use the index in a "conventional" sense (range scan for example).

Add another column:

SELECT thisid, some_other_column
FROM thistable
WHERE INSTR(stringOnumbers,','|| thisid ||',') != 0;

and see what happens.

More hemming and hawing!

DRE, May 07, 2002 - 3:59 pm UTC

Hi Tom, I didnt really know there was a difference but I'm still a bit not jiving. Heres the implemented query:

SELECT
adl_dbt_id,
adl_acc_id
FROM
adl,
acc
WHERE
adl_acc_id = acc_id AND
adl_dispute_credit = 0 AND
INSTR(','|| '164,163,162,161' ||',' , ','|| acc_frw_id ||',') != 0 AND
acc_caod_flag = 1 AND
INSTR(','||'SK,G'||',' , ','|| acc_que_system_q ||',') != 0 AND
acc_que_frr_id = 30

and heres what pops out of query plan:

SELECT STATEMENT CHOOSE 5

NESTED LOOPS 5

TABLE ACCESS (BY INDEX ROWID) of ACC_ACCOUNT ANALYZED 3
INDEX (RANGE SCAN) of ACC_QUE_FRR_ID_IDX ANALYZED 1

TABLE ACCESS (BY INDEX ROWID) of ADL_ACCDBT_LINK ANALYZED 2
INDEX (RANGE SCAN) of ADL_ACC_ID_IDX ANALYZED 1

Now I'm pulling various fields out of the select and the plan still says range scan and by index rowid which as I understand means its using the index properly. What am I missing? Err. thats too broad. Perhaps you can point me somewhere where I can get a better understanding. DRE


Tom Kyte
May 07, 2002 - 7:42 pm UTC

I'll make the resonable assumption that

ACC_QUE_FRR_ID_IDX

is on the acc_que_frr_id column and that

ADL_ACC_ID_IDX

is on the adl_acc_id column.


Then, what is happening is:

the predicate "acc_que_frr_id = 30" is being used to find the rows in the ACC table. this uses the index ACC_QUE_FRR_ID_IDX. Then, the TABLE ACCESS of ACC happens and this predicate:

INSTR(','|| '164,163,162,161' ||',' , ','|| acc_frw_id ||',') != 0
AND acc_caod_flag = 1 AND
INSTR(','||'SK,G'||',' , ','|| acc_que_system_q ||',') != 0

is evaluated. this does not (and the instrs in fact CANNOT) use an index. Then the index on ADL(ADL_ACC_ID) is used to find the joined to row in ADL.

So, any indexes on ACC_FRW_ID and ACC_QUE_SYSTEM_Q cannot be used by your predicate. In fact, the query you gave me:

SELECT thisid
FROM thistable
WHERE INSTR(stringOnumbers,','|| thisid ||',') != 0;


would not use a range scan (if you run it) it would FAST FULL SCAN.

So, I stand by my answer -- your example doesn't show an index on "thisid" being used when the predicate is instr(stringOnumber,','||thisid||',') is used.






missing something

George, July 12, 2002 - 1:51 pm UTC

Tom,

It seems like your "trick" would accomplish what I need
to... but I can't seem to make it work (THE removed since
I'm working with 9i)

SQL> desc string_list_t
 string_list_t TABLE OF VARCHAR2(32000)

SQL> l
  1  declare
  2     v_value_list  string_list_t;
  3     v_rtn         number;
  4  begin
  5     spi_utils.add_string( v_value_list, 'X' );
  6     spi_utils.add_string( v_value_list, 'Y' );
  7     spi_utils.add_string( v_value_list, 'Z' );
  8     v_rtn := 0;
  9     select 1
 10     into   v_rtn
 11     from   dual
 12     where  dummy in ( select cast( v_value_list as string_list_t ) from dual );
 13     dbms_output.put_line( 'expect success, returned ' || to_char( v_rtn ));
 14* end;
SQL> /
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes
ORA-06512: at line 9

Honestly, I don't get why the CAST function is included
since v_value_list is already of type string_list_t, but
clearly I don't understand something that is necessary.
Can you help... please?

 

Tom Kyte
July 12, 2002 - 5:41 pm UTC

ops$tkyte@ORA9I.WORLD> create or replace type string_list_t as TABLE OF VARCHAR2(4000)
  2  /

Type created.

ops$tkyte@ORA9I.WORLD> declare
  2     v_value_list  string_list_t := string_list_t( 'X','Y','Z' );
  3     v_rtn         number;
  4  begin
  5     v_rtn := 0;
  6     select 1
  7     into   v_rtn
  8     from   dual
  9     where  dummy in (select * from TABLE ( cast( v_value_list  as string_list_t ) ) );
 10     dbms_output.put_line( 'expect success, returned ' || to_char( v_rtn ));
 11  end;
 12  /
expect success, returned 1

PL/SQL procedure successfully completed 

in list

Munz, July 13, 2002 - 7:16 pm UTC

TOm:

AS a followup to your answer:
---------------------
If you can have AT MOST three items, by all means code:

select * from t where state in ( p_state(1), p_state(2), p_state(3) );

If you know the (reasonable) upper bound on the number of elements -- go for it,
just code the explicit binds.
----------

Do you mean defining p_state(1) as IN parameter into cursor and defining the value in the PL/SQL program

create or replace proc1 as

p_state varchar2 types.array;

cursor find_state(p_state(1),p_state(2),p_state(3)) is
select * from table where state in (p_state(1),p_state(2),p_state(3));

Is this correct?

Thanks


Tom Kyte
July 13, 2002 - 7:23 pm UTC

I would just code

for x in ( select * from t where state in ( p_state(1), p_state(2), p_state(3) ) )
loop
...


(hate explicit cursors)

Your syntax is wrong, the "concept" is ok but the syntax is wrong.

it would be:


cursor find_state(a in varchar2, b in varchar2, c in varchar2 ) is
select * from table where state in (a,b,c);



performance issue?

George, July 17, 2002 - 2:00 pm UTC

Tom,

Thanks for the help... solution worked exactly as purported-
but now we are seeing terrible performance.  The problem,
as best as I can tell based on the explain plan and a number
of timing tests, is that a hard-coded IN clause allows this select to happen against the primary key index, but the TABLE(CAST()) is performing more like a full scan.  Is there anything I can do?

Thanks in advance.


declare
   xyz xyz_t := xyz_t( '000A00A600000020','000A00A600000021','000A00A600000022',
       '000A00A600000023','000A00A600000024','000A00A600000025',
       '000A00A600000026','000A00A600000027','000A00A600000028',
       '000A00A600000029',
       '000A00A600000030','000A00A600000031','000A00A600000032',
       '000A00A600000033','000A00A600000034','000A00A600000035',
       '000A00A600000036','000A00A600000037','000A00A600000038',
       '000A00A600000039',
       '000A00A600000040','000A00A600000041','000A00A600000032',
       '000A00A600000043','000A00A600000044','000A00A600000045',
       '000A00A600000046','000A00A600000047','000A00A600000048',
       '000A00A600000049',
       '000A00A600000050','000A00A600000051','000A00A600000052',
       '000A00A600000053','000A00A600000054','000A00A600000055',
       '000A00A600000056','000A00A600000057','000A00A600000058',
       '000A00A600000059',
       '000A00A600000060','000A00A600000061','000A00A600000062',
       '000A00A600000063','000A00A600000064','000A00A600000065',
       '000A00A600000066','000A00A600000067','000A00A600000068',
       '000A00A600000069',
       '000A00A600000070','000A00A600000071','000A00A600000072',
       '000A00A600000073','000A00A600000074','000A00A600000075',
       '000A00A600000076','000A00A600000077','000A00A600000078',
       '000A00A600000079');
    p_outoids xyz_t := xyz_t();
    p_names xyz_t := xyz_t();
    x number;
    abc number;
    lmn number;
begin
   abc := dbms_utility.get_time;
         select obj_id
            ,obj_nm
        BULK COLLECT
        into p_outoids
            ,p_names
        from calsdba.OBJECT
       where obj_id IN ( '000A00A600000020','000A00A600000021','000A00A600000022',
       '000A00A600000023','000A00A600000024','000A00A600000025',
       '000A00A600000026','000A00A600000027','000A00A600000028',
       '000A00A600000029',
       '000A00A600000030','000A00A600000031','000A00A600000032',
       '000A00A600000033','000A00A600000034','000A00A600000035',
       '000A00A600000036','000A00A600000037','000A00A600000038',
       '000A00A600000039',
       '000A00A600000040','000A00A600000041','000A00A600000032',
       '000A00A600000043','000A00A600000044','000A00A600000045',
       '000A00A600000046','000A00A600000047','000A00A600000048',
       '000A00A600000049',
       '000A00A600000050','000A00A600000051','000A00A600000052',
       '000A00A600000053','000A00A600000054','000A00A600000055',
       '000A00A600000056','000A00A600000057','000A00A600000058',
       '000A00A600000059',
       '000A00A600000060','000A00A600000061','000A00A600000062',
       '000A00A600000063','000A00A600000064','000A00A600000065',
       '000A00A600000066','000A00A600000067','000A00A600000068',
       '000A00A600000069',
       '000A00A600000070','000A00A600000071','000A00A600000072',
       '000A00A600000073','000A00A600000074','000A00A600000075',
       '000A00A600000076','000A00A600000077','000A00A600000078',
       '000A00A600000079');
   lmn := dbms_utility.get_time;
   dbms_output.put_line( 'the hard coded in time was: ' || to_char( lmn - abc ));
   abc := dbms_utility.get_time;
         select obj_id
            ,obj_nm
        BULK COLLECT
        into p_outoids
            ,p_names
        from calsdba.OBJECT
       where obj_id IN (
             (SELECT * FROM TABLE(CAST(xyz as xyz_t))));
   lmn := dbms_utility.get_time;
   dbms_output.put_line( 'the where in time was: ' || to_char( lmn - abc ));
end;
/

SQL> /
the hard coded in time was: 2
the where in time was: 1596

PL/SQL procedure successfully completed.
 

Tom Kyte
July 17, 2002 - 2:17 pm UTC

option 2:

build the string as ... where x in ( 1, 2, 3, 4, 5, 6, 7 ) ....

but enable cursor_sharing = force before you parse it , cursor_sharing = exact after you parse it. At least that way you will minimize the number of statements in the shared pool to be minimized.

global temp table?

George, July 17, 2002 - 4:21 pm UTC

Tom,

Option 2 seems like a lot of work for not a lot of bang.
And, it limits the number of values in our list.

Instead, I tried defining a global temp table, inserting
my values into that, and selecting from the gtt in the
IN clause. Its faster because it does a hash join now,
but it still doesn't use the primary key index on "OBJECT".
Is this a "feature", or is there something I can do to
take advantage of our index?

create global temporary table inlist( str_val varchar(20) );

xyz str_list_t := str_list_t(''000A00A600000076','000A00A600000077','000A00A600000078');

insert into inlist( str_val )
SELECT * FROM TABLE(CAST(xyz as str_list_t));

select obj_id
,obj_nm
BULK COLLECT
into p_outoids
,p_names
from calsdba.OBJECT
where obj_id IN ( select str_val from inlist );

Thanks

Tom Kyte
July 18, 2002 - 5:36 am UTC

NOT A LOT OF BANG????

go ahead and use literals -- see what kind of "bang" you get from that (a bang like "bang, you just shot yourself in the foot")

Tell me -- how hard is:

alter session set cursor_sharing=force;
parse
alter session set cursor_sharing=exact;

??????

Getting some error on compilation

Yogesh, July 17, 2002 - 5:14 pm UTC

Hi Tom,

I tried this approach in my stored procedure to which I am passing a string. I can compile the procedure stand-alone, but when in a package, it gives me an error "PLS-00382: expression is of wrong type". Do u know what could be going wrong.

Thank you,


Tom Kyte
July 18, 2002 - 8:20 am UTC

works for me.  provide an example (you know, like i always do)

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package demo_pkg
  2  as
  3          function str2tbl( p_str in varchar2 ) return myTableType;
  4  end;
  5  /

Package created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package body demo_pkg
  2  as
  3  
  4  function str2tbl( p_str in varchar2 ) return myTableType
  5  is
  6      l_str   long default p_str || ',';
  7      l_n        number;
  8      l_data    myTableType := myTabletype();
  9  begin
 10      loop
 11          l_n := instr( l_str, ',' );
 12          exit when (nvl(l_n,0) = 0);
 13          l_data.extend;
 14          l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 15          l_str := substr( l_str, l_n+1 );
 16      end loop;
 17      return l_data;
 18  end;
 19  
 20  end;
 21  /

Package body created. 

Applying this solution to a cursor

Mark Mclauchlan, July 18, 2002 - 1:48 pm UTC

Tom,

I tried to implement this as a cursor. It works fine in normal SQL but when it's inside a package it compiles with the following error on the casting of the select "PLS-00513: PL/SQL function called from SQL must return value of legal SQL type". I am running this on an 8i database

CREATE OR REPLACE PACKAGE panel_period_pkg AS

TYPE ppidTableType IS TABLE OF NUMBER;

FUNCTION alloc_to_dp(p_str IN VARCHAR2, p_display_period_id IN NUMBER, p_user_id IN VARCHAR2) RETURN VARCHAR2;

FUNCTION create_ppa_list_from_string(p_str IN VARCHAR2) RETURN ppidTableType;

END panel_period_pkg;
/

CREATE OR REPLACE PACKAGE BODY panel_period_pkg AS



FUNCTION alloc_to_dp(p_str IN VARCHAR2, p_display_period_id IN NUMBER, p_user_id IN VARCHAR2) RETURN VARCHAR2 IS

l_err_msg varchar2(30);

CURSOR c_available_pp (p_str IN VARCHAR2)
IS SELECT * FROM display_periods
WHERE display_period_id IN ( SELECT *
FROM THE ( SELECT cast( create_ppa_list_from_string( p_str) AS ppidTableType )
FROM dual) ); --This line raises an error

BEGIN



RETURN l_err_msg;

END alloc_to_dp;



/* This function takes the panel period id's passed in as a string as returns them */
/* as a pl/sql tavle to be used in the main select of panel period availabilty*/
FUNCTION create_ppa_list_from_string( p_str in varchar2 ) RETURN ppidTableType IS

l_str long default p_str || ',';
l_n number;
l_data ppidTableType := ppidTableType();

BEGIN

LOOP
l_n := instr( l_str, ',' );
EXIT WHEN (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
END LOOP;
RETURN l_data;

END create_ppa_list_from_string;


END panel_period_pkg;
/

Any suggestions?





Tom Kyte
July 18, 2002 - 2:16 pm UTC

The type must be a SQL type -- not a plsql type.

move the type outside of the package -- it is a rule, it cannot be any other way. The type must be known to SQL -- not just plsql.

usefulness of cursor_sharing=force

George, July 23, 2002 - 5:46 pm UTC

Tom,

I am sorry if I offended you by doubting your suggestion.  And after some review of this feature, the work is much less than I assumed.. and I am seeing performance in line
with my expectations (derived from running the statement
ad-hoc through SQLPlus.)

Still, I am quite convinced that I am benefitting from an
execution path that utilizes the the index where neither
the plsql table nor the temp table do.  Can you think of
any reason those solutions wouldn't use the index?

SQL> desc calsdba.object
 Name                           Null?    Type
 --------------------- -------- ---------------------
 OBJ_ID                NOT NULL VARCHAR2(16)
 OBJ_NM                         VARCHAR2(250)
....

  1  select index_name, column_name from user_ind_columns
  2* where index_name = 'OBJECT_PK'
SQL> /

INDEX_NAME                     COLUMN_NAME
------------------------------ --------------------
OBJECT_PK                      OBJ_ID


And I am still in a small bind, since a comma separated
element list is limited to 1000 elements.  And I haven't
seen how to set CURSOR_SHARING from within a procedure,
which is the only option I'd have (I tested from a sqlplus
prompt.)  Is this code what you'd have expected?


create or replace procedure do_select( where_clz in varchar2 )
as
   selstmt   varchar2(9999) :=
      'select obj_id, obj_nm from calsdba.OBJECT ' || where_clz;

    type rc is ref cursor;
    selrc rc;
    p_outoids string_list_t := string_list_t();
    p_names string_list_t := string_list_t();
    x number;
    abc number;
    lmn number;

begin

   abc := dbms_utility.get_time;

   open selrc for selstmt;

   fetch selrc BULK COLLECT into p_outoids, p_names;

   close selrc;

   lmn := dbms_utility.get_time;

   dbms_output.put_line( 'the fetch time was: ' || to_char( lmn - abc ));
end;
/

alter session set cursor_sharing=force

declare
   where_clz varchar2(20000) :=
      'where obj_id IN ( ' ||
      '''000A00A600000020'',''000A00A600000021'',''000A00A600000022'', ' ||
      '''000A00A600000023'',''000A00A600000024'',''000A00A600000025'', ' ||
      '''000A00A600000026'',''000A00A600000027'',''000A00A600000028'', ' ||
      '''000A00A600000029'' )';
begin
   do_select( where_clz );
end;
/

SQL> /
000A00A600000020
the fetch time was: 0

PL/SQL procedure successfully completed.


I wonder if you were suggesting something different because that sequence of events ran just as quickly whether
I included the cursor_sharing statement or not...?

In your book you say: "A well-developed application would never need [CURSOR_SHARING]"... if you still believe that
then you must believe my application is not well-developed
to suggest it.. but the app isn't developed yet!  Please
help me to develope it correctly.

Thanks, George 

Tom Kyte
July 23, 2002 - 9:19 pm UTC

calsdba, thats not cals like in computer aided logistics support system is it?


You didn't offend me -- I was just pointing out that if you avoid bind variables -- you will hate yourself later.

in ( 1, 2, 3 )

is very different from

in ( select .... )

in the first case, the optimizer knows "hey, the set is 3 items -- 3 index probes will do it -- i can put that in the plan and go". In the second case the optimizer says "hey, unknown number of items -- cannot just set 3 index probes in the plan and go -- gotta do nested loops or something else"

Just different plans.


execute immediate 'alter session set cursor_sharing=force';


and if you have more then 1,000 elements -- you don't want to do 1,000 index probes. At some point between 0 and 1000 the other plan will be better for you.




no more questions... at least on this subject :-)

George, July 24, 2002 - 2:20 pm UTC

CALS is the project you suspect - I joined this team about 3 months ago to help write some generic/dynamic procedures to facilitate replacing some custom code. I have already pointed out that it will be very hard (perhaps impossible) to tune these new functions properly since they're expected to perform in a numer of to be determined situations (i.e. 1000 element selection lists.) Still, the codebase is large so we'll have to handle performance one (slow)function at a time.

back to the question.. with your (thorough) explanation
in hand, I am ready to capitulate. I did try to force using the index by hinting the query with some success, but given our environment I tend to believe the database will do a better job than (my current thoughts,) so I dropped that idea. Instead I'll just add this to the growing evidence that I can't fix everything, and provide options instead of a firm reckommendation.

Thanks again, this discussion has been very helpful :-)

Tom Kyte
July 24, 2002 - 10:57 pm UTC

Hey, I worked on the CALS project 1989-1993 ;)

When I was working on it -- it was DEC Ultix MLS+ (b1 multi-level secure OS) and Trusted Oracle 7.0.9 with Xterms. What a difference a decade makes....

same problem as above

A reader, October 10, 2002 - 5:09 pm UTC

Why is that while you use in list as (select cast(...as)) etc it is slower than you directly hardcode the values in the in list?

Bind variables should make the execution faster isnt it?

I have a similar query as Geroge's,
Inside a package/procedure i have:
arr_list tbl_list := tbl_list(obj_list('','',''));
/*obj_list is an object in the database, and tbl_list is table of obj_list. [ obj_list ->dep_no varcjar2(10), da varchar2(20)]
'arr_list' populated with pairs of values(dep_no,da) by a loop .say a max of...1 to 100 with values that are coming inside teh proc as parameters*/
/* then i have an out parameter as a refcursor:(method1)*/
open c_refcur for
select ....
where.....and
where.....and
where (something1,something2) in (select CC.dep_no, CC.da FROM TABLE( CAST(arr_list AS tbl_list)) AS CC) ;


/*end of proc*/

If i pass say just 2 dep_nos and 2 das (i.e i loop twice) to populate the arr_list) , teh proc takes about 8 seconds to execute.

Now if i change that refcursor and make it dynamic:(method2)
v_inlist := /* collect pairs of inputs from proc and generate string as '( (depno1,da1),(dep_no2,da2),(dep_no3,da3) )' etc/*
open c_refcur for
'select ....'||
'where.....and '||
'where.....and '||
'where (something1,something2) IN ''||
v_inlist ;

This way it takes less than a second.

Now repeated execution of method1 will make use of bind variables (when passed with diff parameters). STill the timetaken is 7 or 8 seconds everytime.

The method 2 while executed with diff values takes less than a second all teh times...

Now i tried passing many diff values as parameters starting from 1 to 20 ...works the same way.

So does this mean that one needs to weigh the options while using bind variables? WHy is it slower .

Also, when we do 'select CC.dep_no, CC.da FROM TABLE( CAST(arr_list AS tbl_list)) AS CC' , is this treated as a normal table inside the proc? Meaning, if it does then can we make some dynamic indexes etc . will that ake it faster?







Tom Kyte
October 11, 2002 - 7:49 pm UTC

think about it:

where x in ( 1, 2, 3, 4, 5 )

where x in ( select z from a )



Just think about the differences between those two predicates....... Nothing to do with bind variables -- just think about the potential differences between those two predicates (how many rows will come back from A -- 1? 10? 1000? 10000?)

Casting in Dynamic SQL

A reader, November 22, 2002 - 6:44 pm UTC

Will this casting work with dynamic SQL?

When I tried it with static SQL, it works fine. With dynamic SQL, it gives a strange error. See below.

SQL> DECLARE
  2     l_tbl x_table_type := x_table_type(1, 2, 3);
  3  
  4  BEGIN
  5     FOR x IN (SELECT column_value FROM TABLE(CAST (l_tbl AS x_table_type))) LOOP
  6        dbms_output.put_line(x.column_value);
  7     END LOOP;
  8  END;
  9  /
1
2
3

PL/SQL procedure successfully completed.


SQL> DECLARE
  2     type cur_type IS REF CURSOR;
  3     x cur_type;
  4     TYPE l_record_type IS RECORD (col1 NUMBER);
  5     l_record l_record_type;
  6  
  7     l_tbl x_table_type := x_table_type(1, 2, 3);
  8  
  9     l_sql VARCHAR2(4000) := 
 10        'SELECT column_value FROM TABLE(CAST (l_tbl AS x_table_type))';
 11  
 12  BEGIN
 13     OPEN x FOR l_sql;
 14  
 15     LOOP
 16     FETCH x into l_record;
 17     EXIT WHEN x%NOTFOUND;
 18  
 19     dbms_output.put_line(l_record.col1);
 20     END LOOP;
 21  
 22     CLOSE x;
 23  END;
 24  /
DECLARE
*
ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at line 13
 

Tom Kyte
November 22, 2002 - 7:26 pm UTC

that is not strange -- type it in SQLPLUS and you'll get the same exact thing.

is l_tbl a column? no- you aren't in Plsql anymore when you drop down to dynamic sql - its all sql. hence l_tbl just doesnt exists.

9 l_sql VARCHAR2(4000) :=
10 'SELECT column_value FROM TABLE(CAST (:l_tbl AS x_table_type))';
11
12 BEGIN
13 OPEN x FOR l_sql using l_tbl;

see what that does for you.

How parameters in IN list behave

Dan, February 05, 2003 - 9:37 am UTC

Tom,

I noticed that when values provided to IN list are sorted in ascending for example

Select * from loans where loan_id IN ( 5, 6, 3);

Resulted in
3, 5, 6

Why is this??

Dan

Do you need Oracle Object installed?

Anil, March 27, 2003 - 6:11 am UTC

I tried getting the example to work by putting the type (table of number) in a package and having the function str2tbl reference the packaged type but I'm getting a type mismatch error when I attempt the cast.

Does the type need to be declared as in the example:
"create or replace type myTableType as table of number;"?

I tried that, but apparently we don't have the "Objects" functionality installed in Oracle.

Please bare with me, I somewhat new to all of this!

Cheers.

Tom Kyte
March 27, 2003 - 8:18 am UTC

you must have a really old (like 8.0) version of Oracle.

Yes, you NEED the objects option (not an option in 8i) installed.

Select statement

lakshmi, May 05, 2003 - 10:25 am UTC

Excellent !!!!

What if p_str of 4000 bytes is not enough?

Alex Daher, May 12, 2003 - 3:13 pm UTC

Tom,

Suppose the following type and function:

create or replace type myTableType as table of number;
/
create or replace function str2tbl( p_str in varchar2 ) return myTableType
as
l_str long default p_str || ',';
l_n number;
l_data myTableType := myTabletype();
begin
loop
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;
/

So, we can query like:
cd_aldaher_621@ORACLE8> select * from Table(select cast( str2tbl( '947, 234' ) as mytableType ) from dual);

COLUMN_VALUE
------------
947
234

OK, but what if varchar2(4000) is not enough? If the number of elements of the list is so high that the 'p_str' in 'str2tbl' can be veeeery big and 4000 bytes is not big enough? What approach should I use?

Any idea?

Thank you for the great site!
Alex Daher

Tom Kyte
May 12, 2003 - 3:30 pm UTC

populate a global temporary table via a bulk insert and use it.

Awesome......

Bala Nemani, June 03, 2003 - 11:22 am UTC

Very...Very Helpful...

Won't compile on str2tbl...

A reader, August 21, 2003 - 5:24 am UTC

Hi Tom,

I've used your example to create...

    open l_network_reqs_to_process for
    select nom.nom_nominal_id, nre.nre_nr_id, nre.nre_name
    from omx_mig_nominals nom, omx_mig_network_requirements nre
    where nom.nom_nominal_id  = nre.nre_nominal_id
    and   nom.nom_hier_status = omx_mig_logging_01.GC_OK_STATUS
    and   nom.nom_hier_mig_phase = omx_mig_logging_01.GC_CALC_WRK_PLANS
    and   nre.nre_nr_id in ( select *
                             from THE ( select cast( str2tbl(i_nr_list) as omx_t_num_tab ) 
                                        from dual 
                                      )
                           );


Where omx_t_num_tab is...

SQL> desc omx_t_num_tab
 omx_t_num_tab TABLE OF NUMBER

the str2tbl function is defined in the same package.

When I attempt to compile the package I get a "PLS-00231: function 'STR2TBL' may not be used in SQL" on the "open for" statement.

I don't understand why I am not allowed to use it in my scenario, but you are allowed to use it in yours?

Have I done something very stupid?

Thanks,

Mike.
 

Tom Kyte
August 21, 2003 - 6:08 pm UTC

version?

I was being stupid!

Mike, August 21, 2003 - 9:27 am UTC

Ignore the last "A Reader" I was indeed being stupid, and not reading your posts properly, with the str2tbl function being a Stored function rather than being in the package it works. Which is obvious.



Vipin, September 04, 2003 - 8:17 pm UTC

Hi Tom,

Creating a type of table and accessing the comma delimited parameter in IN 
clause is the best way of handling these scenarios. But I did the following 
testcase and realized that the approach always go for a full table scan if the 
statistics are available and time taken is also pretty high.

Please see the test case below:-

SQL>  Create table t
  2   (id number primary key,
  3   name varchar2(100))
  4  
SQL> /

Table created.

SQL> 
SQL>  
SQL>  INSERT INTO t
  2   SELECT Id, NAME
  3   FROM   big_table
  4  
SQL> /

516430 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> select count(*)
  2  from   t
  3  
SQL> /

  COUNT(*)
----------
    516430

SQL> 
SQL> set autotrace on
SQL> set timing on
SQL> 
SQL> 
SQL> CREATE OR REPLACE TYPE my_table IS TABLE OF NUMBER;
  2  /

Type created.

Elapsed: 00:00:00.07
SQL> SELECT * FROM t
  2  WHERE id IN (SELECT * FROM table(CAST(my_table(34,78) AS my_table)))
  3  /

        ID
----------
NAME
--------------------------------------------------------------------------------
        34
/11799933_SchemaProtectionDoma

        78
/12f385b1_DefaultCellEditor3


Elapsed: 00:00:00.06

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     VIEW OF 'VW_NSO_1'
   3    2       SORT (UNIQUE)
   4    3         COLLECTION ITERATOR (CONSTRUCTOR FETCH)
   5    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   6    5       INDEX (UNIQUE SCAN) OF 'SYS_C008705' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         21  consistent gets
          3  physical reads
         68  redo size
        520  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed



Please observe that the time taken is 0.06 seconds and INDEX UNIQUE SCAN is 
used.




SQL> analyze table t compute statistics for table for all indexes for all 
indexed columns;

Table analyzed.

Elapsed: 00:00:13.04
SQL> SELECT * FROM t
  2  WHERE id IN (SELECT * FROM table(CAST(my_table(34,78) AS my_table)))
  3  /

        ID
----------
NAME
--------------------------------------------------------------------------------
        34
/11799933_SchemaProtectionDoma

        78
/12f385b1_DefaultCellEditor3


Elapsed: 00:00:01.09

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5680951 Card=1 Bytes
          =30)

   1    0   NESTED LOOPS (SEMI) (Cost=5680951 Card=1 Bytes=30)
   2    1     TABLE ACCESS (FULL) OF 'T' (Cost=221 Card=516430 Bytes=1
          5492900)

   3    1     COLLECTION ITERATOR (CONSTRUCTOR FETCH)




Statistics
----------------------------------------------------------
         22  recursive calls
          0  db block gets
       2289  consistent gets
        814  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed



Please observe that the time taken is 1.09 seconds and TABLE FULL SCAN is used.



SQL> 
SQL> 
SQL> 
SQL> analyze table t delete statistics;

Table analyzed.

Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> SELECT * FROM t
  2  WHERE id IN (SELECT * FROM table(CAST(my_table(34,78) AS my_table)))
  3  /

        ID
----------
NAME
--------------------------------------------------------------------------------
        34
/11799933_SchemaProtectionDoma

        78
/12f385b1_DefaultCellEditor3


Elapsed: 00:00:00.05

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     VIEW OF 'VW_NSO_1'
   3    2       SORT (UNIQUE)
   4    3         COLLECTION ITERATOR (CONSTRUCTOR FETCH)
   5    1     TABLE ACCESS (BY INDEX ROWID) OF 'T'
   6    5       INDEX (UNIQUE SCAN) OF 'SYS_C008705' (UNIQUE)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          1  physical reads
          0  redo size
        520  bytes sent via SQL*Net to client
        499  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          2  rows processed

SQL> 


Please observe that the time taken is 0.05 seconds and INDEX UNIQUE SCAN is 
used.

Could you please explain why table full scan is used when statistics are 
available and more over doesn't this limit us from using this technique.

 

 

Tom Kyte
September 05, 2003 - 3:38 pm UTC

why do people put the same thing in many places??? squeaky wheels -- grease?

look where you asked this elsewhere for my followup.

Vipin, September 05, 2003 - 4:48 pm UTC

Hi Tom,

Sorry for putting this in more than one place , but anyway thanks for the followup.

Working on the same trick

Pramitayan chinya, September 11, 2003 - 3:22 pm UTC

Refurring to the review:
cursors with in sql February 24, 2002
Reviewer: Munz from reston, va

I want to pass two lists of numbers to the procedure and get a result set out of it.
The lists are dynamically varying in values as well as count.
The trick discussed here does not seem to work on a cursor declared in a stored procedure. i get the error


PL/SQL: SQL Statement ignored
PLS-00320: the declaration of the type of this expression is
incomplete or malformed

PLS-00513: PL/SQL function called from SQL must return value of
legal SQL type

PL/SQL: SQL Statement ignored


My understanding is that this is because SQL not supporting the PL/SQL function. what would be your recommendation on work around?Am i wrong anywhere?
This is the package..

CREATE OR REPLACE PACKAGE BODY WOS_PROC_PKG
AS
FUNCTION STR2NUM( P_STR IN VARCHAR2 ) RETURN
VALUE
AS
L_STR LONG default p_str || ',';
l_n number;
l_data VALUE := VALUE();
begin
loop
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;
PROCEDURE WOS_PROC_GROUP(LOCATIONID_WHERE_CLAUSE IN VARCHAR2,
MERCHANDISEID_WHERE_CLAUSE IN VARCHAR2,RESULT_SET OUT RESULT_SET_TYPE)
AS
CURSOR OH_VAR_CUR(DATE_VAR IN DATE)
IS
SELECT NVL(ON_HAND_QTY,0),MERCHANDISE_ID,LOCATION_ID FROM OAK.DAILY_INV_TBL
WHERE TO_CHAR(LOCATION_ID) IN ( SELECT * FROM THE(select CAST(str2NUM( 'LOCATIONID_WHERE_CLAUSE' ) as VALUE) from dual))
AND TO_CHAR(MERCHANDISE_ID) IN (SELECT * FROM THE( select CAST(str2NUM( 'MERCHANDISEID_WHERE_CLAUSE' ) as VALUE) from dual))
AND CALENDAR_DT > DATE_VAR
ORDER BY MERCHANDISE_ID,LOCATION_ID;
CURSOR WOS_CUR(DATE_VAR IN DATE,LOCATIONID_VAR IN NUMBER,MERCHANDISEID_VAR IN NUMBER)
IS
SELECT WEEK_END_DT, NVL(ACTUAL_UNITS,0)+NVL(PLANNED_UNITS,0) WEEKLY_UNITS,
LOCATION_ID,MERCHANDISE_ID FROM
FORECAST_MERCH_LOCATIONS_TBL
WHERE
WEEK_END_DT > DATE_VAR
AND LOCATION_ID = LOCATIONID_VAR
AND MERCHANDISE_ID =MERCHANDISEID_VAR;
OH_VAR OAK.DAILY_INV_TBL.ON_HAND_QTY%TYPE;
OH_NEW_VAR OAK.DAILY_INV_TBL.ON_HAND_QTY%TYPE;
COLOR_VAR FORECAST_BOC_EOC_UNITS_TBL.COLOR_ID%TYPE;
STORE_VAR FORECAST_MERCH_LOCATIONS_TBL.LOCATION_ID%TYPE;
WEEK_OF_SALES_VAR FORECAST_MERCH_LOCATIONS_TBL.WEEK_END_DT%TYPE;
SALES_VAR NUMBER;
WOS_SUM NUMBER := 0;
WEEK_COUNT NUMBER;
DECIMAL NUMBER;
MERCHANDISEID_VAR NUMBER;
LOCATIONID_VAR NUMBER;
OH_HIGH_FLAG VARCHAR2(10);
BEGIN
WEEK_COUNT := 0;
OPEN OH_VAR_CUR(SYSDATE);
LOOP
FETCH OH_VAR_CUR INTO OH_VAR,MERCHANDISEID_VAR,LOCATIONID_VAR;
IF
OH_VAR > 0
THEN
OPEN WOS_CUR(SYSDATE,LOCATIONID_VAR,MERCHANDISEID_VAR);
LOOP
FETCH WOS_CUR INTO WEEK_OF_SALES_VAR,SALES_VAR,STORE_VAR,COLOR_VAR;
WOS_SUM:= WOS_SUM + SALES_VAR;
WEEK_COUNT:=WEEK_COUNT + 1;
EXIT WHEN WOS_SUM > OH_VAR OR WOS_CUR%NOTFOUND;
END LOOP;
WEEK_COUNT:= WEEK_COUNT-1;
WOS_SUM:=WOS_SUM-SALES_VAR;
IF
WEEK_COUNT > 52 OR WOS_CUR%NOTFOUND
THEN
EXECUTE IMMEDIATE 'INSERT INTO WOS_PROC_TEMP
VALUES(NULL,NULL,NULL,TRUE,0)';
ELSIF
OH_VAR > WOS_SUM
THEN
BEGIN
OH_NEW_VAR:= OH_VAR-WOS_SUM;
DECIMAL := OH_NEW_VAR/SALES_VAR;
WEEK_COUNT := WEEK_COUNT + DECIMAL;
END;
END IF;
EXECUTE IMMEDIATE 'INSERT INTO WOS_PROC_TEMP
VALUES(STORE_VAR,COLOR_VAR,WEEK_COUNT,OH_HIGH_FLAG,OH_VAR)';
ELSE
EXECUTE IMMEDIATE 'INSERT INTO WOS_PROC_TEMP
VALUES(NULL,NULL,NULL,TRUE,0)';
END IF;
EXIT WHEN OH_VAR_CUR%NOTFOUND;
END LOOP;
OPEN RESULT_SET FOR SELECT LOCATION_ID,MERCHANDISE_ID,WEEK_COUNT,OH_HIGH_FLAG FROM WOS_PROC_TEMP;
CLOSE WOS_CUR;
CLOSE OH_VAR_CUR;
END;
END;

Thanks a lot for your time,
Pramit


Tom Kyte
September 11, 2003 - 7:44 pm UTC

well, i don't see where or how you defined "value"

Well am i missing anything here???

A reader, September 11, 2003 - 9:00 pm UTC

Hi,
I did declare VALUE in the package SPEC..

CREATE OR REPLACE PACKAGE WOS_PROC_PKG
AS
TYPE RESULT_SET_TYPE IS REF CURSOR;
TYPE VALUE IS TABLE OF NUMBER;
FUNCTION STR2NUM( P_STR IN VARCHAR2 ) RETURN
VALUE;
PROCEDURE
WOS_PROC_GROUP(LOCATIONID_WHERE_CLAUSE IN VARCHAR2,
MERCHANDISEID_WHERE_CLAUSE IN VARCHAR2,RESULT_SET OUT RESULT_SET_TYPE);
END;


It has compiled as expected...
Am i going wrong anywhere inside the package body?

Thanks for your time,
Pramit

Tom Kyte
September 12, 2003 - 9:44 am UTC

you cannot put it there if you want to use it in sql


create type value as table of number
/




Another Solution, thoes this work?

Ricardo Patrocínio, December 30, 2003 - 2:02 pm UTC

Hi Tom,

Before I found this tread I've solved this problem like this:

Declare

TYPE refcursor IS REF CURSOR;

my$condition varchar2(80);
my$cursor refcursor;

Begin

my$condition := '18404,18405,18406';

OPEN my$cursor for
Select
1
From
my$table
where
my$condition like '%' || my$id || '%';

End;

And it worked just fine, but I'd like your opinion on this solution.

NOTE: the my$condition variable is my input parameter, i've just puted it here for clarity purpose.

Thank you
R.P.

Tom Kyte
December 30, 2003 - 2:19 pm UTC

no indexes, full scan. if that is OK with you, it works just dandy.

if the goal is to have a varying in list that can use indexes - that approach is no good.

The size accepted by the LONG variable..

Naveen, January 02, 2004 - 6:12 am UTC

Hi Tom,

Happy New Year.

Kindly help me with this problem. I was using your string tokenizer function to seperate comma delimiters from the strings. Problem is that when i pass data of more than 32,767 characters its throwing numeric or value error even though the variable is of LONG datatype. Why is this happening and is there a way to overcome this.

Thank you
Nav.


Tom Kyte
January 02, 2004 - 9:37 am UTC

32k is the biggest LONG plsql will ever deal with.

no ways around it.

Thanks Tom..but

Naveen, January 02, 2004 - 11:46 am UTC

Hi Tom,

Is the restriction only for local variables or it applies to the input parameters as well. I think i am asking a dumb question. What i wanted to try is, before assiging the input parameter to a local variable i'll break the string and assign. But this can happen only when the input parameter itself can support more than 32k. I tried simulating it by assiging more than 32k. It throws the error at the location where i am assiging the input parameter to a local variable. So i am not able to make out whether the restriction applies to the input parameter also. But logically, i feel restriction applies to the parameter also. Could you please clarify it.

Thank you.
Nav.


Tom Kyte
January 02, 2004 - 1:46 pm UTC

32k is the MAX size of any plsql variable there.

the "input variable" is a plsql variable.

if you have 32k of stuff here, I personally think you need to rethink something. that is HUGE. I'd recommend looking into global temporary tables -- stop playing with a huge string and just stuff the rows into a gtt and use it instead.

The restriction..

Naveen, January 02, 2004 - 10:59 pm UTC

Hi Tom,

The Application server we are using does not support REF Cursors, Varrays etc. If the user accessing the application deletes the records that are being displayed, i will get comma seperated ids of those records, i'll seperate commas and delete the rows. What should i do in these cases. There is no doubt that data keeps increasing and some day 32k limit would be reached. The reason why i haven't used GTT in the first case is, there is restriction in the design that all the DML has to be handeled in the Pl/Sql procedures. If i use GTT in procedures i need to have the string. So based on your advice i'll try to convince my superiors and change the approach.

Thank you
Nav.

Tom Kyte
January 03, 2004 - 9:11 am UTC

what "application server" are you using that doesn't support jdbc/odbc ???


parse it, insert it into a gtt (global temp table) and delete from t where id in ( select * from gtt)

Thanks again..

Naveen, January 04, 2004 - 10:42 pm UTC

Hi Tom,

Thanks for the advice. And the application server we are using is JRun3.1. I aware that JDBC/ODBC drivers support all those thing i mentioned earlier. But JRun has its own restrictions(like, you can get values from Varray, but cannot set values to it) even though it is using JDBC/ODBC drivers. Anyway, i am following your approach(GTT) and it seems the most sensible approach.

Have a nice day.

Thank you
Nav.

Tom Kyte
January 05, 2004 - 7:14 am UTC

you can drop in better jdbc drivers -- that is what java is all about. you can fully utilize your database if you so desire.

Error "invalid column name" with DBMS_SQL.parse

robert, February 04, 2004 - 3:59 pm UTC

Tom, pls help !..I kept hitting error ORA-00904 with the INSERT under DBMS_SQL.PARSE
below:
INSERT INTO
tblctsearchresult (userseq, clientlistrowid, datestamp, userid)
SELECT :l_seq, id, sysdate, user
FROM v_clientlist
WHERE buscodeid IN
( SELECT * FROM THE ( SELECT CAST( common_util.str2numtbl ( :p_cbus ) as numbertabletype ) from dual ) )

ORA-00904: invalid column name

Same sql (binds removed) in sqlplus executes w/o problem:

INSERT INTO tblctsearchresult
(userseq, clientlistrowid, datestamp, userid)
SELECT 5, id, sysdate, user FROM v_clientlist
WHERE buscodeid IN ( SELECT * FROM THE ( SELECT CAST( common_util.str2numtbl('20,30,29')
as numbertabletype ) from dual ) );

825 rows created.

Thanks


Tom Kyte
February 04, 2004 - 6:02 pm UTC

I'll need some help reproducing....
ops$tkyte@ORA817DEV> drop table tblctsearchresult;
 
Table dropped.
 
ops$tkyte@ORA817DEV> create table tblctsearchresult ( userseq number, clientlistrowid number, datestamp date, userid varchar2(30)
  2  );
 
Table created.
 
ops$tkyte@ORA817DEV> drop table v_clientlist;
 
Table dropped.
 
ops$tkyte@ORA817DEV> create table v_clientlist ( id number , buscodeid number );
 
Table created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace type numberTableType as table of number
  2  /
 
Type created.
 
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace function str2numtbl( p_string in varchar2 ) return numberTableType
  2  as
  3          l_data numberTableType := numberTableType( 1,2,3 );
  4  begin
  5          return l_data;
  6  end;
  7  /
 
Function created.
 
ops$tkyte@ORA817DEV> show errors
No errors.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2      l_theCursor     integer default dbms_sql.open_cursor;
  3  begin
  4      dbms_sql.parse(  l_theCursor, '
  5  INSERT INTO
  6  tblctsearchresult (userseq, clientlistrowid, datestamp, userid)
  7  SELECT :l_seq, id, sysdate, user
  8    FROM v_clientlist
  9   WHERE buscodeid IN
 10   ( SELECT * FROM THE ( SELECT CAST( str2numtbl     ( :p_cbus ) as
 11  numbertabletype ) from dual ) )
 12   ',
 13  dbms_sql.native );
 14
 15          dbms_sql.bind_variable( l_theCursor, 'l_seq', 100 );
 16          dbms_sql.bind_variable( l_theCursor, 'p_cbus', 'hello' );
 17
 18          dbms_output.put_line( dbms_sql.execute( l_theCursor ) );
 19
 20  end;
 21  /
0
 
PL/SQL procedure successfully completed.
 


Any reason you are not using execute immediate???
 

re "invalid column error"

robert, February 04, 2004 - 4:50 pm UTC

This is 8.1.7

Tom Kyte
February 04, 2004 - 6:06 pm UTC

see above, cannot reproduce.

re Error "invalid column name" in PARSE

Robert, February 05, 2004 - 11:52 am UTC


Out of the woods now...thanks Tom,
Parsing is done by another schema, I fully-qualified identifiers in my code, granted EXECUTE on the Nested Table Type (didn't know that)...not sure which action fixed it thou.

DBMS_SQL seems more appropriate than EXE. IMMED in my case here (build search sql) because I get better control of binding (ala webdb.wwv_bind technique).

One "glitch" I have now is with SQL%ROWCOUNT after exe the INSERT...I cannot seem to rely on it.
This is WEB app and I am storing rowid in the search result table for pagination...so if first search inserted 300 then 2nd search inserted nothing..the SQL%ROWCOUNT value 300 is still retained...is this a cache thing ? isn't 2nd search a separate session from the first ? (8.1.7/OAS 4.0.8)

Thanks

Tom Kyte
February 05, 2004 - 7:27 pm UTC

sys_context :) I use to bind in native dynamic sql.

sql%rowcount won't work at all with dbms-sql, that'll only work with static sql.


dbms-sql returns the number of rows affected, dbms_sql.execute returns the rows affected.

Inconsistent datatypes

Alan Wagner, February 05, 2004 - 12:44 pm UTC

Tom, I'm finding your information VERY useful, thanks for providing this forum for us novices to utilize.

Here 's the code that is generating an error:
CREATE OR REPLACE TYPE SGYDBO.sgyTpcSel as table of number;

CREATE OR REPLACE FUNCTION SGYDBO.str2tbl( tpcs in varchar2 ) return sgyTpcSel
as
l_str long default tpcs || ',';
l_n number;
l_data sgyTpcSel := sgyTpcSel();
begin
loop
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;

CREATE OR REPLACE PROCEDURE sgyproc.sel_tpc_for_entity
(
tpcs in varchar2,
entId in char/*,
prfls_cursor in out sel_prfls_pkg.prfls*/
)
as


cursor rule_val is
select sgy_tran_dr_cr_mpng.ent_id, sgy_ent.ent_name, sgy_tran_dr_cr_mpng.tran_dr_cr_mpng_id, sgy_tran_dr_cr_mpng.tran_rptg_cde,
sgy_tran_dr_cr_mpng.dr_cr_id, sgy_tran_dr_cr_mpng.dr_cr_type,
sgy_tran_rptg_dr_cr.dr_cr_ord,sgy_dr_cr_cde.dr_cr_name,
sgy_tran_rptg_cde.tran_rptg_desc, sgy_tran_subctg_cde.tran_subctg_name,
sgy_tran_supctg_cde.tran_supctg_name, sgy_tran_dr_cr_mpng_prf.mpng_prf_id, sgy_mpng_prf.mpng_prf_name, sgy_mpng_prf.mpng_prf_desc,
sgy_gl_acc.gl_acc_id, sgy_gl_acc.gl_acc_num, sgy_gl_acc.gl_acc_name, sgy_mpng_prf_hrchy.hrchy_num
from sgy_tran_dr_cr_mpng,
sgy_tran_rptg_dr_cr,
sgy_dr_cr_cde,
sgy_tran_rptg_cde,
sgy_tran_subctg_cde,
sgy_tran_supctg_cde,
sgy_gl_acc,
sgy_tran_dr_cr_mpng_prf,
sgy_mpng_prf,
sgy_mpng_prf_hrchy,
sgy_ent
where sgy_tran_dr_cr_mpng.ent_id = entId
and sgy_tran_rptg_cde.tran_rptg_cde in
(select * from THE (select cast(sgydbo.str2tbl(tpcs) as number) from dual))
and sgy_tran_dr_cr_mpng.ent_id = sgy_ent.ent_id

error: inconsistent datatypes

by the way sgydbo created str2tbl if I remove that from the call I receive invalid column error.
Any help would be much appreciated.
Thanks


Tom Kyte
February 06, 2004 - 8:11 am UTC

can you get the query down to the smallest possible example and include the entire test case (eg: a script anyone of us could run on our machine). remove tables -- remove as much as you can (perhaps then, it'll be obvious what the issue is -- but maybe not)

Make it "really small"

Re:Inconsistent datatypes

Alan, February 06, 2004 - 3:05 pm UTC

Here's the reader's digest version.

The input value will be 999200,2983. The "cde" in the where clause is a varchar2(6).

CREATE OR REPLACE TYPE sgyTpcSel as table of varchar2(255);
/

CREATE OR REPLACE FUNCTION str2tbl( tpcs in varchar2 ) return sgyTpcSel
as
l_str varchar2(255)default tpcs || ',';
l_n number;
l_data sgyTpcSel := sgyTpcSel();
begin
loop
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;
/
_______________
CREATE OR REPLACE PROCEDURE sel_tpc_for_entity2
(
tpcs in varchar2
)
as
w_name varchar2(80);
cursor rule_val is
select desc
from yourTable
where cde in
(select * from (select cast(str2tbl(tpcs) as sgyTpcSel) from dual));


begin

Open rule_val;
Loop
Fetch rule_val
into w_name;
exit when rule_val%NOTFOUND;
end loop;
close rule_val;
end
;
/

Everything compiles fine, the trouble is when I attempt to debug it, I receive an inconsistent datatype in the stored proc at the select statement. At one time I had everything working to point of coming back from the function. However then the l_data was null, in trying to fix that I've seemed to cause the above. So I basically have two issues.

Thanks

Tom Kyte
February 07, 2004 - 2:05 pm UTC

use the syntax on line 11 -- yours above isn't like mine way above -- but that was the "old deprecated 8.0" synatx anyway, this is much easier now in 8i and up:

ops$tkyte@ORA10G> CREATE OR REPLACE PROCEDURE sel_tpc_for_entity2
  2  (
  3   tpcs in varchar2
  4  )
  5  as
  6      w_name varchar2(80);
  7     cursor rule_val is
  8          select descript
  9                    from yourTable
 10                   where cde in
 11                     (select * from TABLE( cast(str2tbl(tpcs) as sgyTpcSel)) );
 12  begin
 13
 14      Open rule_val;
 15        Loop
 16           Fetch rule_val
 17            into  w_name;
 18            exit when rule_val%NOTFOUND;
 19        end loop;
 20        close rule_val;
 21  end ;
 22  /
 
Procedure created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec sel_tpc_for_entity2( '999200,2983' );
 
PL/SQL procedure successfully completed.
 
 

Re:Inconsistent DataTypes

Alan, February 09, 2004 - 10:39 am UTC

The change you suggested worked great! Thanks.
I'm still having a problem with the function though. When debugging the field l_data is unknown and does not get populated with the results. Am I missing something?

CREATE OR REPLACE FUNCTION SGYDBO.str2tbl( tpcs in varchar2 ) return sgyTpcSel
as
l_str varchar2(255) default tpcs || ',';
l_n number;
l_data sgyTpcSel := sgydbo.sgyTpcSel();
begin
loop
l_n := instr( l_str, ',' );
exit when (nvl(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
l_str := substr( l_str, l_n+1 );
end loop;
return l_data;
end;
/


Thanks

Tom Kyte
February 09, 2004 - 10:46 am UTC

please define "l_data is unknown and does not get...".  that doesn't make sense to me.


ops$tkyte@ORA920PC> create or replace type sgyTpcSel as table of number
  2  /
 
Type created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> CREATE OR REPLACE FUNCTION str2tbl( tpcs in varchar2 ) return sgyTpcSel
  2      as
  3          l_str    varchar2(255) default tpcs || ',';
  4          l_n        number;
  5          l_data    sgyTpcSel := sgyTpcSel();
  6      begin
  7          loop
  8              l_n := instr( l_str, ',' );
  9              exit when (nvl(l_n,0) = 0);
 10             l_data.extend;
 11             l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
 12             l_str := substr( l_str, l_n+1 );
 13         end loop;
 14         return l_data;
 15      end;
 16  /
 
Function created.
 
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select *
  2    from TABLE( cast( str2tbl( '1,2,3' ) as sgyTpcSel )  )
  3  /
 
COLUMN_VALUE
------------
           1
           2
           3
 
ops$tkyte@ORA920PC>
 

how to use with bind variables?

Ryan Gaffuri, March 02, 2004 - 9:13 am UTC

Anyway to set this up so we can reuse sql and not parse it every time

Tom Kyte
March 02, 2004 - 3:54 pm UTC

bind the string '1,2,3' -- don't use a literal, bind a string with 1,2,3 in it.

hard parsing and variable inlist

Ryan, March 08, 2004 - 12:03 pm UTC

I ran the code as you stated with a bind variable. I am testing for a hard parse. I simply changed the data values and ran it twice. I used the following script to check for a hard parse: ( i ran it before running the test and immediately after)

select b.name,a.*
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and a.sid = (select distinct sid from v$mystat)
and name like '%parse%'

It showed that hard parse incremented by one when I run the variable inlist and change the value.

however, when I do a 10046 trace and check the 'parse' column of the statistics table I see only .01 CPU usage and nothing else. That infers to me that a hard parse did not occur?

Can you explain the discrepency?

CREATE OR REPLACE PACKAGE BODY GetInList AS
FUNCTION GetNumberList (pString IN VARCHAR2) RETURN NumberTableType
IS
-- l_string long default pString || ',';
l_string VARCHAR2(2000);
l_data NumberTableType := NumberTableType();
n NUMBER;
BEGIN
-- DBMS_OUTPUT.PUT_LINE (pString);
l_string := ltrim (rtrim (pString,''''),'''') || ',';
-- DBMS_OUTPUT.PUT_LINE (l_string);
LOOP
EXIT WHEN l_string IS NULL;
n := INSTR (l_string,',');
l_data.extend;
l_data(l_data.count) :=
LTRIM( RTRIM( SUBSTR( l_string,1,n-1)));
l_string := SUBSTR (l_string,n+1);
END LOOP;
RETURN l_data;
END;
END GetInList;
/

-- test script (we change the value of InTable(1) to test for a hard parse.
declare
TYPE VarcharTable IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
InTable VarcharTable;
InList VARCHAR2(200);
CURSOR TestCur IS
SELECT col
FROM tab
WHERE col in
( SELECT * FROM TABLE (CAST (GETINLIST.GetNumberList (InList) AS NUMBERTABLETYPE)) );
BEGIN
InTable(1) := '''10''';
FOR ListId IN InTable.First..InTable.Last LOOP
InList := InTable(ListId);
DBMS_OUTPUT.PUT_LINE ('InList = ' || InList);
FOR TestRec IN TestCur LOOP
DBMS_OUTPUT.PUT_LINE (TestRec.col);
END LOOP;
END LOOP;
END;

Tom Kyte
March 08, 2004 - 2:23 pm UTC

run it a couple of times -- you are counting all sql in your session. run it over and over and you should eventually see "0" hard parses.

Rob Kato, March 23, 2004 - 4:58 pm UTC

I have a query similare to yours >
select * from all_users where user_id in ( select *
from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )

I put an index on what would be the user_id column but Oracle will not pick it up at all.
Not even when I try to force it using a hint. Any suggestions?

Tom Kyte
March 24, 2004 - 8:14 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3779680732446#15740265481549 <code>


but, if not using a hint -- that might mean that user_id is a number ans str2tbl is a string and you are doing a conversion -- sooo, select TO_NUMBER(column_value) from the( str2table(.....) )

to convert

Efficient static query

Sha, June 28, 2004 - 3:43 pm UTC

Hi Tom,

Please tell me the difference between the following 2 static queries which is more efficient/overhead and why ?

I want to make sure that all the indexes are used. e.g. in this case index on id.

1)
select * from t1 where id in (select * from the (select cast(str2tbl('ABC', 'XYZ') as myTableType) from dual))

2)
select b.* from t1 b,TABLE(select cast(str2tbl('ABC', 'XYZ') as myTableType) from dual) a
where b.id = a.COLUMN_VALUE


Currently I have no choice but to use dynamic query with cursor_sharing = force.
select * from t1 where id in ('ABC', 'XYZ') But ideally I would like to get rid of this dynamic query and replace with a static one.

Thanks as always,

Tom Kyte
June 28, 2004 - 4:25 pm UTC

they are not the same query. if you passed in 'ABC', 'ABC' and have a table t1 with 'ABC' in it and run both, you'll see.

therefore, use the one that gives the answer you need (probably #1)

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3779680732446#15740265481549 <code>

Use AND instead of OR functionality

Mark Nijhof, July 30, 2004 - 5:41 am UTC

Hi Tom,

I have this statement in a procedure:
((
Upper(T_STILLBILDE_EMNE.EMNEORD) IN
(SELECT
Upper(column_value)
FROM
TABLE(CAST(ParseInList(p_keywords) AS MyTable))
) AND
p_must_have_all_keywords <> 1
) OR (
p_must_have_all_keywords = 1
)) AND

now in the part where p_must_have_all_keywords is 1 I need the exact same thing as used before but now instead of using OR (what the IN (1,2,3) clause does) I need it to be AND. Is there a way of doing this whitout going to dynamic sql? And is so is it possible to have only a little part of the procedure be dynamic sql? Like:

((
Upper(T_STILLBILDE_EMNE.EMNEORD) IN
(SELECT
Upper(column_value)
FROM
TABLE(CAST(ParseInList(p_keywords) AS MyTable))
) AND
p_must_have_all_keywords <> 1
) OR (
v_dynamic_sql_a_lot_of_and_statements AND
p_must_have_all_keywords = 1
)) AND

Thanks,

Mark


Tom Kyte
July 30, 2004 - 4:37 pm UTC

in for a penny, in for a pound.

a little dynamic sql -> it is entirely dynamic sql.

varying elements in IN list

Ramakrishnan, August 04, 2004 - 4:33 am UTC

Tom,
Thanks for your valuable helps....Can we use the below quey to split the variable elements in the IN list.

select
SUBSTR('10,22,33,44,45,',
DECODE(rownum-1,0,1,instr('10,22,33,44,45,',',',1,rownum-1)+1),
instr('10,22,33,44,45,',',',1,rownum) - DECODE(rownum-1,0,1,instr('10,22,33,44,45,',',',1,rownum-1)+1)
)
from all_objects
where DECODE(instr('10,22,33,44,45,',',',1,rownum),0,null,instr('10,22,33,44,45,',',',1,rownum)) <= instr('10,22,33,44,45,',',',-1,1)
/



Tom Kyte
August 04, 2004 - 10:01 am UTC

sure:


ops$tkyte@ORA9IR2> variable x varchar2(255)
ops$tkyte@ORA9IR2> exec :x := '10,22,33,44,45'
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select
  2  SUBSTR(:x,
  3             instr( ','||:x||',', ',', 1, rownum ),
  4         instr( ','||:x||',', ',', 1, rownum+1)
  5                   - instr(','||:x||',', ',', 1, rownum)-1 ) column_value
  6   from all_objects
  7  where rownum <= (length(:x)-length(replace(:x,',',''))+1)
  8  /
 
COLUMN_VALUE
-------------------------------------------------------------------------------
10
22
33
44
45


is another way. 

Help with Bind Varibles

A reader, August 12, 2004 - 6:55 am UTC

Hey Tom.

Thanks for all your help so far with bind varibles but I am having a little trouble with one of them at the moment.

Background:

I have a function that accepts 1 variable (varchar2)that is used to build a dynamic query. The resulting query is returned in the form of a string i.e.

FUNCTION update_report (p_hr_update_str IN VARCHAR2)
RETURN VARCHAR2
IS
l_query VARCHAR2 (20480);
BEGIN
l_query :=
'SELECT employees.emp_no, employees.dept_id dept_id, employees.pb_name payband,
employees.trade trade, employees.NAME name,
employees.hours,
hr.uin_id dept_id2, hr.pb_name payband2,
hr.trade trade2, hr.NAME name2,
hr.hours hours2
FROM hr, employees
WHERE hr_id IN ('
|| p_hr_update_str
|| ')
AND employees.emp_no = hr.emp_no';
RETURN l_query;
END update_report;

The package that calls this function conatins some DBMS_SQL that is executed in order to produce a report using a SYLK report engine that I have written.

The string (p_hr_update_str) that comes into my report generation package is in the form of:

'98, 125, 7845, 12' etc and can contain many elements.

As you can see from my function, p_hr_update_str is used in the IN clause of the query to return my result set.

I have similar reports using this method that accept only value value i.e. p_dept = IT and interogating the shared pool, the query appears to adopt a bind variable approach which is what i am after.

When however i run in a string as above, these values are hard coded into the query and hence I have many queries existing in the shared pool where only the values in the IN clause are different.

It is this I need help with. Converting my string into a bind variable.

So far from the posts above I have created my type and function (the one you provided as str2tbl above) but I am not too sure how I can use these to replace my p_hr_update_str in my query.

Any help would be greatly appreciated.

D





Tom Kyte
August 12, 2004 - 9:22 am UTC

ummm, page up? that is the crux of what this page is all about.

why would this not work?

Mary, September 17, 2004 - 5:17 pm UTC

Tom,

I have a string of numbers that i am passing to my query:

var_string varchar2(100);
var_string := ltrim(rtrim(substr('0,1',1)));

select number_field from TableA
where number_field in (var_string);

I am getting an error. how can i get this to work?

thank you

Tom Kyte
September 17, 2004 - 7:59 pm UTC

that is the same as:

select number_field from tableA
where number_field in ( '0,1' );


(you are probably getting "invalid number" as '0,1' ISN'T a number)

please see the original response way at the top of this page -- it is precisely the same exact issue.

a pure sql-way to do it

SivadLeima, October 10, 2004 - 6:54 pm UTC

Tom, your words makes me proud at being an oracle developer
Here is a pure sql way for doing it:
SQL> Select * From V$Version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


SQL> var Str VarChar2(4000);
SQL>  begin
  2   :Str := 'PUBLIC,QS_CBADM,OUTLN';
  3  end;
  4  /

PL/SQL procedure successfully completed.
SQL> Select SubStr(:Str, Cut + 1,
  2    Case When Len > 0 Then
  3                       Len - 1
  4    Else
  5                       Length(:Str) - Cut
  6    End )
  7    As List
  8    From
  9    (
 10    Select Cut, Lead(cut,1,0) Over (order by cut) - Cut As Len From
 11            (
 12            Select Distinct InStr(:Str, ',', RowNum) As Cut
 13            From all_objects Where RowNum <= Length(:str)
 14            )
 15    )
 16  /

LIST
--------------------------------------------------------------------------------
PUBLIC
QS_CBADM
OUTLN

e.g, a query that use this way:

SQL> Select Count(*) From All_Objects
  2  Where 
  3  Owner In (
  4  Select SubStr(:Str, Cut + 1,
  5    Case When Len > 0 Then
  6                       Len - 1
  7    Else
  8                       Length(:Str) - Cut
  9    End )
 10    As List
 11    From
 12    (
 13    Select Cut, Lead(cut,1,0) Over (order by cut) - Cut As Len From
 14            (
 15            Select Distinct InStr(:Str, ',', RowNum) As Cut
 16            From all_objects Where RowNum <= Length(:str)
 17            )
 18    )
 19  )
 20  /

  COUNT(*)
----------
     11571
SQL> print str

STR
--------------------------------------------------------------------------------
PUBLIC,QS_CBADM,OUTLN
SQL> Begin
  2  :Str := 'QS_CBADM,OUTLN';
  3  End;
  4  /

PL/SQL procedure successfully completed.

SQL> Print Str

STR
---------------------------------------------------------------------------
QS_CBADM,OUTLN

SQL>  Select Count(*) From All_Objects
  2   Where 
  3   Owner In (
  4   Select SubStr(:Str, Cut + 1,
  5     Case When Len > 0 Then
  6                        Len - 1
  7     Else
  8                        Length(:Str) - Cut
  9     End )
 10     As List
 11     From
 12     (
 13     Select Cut, Lead(cut,1,0) Over (order by cut) - Cut As Len From
 14             (
 15             Select Distinct InStr(:Str, ',', RowNum) As Cut
 16             From all_objects Where RowNum <= Length(:str)
 17             )
 18     )
 19   )
 20  /

  COUNT(*)
----------
        31
Not so generic as your solution, but nice to know it can be done.
Amiel (Holy land, Jerusalem)

 

Oops....

SivadLeima, October 11, 2004 - 5:20 pm UTC

I didn't read the whole thread...
as usual Tom did it before me (about 3 years...)
and in much more elegant way :)
nevertheless it was fun!

Amiel (Holy Land, jerusalem).

Input String larger than 4k

Vinnie, October 21, 2004 - 12:51 pm UTC

I seem to be getting an error when my input string into STR2TBL is large. What could another approach be for this?

I am calling the function from a JAVA app.

Tom Kyte
October 21, 2004 - 3:12 pm UTC

use a global temporary table and have the java app bulk insert into it and use

where in ( select * from gtt)




Bulk Insert

Vinnie, October 22, 2004 - 10:41 am UTC

Would it be better to have the JAVA app do the
Create temporary table or have the app call a stored procedure to do it?

If the Java app, do you have an example of this?

Tom Kyte
October 22, 2004 - 5:56 pm UTC

it would be better for sqlplus to do it ONCE?

SQL> create global temporary table gtt ( x number ) on commit delete rows;


period -- it becomes part of your application schema.  don't even think about dynamically creating these at run time (might as well just not bind if you did that!  it would be a hard parse each time since you have a different table each time) 

Rownum with Table Operator??

Prasad, October 23, 2004 - 2:47 am UTC

CREATE OR REPLACE TYPE NumberTableType AS TABLE OF NUMBER;
/

CREATE OR REPLACE TYPE VarCharTableType AS TABLE OF VARCHAR2(2000);
/

TYPE RPT_SUMRY_CV_TYPE IS REF CURSOR; -- defined in package spec.

PROCEDURE get_volume_summary(p_result OUT RPT_SUMRY_CV_TYPE) IS
l_status_list VarCharTableType := VarCharTableType();
l_count_list NumberTableType := NumberTableType();

BEGIN
l_status_list.extend;
l_status_list(l_status_list.COUNT) := 'PLANNED';
l_count_list.extend;
l_count_list(l_count_list.COUNT) := 3;

l_status_list.extend;
l_status_list(l_status_list.COUNT) := 'VERIFIED';
l_count_list.extend;
l_count_list(l_count_list.COUNT) := 10;

open p_result for (SELECT a.column_value ac, b.column_value bc FROM TABLE(cast (l_status_list AS VarCharTableType)) a,
TABLE(cast (l_count_list AS NumberTableType)) b);

END;
/

Result: (as expected)
PLANNED 3
VERIFIED 3
PLANNED 10
VERIFIED 10

But what I really want to do is join l_status_list and l_count_list so that I only get following result

PLANNED 3
VERIFIED 10

(only returns row n from both lists for 1..n).

Any way of achieving the required result?

Thanks

Tom Kyte
October 23, 2004 - 10:01 am UTC

sorry -- not following you.


if you just wanted "row n" from both, why not

select l_status_list(N), l_count_list(N) from dual;

??

Prasad, October 23, 2004 - 10:13 am UTC

Sorry if I did not make myself clear.

I would like the "open p_result for ...." to return following

l_status_list(1), l_count_list(1)
l_status_list(2), l_count_list(2)
..
l_status_list(n), l_count_list(n)

NOT

l_status_list(1), l_count_list(1)
l_status_list(1), l_count_list(2)
..
l_status_list(1), l_count_list(n)
.. upto

l_status_list(n), l_count_list(n)


Thanks again

Tom Kyte
October 23, 2004 - 10:22 am UTC

wonder how your lack of clarity translates into the rating. always curious out that. If I don't immediately see the question. hmmm. (this followup directly conflicts with your prior stated goal of:

<quote>
But what I really want to do is join l_status_list and l_count_list so that I
only get following result

PLANNED 3
VERIFIED 10
</quote>

you want to join these two collections by their indices apparently, very very different from what you said above in words and in "depiction"


but anyway:

open p_result for
SELECT a.column_value ac, b.column_value bc FROM
(select rownum r, column_value
from TABLE(cast (l_status_list AS VarCharTableType))) a,
(select rownum r, column_value
from TABLE(cast (l_count_list AS NumberTableType))) b
where a.r = b.r;

althoug it seems to me that you should have ONE collection (eg: your type is wrong) with two attributes instead of two collections.

Rownum with Table Operator

Prasad, October 23, 2004 - 10:50 am UTC

Lower rating because your first reply didn't help me at all. It applies to your first reply only.

I because a PL/SQL programmer 3 months ago after nearly 4 years of Java :). And already made your site my home page!, and its late here in and I kept awake to see if you answer my question.

I know my collection is wrong, but this will do the job till I get sorted it out. Will try your answer at work on Monday.


Thank you.




GTT

Vinnie, October 24, 2004 - 8:56 pm UTC

The only problem with using a GTT is that the session is generating a report that is using the varying IN array list and the session generates these reports in pararrel. Dosen't the GTT truncate when the session commits? If this is so, the session using the GTT that contains a list from both reports. Can setarray be used? If so, do you have an example?

Tom Kyte
October 25, 2004 - 7:42 am UTC

"and the session generates these reports in parallel"

please elaborate -- as a session is a serial thing.

GTT

Vinnie, October 25, 2004 - 11:19 am UTC

The Java App spawns threads which each are connected to the same session. Wouldn't a commit on 1 thread thus truncate the GTT for the others?

Tom Kyte
October 25, 2004 - 11:45 am UTC

how does it do that -- only one thread at a time could possibly use the session. are you sure it works as you describe (it would not be "threaded" at the database level, it must be serialized -- what would the point of the threads be in that case...)

GTT

Vinnie, October 25, 2004 - 12:55 pm UTC

Each report spawns a new thread which use the same connection to the database. Each report has multiple queries. So even though the dB processes the queries in serial, the reports could be generating these queries in pararrel, correct?

Tom Kyte
October 25, 2004 - 1:21 pm UTC

they won't be executing them in parallel -- one at a time. only one thread is going to use that connection at a time.

doesn't seem to make much sense, why would they not have their own connections.

but yes, as it stands, an "on commit delete rows" gtt would be an issue.

like operator

Prasad, November 04, 2004 - 12:03 am UTC

Dear Tom,

Is it possible to do the same with the 'like' operator instead of 'in'.

For example need a valid query for following pseudo code:

select * from t where x like any(:a_varying_csv_list)

above :a_varying_csv_list = 'aa','bb','cc', ....

The problem I face is 'any' operator doesn't support 'like'. And I don't know how many values are in the list.

Currently I only support like if the list has one value, else I look for exact match. Other only work around I see is to assume a safe upper limit for number of list elements (=5)

Thanks
Prasad


Tom Kyte
November 05, 2004 - 11:36 am UTC

no, that bind variable is a SINGLE CHARACTER STRING -- PERIOD.

it is "not a list"

that would not work at all.


it really sounds like you want to use Oracle Text and its significantly more advance text search capabilities via the CONTAINS clause in SQL. See the Oracle Text docs.

Without using str2tbl

A reader, December 16, 2004 - 11:02 am UTC

Is there a way to do this without using the str2tbl function? Using pure SQL? Using constructors for the 'create or replace type t as table of varchar2(4000)'?

Thanks

Tom Kyte
December 16, 2004 - 11:40 am UTC

yes, you could create a full object type -- but how would that be "pure sql" since the constructor would itself be written in plsql? you gotta parse the string.

Full object type

A reader, December 16, 2004 - 2:32 pm UTC

What do you mean by full object type? One with a body and code in it? What would the body contain? Guess I dont understand what you mean. Thanks

Tom Kyte
December 16, 2004 - 2:57 pm UTC

in order to have a non-default constructor, you'll be creating a type body. The body would contain the code in str2tbl.


you have a string
string needs to be parsed
code to do parsing must be written
code that does parsing would be in the type body.

Why CAST?

A reader, December 29, 2004 - 9:30 am UTC

Since str2tbl is defined as

create or replace function str2tbl( p_str in varchar2 ) return myTableType

the SQL engine knows that the str2tbl function returns "myTableType"

So why do I need to CAST(str2tbl(...) as myTableType)?

Its not as if I need to do

CAST(to_char(...) as varchar2)

So whats different about this?

Thanks

Tom Kyte
December 29, 2004 - 10:30 am UTC

remove it and see -- later releases did not need it, earlier ones did.

CAST

A reader, December 29, 2004 - 11:01 am UTC

Ah, in 9iR2, I can simply do

select * from table(str2tbl('1,2,3,4,5,5,6,7'));

Very nice.

Another question:

How can I modify your str2tbl function to accept a delimiter instead of hardcoding ','? If the delimiter is NULL, then the string should be broken down by each character.

Thanks

Tom Kyte
December 29, 2004 - 7:05 pm UTC

create or replace function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return

and global replace ',' with p_delim (and change logic to be your "character at a time" one -- although you might just write str2chars for that....)



Delimiter

A reader, December 29, 2004 - 11:28 am UTC

OK I figured it out

CREATE OR REPLACE FUNCTION str2tbl(
p_string IN VARCHAR2,
p_delimiter IN VARCHAR2 DEFAULT ','
)
RETURN varchar2_tab
AS
l_string LONG DEFAULT p_string || p_delimiter;
l_index INTEGER;
l_table varchar2_tab := varchar2_tab();
BEGIN
IF (p_string IS NULL)
THEN
RETURN l_table;
END IF;

IF (p_delimiter IS NULL)
THEN
FOR i IN 1..length(l_string)
LOOP
l_table.extend;
l_table(l_table.count) := trim(substr(l_string,i,1));
END LOOP;
RETURN l_table;
END IF;

LOOP
l_index := instr(l_string,p_delimiter);
exit when (nvl(l_index,0) = 0);
l_table.extend;
l_table(l_table.count) := trim(substr(l_string,1,l_index-1));
l_string := substr(l_string,l_index+length(p_delimiter));
END LOOP;
RETURN l_table;
END str2tbl;
/

Thanks

How to display the Items in the list but missing in the Table

kamal, February 02, 2005 - 5:43 am UTC

I have list of 200 names and i have to check in a table and find out the names which are present in the list but not in the table. I have to do it in SQL

one way to do it combine all the names into one inline view using union and then do a minus from the original table.

example : (Select 'A' from dual
union
Select 'B' from dual)
minus
select name from t where name in ('A','B');

But i dont want to format all the two hundred names like that.....

Is there any other way to do it in SQL without much formatting like above??..

Thanks in advance
kamal

Tom Kyte
February 02, 2005 - 7:52 am UTC

did you see above?


select column_value
  from table( cast( str2tbl( :your_bind ) as myTableType )
 where column_value not in ( select name from t );

just put them in a string, or if they are in an arrray already in your program, you could use that directly.  eg:

ops$tkyte@ORA9IR2> create or replace type myTableType as table of varchar2(30)
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_names myTableType := myTableType('SYS', 'a', 'b', 'c', 'SYSTEM' );
  3  begin
  4          for x in (select column_value
  5                      from table( cast(l_names as myTableType) )
  6                             where column_value not in (select username from all_users) )
  7          loop
  8                  dbms_output.put_line( x.column_value );
  9          end loop;
 10  end;
 11  /
a
b
c
 
PL/SQL procedure successfully completed.
 
 

using variable as a where clause

Thiru, February 10, 2005 - 9:56 am UTC

I am trying to use a where clause that is defined in a variable as there would be varied where clauses based on the IN parameters to a procedure. Is it required that use of DBMS_SQL is required? or is there another way? I tried to just plug in the variable value but looks like it's not correct.

declare
where_clause varchar2(200) := 'select id from tbl2';
l_cnt number;
begin

select count(*) into l_cnt from tbl where id in(where_clause) ;
dbms_output.put_line(l_cnt);
end;
/

Gives Invalid Number exception.

Tom Kyte
February 11, 2005 - 3:16 am UTC

you have to use dynamic sql, be it native dynamic sql

open refcursor for sql_statement;

or dbms_sql.

and MAKE SURE you binds.

and watch out for sql injection.

Variable Like list

koms, April 22, 2005 - 10:09 am UTC

Tom,
I have a strange requirement , I do not know whether its possible in Oracle 8i or not.

I have read your post on how to handle variable number of elements in IN list, but what if instead of IN I need to use LIKE %.
I tried that and I get "single-row subquery returns more than one row"
Is it possible someway or the other ( e.g. fucntion . procedure ) to efficiently handle variable LIKE list.

Regards,


Tom Kyte
April 22, 2005 - 10:58 am UTC

you cannot, in 10g a regular expression could probably do that in a single regexp_like call -- but in 9ir2 and before, you have a bunch of "ors" (or use a TEXT index -- and a single contains clause)

like-list

AndersH, April 23, 2005 - 8:59 am UTC

Couldn't you do something like (npi) this?

SELECT e.*
FROM scott.emp e
INNER JOIN (
SELECT /*+cardinality(t 10)*/ column_value v
FROM TABLE(
SELECT CAST(str2varchartable('%OR%,%IL%') AS varchartabletype)
FROM dual) t
WHERE rownum>=0) t ON e.ename LIKE t.v

Instead of inventing our own str2<whatever>, we might use xml which would also allow us to use multiple columns, although last I tried Oracle kept burping internal errors at me.

Tom Kyte
April 23, 2005 - 9:33 am UTC

it'll return dups (if I am like both OR and IL)

I would text index it and use contains personally. It has leading edge % optimizations even)
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:37336026927381 <code>

I would not want to do this as a "LIKE JOIN" (non-equi join)

need to suppress semi-join on a collection (always_semi_join can't be considered)

Vladimir Sadilovskiy, June 06, 2005 - 5:03 pm UTC

Tom,

Can you please explain

1. Why CBO considers semi-join on a collection in "IN-LIST". Such course of actions propagates incorrect cardinality to the further steps of the execution path and eventually leads to a bad plan on high cardinalities of the collection.

Artificial setup environment can be achieved by running following:

create table t1 as select * from all_objects;

create unique index t1_i on t1(object_id);

create table t2 as select * from all_objects;

create index t2_i on t2(object_id);

update t1 set status = 0;

update t2 set status = 0;

create or replace type table_of_number as table of number;
/

create or replace function getnumericlist(card_n number) return table_of_number
as
l_numeric_list table_of_number;
begin
select object_id
bulk collect into l_numeric_list
from (select distinct object_id from t1)
where rownum <= card_n;

return l_numeric_list;
end getnumericlist;
/

begin
dbms_stats.delete_table_stats(null, 't1');
dbms_stats.gather_table_stats(null, 't1',
estimate_percent => 100,
method_opt => 'for all columns size 254',
cascade => true);

dbms_stats.delete_table_stats(null, 't2');
dbms_stats.gather_table_stats(null, 't2',
estimate_percent => 100,
method_opt => 'for all columns size 254',
cascade => true);
end;
/


Test query:
select count(*) from (
select t1.object_name,t1.object_type, t2.object_name
from t1, t2
where t1.object_id in (select /*+ cardinality(nlist 1000) */ *
from table(cast(getnumericlist(1000) as table_of_number)) nlist)
and t2.object_id = t1.object_id
and t1.status = 0
and t2.status = 0);

(for proper test use same value for getnumericlist function as for cardinality parameter)

Plan for low carinality of the collection:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=71 Card=1 Bytes=16)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=7)
3 2 NESTED LOOPS (Cost=71 Card=10 Bytes=160)
4 3 NESTED LOOPS (Cost=51 Card=10 Bytes=90)
5 4 SORT (UNIQUE)
6 5 COLLECTION ITERATOR (PICKLER FETCH) OF 'GETNUMERICLIST'
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1 Bytes=7)
8 7 INDEX (UNIQUE SCAN) OF 'T1_I' (UNIQUE)
9 3 INDEX (RANGE SCAN) OF 'T2_I' (NON-UNIQUE) (Cost=1 Card=1)

Offending plan for collection cardinality > 13 (my case.. magic number!? :)) :
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=93 Card=1 Bytes=16)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=7)
3 2 NESTED LOOPS (Cost=93 Card=1 Bytes=16)
4 3 HASH JOIN (SEMI) (Cost=91 Card=1 Bytes=9)
5 4 TABLE ACCESS (FULL) OF 'T1' (Cost=51 Card=30295 Bytes=212065)
6 4 COLLECTION ITERATOR (PICKLER FETCH) OF 'GETNUMERICLIST'
7 3 INDEX (RANGE SCAN) OF 'T2_I' (NON-UNIQUE) (Cost=1 Card=1)

As you can see the cardinality of the outcome from collection and T1 is 1. It is always evaluated as 1 regardless of the collection cardinality. In fact it is evaluated as "card(t1) * selectivity(t1.object_id)" from 10053 trace.

Result of the offending query with collection cardinality = 100000

Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
55746 consistent gets
0 physical reads
0 redo size
211 bytes sent via SQL*Net to client
344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed

Probably one can predict that hash join on T2 reduces LIOs substantially:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=143 Card=1 Bytes=16)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=143 Card=1 Bytes=16)
3 2 HASH JOIN (SEMI) (Cost=91 Card=1 Bytes=9)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=51 Card=30295 Bytes=212065)
5 3 COLLECTION ITERATOR (PICKLER FETCH) OF 'GETNUMERICLIST'
6 2 TABLE ACCESS (FULL) OF 'T2' (Cost=51 Card=30296 Bytes=212072)


Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
456 consistent gets
0 physical reads
0 redo size
211 bytes sent via SQL*Net to client
344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed


2. This doesn't happend with real tables. Is it possible to suppress semi-join for this particular type of queries?

Side note: always_semi_join=off isn’t working for me in some versions (9.2.0.5+SA #68, 9.2.0.6); 10.1.0.2 works by I'd rather find the conceptual workaround that will allow legitimate semi-joins take place.

Thanks.

- Vladimir


should have used _always_semi_join=off instead... still...

Vladimir Sadilovskiy, June 09, 2005 - 12:14 am UTC


response fixed my problem

Tommy Skodje, August 01, 2005 - 6:14 am UTC


4000 character limit

Julius, November 01, 2005 - 7:55 pm UTC

In response to the 4000 character limit (</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061#9734635621139 <code>
I was wondering if replacing varchar2 with clob would help?


DEV1>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for Solaris: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

DEV1>create or replace type t_NTtype as table of number;
2 /

Type created.

DEV1>create or replace function sss( p_str in clob )
2 return t_NTtype
3 as
4 l_str clob default p_str || ',';
5 l_n number;
6 l_data t_NTtype := t_NTtype();
7 l_cnt number := 0;
8 begin
9 loop
10 l_n := instr( l_str, ',' );
11 exit when (nvl(l_n,0) = 0);
12 l_cnt := l_cnt + 1;
13 l_data.extend;
14 l_data( l_data.count ) := to_number(ltrim(rtrim(substr(l_str,1,l_n-1))));
15 l_str := substr( l_str, l_n+1 );
16 end loop;
17 dbms_output.put_line('l_data.count='||l_data.count);
18 return l_data;
19 end;
20 /

Function created.

DEV1>create table t1 (id number);

Table created.

DEV1>insert into t1 values (1);

1 row created.

DEV1>insert into t1 values (12);

1 row created.

DEV1>insert into t1 values (123);

1 row created.

DEV1>set serveroutput on size 1000000
DEV1>declare
2 v_didtab t_NTtype;
3 v_dlist varchar2(32000) := '';
4 v_dlist_c clob;
5 v_nn number := 0;
6
7 begin
8
9 v_dlist := lpad('123,',30000,'123,');
10 dbms_output.put_line('length(v_dlist)='||length(v_dlist));
11 dbms_output.put_line(substr(v_dlist,1,50));
12
13 v_dlist_c := v_dlist;
14
15 select id bulk collect into v_didtab
16 from T1
17 where
18 id in
19 (
20 select /*+ cardinality (t 10) */ * from
21 table(cast( sss( v_dlist_c ) as t_NTtype )) t
22 where rownum>=0
23 );
24
25 end;
26 /
length(v_dlist)=30000
123,123,123,123,123,123,123,123,123,123,123,123,12
l_data.count=7501

PL/SQL procedure successfully completed.

DEV1>

variable inlist

A reader, December 29, 2005 - 9:24 am UTC

Hi

I have seen that your way of dealing (most of times) is using a user defined type and then cast the type.

I have a question, is this possible to do in PL/SQL?

begin
l_string := 'SCOTT, OH'
insert into x select * from dba_users
where username in (l_string);
end;
/

I get no data found!

Tom Kyte
December 29, 2005 - 12:13 pm UTC

ops$tkyte@ORA9IR2> create table t as select * from all_users where 1=0;

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2      l_string varchar2(100) := 'SCOTT, SYS';
  3  begin
  4      insert into t
  5      select *
  6        from all_users
  7       where username in
  8       ( select  trim(
  9                 substr (txt,
 10                 instr (txt, ',', 1, level  ) + 1,
 11                 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, 
                                                                 level) -1 ))
 12           from (select ','||l_string||',' txt from dual)
 13        connect by level <= 
                       length(l_string)-length(replace(l_string,',',''))+1
 14       );
 15  end;
 16  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> select * from t;

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SCOTT                                  60 06-DEC-03
SYS                                     0 06-DEC-03

Is another way in 9ir2 and above to do this. 

The laster 10g version of function str2tbl

Charlie Zhu, January 12, 2006 - 7:43 pm UTC

Hi Tom,

This function is really helpful, I'm fight with the high Soft Parse rate these days, caused by the varying elements IN list.

I just wonder what is the newest version of str2tbl for Oracle 10.2?

Is it below one?

create or replace type myTable as table of varchar2(25)
/

Type created.

create or replace function str2tbl( p_str in varchar2 )
return myTable
pipelined
as
l_str varchar2(5000) default p_str || ' ';
l_n number;
begin
loop
l_n := instr( l_str, ' ' );
exit when (nvl(l_n,0) = 0);
pipe row( ltrim(rtrim(substr(l_str,1,l_n-1))) );
l_str := ltrim( substr( l_str, l_n+1 ) );
end loop;
return;
end;
/

Thanks, Charlie

Tom Kyte
January 13, 2006 - 11:03 am UTC

that works, or you can:


ops$tkyte@ORA9IR2> variable txt varchar2(25)
ops$tkyte@ORA9IR2> exec :txt := 'a,bb,ccc,d,e,f';

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> with data
  2  as
  3  (
  4  select substr (txt,
  5                 instr (txt, ',', 1, level  ) + 1,
  6                 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
  7           as token
  8    from (select ','||:txt||',' txt from dual)
  9  connect by level <= length(:txt)-length(replace(:txt,',',''))+1
 10  )
 11  select * from data;

TOKEN
----------------------------------
a
bb
ccc
d
e
f

6 rows selected.
 

Too complex for Java Programmer

Charlie Zhu, January 13, 2006 - 2:00 pm UTC

Hi Tom,

The 2nd method requires a little less Latches and less PGA(64K).
I guess the Jave programmer will like the 1st one.

Can you help to hide the complexity of 2nd method (In-line SubQuery) ?

FYI,

I reversed their position here, Run2 is Str2Table(),

Declare
li_loop_cnt pls_integer := 2000;
txts varchar2(4000) := '22762 25283 26305 36062 51307 53058 53631 69454 70019 74271 77052 77061 77078 77082 77090 77092 77095 77096 77097 147129646 147129670 147129684 147129696 147129718 147129734 147129754 606842501 147129776 147129788 147129796 147129802 147129850 147129862 147129864 147129866 147129868 606841856 606841857';
txtc varchar2(4000) := '22762,25283,26305,36062,51307,53058,53631,69454,70019,74271,77052,77061,77078,77082,77090,77092,77095,77096,77097,147129646,147129670,147129684,147129696,147129718,147129734,147129754,606842501,147129776,147129788,147129796,147129802,147129850,147129862,147129864,147129866,147129868,606841856,606841857';

Begin

runStats_pkg.rs_start;

For i in 1 .. li_loop_cnt Loop
For c1 in (
with sq
as
(
select substr (ctxt,
instr (coltxt, ',', 1, level ) + 1,
instr (coltxt, ',', 1, level+1) - instr (coltxt, ',', 1, level) -1 )
as token
from (select ','||txtc||',' coltxt from dual)
connect by level <= length(txtc)-length(replace(txtc,',',''))+1
)
select
a.listingsid, a.rowdf
from abelisting.listings a, sq
where a.listingsid = sq.token
)Loop
Null;
End Loop;

End loop;

runStats_pkg.rs_middle;

For i in 1 .. li_loop_cnt Loop
For c1 in (
select
a.listingsid, a.rowdf
from TABLE(str2tbl
--('238732766 238732767 238732768 238732769 238732770 238732771 238732772 238732773 238732774 238732775 238732776 238732777 238732778 238732779 238732780 238732781 238732782 238732783 238732784 95642 95674 153350 153410 321004 409569 409761')
(txts)
) b,
abelisting.listings a
where a.listingsid = to_Number(b.column_value)
)
Loop
Null;
End Loop;

End loop;


runStats_pkg.rs_stop;

End;
/

STAT...Elapsed Time 156 144 -12
STAT...recursive cpu usage 154 140 -14

Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
174,459 190,431 15,972 91.61%


Tom Kyte
January 13, 2006 - 2:08 pm UTC

educate the java programmer. Hey, if they can do Java, they better be able to get their head around a little SQL.

Use either one though.

Is there a way to apply this to another query?

Gary, January 27, 2006 - 7:12 pm UTC

I'd like to pull my list of comma separated values from another query but cannot find a resolution. For example, instead of:

from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from
dual ) )

I'm looking for:

from THE ( select cast( str2tbl( SELECT VALUE FROM TABLE WHERE COLUMN = 'A' ) as mytableType ) from
dual ) )

Tom Kyte
January 28, 2006 - 12:57 pm UTC

ops$tkyte@ORA10GR1> create table t ( x varchar2(20) );

Table created.

ops$tkyte@ORA10GR1> insert into t values ( '1,2,3' );

1 row created.

ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select *
  2    from table( str2tbl( (select x from t) ) )
  3  /

COLUMN_VALUE
------------------------------
1
2
3


just need one more set of ()'s 

Thanks!

Gary, January 29, 2006 - 6:42 pm UTC

In hindsight, that makes sense. I was beating my head against a wall trying to get it to work and probably tried everything BUT the additional enclosing parens. :-)

Thanks!!

To reduce JDBC network traffic

Charlie Zhu, March 08, 2006 - 3:47 pm UTC

Change to only use 1 "?" to only pass one Variable, reduce the network traffic.

{code}
PreparedStatement pstat = null;
String l_string, l_sql;

l_string = "535657837,535657838,238732767,238732768,238732769,238732770";

l_sql = "WITH sq " +
"as " +
"( " +
" SELECT substr(x, instr(x,',',1,level)+1, instr(x,',',1,level+1) - instr(x,',',1,level) -1) token " +
" FROM (select ','||l_str||',' x, l_str from (select ? l_str from dual)) " +
" CONNECT BY level <= length(l_str)-length(replace(l_str,',',''))+1 " +
") " +
"select /* */ " +
"a.listingsid, a.bsacode " +
"from abelisting.Listsbyrsrchattrs a, sq " +
"where a.listingsid = sq.token " ;

pstat = conn1.prepareStatement(l_sql);
pstat.setString(1, l_string);
ResultSet rset = pstat.executeQuery();
{code}

in-line subquery vs. String2TBL():

Run1 latches total versus Run2 -- difference and pct
Run1 Run2 Diff Pct
182,542 254,470 71,928 71.73%

Tom Kyte
March 09, 2006 - 12:44 pm UTC

I don't understand the question?

It's a suggestion

Charlie Zhu, March 10, 2006 - 6:53 pm UTC

No question here.

An enhancement, you only need to pass one Variable/Parameter to JDBC call.

{code}
PreparedStatement pstat = null;
String l_string, l_sql;

l_string = "535657837,535657838,238732767,238732768,238732769,238732770";

l_sql = "WITH sq " +
"as " +
"( " +
" SELECT substr(x, instr(x,',',1,level)+1, instr(x,',',1,level+1) - instr(x,',',1,level) -1) token " +
" FROM (select ','||l_str||',' x, l_str from (select ? l_str from dual)) " +
" CONNECT BY level <= length(l_str)-length(replace(l_str,',',''))+1 " +
") " +
"select /* */ " +
"a.listingsid, a.bsacode " +
"from abelisting.Listsbyrsrchattrs a, sq " +
"where a.listingsid = To_Number(sq.token) " ;

pstat = conn1.prepareStatement(l_sql);
pstat.setString(1, l_string);
ResultSet rset = pstat.executeQuery();
{code}


Maybe, not exactly match the theme, but...

Dmytro, March 16, 2006 - 2:08 pm UTC

...so we have not "varying elements in IN list", but parameters of the function, that returns the refcursor. It does search of some data in the table, using filters on some fields. For example, table details and fields det_kind and det_size.
But in some cases filters must not be applied at all or only some of them must be used. So client-side sets corresponding parameters to NULL in this case.
So, I was writing WHERE-clause like this:
OPEN ret_cur FOR
SELECT *
FROM details d
WHERE (d.det_kind = p_det_kind) OR (p_det_kind IS NULL)
AND (d.det_size = p_det_size) OR (p_det_size IS NULL);

It's simplified query without joined tables (there are some filters on their fields too). But some time later my superior said me to rewrite all this thing in dynamic SQL, because, according to his words, this "OR IS NULL" will hit query plans and perfomance.
Ok, no problem, now it looks like this:

IF p_det_kind IS NOT NULL
THEN
where_cl := where_cl + ' (d.det_kind = :p_det_kind)';
ELSE
where_cl := where_cl + ' (:p_det_kind IS NULL)';
END IF;

IF p_det_size IS NOT NULL
THEN
where_cl := where_cl + ' AND(d.det_size = :p_det_size)';
ELSE
where_cl := where_cl + ' (:p_det_size IS NULL)';
END IF;

OPEN ret_cur FOR
'SELECT *
FROM details d
WHERE ' || where_cl
USING p_det_kind
,p_det_size;

It's not a problem for me, but I still wonder was there really so big problem to use dynamic SQL and create significantly more compicated code or simple SQL was not so bad.

So, about "varying number of filters" not "elements in IN list", but will really appreciate your answer.

PS. Oh, at least at one thing new approach is better - some times it helps avoid few joins if filter is on the field on joined table, but what if we have no filters like this?

Tom Kyte
March 16, 2006 - 2:58 pm UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

yes, it can make a big difference, as far as access paths go.

Wow, helped a lot!..

Dmytro, March 17, 2006 - 5:31 am UTC

Approach with application context is even much more flexible, than our open for ... using statements. Before, I need to use dbms_sql to do queries with variable parameter lists and after that, have a lot of problems, transforming table-type into refcursor, because our client-side developers want no other types, than refcursors. :)
Thanks. Cool as always ;)

Confused about this query's behaviour??

Maverick, April 05, 2006 - 5:28 pm UTC

Tom, Can you help me with this problem. I know it is not the correct approach, but i was just testing some things and this was driving me crazy. if I use the variable then it will return nothing, if i hardcode them in where clause i get count 2.

CREATE TABLE EMP_CHAR
(
EMPNO VARCHAR2(10 BYTE),
NAME VARCHAR2(10 BYTE)
)

INSERT INTO EMP_CHAR ( EMPNO, NAME ) VALUES (
'1234', 'test');
INSERT INTO EMP_CHAR ( EMPNO, NAME ) VALUES (
'2345', 'test123');
INSERT INTO EMP_CHAR ( EMPNO, NAME ) VALUES (
'2346', 'mytest');
COMMIT;



testschema@test10G> ;
1 declare
2 v_count integer:=0;
3 v_emp varchar2(100):='(''1234'',''2345'')';
4 begin
5 select count(*) into v_count from emp_char
6 where empno in v_emp
7 --where empno in ('1234','2345')
8 ;
9 dbms_output.put_line('count from Emp '||v_count||' '||v_emp);
10 exception
11 when others then dbms_output.put_line('error '||sqlerrm);
12 end;
13
14 /
count from Emp 0 ('1234','2345')

PL/SQL procedure successfully completed.

testschema@test10G>

testschema@test10G>ed
Wrote file afiedt.buf

1 declare
2 v_count integer:=0;
3 v_emp varchar2(100):='(''1234'',''2345'')';
4 begin
5 select count(*) into v_count from emp_char
6 --where empno in v_emp
7 where empno in ('1234','2345')
8 ;
9 dbms_output.put_line('count from Emp '||v_count||' '||v_emp);
10 exception
11 when others then dbms_output.put_line('error '||sqlerrm);
12* end;
13 /
count from Emp 2 ('1234','2345')

PL/SQL procedure successfully completed.

testschema@test10G>

Any suggestions? Thx

Tom Kyte
April 06, 2006 - 9:52 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061 <code>

explains this.


you are saying with the string concatentation that you want the empno that is in the set that contains ONE STRING and that string is

'''1234'',''2345'''



But Query builds correctly

Maverick, April 06, 2006 - 10:10 am UTC

Tom, when i use method 1 my query builds correctly [where empno in ('1234','2345') ] exactly as it was hard coded in second method. I do not understand it.
your last comment was
"you are saying with the string concatentation that you want the empno that is in
the set that contains ONE STRING and that string is

'''1234'',''2345'''
"
But I was building query as empno in ('1234','2345'), so why does it think, it is a set that has just one string?

Thx,


Tom Kyte
April 07, 2006 - 3:50 pm UTC

5 select count(*) into v_count from emp_char
6 where empno in v_emp


v_emp is a scalar thing - NOT A SET, it is a single string. a string that contains the values '1234', '2345'


it is NOT a set
it is a scalar value

that is all.

Compensate amount

Debasish, April 11, 2006 - 4:28 am UTC

I have a table with following data

AMT
----------
10
-10
20
-20
40
-40
30
50
60
-60
80
I want to select only those rows whose values are not compensate.

i.e the result should be

sql> 30
50
80


Tom Kyte
April 11, 2006 - 2:28 pm UTC

wow, that is a bad design isn't it.

no create
no inserts
no look

Brute force....

Lars Stampe Villadsen, April 11, 2006 - 2:59 pm UTC

SELECT AMT FROM T
MINUS
SELECT -AMT FROM T

But.... If you table holds:

10
-10
10

Will all three rows be removed although there is no 'match' for the last 10 - so this is not something you should use in production - unless you know that your AMT only appears once as a positive number in your table - otherwise could this be a sketch of a solution:

SELECT AMT, COUNT(*) FROM T GROUP BY AMT
MINUS
SELECT -AMT, COUNT(*) FROM T GROUP BY -AMT

The output here will contain:
10, 2
-10, 1

So it is still not perfect but...

Tom Kyte
April 11, 2006 - 7:22 pm UTC

oh we can do it with a self join - but

no create
no inserts
no look

Thank you Tom

Lorenzo, September 05, 2006 - 1:45 pm UTC

I have some version of oracle 9i and the CAST is needed.

Great post.

I've passed this technique to a coworker

Mark Brady, September 08, 2006 - 5:46 pm UTC

and they tried to find the documentation for the "THE" operator in

"where user_id in ( select * from THE ( select cast( str2tbl"

and couldn't find it.

Neither could I. I guess it's not a bug in the docs to not have that in the index but it sure is inconvenient. It's not in the SQL reference index or the master index. Most search engines ignore articles and if you quote the string you get everything. Can you point me to the docs?



Tom Kyte
September 09, 2006 - 12:07 pm UTC

it is "archaic" - it was the original 8.0 syntax. it is not necessary.

"THE" keyword

Paul James, September 11, 2006 - 9:23 am UTC

I attempted to implement a query using the form:
( select * from THE ( select cast( str2tbl"...

without using "THE", since it is supposed to be "archaic" and "not necessary".

However, the only way I can get it to work is by leaving "THE" in the query. (This is on 9.2.0.5)
Any more details on what "THE" does?


Tom Kyte
September 11, 2006 - 10:34 am UTC

you need to use the "TABLE" syntax in place of the "archaic" "THE" syntax.


ops$tkyte%ORA10GR2> select * from all_users
  2  where user_id in ( select *
  3    from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )
  4  /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 30-JUN-05

ops$tkyte%ORA10GR2> select * from all_users
  2  where user_id in (select * from TABLE( str2tbl( '1, 3, 5, 7, 99' ) ) )
  3  /

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM                                  5 30-JUN-05
 

"THE" keyword - part 2

Paul James, September 11, 2006 - 9:29 am UTC

Finally found a reference for "THE".

See:

</code> http://www.unix.org.ua/orelly/oracle/prog2/ch19_05.htm <code>

type cast in a string

sara, October 25, 2006 - 3:34 pm UTC

Hi Tom,

I have a query like this. The input (p_con_id) is a string for example ('10,20,30,40,50'). Since con_id is a number column I am casting that into an array number type and including in the string in the cursor. But it says invalid column name... Can you guide me?

procedure (p_con_id,,v_sname,o_cur)
is
open o_cur for
'SELECT /*+ordered*/ a.id art_id,
to_char(a.date, ''fmMonth DD, YYYY'') publish
FROM key k, art a, art_c ac
WHERE lower(k.name) like ''%'||v_sname||'%''
AND a.id = ac.id
AND ac.con_id in ( SELECT * FROM THE (SELECT
CAST(get_numbers_from_string('||p_con_id||') AS array_number_tab)
from dual))
UNION
SELECT /*+ordered*/ ea.id art_id,
to_char(ea.date, ''fmMonth DD, YYYY'') publish
FROM key k, ext_art ea, ext_art_c eac
WHERE lower(k.name) like ''%'||v_sname||'%''
AND ea.id = eac.id
AND eac.container_id in ( SELECT * FROM THE (SELECT CAST(get_numbers_from_string('||p_con_id||') AS array_number_tab)
from dual))
ORDER BY pub_date DESC';

--
this is the function for conversion:
FUNCTION get_numbers_from_string(
p_id IN VARCHAR)
RETURN array_number_tab
IS
l_str LONG DEFAULT p_id || ',';
l_n NUMBER;
l_data array_number_tab := array_number_tab();
BEGIN
LOOP
l_n := INSTR( l_str, ',' );
EXIT WHEN (NVL(l_n,0) = 0);
l_data.extend;
l_data( l_data.count ) := TO_NUMBER(LTRIM(RTRIM(SUBSTR(l_str,1,l_n-1))));
l_str := SUBSTR( l_str, l_n+1 );
END LOOP;
RETURN l_data;
END get_numbers_from_string;

Thanks

Sara

Tom Kyte
October 25, 2006 - 4:27 pm UTC

type cast in a string

sara, October 26, 2006 - 10:14 am UTC

Thanks Tom.

But I need to use the type cast in a string and then open a cursor for the string. If I do that without the string, it is working fine. But with the string it is giving column not found. How do i resolve this. Thanks in advance.

like :
open o_cur for
'SELECT /*+ordered*/ a.id art_id,
to_char(a.date, ''fmMonth DD, YYYY'') publish
FROM key k, art a, art_c ac
WHERE lower(k.name) like ''%'||v_sname||'%''
AND a.id = ac.id
AND ac.con_id in ( SELECT * FROM THE (SELECT
CAST(get_numbers_from_string('||p_con_id||') AS array_number_tab)
from dual))
UNION
SELECT /*+ordered*/ ea.id art_id,

to_char(ea.date, ''fmMonth DD, YYYY'') publish
FROM key k, ext_art ea, ext_art_c eac
WHERE lower(k.name) like ''%'||v_sname||'%''
AND ea.id = eac.id
AND eac.container_id in ( SELECT * FROM THE (SELECT
CAST(get_numbers_from_string('||p_con_id||') AS array_number_tab)
from dual))
ORDER BY pub_date DESC';



Tom Kyte
October 26, 2006 - 12:08 pm UTC

I don't know what you mean, I cannot actually, well, you know - RUN your example, so I cannot help you debug it. If you gave a SHORT CONCISE yet 100% complete (did I mention SHORT, as small as POSSIBLE).


all i see is YOU ARE NOT USING BIND VARIABLES - STOP THAT RIGHT NOW, use binds.

open p_cursor
for 'select ...... :string ....' USING p_con_id;



varyi9ng in list

Herbert, March 07, 2007 - 4:14 am UTC

Tom,

I red your blog about the varying in list. Great example.
What is the advantage(in this case) to use the sys_context('my_ctx','txt') instead of a say my_package.get_txt? The package would have a txt global variable wich is set bij a m_package.set_txt('value') more or less the same as what happens in the my_ctx_procedure.
Tom Kyte
March 07, 2007 - 10:28 am UTC

because the database KNOWS that the context function should work like a bind variable.

hence sys_context is called ONCE per query and "bound in" in effect.

Where as:

where x = your_pkg.your_function

might have your_pkg being invoked many millions or billions of times. Sys_context is known to the sql parser, your function is not. it is more efficient.

is there way to create index on object columns?

Yong, March 07, 2007 - 2:44 pm UTC

I have client use object table type as

SQL> desc swc_ypub_file_instance
Name Null? Type
----------------------------------------- -------- ----------------------------
INSTANCE_ID NOT NULL NUMBER
FILE_ID NOT NULL NUMBER
INSTANCE_STATUS_CD NOT NULL VARCHAR2(10)
PUBLISH_USER_ID NOT NULL VARCHAR2(20)
PUBLISH_DATE NOT NULL DATE
CREATE_USER_ID NOT NULL VARCHAR2(20)
CREATE_DATE NOT NULL DATE
UPDATE_USER_ID NOT NULL VARCHAR2(20)
UPDATE_DATE NOT NULL DATE
AVAILABLE_FOR_DOWNLOAD_IND VARCHAR2(1)
OBJECT_ID NUMBER
FILE_PATH SWC_ADMIN.FILE_PATH_TABLE
ATTR_VALUE SWC_ADMIN.ATTR_VALUE_TABLE

SQL> desc SWC_ADMIN.ATTR_VALUE_TABLE
SWC_ADMIN.ATTR_VALUE_TABLE TABLE OF SWC_ADMIN.ATTR_VALUE_TYPE
Name Null? Type
----------------------------------------- -------- ----------------------------
ATTR_VALUE_ID NUMBER
ATTR_ID NUMBER
ATTR_LOCALE_CD VARCHAR2(8)
ATTR_SEQ_NUM NUMBER
CREATE_USER_ID VARCHAR2(20)
CREATE_DATE DATE
UPDATE_USER_ID VARCHAR2(20)
UPDATE_DATE DATE
ATTR_VALUE_TEXT VARCHAR2(4000)

is there way to create index on attr_id and attr_value_text
for query like this?

select TABLE(FILE_INST.attr_value)
from swc_ypub_file_instance file_inst
where inst_attval.attr_id = 204
AND inst_attval.attr_value_text ='280831063'
/

Loop in PL/SQL

A reader, March 14, 2007 - 10:17 pm UTC

Hi, Tom,

In ksh script, we can have loop like:
for fs in /oradata1 /oradata2 /oradata3
do
echo "Processing for $fs"
done

And in PL/SQL, we can do something like:
declare
cursor c1 is
select '/oradata1' as fs from dual union
select '/oradata2' from dual union
select '/oradata3' from dual;
begin
for rec in c1 loop
dbms_output.put_line('Processing for ' || rec.fs);
end loop;
end;

Is there any way to simply the PL/SQL loop to be like ksh loop?

Thanks
Tom Kyte
March 15, 2007 - 9:18 am UTC

ops$tkyte%ORA10GR2> declare
  2     type array is table of varchar2(2000);
  3     l_data array := array( 'x','y','z');
  4  begin
  5     for i in 1 .. l_data.count
  6     loop
  7         dbms_output.put_line( 'processing ' || l_data(i) );
  8     end loop;
  9  end;
 10  /
processing x
processing y
processing z

PL/SQL procedure successfully completed.

Thank you!

A reader, March 15, 2007 - 9:26 pm UTC


creating type outside package

Lakshmi, April 04, 2007 - 2:34 am UTC

Hi Tom

I have tried to use the example which you had given with minor modification (created a type within a package instead of outside) as below
CREATE OR REPLACE Package scantest AUTHID CURRENT_USER
IS


TYPE myTableType IS TABLE OF NUMBER;    

function str2tbl( p_str in varchar2 ) return myTableType;

PROCEDURE sp_bget_scanner ;

-- end of package
END;
/

CREATE OR REPLACE Package Body scantest
IS

function str2tbl( p_str in varchar2 ) return myTableType
is
    l_str   long default p_str || ',';
    l_n        number;
    l_data    myTableType := myTableType();
begin
    loop
        l_n := instr( l_str, ',' );
        exit when (nvl(l_n,0) = 0);
        l_data.extend;
        l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
        l_str := substr( l_str, l_n+1 );
    end loop;
    return l_data;
end;


PROCEDURE sp_bget_scanner 

AS   
list VARCHAR2(30); 
n NUMBER(5);  

BEGIN
 
    list := '1,2,3' ;
    dbms_output.put_line('l_list');  
      SELECT Count(SCANNER_NAME) INTO n
      FROM SMLC_A.temp_scan
      WHERE object IN ( select * from table (select cast( str2tbl( list) AS myTableType ) from dual)  );   

      dbms_output.put_line('count');
      dbms_output.put_line(n);


EXCEPTION

WHEN OTHERS
      THEN
        raise_application_error (-20299,    'SP_BGET_SCANNER: '
                                          || SQLERRM);
END;


-- end of package body
END;


I am getting the following error:
PL/SQL: ORA-00902: invalid datatype

Can you please tell us why is this?
Tom Kyte
April 04, 2007 - 10:08 am UTC

because myTableType does not exist in SQL, it exists in plsql - plsql is a layer on TOP of sql.

create the type as I have demonstrated.

working in someother case ...

Lakshmi, April 04, 2007 - 1:50 pm UTC

but type declaration in some other similar code is working ...
Can you please tell what is the difference?
CREATE OR REPLACE Package pa_smlcscanner AUTHID CURRENT_USER
IS
TYPE numarray IS TABLE OF NUMBER
      INDEX BY BINARY_INTEGER;
.....
PROCEDURE sp_get_meas_objlist(
        .....
)

AS
        l_measlist NUMARRAY;        
        c1 generic_cursor_type;

BEGIN
        OPEN c1 FOR
         SELECT MEASUREMENT_TYPE
         FROM SMLC_A.scanner_measurement_list
         WHERE SCANNER_NAME = p_scannername;
      FETCH c1 BULK COLLECT INTO l_measlist;
....



Thanks
Tom Kyte
April 04, 2007 - 3:52 pm UTC

you are fetching into a host variable, SQL never sees your plsql table, it is a program variable being retrieved into.

Variable LIKE list

Duke Ganote, January 15, 2008 - 1:58 am UTC

I had a situation where someone had added columns to our datawarehouse table, but not yet to a datamart table (in the same database).  I wanted to see what stored PL/SQL used those new columns.

I found koms' question on a variable LIKE list
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061#39386992947284
and AndersH's comment on a LIKE-list thereafter.

Since I'm querying data dictionary tables, I assume your preferred solution of text indexing doesn't apply.

So my approach was similar to AndersH's:

SQL> create table datawarehouse_table ( x number, new_column_name number );

Table created.

SQL> create table datamart_table ( x number );

Table created.

SQL> create procedure etl_procedure is
  2  begin
  3    update datawarehouse_table set new_column_name = 1;
  4  end; -- just a lame demo of ETL
  5  /

Procedure created.

SQL> select s.name, s.line
  2       , t.column_name
  3    from user_source s
  4       , ( select column_name
  5             from user_tab_columns
  6            where table_name = 'DATAWAREHOUSE_TABLE'
  7           MINUS
  8           select column_name
  9             from user_tab_columns
 10            where table_name = 'DATAMART_TABLE'
 11         ) t
 12   where UPPER(s.text) like '%'||t.column_name||'%'
 13  /

NAME                                 LINE COLUMN_NAME
------------------------------ ---------- -----------------
ETL_PROCEDURE                           3 NEW_COLUMN_NAME

or, using REGEXP_LIKE instead of ESCAPE_

Duke Ganote, January 15, 2008 - 8:37 am UTC

drop procedure etl_procedure
/
create or replace procedure etl_procedure is
begin
update datawarehouse_table set new_column_name = 1;
end; -- %newbcolumnbname%
/
select s.name, s.line
, t.column_name
from user_source s
, ( select column_name
from user_tab_columns
where table_name = 'DATAWAREHOUSE_TABLE'
MINUS
select column_name
from user_tab_columns
where table_name = 'DATAMART_TABLE'
) t
where REGEXP_LIKE( UPPER(s.text) , t.column_name )
/
NAME LINE COLUMN_NAME
------------------------------ ---------- ----------------
ETL_PROCEDURE 3 NEW_COLUMN_NAME

Something strange???

Tim, April 23, 2008 - 12:46 am UTC

Dear Tom.
Pls, see below:

SQL> select 5 * 2  as x  from dual where '1' not in (' 1             ');

         X
----------
        10

SQL> select 5 * 2  as x  from dual where '1' not in ('1               ');

no rows selected

Any idea?

Tom Kyte
April 28, 2008 - 9:08 am UTC

when comparing string literals, they are considered "char" types.

ops$tkyte%ORA10GR2> select * from dual where '1' = '1 ';

D
-
X

ops$tkyte%ORA10GR2> select * from dual where '1' = ' 1';

no rows selected


when a char(n) is compared to a char(m) (different lengths) the lesser of the two is (by ANSI definition) promoted to the other longer length

hence:
'1' = '1 '

is identical to 

'1 ' = '1 '

and 

'1' = ' 1'

is identical to

'1 ' = ' 1'


so, "one<blank>" = "one<blank>" but "one<blank>" is NOT equal to "<blank>one"


Thanks

Tim, May 01, 2008 - 3:32 am UTC


Thanks

Tim, May 01, 2008 - 3:32 am UTC


Using a refcursor with a varying in list

A reader, December 02, 2008 - 3:40 pm UTC


CREATE TABLE x AS
SELECT object_id, object_Name, object_Type
FROM   user_objects;


CREATE OR REPLACE PROCEDURE test_proc (
   p_obj_id1        NUMBER,
   p_obj_id2        NUMBER,
   p_cursor  IN OUT SYS_REFCURSOR
)
AS
   l_SQL LONG;

BEGIN
   l_SQL := 'SELECT * FROM x WHERE object_id IN (:b1, :b2)';

   OPEN p_cursor FOR l_SQL USING p_obj_id1, p_obj_id2;
END;
/

var c refcursor
set autoprint on

exec test_proc(58962, 55840, :c);

 OBJECT_ID OBJECT_NAME               OBJECT_TYPE
---------- ------------------------- -----------
     58962 X                         TABLE
     55840 SYS_C0010124              INDEX


Is there any way I can achieve something similar to the above code without using the str2table trick? Basically, there could be, say, up to 10 bind variables in the in clause. I'd like to generate a dynamic SQL based on the number of bind variables provided. I could probably code something like

   IF (<# of binds> = 1) THEN
      OPEN p_cursor FOR l_SQL Using p_obj_id1;

   ELSIF (<# of binds> = 2) THEN
      OPEN p_cursor FOR l_SQL Using p_object_id1, p_obj_id2;

   <and so forth>


However, is there a more dynamic way of doing this? Thanks.

Tom Kyte
December 09, 2008 - 9:26 am UTC

ops$tkyte%ORA10GR2> create or replace type myTableType as table of number
  2  /

Type created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure test_proc( p_inputs in myTableType, p_cursor in out sys_refcursor )
  2  as
  3  begin
  4          open p_cursor
  5           for 'select * from all_users where user_id in (select * from TABLE(:x) )' using p_inputs;
  6  end;
  7  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> exec test_proc( myTableType( 0, 5, uid ), :x )

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> print x

USERNAME                          USER_ID CREATED
------------------------------ ---------- ---------
SYS                                     0 30-JUN-05
SYSTEM                                  5 30-JUN-05
OPS$TKYTE                             481 24-NOV-08


Using a refcursor with a varying in list

A reader, December 10, 2008 - 10:22 pm UTC

Does it make sense to use the cardinality hint in this case since at the time of binding I'd already know how many elements there are in the in list?
Tom Kyte
December 11, 2008 - 7:35 am UTC

it might make sense to use a SINGLE cardinality that represents the best guess as to the average amount of elements.

What I'm trying to say is "do not use cardinality 1, cardinality 2, .... cardinality N" - just use "cardinality 12" if 12 is about the average.


else the default number of rows will be based on the block size - I have an 8k block size so 8168 is the 'guess' used for me


ops$tkyte%ORA10GR2> declare
  2          l_data sys.odcinumberlist := sys.odcinumberlist(1,2,3);
  3  begin
  4          for x in (select * from dual, table(l_data))
  5          loop
  6                  null;
  7          end loop;
  8          for x in (select * from table(dbms_xplan.display_cursor))
  9          loop
 10                  dbms_output.put_line( x.plan_table_output );
 11          end loop;
 12  end;
 13  /
SQL_ID  0bgvv3nyvwc27, child number 0
-------------------------------------
SELECT * FROM DUAL, TABLE(:B1 )
Plan hash value: 3363747227
-------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |       |       |    35 (100)|          |
|   1 |  NESTED LOOPS                      |      |  8168 | 32672 |    35   (3)| 00:00:01 |
|   2 |   TABLE ACCESS FULL                | DUAL |     1 |     2 |     2   (0)| 00:00:01 |
|   3 |   COLLECTION ITERATOR PICKLER FETCH|      |       |       |            |          |
-------------------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

Replacing IN list with WITH clause causes an error

Lise, January 29, 2009 - 6:09 am UTC

Hi,
I have two with clauses, the second references the first one. The first one references a sql type.
I have tried to recreate it here assuming you have a dummy table called Temp1 in your user schema.

DECLARE
lt_data_types t_varchar_table := t_varchar_table('NUMBER', 'DATE');
ln_count NUMBER;
rec SYS_REFCURSOR;
BEGIN
OPEN rec FOR
WITH
my_columns AS
(SELECT table_name
FROM user_tab_columns
,(SELECT DISTINCT COLUMN_VALUE FROM TABLE(CAST(lt_data_types AS t_varchar_table))) data_types
WHERE data_type = data_types.COLUMN_VALUE),
my_tables AS
(SELECT TO_CHAR((SELECT COUNT(*)
FROM my_columns m
WHERE m.table_name = 'Temp1')) "Val"
,TO_CHAR((SELECT COUNT(*)
FROM my_columns a
WHERE a.table_name = 'Temp1')) "Val2"
FROM dual)
SELECT COUNT(*)
INTO ln_count
FROM my_tables;
dbms_output.put_line('Got ' || TO_CHAR(ln_count));
END;

It falls over with a ORA error 00932. It works fine if I do not use the SQL type. It also works fine if I take away the second column named "Val2".
Any ideas would be much appreciated.
Thanks
Tom Kyte
January 30, 2009 - 2:11 pm UTC

i cannot even get it to go that far!

probably because I don't have a table named my_tables :(

nor the types

no my_columns....




Here are the details

Lise, February 05, 2009 - 4:32 am UTC

my_tables is defined within the WITH clause as a selection from my_columns. my_columns is also defined within the WITH clause just above the my_tables. This one selects from user_tab_columns.

Sorry - type declaration is:
CREATE OR REPLACE TYPE t_varchar_table IS TABLE OF VARCHAR2(100);

I am running 9i Rel 2
Tom Kyte
February 05, 2009 - 10:59 am UTC

appears to be 9i specific, works in 10g. It is happening with the double reference to my_columns in the scalar subquery

ops$tkyte%ORA9IR2> DECLARE
  2  lt_data_types t_varchar_table := t_varchar_table('NUMBER', 'DATE');
  3  ln_count NUMBER;
  4  rec SYS_REFCURSOR;
  5  BEGIN
  6
  7  WITH
  8          data_types as
  9          (SELECT COLUMN_VALUE FROM TABLE(CAST(lt_data_types AS t_varchar_table))),
 10      my_columns AS
 11           (SELECT table_name
 12            FROM   user_tab_columns, data_types
 13            WHERE data_type = data_types.COLUMN_VALUE),
 14      my_tables AS
 15           (SELECT TO_CHAR((SELECT COUNT(*)
 16                           FROM   my_columns m
 17                           WHERE  m.table_name = 'Temp1')) "Val"
 18                  /* ,TO_CHAR((SELECT COUNT(*)
 19                           FROM   my_columns a
 20                           WHERE  a.table_name = 'Temp1')) "Val2"                            */
 21            FROM dual)
 22     SELECT COUNT(*) into ln_count
 23     FROM   my_tables;
 24     dbms_output.put_line('Got ' || TO_CHAR(ln_count));
 25  END;
 26  /
Got 1

PL/SQL procedure successfully completed.


you would have to code by the way:

open cursor for ....
FETCH cursor into ....
close cursor

you would not use a select into with an explicit cursor. You would use an implicit cursor to "into"

Joining two tables where matching column in one table is a comma separated list

A Reader, March 09, 2009 - 5:20 pm UTC

Tom,

I am looking for help to come up with a sql to join two tables (test, test2) where the data in the matching column of one the tables (test) is a comma separated list. I tried with pl/sql function to parse the comma separated string in 'test' table and exploding in each line into many and then matching it with the 'test2' table, however,
performance of the pl/sql logic is very slow. Would it possible to have a just sql for the matching instead of using a pl/sql? I would really appreciate your help for
this. Thanks.

create table test(x varchar2(10), y varchar2(50));

insert into test values('A110','ABD,AAC,ABA,ABC');

insert into test values('A111','ABD,ABC');

insert into test values('A112','ABA,AAC');




create table test2(y varchar2(3));

insert into test2 values('ABC');

insert into test2 values('ABD');



xe>select * From test;

X Y
---------- ---------------------
A110 ABD,AAC,ABA,ABC
A111 ABD,ABC
A112 ABA,AAC

xe>select * from test2;

Y
---
ABC
ABD



--Results

xe>select * from test where y in (select y from test2);

X Y
---------- ----------------
A110 ABD,AAC,ABA,ABC
A111 ABD,ABC


Tom Kyte
March 10, 2009 - 2:39 am UTC

.... where the data in the matching column of one the tables (test) is a comma separated list ....

yuck, can you say "will never perform reasonably in our lifetime" ?


You understand that indexing isn't going to help, this will always be a full scan of at least the table with the CSV (comma separate values) list?

can we PLEASE fix this - you should never store a CSV list like that, this will never return reasonable results performance wise.


Can I do this in pure SQL? Sure.
Would I do this in pure SQL? Never.


Bind in list and Oracle's sql parsing

Vijay, July 12, 2009 - 1:17 am UTC

Hi Tom
I was confronted with a seemingly complex sql for binding for an in-list. I can say I understand binding in the simple sense. My confution is why is it so convoluted for in-list.

Is it because the data-type of the terminal parameter is not known in the inlist & oracle cannot allocate space upfront upfront?
So what exactly happens in hard-parsing. When is hard parsing considered complete and useful enough that oracle caches it for subsequent soft parse?

One can argue why a subquery cannot be a bind variable or even a table name be a bind variable for that matter.

In the following SQL example, there are 3 selects. Is everything in the "with" clause reparsed in sebsequent issues to this sql with different in-list?

Would I be wrong if I said that only the last select is hard parsed and kept for subsequent soft parse lookups whereas the first two selects encountered will always be hard parsed? Since 'abc' below is a pseudo table, it will be re computed with each new comma separated list.



WITH abc as (
SELECT TRIM(SUBSTR(txt, INSTR(txt, ',', 1, level) + 1,
INSTR(txt, ',', 1, level+1) -
INSTR(txt, ',', 1, level) -1)
) as token
FROM (SELECT ','||:clid_inlist||',' txt FROM dual)
CONNECT BY level <= length(:clid_inlist0)-length(replace(:clid_inlist0,',',''))+1 )
select e.fname, e.lname from emp e, abc list where e.clientid = list.token ;

Vijay
Tom Kyte
July 14, 2009 - 5:34 pm UTC

http://asktom.oracle.com/Misc/varying-in-lists.html

... Is it because the data-type of the terminal parameter is not known in the
inlist & oracle cannot allocate space upfront upfront?
...

nothing like that at all.

where column IN ({set})

A set is either

a) a discrete set, you supply it, like "where x in (1,2,4,5)" - there is nothing to "parse" here, there are 4 distinct ELEMENTS, it is not a string to be parsed, there is no terminator concept

b) a result set, you give a query



I only see one sql statement there at the end - there is no "last select", there is only ONE sql statement. It happens to have the keyword select in it many times, but there is only ONE sql statement there to be parsed and executed.

varying dynamic column list in query

Michael, August 07, 2009 - 12:35 pm UTC

Hi Tom,

A customer is having several tables with different number of columns but same data type (number).

He wants to =>
select * from table (function ('table_name'));

How does the function have to look like?

Thanks,
Michael
Tom Kyte
August 07, 2009 - 2:39 pm UTC

not going to happen

A plsql pipelined function returns a collection of object types.

The object type is static, compiled into the database.

It has a fixed number of attributes.


Only if your customer is willing to receive a collection variable (like an array) with the numbers in it - fixing the number of columns needed to be "one" - could this work.

Split comma saparated list

Ravi B, November 19, 2010 - 4:44 pm UTC

Hi Tom,

I want to split a 'token' separated IP address string into rows.

Following is a simple test case.

drop table test;
create table test(id number,v varchar2(1000));

insert into test values(1,'192.168.1.1<TEST,>192.168.1.2<TEST,>192.168.1.4<TEST,>192.168.1.6<TEST,>192.168.1.7<TEST,>192.168.1.8<TEST,>');
insert into test values(2,'193.168.1.1<TEST,>193.168.1.2<TEST,>193.168.1.4<TEST,>193.168.1.6<TEST,>193.168.1.7<TEST,>');

select
trim( substr (str,
instr (str, ',', 1, level ) + 1,
instr (str, ',', 1, level+1)
- instr (str, ',', 1, level) -1 ) )
as token
from (select id,','||rtrim(ltrim(replace(v,'<TEST,>',','),','),',')||',' str
from test
--where id=1
)
connect by level <=
length(str)-length(replace(str,',','')) -1;

I am expecting 6+5 IP addresses (rows) from the SQL. But it gives 94 rows. The SQL works if i limit by each id.

Could you please explain how connect by level works in this case.

Thanks!
Tom Kyte
November 20, 2010 - 5:22 am UTC

ops$tkyte%ORA10GR2> select id, column_value
  2    from (select id, ','||rtrim(ltrim(replace(v,'<TEST,>',','),','),',')||','  str from test) test,
  3         TABLE(
  4         cast( multiset( select trim( substr (str, instr (str, ',', 1, level  ) + 1,
  5                                instr (str, ',', 1, level+1) - instr (str, ',', 1, level) -1 ) )
  6                           from dual
  7                        connect by level <= length(str)-length(replace(str,',','')) -1 ) as sys.odciVarchar2List )
  8                      )
  9  /

        ID COLUMN_VALUE
---------- ------------------------------
         1 192.168.1.1
         1 192.168.1.2
         1 192.168.1.4
         1 192.168.1.6
         1 192.168.1.7
         1 192.168.1.8
         2 193.168.1.1
         2 193.168.1.2
         2 193.168.1.4
         2 193.168.1.6
         2 193.168.1.7

11 rows selected.


that little connect by level trick presumes a single row - else you end up getting a lot of rows connected to rows you didn't mean to connect them to.

Using the TABLE unnesting lets you work a row at a time and gets you want you wanted.

SQL ANSI compliant

navrsale, January 31, 2011 - 2:07 pm UTC

not sure if the original question answer is SQL ANSI compliant? if not, would it be possible to make it compliant so that the code is portable?

Reason behind abrupt behaviour

Sid, July 28, 2011 - 8:52 am UTC

Hi Tom,

Your solution worked for me..But i was wondering why the below code wasn't working...I m a newbie in Pl/sql so not have that much knowledge..just thought if you could explain the reason behind such behaviour as depicted below :

DECLARE
Var1 VARCHAR2(30);
Var2 VARchar2(50);
strr varchar2(25);
gllist varchar2(100);
glist_fin varchar2(100);

BEGIN
strr := '''' ||','||'''';
gllist := 'NAME1,NAME2,NAME3';
glist_fin := ''''||replace(gllist,',',trim(strr))||''''; -- converting value into Quoted comma separated list
dbms_output.put_line(‘val of Glist_fin :’ ||glist_fin);


SELECT coulmn1 into var2
FROM TABLE_NAME
WHERE groupleader IN (glist_fin);
/* to behave like Groupleader in ('NAME1','NAME2','NAME3') */

dbms_output.put_line(var1);
dbms_output.put_line(var2);

Exception when NO_DATA_FOUND Then
dbms_output.put_line('Exception is found');
END;

*************************
Output I get is :
*************************
anonymous block completed
val of Glist_fin : 'NAME1','NAME2','NAME3'
Exception is found

**************************
Problem faced-
If we try executing
SELECT DISTINCT AMDOCS_VP into var2
FROM MST_LK_MOTSGL
WHERE groupleader IN ('NAME1','NAME2','NAME3');

We get result, but when I dynamically pass it through a variable it’s showing No data found.. 

Tom Kyte
July 28, 2011 - 7:29 pm UTC



http://asktom.oracle.com/Misc/varying-in-lists.html

tells you why your query doesn't work.

Reason behind abrupt behaviour

Sid, July 28, 2011 - 9:10 am UTC

Hi Tom,

A litle correction in the problem faced

DECLARE

Var1 VARCHAR2(30);
Var2 VARchar2(50);
strr varchar2(25);
gllist varchar2(100);
glist_fin varchar2(100);

BEGIN
strr := '''' ||','||'''';
gllist := 'NAME1,NAME2,NAME3';
glist_fin := ''''||replace(gllist,',',trim(strr))||''''; -- converting value into Quoted comma separated list
dbms_output.put_line(‘val of Glist_fin :’ ||glist_fin);


SELECT coulmn1 into var2
FROM TABLE_NAME
WHERE groupleader IN (glist_fin);
/* to behave like Groupleader in ('NAME1','NAME2','NAME3') */

dbms_output.put_line(var1);
dbms_output.put_line(var2);

Exception when NO_DATA_FOUND Then
dbms_output.put_line('Exception is found');
END;

*************************
Output I get is :
*************************
anonymous block completed
val of Glist_fin : 'NAME1','NAME2','NAME3'
Exception is found

**************************
Problem faced-
If we try executing
SELECT coulmn1 into var2
FROM table_name
WHERE groupleader IN ('NAME1','NAME2','NAME3');

We get result, but when I dynamically pass it through a variable it’s showing No data found.. 

Variable Inlist

Shimmy, March 15, 2012 - 7:46 am UTC

If there are no indexes on the table(SK_IDS), is there any advantage using Query1 instead of Query2?
CREATE OR REPLACE PACKAGE CUSTOM.SK_VARIABLE_INLIST_SQL AS  
    FUNCTION IN_LIST_NUM(P_STRING IN VARCHAR2) RETURN sys.odcinumberList ;
END SK_VARIABLE_INLIST_SQL;
/

CREATE OR REPLACE PACKAGE BODY CUSTOM.SK_VARIABLE_INLIST_SQL AS 
    FUNCTION IN_LIST_NUM(P_STRING IN VARCHAR2) 
    RETURN sys.odcinumberList IS 
        L_STRING        LONG DEFAULT P_STRING || ',';
        L_DATA          sys.odcinumberList := sys.odcinumberList();
        N               NUMBER;
    BEGIN
        LOOP
            EXIT WHEN L_STRING IS NULL;
            N := INSTR(L_STRING, ',');
            L_DATA.EXTEND;
            L_DATA(L_DATA.COUNT) :=  TRIM(SUBSTR(L_STRING, 1, N-1 ));
            L_STRING := SUBSTR(L_STRING, N+1);
        END LOOP;
        RETURN L_DATA;
    END IN_LIST_NUM;
END SK_VARIABLE_INLIST_SQL;
/

DROP TABLE SK_IDS;

CREATE TABLE SK_IDS AS
(SELECT MOD(LEVEL, 10) DEPT, LEVEL ID
 FROM DUAL
 CONNECT BY LEVEL <= 100);
 
VARIABLE VAR_IN VARCHAR2(1000);
exec :VAR_IN := '2,3'

Query1)
SELECT  DEPT, ID
FROM SK_IDS 
WHERE DEPT IN (SELECT *
        FROM TABLE(SK_VARIABLE_INLIST_SQL.IN_LIST_NUM(:VAR_IN)));

Query2)       
SELECT DEPT, ID
FROM SK_IDS
WHERE INSTR(','||:VAR_IN||',', ','||DEPT||',') > 0;

Tom Kyte
March 15, 2012 - 7:53 am UTC

probably not - depends on how expensive the instr() function is going to be to evaluate.

IN list with varying elements and wild card

A reader, December 03, 2012 - 9:51 am UTC

Tom,
Someone asked me this question and my first response was "If this can be done, only Tom Kyte knows how", so I ask here:

The developer wants to do a variable IN list with some elements being a wild card. For example he is selecting name and wants:

select ... from table where ... name IN (A*, B*...)

where A* will be all name beginning with A, B* will be all names beginning with B and so on.

I am not sure if elements in an IN list can be wild carded as in a LIKE clause.

Thanks...

Tom Kyte
December 03, 2012 - 11:39 am UTC

they cannot be, not in IN, IN is translated into "=" - always.

that said...

ops$tkyte%ORA11GR2> variable txt varchar2(100)
ops$tkyte%ORA11GR2> exec :txt := 'DBMS_OFF%, DBMS_AWR%'

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> drop table t;

Table dropped.

ops$tkyte%ORA11GR2> create table t as select * from all_objects;

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> with data
  2  as
  3  (
  4  select
  5    trim( substr (txt,
  6          instr (txt, ',', 1, level  ) + 1,
  7          instr (txt, ',', 1, level+1)
  8             - instr (txt, ',', 1, level) -1 ) )
  9      as token
 10     from (select ','||:txt||',' txt
 11             from dual)
 12   connect by level <=
 13      length(:txt)-length(replace(:txt,',',''))+1
 14   )
 15  select owner, object_name, object_type
 16    from t, data
 17   where t.object_name like data.token
 18  /

OWNER                          OBJECT_NAME                    OBJECT_TYPE
------------------------------ ------------------------------ -------------------
SYS                            DBMS_OFFLINE_SNAPSHOT          PACKAGE
SYS                            DBMS_OFFLINE_RGT               PACKAGE
PUBLIC                         DBMS_OFFLINE_RGT               SYNONYM
SYS                            DBMS_OFFLINE_INTERNAL          PACKAGE
SYS                            DBMS_OFFLINE_UTL               PACKAGE
SYS                            DBMS_OFFLINE_OG                PACKAGE
SYS                            DBMS_OFFLINE_SNAPSHOT          PACKAGE BODY
PUBLIC                         DBMS_OFFLINE_SNAPSHOT          SYNONYM
SYS                            DBMS_OFFLINE_RGT               PACKAGE BODY
SYS                            DBMS_OFFLINE_UTL               PACKAGE BODY
SYS                            DBMS_OFFLINE_INTERNAL          PACKAGE BODY
SYS                            DBMS_OFFLINE_OG                PACKAGE BODY
PUBLIC                         DBMS_OFFLINE_OG                SYNONYM
SYS                            DBMS_AWR_REPORT_LAYOUT         PACKAGE
SYS                            DBMS_AWR_REPORT_LAYOUT         PACKAGE BODY

15 rows selected.


if your like terms are not mutually exclusive (if something could be like two or more of your items) then you might need a distinct or some other operation to get the right output - here we didn't since my search terms are mutually exclusive...

IN list with varying elements and wild card

A reader, December 03, 2012 - 3:35 pm UTC

Vertigo just looking at the query...How do you do it? Amazing. Thanks...!!!

Explanation from another angle...

Jose Laurindo Chiappa, December 04, 2012 - 7:28 am UTC

  First, the gist of it is the use of the connect by level trick to generate data (a old one but very very useful, see http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:40476301944675 ) , together a WITH section - think in WITH as a "temporary table", a way to avoid creating a physical table : the docs are full with examples, and so is this site...
  Let´s see only the "temporary table with data" section isolated :
  
chiappa@O10GR2::SQL>variable txt varchar2(100)
chiappa@O10GR2::SQL>exec :txt := 'DBMS_OFF%, DBMS_AWR%';
chiappa@O10GR2::SQL>with data
  2    as
  3    (
  4    select
  5      trim( substr (txt,
  6            instr (txt, ',', 1, level  ) + 1,
  7            instr (txt, ',', 1, level+1)
  8               - instr (txt, ',', 1, level) -1 ) )
  9        as token
 10       from (select ','||:txt||',' txt
 11               from dual)
 12     connect by level <=
 13        length(:txt)-length(replace(:txt,',',''))+1
 14     )
 15    select *
 16*     from data;

 TOKEN
----------------------------------------------------------------------------------------------------------------------------------
DBMS_OFF%
DBMS_AWR%

See ? But don´t be misguided by the trick - the point here is that you CAN use comparision values to the LIKE operator inside a table : see this below, with a real table to be clearer, focusing on the LIKE values :

chiappa@O10GR2::SQL>create table test_token(str_token varchar2(20));

Tabela criada.

chiappa@O10GR2::SQL>insert into test_token values ('DBMS_OFF%');

chiappa@O10GR2::SQL>insert into test_token values ('DBMS_AWR%');

chiappa@O10GR2::SQL>select * from test_token;

STR_TOKEN
--------------------
DBMS_OFF%
DBMS_AWR%

chiappa@O10GR2::SQL>select owner, object_name, object_type from dba_objects, test_token
  2  where object_name like str_token;

OWNER            OBJECT_NAME                    OBJECT_TYPE
---------------- ------------------------------ -------------------
SYS              DBMS_OFFLINE_SNAPSHOT          PACKAGE
SYS              DBMS_OFFLINE_SNAPSHOT          PACKAGE BODY
PUBLIC           DBMS_OFFLINE_SNAPSHOT          SYNONYM
SYS              DBMS_OFFLINE_RGT               PACKAGE
PUBLIC           DBMS_OFFLINE_RGT               SYNONYM
SYS              DBMS_OFFLINE_RGT               PACKAGE BODY
SYS              DBMS_OFFLINE_OG                PACKAGE
SYS              DBMS_OFFLINE_INTERNAL          PACKAGE
SYS              DBMS_OFFLINE_UTL               PACKAGE
SYS              DBMS_OFFLINE_UTL               PACKAGE BODY
SYS              DBMS_OFFLINE_INTERNAL          PACKAGE BODY
SYS              DBMS_OFFLINE_OG                PACKAGE BODY
PUBLIC           DBMS_OFFLINE_OG                SYNONYM

13 rows selected.

Same thing if I use the "trick" avoiding a physical table creation for the comparision values - and let me create a T table just to follow Tom´s example :

chiappa@O10GR2::SQL>create table t as select * from dba_objects;

Table created.

chiappa@O10GR2::SQL>with data
  2   as
  3   (
  4   select
  5     trim( substr (txt,
  6           instr (txt, ',', 1, level  ) + 1,
  7           instr (txt, ',', 1, level+1)
  8              - instr (txt, ',', 1, level) -1 ) )
  9       as token
 10      from (select ','||:txt||',' txt
 11              from dual)
 12    connect by level <=
 13       length(:txt)-length(replace(:txt,',',''))+1
 14    )
 15   select owner, object_name, object_type
 16     from t, data
 17    where t.object_name like data.token
 18   /

OWNER            OBJECT_NAME                    OBJECT_TYPE
---------------- ------------------------------ -------------------
SYS              DBMS_OFFLINE_SNAPSHOT          PACKAGE
SYS              DBMS_OFFLINE_SNAPSHOT          PACKAGE BODY
PUBLIC           DBMS_OFFLINE_SNAPSHOT          SYNONYM
SYS              DBMS_OFFLINE_RGT               PACKAGE
PUBLIC           DBMS_OFFLINE_RGT               SYNONYM
SYS              DBMS_OFFLINE_RGT               PACKAGE BODY
SYS              DBMS_OFFLINE_OG                PACKAGE
SYS              DBMS_OFFLINE_INTERNAL          PACKAGE
SYS              DBMS_OFFLINE_UTL               PACKAGE
SYS              DBMS_OFFLINE_UTL               PACKAGE BODY
SYS              DBMS_OFFLINE_INTERNAL          PACKAGE BODY
SYS              DBMS_OFFLINE_OG                PACKAGE BODY
PUBLIC           DBMS_OFFLINE_OG                SYNONYM

13 rows selected.

 Hope the logic is a little clearer now... 
 
  Best regards,
  
    J. Laurindo Chiappa
 

Thanks Jose

A reader, December 05, 2012 - 8:21 am UTC

It makes perfect sense.

Extensible Optimizer with two parameters

Rajeshwaran, Jeyabal, February 03, 2013 - 1:21 am UTC

Tom,

I was reading your column on Oracle magainze and working with Table functions having 2 parameters and it seems Extensible optimizer doesn't work correctly, Can you help me what am i doing wrong?
http://www.oracle-developer.net/display.php?id=427
From the below plan, you can see that I pass cardinality as 5, but Explain plan still shows it as 1.

The scripts i used for this demo is provided below.
rajesh@ORA11G>
rajesh@ORA11G> select * from table(parse_list('a,b,c,d,e,f',5));

ODCIStatsTableFunction-p_num_rows = 5
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 58440541

------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |            |     1 |     2 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |     1 |     2 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

rajesh@ORA11G>

Script used for Testing.
rajesh@ORA11G> create or replace type vctype
  2  is table of varchar2(20);
  3  /

Type created.
rajesh@ORA11G>
rajesh@ORA11G> create or replace function
  2  parse_list(p_in varchar2,p_num_rows in number)
  3  return vctype
  4  is
  5     l_data vctype := vctype();
  6     l_txt varchar2(40) := p_in||',';
  7  begin
  8     for x in (
  9             select regexp_substr(l_txt,'[^,]+',1,level) val
 10             from dual
 11             connect by level <= ( length(p_in) -
 12                     length(replace(p_in,','))+1 ) )
 13     loop
 14             l_data.extend;
 15             l_data(l_data.last) := x.val;
 16     end loop;
 17     return l_data;
 18  end;
 19  /

Function created.
rajesh@ORA11G> create or replace type parse_typ
  2  is object
  3  (  x  number,
  4
  5     static function ODCIGetInterfaces
  6     (       p_out out sys.ODCIObjectList  )
  7     return number,
  8
  9     static function ODCIStatsTableFunction
 10     (       func IN SYS.ODCIFuncInfo,
 11             outStats OUT SYS.ODCITabFuncStats,
 12             argDesc IN SYS.ODCIArgDescList,
 13             p_in in varchar2,
 14             p_num_rows in number )
 15     return number
 16  );
 17  /

Type created.
rajesh@ORA11G> create or replace type body parse_typ
  2  is
  3     static function ODCIGetInterfaces
  4     ( p_out out sys.ODCIObjectList)
  5     return number
  6     is
  7     begin
  8             p_out := sys.odciobjectlist( sys.odciobject('SYS','ODCISTATS2') );
  9             return sys.odciconst.Success;
 10     end;
 11
 12     static function ODCIStatsTableFunction
 13     (       func IN SYS.ODCIFuncInfo,
 14             outStats OUT SYS.ODCITabFuncStats,
 15             argDesc IN SYS.ODCIArgDescList,
 16             p_in in varchar2,
 17             p_num_rows in number )
 18     return number
 19     is
 20     begin
 21             dbms_output.put_line ( ' ODCIStatsTableFunction-p_num_rows = '||p_num_rows);
 22             outStats := SYS.ODCITabFuncStats( p_num_rows );
 23     end;
 24  end;
 25  /

Type body created.
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> associate statistics with functions
  2  parse_list using parse_typ;

Statistics associated.
rajesh@ORA11G> variable x varchar2(20);
rajesh@ORA11G> variable y number;
rajesh@ORA11G> exec :x := 'a,b,c,d,e';

PL/SQL procedure successfully completed.
rajesh@ORA11G> exec :y := 5;

PL/SQL procedure successfully completed.
rajesh@ORA11G>
rajesh@ORA11G>

Tom Kyte
February 04, 2013 - 9:52 am UTC

 12     static function ODCIStatsTableFunction
 13     (       func IN SYS.ODCIFuncInfo,
 14             outStats OUT SYS.ODCITabFuncStats,
 15             argDesc IN SYS.ODCIArgDescList,
 16             p_in in varchar2,
 17             p_num_rows in number )
 18     return number
 19     is
 20     begin
 21             dbms_output.put_line ( ' ODCIStatsTableFunction-p_num_rows = '||p_num_rows);
 22             outStats := SYS.ODCITabFuncStats( p_num_rows );
 23                     RETURN ODCIConst.success;
 24     end;
 25  end;
 26  /



you were missing a return in your function. when we invoked it, it was failing every time.

Doesn't work for bind variables

Rajeshwaran, Jeyabal, February 04, 2013 - 10:04 am UTC

Does that doesn't support bind variables?

rajesh@ORA11G> variable x varchar2(20);
rajesh@ORA11G> variable y number;
rajesh@ORA11G> exec :x := 'a,b,c,d,e';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02
rajesh@ORA11G> exec :y := 5;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03
rajesh@ORA11G> select * from table(parse_list(:x,:y));

Elapsed: 00:00:00.09

Execution Plan
----------------------------------------------------------
Plan hash value: 58440541

------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |            |     1 |     2 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |     1 |     2 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
        211  recursive calls  
          0  sorts (disk)
          5  rows processed

rajesh@ORA11G> select * from table(parse_list('a,b,c',3));
Elapsed: 00:00:00.08

Execution Plan
----------------------------------------------------------
Plan hash value: 58440541

------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |            |     3 |     6 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| PARSE_LIST |     3 |     6 |    29   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
       1185  recursive calls
          0  sorts (disk)
          3  rows processed

Hrishikesh Deshmukh, April 19, 2013 - 7:58 pm UTC

Tom,

I am using Oracle 11.2. I have created two user defined functions and use them when creating a view. But i always get a error table or view does not exist on the function call.

My function:
create or replace
function CZF_IS_NUMERIC (p_string varchar2)
return NUMBER AUTHID DEFINER
as
l_number number;
begin
l_number := p_string;
return 1;
exception
when others then
return 0;
end;

View: The view is really big...The part where i get error is below

create view RDC_wz.wz_observation_fact_view as
(select
a.etl_source_id,
a.etl_date,
a.data_source_id
from rdc_wz.wz_svc_allergy a
join rdc_wz.wz_service s
on a.service_id = s.service_id
and s.master_service_id is not null
union all
select p.svc_procedure_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'PR_' ||
CASE WHEN RDC_CZ.CZF_IS_NUMERIC(p.proc_code) = 1 THEN
CASE
WHEN length(RDC_CZ.STRIPCHARACTERS(p.proc_code, '[^0-9]')) = 5 THEN 'CPT'
WHEN length(RDC_CZ.STRIPCHARACTERS(p.proc_code, '[^0-9]')) in (3,4) THEN 'ICD-9'
ELSE NULL
END
WHEN RDC_CZ.CZF_IS_NUMERIC(RDC_CZ.STRIPCHARACTERS(p.proc_code, '^A-Z') = 0 THEN
CASE
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,1,1)) = 0 THEN 'HCPCS'
WHEN substr(p.proc_code,5,1)='F' THEN 'CPT-2'
WHEN substr(p.proc_code,5,1)='T' THEN 'CPT-3'
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,5,1)) = 0 THEN NULL
ELSE NULL
END
END
and s.master_service_id is not null
);

ERROR:
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,5,1)) = 0 THEN 'NULL'
*
ERROR at line 54:
ORA-00942: table or view does not exist

The view might look wrong as i have just put some bits of the file.

RDC_CZ is the schema under which the functions have been created. Execute and debug has been granted to PUBLIC. Therefore i dont think there are any privilege issues.

Could you please guide me on this as to why am i getting a Table or view does not exist on a FUNCTION call.

Thanks for all the help!
Tom Kyte
April 22, 2013 - 8:04 pm UTC

I'd need a full up example I can run to reproduce with - do you have one?

I don't see anything obvious...

Hrishikesh Deshmukh, April 23, 2013 - 2:19 pm UTC

create view RDC_wz.wz_observation_fact_view as
(
select a.patient_allergy_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'ME_' || a.med_code_system || '_' || a.med_code as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'ALLERGY' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
null as unit_of_measure,
null as val_varchar,
null as val_num,
null as test_time,
coalesce(a.onset_date, s.svc_start_date) as eff_date,
coalesce(a.resolve_date, s.svc_end_Date) as exp_date,
null as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
a.etl_job_id,
a.etl_source_id,
a.etl_date,
a.data_source_id
from rdc_wz.wz_svc_allergy a
join rdc_wz.wz_service s
on a.service_id = s.service_id
and s.master_service_id is not null
union all
select p.svc_procedure_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'PR_' ||
CASE WHEN RDC_CZ.CZF_IS_NUMERIC(p.proc_code) = 1 THEN
CASE
WHEN length(RDC_CZ.STRIPCHARACTERS(p.proc_code, '[^0-9]')) = 5 THEN 'CPT'
WHEN length(RDC_CZ.STRIPCHARACTERS(p.proc_code, '[^0-9]')) in (3,4) THEN 'ICD-9'
ELSE NULL
END
WHEN RDC_CZ.CZF_IS_NUMERIC(RDC_CZ.STRIPCHARACTERS(p.proc_code, '^A-Z')) = 0 THEN
CASE
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,1,1)) = 0 THEN 'HCPCS'
WHEN substr(p.proc_code,5,1)='F' THEN 'CPT-2'
WHEN substr(p.proc_code,5,1)='T' THEN 'CPT-3'
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,5,1)) = 0 THEN 'NULL'
ELSE NULL
END
END
|| '_' || p.proc_code as code_id,
p.modifier_1 as code_modifier_1,
p.modifier_2 as code_modifier_2,
p.modifier_3 as code_modifier_3,
p.modifier_4 as code_modifier_4,
'PROCEDURE' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
null as unit_of_measure,
null as val_varchar,
null as val_num,
null as test_time,
coalesce(p.proc_start_date, s.svc_start_date) as eff_date,
coalesce(p.proc_end_date, s.svc_end_Date) as exp_date,
null as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
p.etl_job_id,
p.etl_source_id,
p.etl_date,
p.data_source_id
from rdc_wz.wz_svc_procedure p
join rdc_wz.wz_service s
on p.service_id = s.service_id
and s.master_service_id is not null
union all
select d.svc_diagnosis_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'DI_' || case when d.dx_code_type ='ICD9' THEN 'ICD-9-CM'
ELSE
d.dx_code_type
end
--|| '_' || replace(d.dx_code,'.','') as code_id,
|| '_' || d.dx_code as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'DIAGNOSIS' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
null as unit_of_measure,
null as val_varchar,
null as val_num,
null as test_time,
coalesce(d.diagnosis_sympt_onset_date, d.diagnosis_date, s.svc_start_date) as eff_date,
coalesce(d.diagnosis_resolve_date, diagnosis_date, s.svc_end_Date) as exp_date,
null as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
d.etl_job_id,
d.etl_source_id,
d.etl_date,
d.data_source_id
from rdc_wz.wz_svc_diagnosis d
join rdc_wz.wz_service s
on d.service_id = s.service_id
and s.master_service_id is not null
union all
select l.lab_result_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'LA_' || l.test_code_system || '_' || l.test_code as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'LAB' as fact_type,
l.collection_time as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
l.result_unit as unit_of_measure,
result_value as val_varchar,
case
when RDC_CZ.STRIPCHARACTERS(result_value, '[^.0-9]')='.' then '0'
when RDC_CZ.STRIPCHARACTERS(result_value, '[^.0-9]')='..' then '0'
when substr(RDC_CZ.STRIPCHARACTERS(result_value, '[^.0-9]'),LENGTH(RDC_CZ.STRIPCHARACTERS(result_value, '[^.0-9]')),1)='.' THEN substr(RDC_CZ.STRIPCHARACTERS(result_value, '[^.0-9]'),1,LENGTH(RDC_CZ.STRIPCHARACTERS(result_value, '[^.0-9]')) -1)
ELSE
RDC_CZ.STRIPCHARACTERS(result_value, '[^.0-9]')
END as val_num,
l.test_time as test_time,
coalesce(l.test_time, s.svc_start_date) as eff_date,
coalesce(l.test_time, s.svc_end_Date) as exp_date,
l.result_value as result_value,
l.result_status as result_status,
l.result_value_type as result_value_type,
l.ref_normal_min as ref_normal_min,
l.ref_normal_max as ref_normal_max,
l.ref_range_eff_date as ref_range_eff_date,
l.abnormal_flag as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
l.etl_job_id,
l.etl_source_id,
l.etl_date,
l.data_source_id
from rdc_wz.wz_svc_lab_result l
join rdc_wz.wz_service s
on l.service_id = s.service_id
where test_code is not null
and result_value not like '%.%.%'
and substr(RDC_CZ.STRIPCHARACTERS(result_value,'[^.0-9]'),1,1) in ('0','1','2','3','4','5','6','7','8','9')
and s.master_service_id is not null
union all
Select --1000000000000||s.service_id as observation_fact_id,
s.service_id as observation_fact_id,
s.service_id as encounter_id,
null as observation_group_id,
'PR_CPT_99213' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'PROCEDURE' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
null as unit_of_measure,
null as val_varchar,
null as val_num,
null as test_time,
s.svc_start_date as eff_date,
s.svc_end_Date as exp_date,
null as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
s.etl_job_id,
s.etl_source_id,
s.etl_date,
s.data_source_id
from rdc_wz.wz_service s
where s.type_code_orig = 'OFFICE VISIT'
union all
select o.svc_observation_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'LA_SNOMED_364589006' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'OBSERVATION' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
o.value_unit as unit_of_measure,
observation_value as val_varchar,
case when substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')),1)='.' THEN substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),1,LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')) -1)
ELSE
RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')
END as val_num,
o.observation_date as test_time,
coalesce(o.observation_date, s.svc_start_date) as eff_date,
coalesce(o.observation_date, s.svc_end_Date) as exp_date,
o.observation_value as result_value,
o.value_status as result_status,
o.observation_type as result_value_type,
o.ref_min as ref_normal_min,
o.ref_max as ref_normal_max,
o.ref_range_eff_date as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
o.etl_job_id,
o.etl_source_id,
o.etl_date,
o.data_source_id
from rdc_wz.wz_svc_observation o
join rdc_wz.wz_service s
on o.service_id = s.service_id
where observation_type='BIRTH WEIGHT'
and s.master_service_id is not null
union all
select o.svc_observation_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'DI_SNOMED-CT_60621009' as code_id,
--'LA_SNOMED_248358009' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'OBSERVATION' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
o.value_unit as unit_of_measure,
observation_value as val_varchar,
case when substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')),1)='.' THEN substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),1,LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')) -1)
ELSE
RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')
END as val_num,
o.observation_date as test_time,
coalesce(o.observation_date, s.svc_start_date) as eff_date,
coalesce(o.observation_date, s.svc_end_Date) as exp_date,
o.observation_value as result_value,
o.value_status as result_status,
o.observation_type as result_value_type,
o.ref_min as ref_normal_min,
o.ref_max as ref_normal_max,
o.ref_range_eff_date as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
o.etl_job_id,
o.etl_source_id,
o.etl_date,
o.data_source_id
from rdc_wz.wz_svc_observation o
join rdc_wz.wz_service s
on o.service_id = s.service_id
where observation_type='BMI'
and s.master_service_id is not null
union all
select o.svc_observation_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'LA_SNOMED_424927000' as code_id,--not sure
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'OBSERVATION' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
o.value_unit as unit_of_measure,
observation_value as val_varchar,
case when substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')),1)='.' THEN substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),1,LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')) -1)
ELSE
RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')
END as val_num,
observation_date as test_time,
coalesce(o.observation_date, s.svc_start_date) as eff_date,
coalesce(o.observation_date, s.svc_end_Date) as exp_date,
o.observation_value as result_value,
o.value_status as result_status,
o.observation_type as result_value_type,
o.ref_min as ref_normal_min,
o.ref_max as ref_normal_max,
o.ref_range_eff_date as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
o.etl_job_id,
o.etl_source_id,
o.etl_date,
o.data_source_id
from rdc_wz.wz_svc_observation o
join rdc_wz.wz_service s
on o.service_id = s.service_id
where observation_type='WEIGHT'
and s.master_service_id is not null
union all
select o.svc_observation_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'LA_SNOMED_248333004' as code_id, --not sure
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'OBSERVATION' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
o.value_unit as unit_of_measure,
observation_value as val_varchar,
case when substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')),1)='.' THEN substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),1,LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')) -1)
ELSE
RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')
END as val_num,
o.observation_date as test_time,
coalesce(o.observation_date, s.svc_start_date) as eff_date,
coalesce(o.observation_date, s.svc_end_Date) as exp_date,
o.observation_value as result_value,
o.value_status as result_status,
o.observation_type as result_value_type,
o.ref_min as ref_normal_min,
o.ref_max as ref_normal_max,
o.ref_range_eff_date as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
o.etl_job_id,
o.etl_source_id,
o.etl_date,
o.data_source_id
from rdc_wz.wz_svc_observation o
join rdc_wz.wz_service s
on o.service_id = s.service_id
where observation_type='HEIGHT'
and s.master_service_id is not null
union all
select o.svc_observation_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'DI_SNOMED-CT_271650006' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'OBSERVATION' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
o.value_unit as unit_of_measure,
observation_value as val_varchar,
case when substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')),1)='.' THEN substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),1,LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')) -1)
ELSE
RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')
END as val_num,
o.observation_date as test_time,
coalesce(o.observation_date, s.svc_start_date) as eff_date,
coalesce(o.observation_date, s.svc_end_Date) as exp_date,
o.observation_value as result_value,
o.value_status as result_status,
o.observation_type as result_value_type,
o.ref_min as ref_normal_min,
o.ref_max as ref_normal_max,
o.ref_range_eff_date as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
o.etl_job_id,
o.etl_source_id,
o.etl_date,
o.data_source_id
from rdc_wz.wz_svc_observation o
join rdc_wz.wz_service s
on o.service_id = s.service_id
where observation_type in ('BP DIASTOLIC','DIASTOLIC','DIASTOLIC 1','DIASTOLIC 2','DIASTOLIC 3')
and s.master_service_id is not null
union all
select o.svc_observation_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'DI_SNOMED-CT_271649006' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'OBSERVATION' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
o.value_unit as unit_of_measure,
observation_value as val_varchar,
case when substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')),1)='.' THEN substr(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]'),1,LENGTH(RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')) -1)
ELSE
RDC_CZ.STRIPCHARACTERS(observation_value, '[^.0-9]')
END as val_num,
o.observation_date as test_time,
coalesce(o.observation_date, s.svc_start_date) as eff_date,
coalesce(o.observation_date, s.svc_end_Date) as exp_date,
o.observation_value as result_value,
o.value_status as result_status,
o.observation_type as result_value_type,
o.ref_min as ref_normal_min,
o.ref_max as ref_normal_max,
o.ref_range_eff_date as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
o.etl_job_id,
o.etl_source_id,
o.etl_date,
o.data_source_id
from rdc_wz.wz_svc_observation o
join rdc_wz.wz_service s
on o.service_id = s.service_id
where observation_type in ('BP SYSTOLIC','SYSTOLIC','SYSTOLIC 1','SYSTOLIC 2','SYSTOLIC 3')
and s.master_service_id is not null
union all
select v.svc_vaccine_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'VA_' || v.vaccine_code_type || '_' || v.vaccine_code as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'VACCINE' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
null as unit_of_measure,
null as val_varchar,
null as val_num,
null as test_time,
coalesce(v.vaccine_date, s.svc_start_date) as eff_date,
coalesce(v.expiration_date, s.svc_end_Date) as exp_date,
null as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max ,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
v.etl_job_id,
v.etl_source_id,
v.etl_date,
v.data_source_id
from rdc_wz.wz_svc_vaccine v
join rdc_wz.wz_service s
on v.service_id = s.service_id
where v.vaccine_code is not null
and s.master_service_id is not null
union all
select m.svc_medication_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'ME_' || m.rx_code_type || '_' || m.rx_code as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'MEDICATION' as fact_type,
null as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
null as unit_of_measure,
null as val_varchar,
null as val_num,
null as test_time,
coalesce(m.rx_start_date, s.svc_start_date) as eff_date,
coalesce(m.rx_end_date, s.svc_end_Date) as exp_date,
null as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
m.etl_job_id,
m.etl_source_id,
m.etl_date,
m.data_source_id
from rdc_wz.wz_svc_medication m
join rdc_wz.wz_service s
on m.service_id = s.service_id
where m.rx_code is not null
and s.master_service_id is not null
union all
select b.patient_vital_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'PR_SNOMED-CT_271650006' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'VITAL' as fact_type,
b.collection_date as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
null as unit_of_measure,
null as val_varchar,
b.blood_pressure_diastolic as val_num,
null as test_time,
coalesce(b.collection_date, s.svc_start_date) as eff_date,
coalesce(b.collection_date, s.svc_end_Date) as exp_date,
cast(b.blood_pressure_diastolic as varchar2(2000)) as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
b.etl_job_id,
b.etl_source_id,
b.etl_date,
b.data_source_id
from rdc_wz.wz_svc_basic_vital b
join rdc_wz.wz_service s
on b.service_id = s.service_id
where b.blood_pressure_diastolic is not null
and s.master_service_id is not null
union all
select b.patient_vital_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'PR_SNOMED-CT_271649006' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'VITAL' as fact_type,
b.collection_date as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
null as unit_of_measure,
null as val_varchar,
b.blood_pressure_systolic as val_num,
null as test_time,
coalesce(b.collection_date, s.svc_start_date) as eff_date,
coalesce(b.collection_date, s.svc_end_Date) as exp_date,
cast(b.blood_pressure_systolic as varchar2(2000)) as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
b.etl_job_id,
b.etl_source_id,
b.etl_date,
b.data_source_id
from rdc_wz.wz_svc_basic_vital b
join rdc_wz.wz_service s
on b.service_id = s.service_id
where b.blood_pressure_systolic is not null
and s.master_service_id is not null
union all
select b.patient_vital_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'LA_LOINC_39156-5' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'VITAL' as fact_type,
b.collection_date as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
null as unit_of_measure,
null as val_varchar,
b.bmi as val_num,
null as test_time,
coalesce(b.collection_date, s.svc_start_date) as eff_date,
coalesce(b.collection_date, s.svc_end_Date) as exp_date,
cast(b.bmi as varchar2(2000)) as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
b.etl_job_id,
b.etl_source_id,
b.etl_date,
b.data_source_id
from rdc_wz.wz_svc_basic_vital b
join rdc_wz.wz_service s
on b.service_id = s.service_id
where b.bmi is not null
and s.master_service_id is not null
union all
select b.patient_vital_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'LA_LOINC_8310-5' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'VITAL' as fact_type,
b.collection_date as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
b.body_temp_unit as unit_of_measure,
null as val_varchar,
b.body_temperature as val_num,
null as test_time,
coalesce(b.collection_date, s.svc_start_date) as eff_date,
coalesce(b.collection_date, s.svc_end_Date) as exp_date,
cast(b.body_temperature as varchar2(2000)) as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
b.etl_job_id,
b.etl_source_id,
b.etl_date,
b.data_source_id
from rdc_wz.wz_svc_basic_vital b
join rdc_wz.wz_service s
on b.service_id = s.service_id
where b.body_temperature is not null
and s.master_service_id is not null
union all
select b.patient_vital_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'LA_LOINC_8302-2' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'VITAL' as fact_type,
b.collection_date as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
b.height_unit_code as unit_of_measure,
null as val_varchar,
height as val_num,
null as test_time,
coalesce(b.collection_date, s.svc_start_date) as eff_date,
coalesce(b.collection_date, s.svc_end_Date) as exp_date,
cast (b.height as varchar2(2000)) as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
b.etl_job_id,
b.etl_source_id,
b.etl_date,
b.data_source_id
from rdc_wz.wz_svc_basic_vital b
join rdc_wz.wz_service s
on b.service_id = s.service_id
where b.height is not null
and s.master_service_id is not null
union all
select b.patient_vital_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'LA_LOINC_3141-9' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'VITAL' as fact_type,
b.collection_date as collection_date_time,
null as collection_end_date_time,
null as collection_elapsed_time_min,
b.weight_unit_code as unit_of_measure,
null as val_varchar,
b.weight as val_num,
null as test_time,
coalesce(b.collection_date, s.svc_start_date) as eff_date,
coalesce(b.collection_date, s.svc_end_Date) as exp_date,
cast(b.weight as varchar2(2000)) as result_value,
null as result_status,
null as result_value_type,
null as ref_normal_min,
null as ref_normal_max,
null as ref_range_eff_date,
null as abnormal_flag,
null as unmatched_code_flag,
null as precedence_factor,
b.etl_job_id,
b.etl_source_id,
b.etl_date,
b.data_source_id
from rdc_wz.wz_svc_basic_vital b
join rdc_wz.wz_service s
on b.service_id = s.service_id
where b.weight is not null
and s.master_service_id is not null
union all
select b.patient_vital_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'LA_LOINC_8867-4' as code_id,
null as code_modifier_1,
null as code_modifier_2,
null as code_modifier_3,
null as code_modifier_4,
'VITAL' as fact_type,
b.collection_date as collection_date_time,
null as collection_e
Tom Kyte
April 23, 2013 - 3:07 pm UTC

I cannot run that, make the tiniest example (cut out EVERYTHING that doesn't contribute to the problem) but include EVERYTHING NEEDED (eg: tables, dummy functions, etc)

tables without storage clauses and as few columns as can be
empty funcgtions - just to demonstrate the issue

but 100% complete.


a reproducible test case. You cannot expect me to parse a view like that.


sort of what I said above...

I'd need a full up example I can run to reproduce with - do you have one?

Hrishikesh Deshmukh, April 23, 2013 - 3:44 pm UTC

The TEST Case:
The two tables used:
CREATE TABLE "RDC_WZ"."WZ_SERVICE"
( "SERVICE_ID" NUMBER(22,0) NOT NULL ENABLE,
"MASTER_SERVICE_ID" NUMBER(22,0),
CONSTRAINT "PK_WZ_SERVICE" PRIMARY KEY ("SERVICE_ID")
);


CREATE TABLE "RDC_WZ"."WZ_SVC_PROCEDURE"
( "SVC_PROCEDURE_ID" NUMBER(22,0) NOT NULL ENABLE,
"SERVICE_ID" NUMBER(22,0) NOT NULL ENABLE,
"PROC_CODE" VARCHAR2(50 BYTE),
CONSTRAINT "PK_WZ_SVC_PROCEDURE" PRIMARY KEY ("SVC_PROCEDURE_ID")
);

The Data in the tables could look like:
"WZ_SERVICE" TABLE
service_id master_service_id
6 6
11 11
16 16

WZ_SVC_PROCEDURE
svc_procedure_id service_id proc_code
1 6 99350
2 16 99201
3 11 3021F
------------------------------------------------------
The FUNCTION created in a different schema "RDC_CZ":
create or replace
function rdc_cz.CZF_IS_NUMERIC (p_string varchar2)
return NUMBER AUTHID DEFINER
as
l_number number;
begin
l_number := p_string;
return 1;
exception
when others then
return 0;
end;
/
GRANT EXECUTE ON rdc_cz.CZF_IS_NUMERIC TO PUBLIC;
/
------------------------------------------------------
The VIEW to be created:

create view rdc_wz.wz_observation_fact_view as
(
select p.svc_procedure_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'PR_' ||
CASE
WHEN RDC_CZ.CZF_IS_NUMERIC(p.proc_code) = 1 THEN
CASE
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,1,1)) = 0 THEN 'HCPCS'
WHEN substr(p.proc_code,5,1)='F' THEN 'CPT-2'
WHEN substr(p.proc_code,5,1)='T' THEN 'CPT-3'
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,5,1)) = 0 THEN 'NULL'
ELSE NULL
END
END
|| '_' || p.proc_code as code_id
from rdc_wz.wz_svc_procedure p
join rdc_wz.wz_service s
on p.service_id = s.service_id
and s.master_service_id is not null
);

The above set includes everything required and also the error which I am getting when creating the view as mentioned earlier.


Tom Kyte
April 23, 2013 - 7:11 pm UTC

I cannot reproduce the issue in 11.2.0.3 - does this reproduce in your database (do you happen to have any schema objects named after schemas anywhere by chance?)

ops$tkyte%ORA11GR2> select * from v$version;

BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create user rdc_cz identified by rdc_cz;

User created.

ops$tkyte%ORA11GR2> create user rdc_wz identified by rdc_wz;

User created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> CREATE TABLE "RDC_WZ"."WZ_SERVICE"
  2     (    "SERVICE_ID" NUMBER(22,0) NOT NULL ENABLE,
  3          "MASTER_SERVICE_ID" NUMBER(22,0),
  4          CONSTRAINT "PK_WZ_SERVICE" PRIMARY KEY ("SERVICE_ID")
  5     );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2>   CREATE TABLE "RDC_WZ"."WZ_SVC_PROCEDURE"
  2     (    "SVC_PROCEDURE_ID" NUMBER(22,0) NOT NULL ENABLE,
  3      "SERVICE_ID" NUMBER(22,0) NOT NULL ENABLE,
  4      "PROC_CODE" VARCHAR2(50 BYTE),
  5       CONSTRAINT "PK_WZ_SVC_PROCEDURE" PRIMARY KEY ("SVC_PROCEDURE_ID")
  6     );

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace
  2  function        rdc_cz.CZF_IS_NUMERIC (p_string varchar2)
  3   return NUMBER AUTHID DEFINER
  4   as
  5     l_number number;
  6   begin
  7     l_number := p_string;
  8     return 1;
  9   exception
 10     when others then
 11       return 0;
 12   end;
 13  /

Function created.

ops$tkyte%ORA11GR2> GRANT EXECUTE ON rdc_cz.CZF_IS_NUMERIC TO PUBLIC;

Grant succeeded.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create view rdc_wz.wz_observation_fact_view as
  2  (
  3   select p.svc_procedure_id as observation_fact_id,
  4   s.master_service_id as encounter_id,
  5   null as observation_group_id,
  6   'PR_' ||
  7   CASE
  8   WHEN RDC_CZ.CZF_IS_NUMERIC(p.proc_code) = 1 THEN
  9   CASE
 10   WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,1,1)) = 0 THEN 'HCPCS'
 11   WHEN substr(p.proc_code,5,1)='F' THEN 'CPT-2'
 12   WHEN substr(p.proc_code,5,1)='T' THEN 'CPT-3'
 13   WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,5,1)) = 0 THEN 'NULL'
 14   ELSE NULL
 15   END
 16   END
 17   || '_' || p.proc_code as code_id
 18   from rdc_wz.wz_svc_procedure p
 19   join rdc_wz.wz_service s
 20   on p.service_id = s.service_id
 21   and s.master_service_id is not null
 22   );

View created.

Hrishikesh Deshmukh, April 23, 2013 - 7:42 pm UTC

> select * from v$version
BANNER                                                                         
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production     
PL/SQL Release 11.2.0.1.0 - Production                                           
CORE 11.2.0.1.0 Production                                                         
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production                          
NLSRTL Version 11.2.0.1.0 - Production  

When i use the set i sent you it works fine.

"do you happen to have any schema objects named after schemas anywhere by chance?"

If i understand this right it means that for schema "RDC_CZ" i probably have Tables,Functions which use "RDC_CZ" in there names as well. But like the function i sent the naming convention just uses "CZX" or "CZ" before the actual name of the function,procedure or table for simplicity.

Could that also cause issues? I do not seem to understand where could the problem be? Also the database is huge but as far as i know schema objects are not named after schemas anywhere. Is there any way to confirm this?

Thanks a lot Again!
Appreciate all the help

Tom Kyte
April 23, 2013 - 7:51 pm UTC

could be a 11.2.0.1 specific issue, I don't have 11.2.0.1 to test with right now...

if anyone has an 11.2.0.1 out there - can you run:

drop user rdc_wz cascade;
drop user rdc_cz cascade;

select * from v$version;

create user rdc_cz identified by rdc_cz;
create user rdc_wz identified by rdc_wz;

CREATE TABLE "RDC_WZ"."WZ_SERVICE"
   (    "SERVICE_ID" NUMBER(22,0) NOT NULL ENABLE,
        "MASTER_SERVICE_ID" NUMBER(22,0),
        CONSTRAINT "PK_WZ_SERVICE" PRIMARY KEY ("SERVICE_ID")
   );


  CREATE TABLE "RDC_WZ"."WZ_SVC_PROCEDURE"
   (    "SVC_PROCEDURE_ID" NUMBER(22,0) NOT NULL ENABLE,
    "SERVICE_ID" NUMBER(22,0) NOT NULL ENABLE,
    "PROC_CODE" VARCHAR2(50 BYTE),
     CONSTRAINT "PK_WZ_SVC_PROCEDURE" PRIMARY KEY ("SVC_PROCEDURE_ID")
   );

create or replace
function        rdc_cz.CZF_IS_NUMERIC (p_string varchar2)
 return NUMBER AUTHID DEFINER
 as
   l_number number;
 begin
   l_number := p_string;
   return 1;
 exception
   when others then
     return 0;
 end;
/
GRANT EXECUTE ON rdc_cz.CZF_IS_NUMERIC TO PUBLIC;

create view rdc_wz.wz_observation_fact_view as
(
 select p.svc_procedure_id as observation_fact_id,
 s.master_service_id as encounter_id,
 null as observation_group_id,
 'PR_' ||
 CASE
 WHEN RDC_CZ.CZF_IS_NUMERIC(p.proc_code) = 1 THEN
 CASE
 WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,1,1)) = 0 THEN 'HCPCS'
 WHEN substr(p.proc_code,5,1)='F' THEN 'CPT-2'
 WHEN substr(p.proc_code,5,1)='T' THEN 'CPT-3'
 WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,5,1)) = 0 THEN 'NULL'
 ELSE NULL
 END
 END
 || '_' || p.proc_code as code_id
 from rdc_wz.wz_svc_procedure p
 join rdc_wz.wz_service s
 on p.service_id = s.service_id
 and s.master_service_id is not null
 );


in a test database and see if it reproduces (if it does, 11.2.0.1 specific issue, please contact support).

if it does not reproduce, it might be a scoping issue - see what:

select * from dba_objects where object_name in ( select username from dba_users );


returns (to see if there are objects named after schemas...)

Hrishikesh Deshmukh, April 23, 2013 - 8:16 pm UTC

JUST TO CONFIRM....
On firing the query no rows were selected...Therefore no objects have name as the schemas.

Could be specific to 11.2.0.1

Thanks

As requested 11.2.0.1 test

AndyP, April 24, 2013 - 9:34 am UTC


SQL > @testcase

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


User created.


User created.


Table created.


Table created.


Function created.


Grant succeeded.


View created.



Tom Kyte
April 24, 2013 - 12:51 pm UTC

thanks


how about the original poster, if you run that in an 11.2.0.1 database - one that doesn't already have your stuff in it of course - what happens - does it reproduce for you?

Hrishikesh Deshmukh, April 24, 2013 - 2:40 pm UTC

The test set works fine when i create two new schema's with different names and keeping the rest of the things same.

BUT
When i use the shorter version of the view:

create view rdc_wz.wz_observation_fact_view as
(
select p.svc_procedure_id as observation_fact_id,
s.master_service_id as encounter_id,
null as observation_group_id,
'PR_' ||
CASE
WHEN RDC_CZ.CZF_IS_NUMERIC(p.proc_code) = 1 THEN
CASE
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,1,1)) = 0 THEN 'HCPCS'
WHEN substr(p.proc_code,5,1)='F' THEN 'CPT-2'
WHEN substr(p.proc_code,5,1)='T' THEN 'CPT-3'
WHEN RDC_CZ.CZF_IS_NUMERIC(substr(p.proc_code,5,1)) = 0 THEN 'NULL'
ELSE NULL
END
END
|| '_' || p.proc_code as code_id
from rdc_wz.wz_svc_procedure p
join rdc_wz.wz_service s
on p.service_id = s.service_id
and s.master_service_id is not null
);

It still gives me same error of table or view does not exist. Also there are no objects with same name as the schema names(confirmed as mentioned in my last post).
There is nothing wrong with the view as it runs in sqlserver and also the shorter version in oracle in the new schema's.

Thanks!

Hrishikesh Deshmukh, April 24, 2013 - 10:48 pm UTC

Tom,

I finally realized what was going wrong and why it would not reproduce.
I am using Perl Script to create a schema and then load all the SQL Scripts(.sql files) for that schema which creates tables, functions and procedures.
My sql script for the function looks exactly like below:

create or replace function RDC_CZ.CZF_IS_NUMERIC (p_string varchar2)
return NUMBER
as
l_number number;
begin
l_number := p_string;
return 1;
exception
when others then
return 0;
end;
/

GRANT EXECUTE ON RDC_CZ.CZF_IS_NUMERIC TO PUBLIC
/

There is a '/ enter' after 'end;' and NO ';' after the grant statement as I found that this is the way to run script on sqlplus and sqldeveloper. If i run the above function with grant AS IS in SQLDEVELOPER then it runs perfectly and also the view is created. This should be running on the sqlplus as well when it runs SQL Script but this is where the Problem was.
The function was getting created but the Grant below the function would never execute(was ignored) and therefore the view never got created.

I would have to manually go and GRANT EXECUTE on all functions or create a script to do this.

Would you know how "Create function and GRANT" both would work in one SQL SCRIPT (.sql file). Then I could add GRANT for that function in each script as above.

Thanks!!!

Tom Kyte
April 25, 2013 - 1:24 pm UTC

if you were running that script in sqlplus, the grant would work as is.


it must be whatever tool you are using. end the grant with a ';' and that will likely 'fix' it.


glad it was something so straight forward.

Dynamic SQL with dynamic IN list

Al Ricafort, July 02, 2013 - 10:01 am UTC

Hi Tom,

I need to write a package whose inputs are varrays which will be used in an IN list. The output is REF CURSOR and the SQL is dynamically built. The package goes something like this:

create or replace TYPE "VARRTYPE_VARCHAR10"  IS VARRAY (100) OF VARCHAR2(10);

create or replace 
PACKAGE AL_MY_PKG1 AS
  TYPE RC                           IS REF CURSOR;
  
  PROCEDURE GET_DUMMY(   
           i_param_1     IN VARRTYPE_VARCHAR10,
           i_param_2     IN VARRTYPE_VARCHAR10,
           io_cur        IN OUT RC
  );
 END AL_MY_PKG1;

create or replace 
PACKAGE BODY AL_MY_PKG1 AS
  PROCEDURE GET_DUMMY(   
           i_param_1     IN  VARRTYPE_VARCHAR10,
           i_param_2     IN  VARRTYPE_VARCHAR10,
           io_cur        IN OUT RC
  ) AS
           v_query       VARCHAR2(2000) := 'SELECT 1 FROM DUAL WHERE 1 = 1';
  BEGIN   
           IF i_param_1 IS NOT NULL
           THEN
              v_query := v_query || ' AND ''A'' IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:A AS VARRTYPE_VARCHAR10))) ';
           END IF;

           IF i_param_2 IS NOT NULL
           THEN
              v_query := v_query || ' AND ''B'' IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:B AS VARRTYPE_VARCHAR10))) ';
           END IF;
 
          dbms_output.put_line('v_query ' || v_query);

           // How do I improve this part????
           IF i_param_1 IS NULL AND i_param_2 IS NOT NULL 
           THEN
               OPEN io_cur FOR v_query USING i_param_2;
           ELSIF i_param_1 IS NOT NULL AND i_param_2 IS NULL 
           THEN
               OPEN io_cur FOR v_query USING i_param_1;
           ELSE
               OPEN io_cur FOR v_query USING i_param_1,i_param_2;
           END IF;           
  
  END GET_DUMMY;
 END AL_MY_PKG1;


My problem here is that the input varrays parameter in the actual package is more than 2 and they are not mandatory. So if I use the 'If' just like what I did above it would be too much. So how do I open the cursor with varying bind variables?

Thanks.
Tom Kyte
July 02, 2013 - 5:18 pm UTC

read:

http://www.oracle.com/technetwork/issue-archive/2009/09-jul/o49asktom-090487.html

ops$tkyte%ORA11GR2> create table t as select * from all_users where rownum <= 10;

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace
  2  procedure p( p_arr1 in sys.odciVarchar2List,
  3               p_arr2 in sys.odciVarchar2List,
  4               p_cur in out sys_refcursor )
  5  as
  6      l_sql long := 'select * from t ';
  7  begin
  8      if ( p_arr1 is not null )
  9      then
 10          l_sql := l_sql || q'| where username in (select * from table(cast(:p_arr1 as sys.odcivarchar2List))) |';
 11      else
 12          l_sql := l_sql || q'| where (1=1 or :p_arr1 is null) |';
 13      end if;
 14
 15      if ( p_arr2 is not null )
 16      then
 17          l_sql := l_sql || q'| and user_id in (select to_number(column_value) from table(cast(:p_arr2 as sys.odcivarchar2List))) |';
 18      else
 19          l_sql := l_sql || q'| and (1=1 or :p_arr2 is null) |';
 20      end if;
 21
 22      dbms_output.put_line( l_sql );
 23      open p_cur for l_sql using p_arr1, p_arr2;
 24  end;
 25  /

Procedure created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2> set autoprint on
ops$tkyte%ORA11GR2> declare
  2      l_arr1 sys.odciVarchar2List;
  3      l_arr2 sys.odciVarchar2List;
  4  begin
  5      p( l_arr1, l_arr2, :x );
  6  end;
  7  /
select * from t  where (1=1 or :p_arr1 is null)  and (1=1 or :p_arr2 is null)

PL/SQL procedure successfully completed.


USERNAME                USER_ID CREATED
-------------------- ---------- ---------
BIG_TABLE                    91 29-JUN-13
OPS$TKYTE                    90 29-JUN-13
BI                           89 29-JUN-13
PM                           88 29-JUN-13
SH                           87 29-JUN-13
IX                           86 29-JUN-13
OE                           85 29-JUN-13
HR                           84 29-JUN-13
SCOTT                        83 17-SEP-11
OWBSYS                       78 17-SEP-11

10 rows selected.

ops$tkyte%ORA11GR2> declare
  2      l_arr1 sys.odciVarchar2List := sys.odciVarchar2List( 'OPS$TKYTE', 'SCOTT' );
  3      l_arr2 sys.odciVarchar2List := sys.odciVarchar2List( '90' );
  4  begin
  5      p( l_arr1, l_arr2, :x );
  6  end;
  7  /
select * from t  where username in (select * from table(cast(:p_arr1 as
sys.odcivarchar2List)))  and user_id in (select to_number(column_value) from
table(cast(:p_arr2 as sys.odcivarchar2List)))

PL/SQL procedure successfully completed.


USERNAME                USER_ID CREATED
-------------------- ---------- ---------
OPS$TKYTE                    90 29-JUN-13

1 row selected.

Dynamic SQL with dynamic IN list

Al Ricafort, July 02, 2013 - 10:17 am UTC

I am tempted to write it this way:

create or replace 
PACKAGE BODY AL_MY_PKG1 AS
  PROCEDURE GET_DUMMY(   
           i_param_1     IN  VARRTYPE_VARCHAR10,
           i_param_2     IN  VARRTYPE_VARCHAR10,
           io_cur        IN OUT RC
  ) AS
           v_query       VARCHAR2(2000) := 'SELECT 1 FROM DUAL WHERE 1 = 1';
           v_param_1     VARRTYPE_VARCHAR10;
           v_param_2     VARRTYPE_VARCHAR10;
  BEGIN   
           IF i_param_1 IS NOT NULL
           THEN
              v_param_1:=i_param_1;
              v_query := v_query || ' AND ''A'' IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:A AS VARRTYPE_VARCHAR10))) ';
           ELSE
              v_param_1:=VARRTYPE_VARCHAR10('');
              v_query := v_query || ' AND SELECT 1 FROM DUAL WHERE 1 = nvl((select null from  table(cast(:a as VARRTYPE_VARCHAR10))),1)';
           END IF;

           IF i_param_2 IS NOT NULL
           THEN
              v_param_2:=i_param_2;
              v_query := v_query || ' AND ''B'' IN (SELECT COLUMN_VALUE FROM TABLE(CAST(:B AS VARRTYPE_VARCHAR10))) ';
           ELSE
              v_param_2:=VARRTYPE_VARCHAR10('');
              v_query := v_query || ' AND SELECT 1 FROM DUAL WHERE 1 = nvl((select null from  table(cast(:b as VARRTYPE_VARCHAR10))),1)';
           END IF;
 
          dbms_output.put_line('v_query ' || v_query);

          OPEN io_cur FOR v_query USING v_param_1,v_param_2;
  
  END GET_DUMMY;
 END AL_MY_PKG1;

Dynamic SQL with dynamic IN list

Al Ricafort, July 03, 2013 - 12:11 am UTC

"or :p_arr1 is null)"

Now why I didn't think of that?

Thanks Tom.

Dynamic SQL with dynamic IN list

Al Ricafort, July 09, 2013 - 10:35 am UTC

Hi Tom,

Just got me thinking, does 'select * from table(cast(:p_arr1 as sys.odcivarchar2List))' returns the record in the order they are stored in the array?

Thanks.

Al
Tom Kyte
July 16, 2013 - 3:13 pm UTC

from such a simple query, without parallel anything, yes - not necessarily promised - but they do.


but as soon as you start using them in an inlist, a join, anything - no, they will not.


best to always use an order by clause if order is relevant (else you cannot complain when they are not ordered)

pl/sql table of records

Ravi, July 29, 2013 - 4:44 pm UTC

Tom,
We have Oracle 11G and I have a PL/SQL table of records (index by binary_integer). I want to do a SQL Group by of the data to filter it. Is it possible to use SQL on a PL/SQL table of records in 11g and if so how to do this?
Do I need to declare an Type object for this first?
Thanks
Ravi
Tom Kyte
August 02, 2013 - 5:44 pm UTC

you need a SQL type prior to 12c, in 12c you can do this - plsql types will be visible to sql.

prior to that however, you need to move it into a collection whose type is known to SQL (create type - outside of the package)

using JSON_TABLE

Rajeshwaran, Jeyabal, March 06, 2019 - 9:54 am UTC

with 12c in place, we can now use JSON_TABLE to split the delimited values.

something like this:

demo@ORA12C> variable x varchar2(20)
demo@ORA12C> exec :x := 'a,b,c';

PL/SQL procedure successfully completed.

demo@ORA12C> print x

X
----------------------------------------------------------------------------
a,b,c

demo@ORA12C>
demo@ORA12C> select ' {b1:["'||replace(:x,',','","')||'"]}' txt from dual;

TXT
----------------------------------------------------------------------------
 {b1:["a","b","c"]}

demo@ORA12C>
demo@ORA12C> select *
  2  from ( select ' {b1:["'||replace(:x,',','","')||'"]}' txt from dual )
  3  where txt is json ;

TXT
----------------------------------------------------------------------------
 {b1:["a","b","c"]}

demo@ORA12C>
demo@ORA12C> select output_values
  2  from (
  3  select ' {b1:["'||replace(:x,',','","')||'"]}' txt from dual
  4      ) , json_table( txt ,'$.b1[*]'
  5             columns(
  6                     output_values path '$' ) );

OUTPUT_VALUES
----------------------------------------------------------------------------
a
b
c

demo@ORA12C>


However still the cardinality for JSON_TBALE invocation was close the block size.

demo@ORA12C> set autotrace traceonly explain
demo@ORA12C> l
  1  select output_values
  2  from (
  3  select ' {b1:["'||replace(:x,',','","')||'"]}' txt from dual
  4      ) , json_table( txt ,'$.b1[*]'
  5             columns(
  6*                    output_values path '$' ) )
demo@ORA12C> /

Execution Plan
----------------------------------------------------------
Plan hash value: 3617720234

-------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |  8168 | 16336 |    25  (12)| 00:00:01 |
|   1 |  NESTED LOOPS          |      |  8168 | 16336 |    25  (12)| 00:00:01 |
|   2 |   FAST DUAL            |      |     1 |       |     2   (0)| 00:00:01 |
|   3 |   JSONTABLE EVALUATION |      |       |       |            |          |
-------------------------------------------------------------------------------

demo@ORA12C> set autotrace off
demo@ORA12C>

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library