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,

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.

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.

Very useful info and examples.

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:

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,

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

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)

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

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

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

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,

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?

 

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 

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,

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,

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

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,

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. 

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,

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 

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 :-)

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?

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,

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

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.

Excellent !!!!

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

Very...Very Helpful...

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.
 

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.

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.

 

 

Hi Tom,

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

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

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

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.

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.

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.

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.

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, 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

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???
 

This is 8.1.7

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, 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

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

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.
 
 

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

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>
 

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

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;

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?

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,

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,
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)
/

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. 

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,

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, 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)

 

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).

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.

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?

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) 

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

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

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.

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?

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?

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?

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

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

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

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

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

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

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

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.
 
 

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,
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,

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,

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

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>

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!

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. 

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

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.
 

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%

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 ) )

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 

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!!

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%

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}

...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?

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 ;)

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, 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,

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

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...

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

Great post.

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?

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?

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
 

Finally found a reference for "THE".

See:

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

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

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,

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.

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'
/

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

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.

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?

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

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

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

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?

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

D
-
X

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

no rows selected

'1' = '1 '

is identical to 

'1 ' = '1 '

and 

'1' = ' 1'

is identical to

'1 ' = ' 1'

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.

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

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?

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.

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

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

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.

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

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

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

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!

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.

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?

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.. 

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.. 

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,
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...

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.

Vertigo just looking at the query...How do you do it? Amazing. Thanks...!!!

  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
 

It makes perfect sense.

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>

 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  /

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

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!

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

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.

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.

> 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

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
 );

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

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.

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!

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!!!

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.

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.

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;

"or :p_arr1 is null)"

Now why I didn't think of that?

Thanks Tom.

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,
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

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>