cursors with in sql
February 24, 2002 - 10pm Central time zone
Reviewer: Munz from reston, va
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,
Followup February 25, 2002 - 8am Central time zone:
1) is it no different then in a stored procedure that does NOT create HTML.
You will create the table type (myTableType).
You will create a function str2table.
You will create your procedure that generates HTML.
If p_state is a variable that has a comma delimited string like 'PA, VA, MD' -- then yes, what you
have there is OK.
the cast just tells the SQL layer what type to expect back - mytabletype
Lexical
February 25, 2002 - 8am Central time zone
Reviewer: Steve from Blackburn
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.
Followup February 25, 2002 - 10am Central time zone:
does not use bind variables. I despise code that does not use bind variables as the is the best
way to limit your scalability, flood your shared pool, kill your repsonse time and fail.
Use bind variables!
February 25, 2002 - 11am Central time zone
Reviewer: Steve from Blackburn
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.
Followup February 25, 2002 - 11am Central time zone:
It is soft vs hard -- but not for the reason you specify.
If they code:
select * from t where x in ( &blah )
they will generate queries like:
select * from t where x in ( 1 );
select * from t where x in ( 1, 2 );
select * from t where x in ( 2, 1 );
select * from t where x in ( 1, 2, 3 );
....
virtually EVERY query that uses this approach will be hard parsed. That will KILL performance.
You want to avoid hard parses.
Great
February 25, 2002 - 6pm Central time zone
Reviewer: Alla from Palo Alto, CA
Very useful info and examples.
IN statement
February 25, 2002 - 8pm Central time zone
Reviewer: munz from Reston, VA
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,
Followup February 25, 2002 - 8pm Central time zone:
If you can have AT MOST three items, by all means code:
select * from t where state in ( p_state(1), p_state(2), p_state(3) );
If you know the (reasonable) upper bound on the number of elements -- go for it, just code the
explicit binds.
Else, use the str2table trick (in which case -- yes, you would have to "string up" the elements of
the array into a single string)
bind variables
February 26, 2002 - 8pm Central time zone
Reviewer: Munz from Reston, USA
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,
Followup February 27, 2002 - 7am Central time zone:
NULLS will never come out from that.
scott@ORA817DEV.US.ORACLE.COM> select ename from emp where comm in ( 500, NULL, NULL );
ENAME
----------
WARD
scott@ORA817DEV.US.ORACLE.COM> select count(*) from emp where comm is null;
COUNT(*)
----------
10
just set the last N array elements to NULL and they are effectively "ignored"
Ref cursor and collections
April 15, 2002 - 6pm Central time zone
Reviewer: Ranjan from Connecticut, USA
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
Followup April 16, 2002 - 7am Central time zone:
that error equals "call support".
How about with dynamic SQL?
April 16, 2002 - 2pm Central time zone
Reviewer: James from New York
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)
Followup April 16, 2002 - 9pm Central time zone:
right above IS THE APPROACH!!!
str2tbl.
Most helpful example
May 2, 2002 - 8am Central time zone
Reviewer: Andreas Faafeng from Oslo, Norway
Once again, I'm impressed by your wonderfully simple and straight-forward solution to what
initially was an intricate question.
I'm working in a group of 4 people, of which 3 has bought your book.
Your mission to teach us all about latches, bind variables and parsing certainly helps!
Best regards,
Andreas Faafeng
Hmm. What about this?
May 6, 2002 - 5pm Central time zone
Reviewer: dre from Colorado
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
Followup May 6, 2002 - 8pm Central time zone:
that looks just dandy -- I see no constants that would change from query execution to query
execution in there...
Won't use any indexes on thisid -- hope that is not a problem.
Hmm. What about this?
May 7, 2002 - 1pm Central time zone
Reviewer: DRE
Hmm, my query plan says its using the index, not a full table scan. . . ?
Followup May 7, 2002 - 2pm Central time zone:
It is using a FAST FULL INDEX scan (using the table as an index).
So, it is full scanning a "skinny" version of your table. It'll not be able to use the index in a
"conventional" sense (range scan for example).
Add another column:
SELECT thisid, some_other_column
FROM thistable
WHERE INSTR(stringOnumbers,','|| thisid ||',') != 0;
and see what happens.
More hemming and hawing!
May 7, 2002 - 3pm Central time zone
Reviewer: DRE from colorado
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
Followup May 7, 2002 - 7pm Central time zone:
I'll make the resonable assumption that
ACC_QUE_FRR_ID_IDX
is on the acc_que_frr_id column and that
ADL_ACC_ID_IDX
is on the adl_acc_id column.
Then, what is happening is:
the predicate "acc_que_frr_id = 30" is being used to find the rows in the ACC table. this uses
the index ACC_QUE_FRR_ID_IDX. Then, the TABLE ACCESS of ACC happens and this predicate:
INSTR(','|| '164,163,162,161' ||',' , ','|| acc_frw_id ||',') != 0
AND acc_caod_flag = 1 AND
INSTR(','||'SK,G'||',' , ','|| acc_que_system_q ||',') != 0
is evaluated. this does not (and the instrs in fact CANNOT) use an index. Then the index on
ADL(ADL_ACC_ID) is used to find the joined to row in ADL.
So, any indexes on ACC_FRW_ID and ACC_QUE_SYSTEM_Q cannot be used by your predicate. In fact, the
query you gave me:
SELECT thisid
FROM thistable
WHERE INSTR(stringOnumbers,','|| thisid ||',') != 0;
would not use a range scan (if you run it) it would FAST FULL SCAN.
So, I stand by my answer -- your example doesn't show an index on "thisid" being used when the
predicate is instr(stringOnumber,','||thisid||',') is used.
missing something
July 12, 2002 - 1pm Central time zone
Reviewer: George from Chester Springs, PA USA
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?
Followup July 12, 2002 - 5pm Central time zone:
ops$tkyte@ORA9I.WORLD> create or replace type string_list_t as TABLE OF VARCHAR2(4000)
2 /
Type created.
ops$tkyte@ORA9I.WORLD> declare
2 v_value_list string_list_t := string_list_t( 'X','Y','Z' );
3 v_rtn number;
4 begin
5 v_rtn := 0;
6 select 1
7 into v_rtn
8 from dual
9 where dummy in (select * from TABLE ( cast( v_value_list as string_list_t ) ) );
10 dbms_output.put_line( 'expect success, returned ' || to_char( v_rtn ));
11 end;
12 /
expect success, returned 1
PL/SQL procedure successfully completed
in list
July 13, 2002 - 7pm Central time zone
Reviewer: Munz from Reston, USA
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
Followup July 13, 2002 - 7pm Central time zone:
I would just code
for x in ( select * from t where state in ( p_state(1), p_state(2), p_state(3) ) )
loop
...
(hate explicit cursors)
Your syntax is wrong, the "concept" is ok but the syntax is wrong.
it would be:
cursor find_state(a in varchar2, b in varchar2, c in varchar2 ) is
select * from table where state in (a,b,c);
performance issue?
July 17, 2002 - 2pm Central time zone
Reviewer: George from Chester Springs, PA USA
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.
Followup July 17, 2002 - 2pm Central time zone:
option 2:
build the string as ... where x in ( 1, 2, 3, 4, 5, 6, 7 ) ....
but enable cursor_sharing = force before you parse it , cursor_sharing = exact after you parse it.
At least that way you will minimize the number of statements in the shared pool to be minimized.
global temp table?
July 17, 2002 - 4pm Central time zone
Reviewer: George from Chester Springs, PA USA
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
Followup July 18, 2002 - 5am Central time zone:
NOT A LOT OF BANG????
go ahead and use literals -- see what kind of "bang" you get from that (a bang like "bang, you just
shot yourself in the foot")
Tell me -- how hard is:
alter session set cursor_sharing=force;
parse
alter session set cursor_sharing=exact;
??????
Getting some error on compilation
July 17, 2002 - 5pm Central time zone
Reviewer: Yogesh from San Jose, CA
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,
Followup July 18, 2002 - 8am Central time zone:
works for me. provide an example (you know, like i always do)
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package demo_pkg
2 as
3 function str2tbl( p_str in varchar2 ) return myTableType;
4 end;
5 /
Package created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace package body demo_pkg
2 as
3
4 function str2tbl( p_str in varchar2 ) return myTableType
5 is
6 l_str long default p_str || ',';
7 l_n number;
8 l_data myTableType := myTabletype();
9 begin
10 loop
11 l_n := instr( l_str, ',' );
12 exit when (nvl(l_n,0) = 0);
13 l_data.extend;
14 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
15 l_str := substr( l_str, l_n+1 );
16 end loop;
17 return l_data;
18 end;
19
20 end;
21 /
Package body created.
Applying this solution to a cursor
July 18, 2002 - 1pm Central time zone
Reviewer: Mark Mclauchlan from London, UK
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?
Followup July 18, 2002 - 2pm Central time zone:
The type must be a SQL type -- not a plsql type.
move the type outside of the package -- it is a rule, it cannot be any other way. The type must be
known to SQL -- not just plsql.
usefulness of cursor_sharing=force
July 23, 2002 - 5pm Central time zone
Reviewer: George from Chester Springs, PA USA
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
Followup July 23, 2002 - 9pm Central time zone:
calsdba, thats not cals like in computer aided logistics support system is it?
You didn't offend me -- I was just pointing out that if you avoid bind variables -- you will hate
yourself later.
in ( 1, 2, 3 )
is very different from
in ( select .... )
in the first case, the optimizer knows "hey, the set is 3 items -- 3 index probes will do it -- i
can put that in the plan and go". In the second case the optimizer says "hey, unknown number of
items -- cannot just set 3 index probes in the plan and go -- gotta do nested loops or something
else"
Just different plans.
execute immediate 'alter session set cursor_sharing=force';
and if you have more then 1,000 elements -- you don't want to do 1,000 index probes. At some point
between 0 and 1000 the other plan will be better for you.
no more questions... at least on this subject :-)
July 24, 2002 - 2pm Central time zone
Reviewer: George from Chester Springs, PA USA
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 :-)
Followup July 24, 2002 - 10pm Central time zone:
Hey, I worked on the CALS project 1989-1993 ;)
When I was working on it -- it was DEC Ultix MLS+ (b1 multi-level secure OS) and Trusted Oracle
7.0.9 with Xterms. What a difference a decade makes....
same problem as above
October 10, 2002 - 5pm Central time zone
Reviewer: A reader
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?
Followup October 11, 2002 - 7pm Central time zone:
think about it:
where x in ( 1, 2, 3, 4, 5 )
where x in ( select z from a )
Just think about the differences between those two predicates....... Nothing to do with bind
variables -- just think about the potential differences between those two predicates (how many rows
will come back from A -- 1? 10? 1000? 10000?)
Casting in Dynamic SQL
November 22, 2002 - 6pm Central time zone
Reviewer: A reader from USA
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
Followup November 22, 2002 - 7pm Central time zone:
that is not strange -- type it in SQLPLUS and you'll get the same exact thing.
is l_tbl a column? no- you aren't in Plsql anymore when you drop down to dynamic sql - its all
sql. hence l_tbl just doesnt exists.
9 l_sql VARCHAR2(4000) :=
10 'SELECT column_value FROM TABLE(CAST (:l_tbl AS x_table_type))';
11
12 BEGIN
13 OPEN x FOR l_sql using l_tbl;
see what that does for you.
How parameters in IN list behave
February 5, 2003 - 9am Central time zone
Reviewer: Dan from US
Tom,
I noticed that when values provided to IN list are sorted in ascending for example
Select * from loans where loan_id IN ( 5, 6, 3);
Resulted in
3, 5, 6
Why is this??
Dan
Do you need Oracle Object installed?
March 27, 2003 - 6am Central time zone
Reviewer: Anil from Paris, France
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.
Followup March 27, 2003 - 8am Central time zone:
you must have a really old (like 8.0) version of Oracle.
Yes, you NEED the objects option (not an option in 8i) installed.
Select statement
May 5, 2003 - 10am Central time zone
Reviewer: lakshmi from Bangalore India
Excellent !!!!
What if p_str of 4000 bytes is not enough?
May 12, 2003 - 3pm Central time zone
Reviewer: Alex Daher from Brazil
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
Followup May 12, 2003 - 3pm Central time zone:
populate a global temporary table via a bulk insert and use it.
Awesome......
June 3, 2003 - 11am Central time zone
Reviewer: Bala Nemani from USA
Very...Very Helpful...
Won't compile on str2tbl...
August 21, 2003 - 5am Central time zone
Reviewer: A reader
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.
Followup August 21, 2003 - 6pm Central time zone:
version?
I was being stupid!
August 21, 2003 - 9am Central time zone
Reviewer: Mike from England
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.
September 4, 2003 - 8pm Central time zone
Reviewer: Vipin from NYC
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.
Followup September 5, 2003 - 3pm Central time zone:
why do people put the same thing in many places??? squeaky wheels -- grease?
look where you asked this elsewhere for my followup.
September 5, 2003 - 4pm Central time zone
Reviewer: Vipin
Hi Tom,
Sorry for putting this in more than one place , but anyway thanks for the followup.
Working on the same trick
September 11, 2003 - 3pm Central time zone
Reviewer: Pramitayan chinya from Texas,USA
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
Followup September 11, 2003 - 7pm Central time zone:
well, i don't see where or how you defined "value"
Well am i missing anything here???
September 11, 2003 - 9pm Central time zone
Reviewer: A reader
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
Followup September 12, 2003 - 9am Central time zone:
you cannot put it there if you want to use it in sql
create type value as table of number
/
Another Solution, thoes this work?
December 30, 2003 - 2pm Central time zone
Reviewer: Ricardo PatrocÃnio from Porto, PORTUGAL
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.
Followup December 30, 2003 - 2pm Central time zone:
no indexes, full scan. if that is OK with you, it works just dandy.
if the goal is to have a varying in list that can use indexes - that approach is no good.
The size accepted by the LONG variable..
January 2, 2004 - 6am Central time zone
Reviewer: Naveen from India
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.
Followup January 2, 2004 - 9am Central time zone:
32k is the biggest LONG plsql will ever deal with.
no ways around it.
Thanks Tom..but
January 2, 2004 - 11am Central time zone
Reviewer: Naveen from India
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.
Followup January 2, 2004 - 1pm Central time zone:
32k is the MAX size of any plsql variable there.
the "input variable" is a plsql variable.
if you have 32k of stuff here, I personally think you need to rethink something. that is HUGE.
I'd recommend looking into global temporary tables -- stop playing with a huge string and just
stuff the rows into a gtt and use it instead.
The restriction..
January 2, 2004 - 10pm Central time zone
Reviewer: Naveen from India
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.
Followup January 3, 2004 - 9am Central time zone:
what "application server" are you using that doesn't support jdbc/odbc ???
parse it, insert it into a gtt (global temp table) and delete from t where id in ( select * from
gtt)
Thanks again..
January 4, 2004 - 10pm Central time zone
Reviewer: Naveen from India
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.
Followup January 5, 2004 - 7am Central time zone:
you can drop in better jdbc drivers -- that is what java is all about. you can fully utilize your
database if you so desire.
Error "invalid column name" with DBMS_SQL.parse
February 4, 2004 - 3pm Central time zone
Reviewer: robert from CT
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
Followup February 4, 2004 - 6pm Central time zone:
I'll need some help reproducing....
ops$tkyte@ORA817DEV> drop table tblctsearchresult;
Table dropped.
ops$tkyte@ORA817DEV> create table tblctsearchresult ( userseq number, clientlistrowid number,
datestamp date, userid varchar2(30)
2 );
Table created.
ops$tkyte@ORA817DEV> drop table v_clientlist;
Table dropped.
ops$tkyte@ORA817DEV> create table v_clientlist ( id number , buscodeid number );
Table created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace type numberTableType as table of number
2 /
Type created.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> create or replace function str2numtbl( p_string in varchar2 ) return
numberTableType
2 as
3 l_data numberTableType := numberTableType( 1,2,3 );
4 begin
5 return l_data;
6 end;
7 /
Function created.
ops$tkyte@ORA817DEV> show errors
No errors.
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
2 l_theCursor integer default dbms_sql.open_cursor;
3 begin
4 dbms_sql.parse( l_theCursor, '
5 INSERT INTO
6 tblctsearchresult (userseq, clientlistrowid, datestamp, userid)
7 SELECT :l_seq, id, sysdate, user
8 FROM v_clientlist
9 WHERE buscodeid IN
10 ( SELECT * FROM THE ( SELECT CAST( str2numtbl ( :p_cbus ) as
11 numbertabletype ) from dual ) )
12 ',
13 dbms_sql.native );
14
15 dbms_sql.bind_variable( l_theCursor, 'l_seq', 100 );
16 dbms_sql.bind_variable( l_theCursor, 'p_cbus', 'hello' );
17
18 dbms_output.put_line( dbms_sql.execute( l_theCursor ) );
19
20 end;
21 /
0
PL/SQL procedure successfully completed.
Any reason you are not using execute immediate???
re "invalid column error"
February 4, 2004 - 4pm Central time zone
Reviewer: robert from ct
This is 8.1.7
Followup February 4, 2004 - 6pm Central time zone:
see above, cannot reproduce.
re Error "invalid column name" in PARSE
February 5, 2004 - 11am Central time zone
Reviewer: Robert from CT
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
Followup February 5, 2004 - 7pm Central time zone:
sys_context :) I use to bind in native dynamic sql.
sql%rowcount won't work at all with dbms-sql, that'll only work with static sql.
dbms-sql returns the number of rows affected, dbms_sql.execute returns the rows affected.
Inconsistent datatypes
February 5, 2004 - 12pm Central time zone
Reviewer: Alan Wagner from Chicago, IL
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
Followup February 6, 2004 - 8am Central time zone:
can you get the query down to the smallest possible example and include the entire test case (eg: a
script anyone of us could run on our machine). remove tables -- remove as much as you can (perhaps
then, it'll be obvious what the issue is -- but maybe not)
Make it "really small"
Re:Inconsistent datatypes
February 6, 2004 - 3pm Central time zone
Reviewer: Alan from Chicago
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
Followup February 7, 2004 - 2pm Central time zone:
use the syntax on line 11 -- yours above isn't like mine way above -- but that was the "old
deprecated 8.0" synatx anyway, this is much easier now in 8i and up:
ops$tkyte@ORA10G> CREATE OR REPLACE PROCEDURE sel_tpc_for_entity2
2 (
3 tpcs in varchar2
4 )
5 as
6 w_name varchar2(80);
7 cursor rule_val is
8 select descript
9 from yourTable
10 where cde in
11 (select * from TABLE( cast(str2tbl(tpcs) as sgyTpcSel)) );
12 begin
13
14 Open rule_val;
15 Loop
16 Fetch rule_val
17 into w_name;
18 exit when rule_val%NOTFOUND;
19 end loop;
20 close rule_val;
21 end ;
22 /
Procedure created.
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec sel_tpc_for_entity2( '999200,2983' );
PL/SQL procedure successfully completed.
Re:Inconsistent DataTypes
February 9, 2004 - 10am Central time zone
Reviewer: Alan from Chicago
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
Followup February 9, 2004 - 10am Central time zone:
please define "l_data is unknown and does not get...". that doesn't make sense to me.
ops$tkyte@ORA920PC> create or replace type sgyTpcSel as table of number
2 /
Type created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> CREATE OR REPLACE FUNCTION str2tbl( tpcs in varchar2 ) return sgyTpcSel
2 as
3 l_str varchar2(255) default tpcs || ',';
4 l_n number;
5 l_data sgyTpcSel := sgyTpcSel();
6 begin
7 loop
8 l_n := instr( l_str, ',' );
9 exit when (nvl(l_n,0) = 0);
10 l_data.extend;
11 l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
12 l_str := substr( l_str, l_n+1 );
13 end loop;
14 return l_data;
15 end;
16 /
Function created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select *
2 from TABLE( cast( str2tbl( '1,2,3' ) as sgyTpcSel ) )
3 /
COLUMN_VALUE
------------
1
2
3
ops$tkyte@ORA920PC>
how to use with bind variables?
March 2, 2004 - 9am Central time zone
Reviewer: Ryan Gaffuri from Tysons Corner, VA
Anyway to set this up so we can reuse sql and not parse it every time
Followup March 2, 2004 - 3pm Central time zone:
bind the string '1,2,3' -- don't use a literal, bind a string with 1,2,3 in it.
hard parsing and variable inlist
March 8, 2004 - 12pm Central time zone
Reviewer: Ryan from Tysons Corner, VA
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;
Followup March 8, 2004 - 2pm Central time zone:
run it a couple of times -- you are counting all sql in your session. run it over and over and you
should eventually see "0" hard parses.
March 23, 2004 - 4pm Central time zone
Reviewer: Rob Kato from Chicago, IL
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?
Efficient static query
June 28, 2004 - 3pm Central time zone
Reviewer: Sha from USA
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,
Use AND instead of OR functionality
July 30, 2004 - 5am Central time zone
Reviewer: Mark Nijhof from Norway
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
Followup July 30, 2004 - 4pm Central time zone:
in for a penny, in for a pound.
a little dynamic sql -> it is entirely dynamic sql.
varying elements in IN list
August 4, 2004 - 4am Central time zone
Reviewer: Ramakrishnan from India
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)
/
Followup August 4, 2004 - 10am Central time zone:
sure:
ops$tkyte@ORA9IR2> variable x varchar2(255)
ops$tkyte@ORA9IR2> exec :x := '10,22,33,44,45'
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select
2 SUBSTR(:x,
3 instr( ','||:x||',', ',', 1, rownum ),
4 instr( ','||:x||',', ',', 1, rownum+1)
5 - instr(','||:x||',', ',', 1, rownum)-1 ) column_value
6 from all_objects
7 where rownum <= (length(:x)-length(replace(:x,',',''))+1)
8 /
COLUMN_VALUE
-------------------------------------------------------------------------------
10
22
33
44
45
is another way.
Help with Bind Varibles
August 12, 2004 - 6am Central time zone
Reviewer: A reader
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
Followup August 12, 2004 - 9am Central time zone:
ummm, page up? that is the crux of what this page is all about.
why would this not work?
September 17, 2004 - 5pm Central time zone
Reviewer: Mary from washington dc
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
Followup September 17, 2004 - 7pm Central time zone:
that is the same as:
select number_field from tableA
where number_field in ( '0,1' );
(you are probably getting "invalid number" as '0,1' ISN'T a number)
please see the original response way at the top of this page -- it is precisely the same exact
issue.
a pure sql-way to do it
October 10, 2004 - 6pm Central time zone
Reviewer: SivadLeima from Jerusalem, IL
Tom, your words makes me proud at being an oracle developer
Here is a pure sql way for doing it:
SQL> Select * From V$Version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
SQL> var Str VarChar2(4000);
SQL> begin
2 :Str := 'PUBLIC,QS_CBADM,OUTLN';
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> Select SubStr(:Str, Cut + 1,
2 Case When Len > 0 Then
3 Len - 1
4 Else
5 Length(:Str) - Cut
6 End )
7 As List
8 From
9 (
10 Select Cut, Lead(cut,1,0) Over (order by cut) - Cut As Len From
11 (
12 Select Distinct InStr(:Str, ',', RowNum) As Cut
13 From all_objects Where RowNum <= Length(:str)
14 )
15 )
16 /
LIST
--------------------------------------------------------------------------------
PUBLIC
QS_CBADM
OUTLN
e.g, a query that use this way:
SQL> Select Count(*) From All_Objects
2 Where
3 Owner In (
4 Select SubStr(:Str, Cut + 1,
5 Case When Len > 0 Then
6 Len - 1
7 Else
8 Length(:Str) - Cut
9 End )
10 As List
11 From
12 (
13 Select Cut, Lead(cut,1,0) Over (order by cut) - Cut As Len From
14 (
15 Select Distinct InStr(:Str, ',', RowNum) As Cut
16 From all_objects Where RowNum <= Length(:str)
17 )
18 )
19 )
20 /
COUNT(*)
----------
11571
SQL> print str
STR
--------------------------------------------------------------------------------
PUBLIC,QS_CBADM,OUTLN
SQL> Begin
2 :Str := 'QS_CBADM,OUTLN';
3 End;
4 /
PL/SQL procedure successfully completed.
SQL> Print Str
STR
---------------------------------------------------------------------------
QS_CBADM,OUTLN
SQL> Select Count(*) From All_Objects
2 Where
3 Owner In (
4 Select SubStr(:Str, Cut + 1,
5 Case When Len > 0 Then
6 Len - 1
7 Else
8 Length(:Str) - Cut
9 End )
10 As List
11 From
12 (
13 Select Cut, Lead(cut,1,0) Over (order by cut) - Cut As Len From
14 (
15 Select Distinct InStr(:Str, ',', RowNum) As Cut
16 From all_objects Where RowNum <= Length(:str)
17 )
18 )
19 )
20 /
COUNT(*)
----------
31
Not so generic as your solution, but nice to know it can be done.
Amiel (Holy land, Jerusalem)
Oops....
October 11, 2004 - 5pm Central time zone
Reviewer: SivadLeima
I didn't read the whole thread...
as usual Tom did it before me (about 3 years...)
and in much more elegant way :)
nevertheless it was fun!
Amiel (Holy Land, jerusalem).
Input String larger than 4k
October 21, 2004 - 12pm Central time zone
Reviewer: Vinnie from Orlando
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.
Followup October 21, 2004 - 3pm Central time zone:
use a global temporary table and have the java app bulk insert into it and use
where in ( select * from gtt)
Bulk Insert
October 22, 2004 - 10am Central time zone
Reviewer: Vinnie from Orlando
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?
Followup October 22, 2004 - 5pm Central time zone:
it would be better for sqlplus to do it ONCE?
SQL> create global temporary table gtt ( x number ) on commit delete rows;
period -- it becomes part of your application schema. don't even think about dynamically creating
these at run time (might as well just not bind if you did that! it would be a hard parse each time
since you have a different table each time)
Rownum with Table Operator??
October 23, 2004 - 2am Central time zone
Reviewer: Prasad
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
Followup October 23, 2004 - 10am Central time zone:
sorry -- not following you.
if you just wanted "row n" from both, why not
select l_status_list(N), l_count_list(N) from dual;
??
October 23, 2004 - 10am Central time zone
Reviewer: Prasad
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
Followup October 23, 2004 - 10am Central time zone:
wonder how your lack of clarity translates into the rating. always curious out that. If I don't
immediately see the question. hmmm. (this followup directly conflicts with your prior stated goal
of:
<quote>
But what I really want to do is join l_status_list and l_count_list so that I
only get following result
PLANNED 3
VERIFIED 10
</quote>
you want to join these two collections by their indices apparently, very very different from what
you said above in words and in "depiction"
but anyway:
open p_result for
SELECT a.column_value ac, b.column_value bc FROM
(select rownum r, column_value
from TABLE(cast (l_status_list AS VarCharTableType))) a,
(select rownum r, column_value
from TABLE(cast (l_count_list AS NumberTableType))) b
where a.r = b.r;
althoug it seems to me that you should have ONE collection (eg: your type is wrong) with two
attributes instead of two collections.
Rownum with Table Operator
October 23, 2004 - 10am Central time zone
Reviewer: Prasad
Lower rating because your first reply didn't help me at all. It applies to your first reply only.
I because a PL/SQL programmer 3 months ago after nearly 4 years of Java :). And already made your
site my home page!, and its late here in and I kept awake to see if you answer my question.
I know my collection is wrong, but this will do the job till I get sorted it out. Will try your
answer at work on Monday.
Thank you.
GTT
October 24, 2004 - 8pm Central time zone
Reviewer: Vinnie from Orlando
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?
Followup October 25, 2004 - 7am Central time zone:
"and the session generates these reports in parallel"
please elaborate -- as a session is a serial thing.
GTT
October 25, 2004 - 11am Central time zone
Reviewer: Vinnie from Orlando
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?
Followup October 25, 2004 - 11am Central time zone:
how does it do that -- only one thread at a time could possibly use the session. are you sure it
works as you describe (it would not be "threaded" at the database level, it must be serialized --
what would the point of the threads be in that case...)
GTT
October 25, 2004 - 12pm Central time zone
Reviewer: Vinnie from Orlando
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?
Followup October 25, 2004 - 1pm Central time zone:
they won't be executing them in parallel -- one at a time. only one thread is going to use that
connection at a time.
doesn't seem to make much sense, why would they not have their own connections.
but yes, as it stands, an "on commit delete rows" gtt would be an issue.
like operator
November 4, 2004 - 12am Central time zone
Reviewer: Prasad
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
Followup November 5, 2004 - 11am Central time zone:
no, that bind variable is a SINGLE CHARACTER STRING -- PERIOD.
it is "not a list"
that would not work at all.
it really sounds like you want to use Oracle Text and its significantly more advance text search
capabilities via the CONTAINS clause in SQL. See the Oracle Text docs.
Without using str2tbl
December 16, 2004 - 11am Central time zone
Reviewer: A reader
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
Followup December 16, 2004 - 11am Central time zone:
yes, you could create a full object type -- but how would that be "pure sql" since the constructor
would itself be written in plsql? you gotta parse the string.
Full object type
December 16, 2004 - 2pm Central time zone
Reviewer: A reader
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
Followup December 16, 2004 - 2pm Central time zone:
in order to have a non-default constructor, you'll be creating a type body. The body would contain
the code in str2tbl.
you have a string
string needs to be parsed
code to do parsing must be written
code that does parsing would be in the type body.
Why CAST?
December 29, 2004 - 9am Central time zone
Reviewer: A reader
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
Followup December 29, 2004 - 10am Central time zone:
remove it and see -- later releases did not need it, earlier ones did.
CAST
December 29, 2004 - 11am Central time zone
Reviewer: A reader
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
Followup December 29, 2004 - 7pm Central time zone:
create or replace function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' ) return
and global replace ',' with p_delim (and change logic to be your "character at a time" one --
although you might just write str2chars for that....)
Delimiter
December 29, 2004 - 11am Central time zone
Reviewer: A reader
OK I figured it out
CREATE OR REPLACE FUNCTION str2tbl(
p_string IN VARCHAR2,
p_delimiter IN VARCHAR2 DEFAULT ','
)
RETURN varchar2_tab
AS
l_string LONG DEFAULT p_string || p_delimiter;
l_index INTEGER;
l_table varchar2_tab := varchar2_tab();
BEGIN
IF (p_string IS NULL)
THEN
RETURN l_table;
END IF;
IF (p_delimiter IS NULL)
THEN
FOR i IN 1..length(l_string)
LOOP
l_table.extend;
l_table(l_table.count) := trim(substr(l_string,i,1));
END LOOP;
RETURN l_table;
END IF;
LOOP
l_index := instr(l_string,p_delimiter);
exit when (nvl(l_index,0) = 0);
l_table.extend;
l_table(l_table.count) := trim(substr(l_string,1,l_index-1));
l_string := substr(l_string,l_index+length(p_delimiter));
END LOOP;
RETURN l_table;
END str2tbl;
/
Thanks
How to display the Items in the list but missing in the Table
February 2, 2005 - 5am Central time zone
Reviewer: kamal from India
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
Followup February 2, 2005 - 7am Central time zone:
did you see above?
select column_value
from table( cast( str2tbl( :your_bind ) as myTableType )
where column_value not in ( select name from t );
just put them in a string, or if they are in an arrray already in your program, you could use that
directly. eg:
ops$tkyte@ORA9IR2> create or replace type myTableType as table of varchar2(30)
2 /
Type created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 l_names myTableType := myTableType('SYS', 'a', 'b', 'c', 'SYSTEM' );
3 begin
4 for x in (select column_value
5 from table( cast(l_names as myTableType) )
6 where column_value not in (select username from all_users) )
7 loop
8 dbms_output.put_line( x.column_value );
9 end loop;
10 end;
11 /
a
b
c
PL/SQL procedure successfully completed.
using variable as a where clause
February 10, 2005 - 9am Central time zone
Reviewer: Thiru
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.
Followup February 11, 2005 - 3am Central time zone:
you have to use dynamic sql, be it native dynamic sql
open refcursor for sql_statement;
or dbms_sql.
and MAKE SURE you binds.
and watch out for sql injection.
Variable Like list
April 22, 2005 - 10am Central time zone
Reviewer: koms from NY
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,
Followup April 22, 2005 - 10am Central time zone:
you cannot, in 10g a regular expression could probably do that in a single regexp_like call -- but
in 9ir2 and before, you have a bunch of "ors" (or use a TEXT index -- and a single contains clause)
like-list
April 23, 2005 - 8am Central time zone
Reviewer: AndersH
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.
need to suppress semi-join on a collection (always_semi_join can't be considered)
June 6, 2005 - 5pm Central time zone
Reviewer: Vladimir Sadilovskiy from MA, U.S.
Tom,
Can you please explain
1. Why CBO considers semi-join on a collection in "IN-LIST". Such course of actions propagates
incorrect cardinality to the further steps of the execution path and eventually leads to a bad plan
on high cardinalities of the collection.
Artificial setup environment can be achieved by running following:
create table t1 as select * from all_objects;
create unique index t1_i on t1(object_id);
create table t2 as select * from all_objects;
create index t2_i on t2(object_id);
update t1 set status = 0;
update t2 set status = 0;
create or replace type table_of_number as table of number;
/
create or replace function getnumericlist(card_n number) return table_of_number
as
l_numeric_list table_of_number;
begin
select object_id
bulk collect into l_numeric_list
from (select distinct object_id from t1)
where rownum <= card_n;
return l_numeric_list;
end getnumericlist;
/
begin
dbms_stats.delete_table_stats(null, 't1');
dbms_stats.gather_table_stats(null, 't1',
estimate_percent => 100,
method_opt => 'for all columns size 254',
cascade => true);
dbms_stats.delete_table_stats(null, 't2');
dbms_stats.gather_table_stats(null, 't2',
estimate_percent => 100,
method_opt => 'for all columns size 254',
cascade => true);
end;
/
Test query:
select count(*) from (
select t1.object_name,t1.object_type, t2.object_name
from t1, t2
where t1.object_id in (select /*+ cardinality(nlist 1000) */ *
from table(cast(getnumericlist(1000) as table_of_number)) nlist)
and t2.object_id = t1.object_id
and t1.status = 0
and t2.status = 0);
(for proper test use same value for getnumericlist function as for cardinality parameter)
Plan for low carinality of the collection:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=71 Card=1 Bytes=16)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=7)
3 2 NESTED LOOPS (Cost=71 Card=10 Bytes=160)
4 3 NESTED LOOPS (Cost=51 Card=10 Bytes=90)
5 4 SORT (UNIQUE)
6 5 COLLECTION ITERATOR (PICKLER FETCH) OF 'GETNUMERICLIST'
7 4 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=1 Card=1 Bytes=7)
8 7 INDEX (UNIQUE SCAN) OF 'T1_I' (UNIQUE)
9 3 INDEX (RANGE SCAN) OF 'T2_I' (NON-UNIQUE) (Cost=1 Card=1)
Offending plan for collection cardinality > 13 (my case.. magic number!? :)) :
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=93 Card=1 Bytes=16)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=2 Card=1 Bytes=7)
3 2 NESTED LOOPS (Cost=93 Card=1 Bytes=16)
4 3 HASH JOIN (SEMI) (Cost=91 Card=1 Bytes=9)
5 4 TABLE ACCESS (FULL) OF 'T1' (Cost=51 Card=30295 Bytes=212065)
6 4 COLLECTION ITERATOR (PICKLER FETCH) OF 'GETNUMERICLIST'
7 3 INDEX (RANGE SCAN) OF 'T2_I' (NON-UNIQUE) (Cost=1 Card=1)
As you can see the cardinality of the outcome from collection and T1 is 1. It is always evaluated
as 1 regardless of the collection cardinality. In fact it is evaluated as "card(t1) *
selectivity(t1.object_id)" from 10053 trace.
Result of the offending query with collection cardinality = 100000
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
55746 consistent gets
0 physical reads
0 redo size
211 bytes sent via SQL*Net to client
344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
Probably one can predict that hash join on T2 reduces LIOs substantially:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=143 Card=1 Bytes=16)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=143 Card=1 Bytes=16)
3 2 HASH JOIN (SEMI) (Cost=91 Card=1 Bytes=9)
4 3 TABLE ACCESS (FULL) OF 'T1' (Cost=51 Card=30295 Bytes=212065)
5 3 COLLECTION ITERATOR (PICKLER FETCH) OF 'GETNUMERICLIST'
6 2 TABLE ACCESS (FULL) OF 'T2' (Cost=51 Card=30296 Bytes=212072)
Statistics
----------------------------------------------------------
6 recursive calls
0 db block gets
456 consistent gets
0 physical reads
0 redo size
211 bytes sent via SQL*Net to client
344 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
1 rows processed
2. This doesn't happend with real tables. Is it possible to suppress semi-join for this particular
type of queries?
Side note: always_semi_join=off isnÂ’t working for me in some versions (9.2.0.5+SA #68, 9.2.0.6);
10.1.0.2 works by I'd rather find the conceptual workaround that will allow legitimate semi-joins
take place.
Thanks.
- Vladimir
should have used _always_semi_join=off instead... still...
June 9, 2005 - 12am Central time zone
Reviewer: Vladimir Sadilovskiy from MA, U.S.
response fixed my problem
August 1, 2005 - 6am Central time zone
Reviewer: Tommy Skodje from Oslo, Norway
4000 character limit
November 1, 2005 - 7pm Central time zone
Reviewer: Julius from Fremont, CA
In response to the 4000 character limit ( http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:110612348061#9734635621139
I was wondering if replacing varchar2 with clob would help?
DEV1>select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for Solaris: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production
DEV1>create or replace type t_NTtype as table of number;
2 /
Type created.
DEV1>create or replace function sss( p_str in clob )
2 return t_NTtype
3 as
4 l_str clob default p_str || ',';
5 l_n number;
6 l_data t_NTtype := t_NTtype();
7 l_cnt number := 0;
8 begin
9 loop
10 l_n := instr( l_str, ',' );
11 exit when (nvl(l_n,0) = 0);
12 l_cnt := l_cnt + 1;
13 l_data.extend;
14 l_data( l_data.count ) := to_number(ltrim(rtrim(substr(l_str,1,l_n-1))));
15 l_str := substr( l_str, l_n+1 );
16 end loop;
17 dbms_output.put_line('l_data.count='||l_data.count);
18 return l_data;
19 end;
20 /
Function created.
DEV1>create table t1 (id number);
Table created.
DEV1>insert into t1 values (1);
1 row created.
DEV1>insert into t1 values (12);
1 row created.
DEV1>insert into t1 values (123);
1 row created.
DEV1>set serveroutput on size 1000000
DEV1>declare
2 v_didtab t_NTtype;
3 v_dlist varchar2(32000) := '';
4 v_dlist_c clob;
5 v_nn number := 0;
6
7 begin
8
9 v_dlist := lpad('123,',30000,'123,');
10 dbms_output.put_line('length(v_dlist)='||length(v_dlist));
11 dbms_output.put_line(substr(v_dlist,1,50));
12
13 v_dlist_c := v_dlist;
14
15 select id bulk collect into v_didtab
16 from T1
17 where
18 id in
19 (
20 select /*+ cardinality (t 10) */ * from
21 table(cast( sss( v_dlist_c ) as t_NTtype )) t
22 where rownum>=0
23 );
24
25 end;
26 /
length(v_dlist)=30000
123,123,123,123,123,123,123,123,123,123,123,123,12
l_data.count=7501
PL/SQL procedure successfully completed.
DEV1>
variable inlist
December 29, 2005 - 9am Central time zone
Reviewer: A reader
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!
Followup December 29, 2005 - 12pm Central time zone:
ops$tkyte@ORA9IR2> create table t as select * from all_users where 1=0;
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
2 l_string varchar2(100) := 'SCOTT, SYS';
3 begin
4 insert into t
5 select *
6 from all_users
7 where username in
8 ( select trim(
9 substr (txt,
10 instr (txt, ',', 1, level ) + 1,
11 instr (txt, ',', 1, level+1) - instr (txt, ',', 1,
level) -1 ))
12 from (select ','||l_string||',' txt from dual)
13 connect by level <=
length(l_string)-length(replace(l_string,',',''))+1
14 );
15 end;
16 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> select * from t;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SCOTT 60 06-DEC-03
SYS 0 06-DEC-03
Is another way in 9ir2 and above to do this.
The laster 10g version of function str2tbl
January 12, 2006 - 7pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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
Followup January 13, 2006 - 11am Central time zone:
that works, or you can:
ops$tkyte@ORA9IR2> variable txt varchar2(25)
ops$tkyte@ORA9IR2> exec :txt := 'a,bb,ccc,d,e,f';
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> with data
2 as
3 (
4 select substr (txt,
5 instr (txt, ',', 1, level ) + 1,
6 instr (txt, ',', 1, level+1) - instr (txt, ',', 1, level) -1 )
7 as token
8 from (select ','||:txt||',' txt from dual)
9 connect by level <= length(:txt)-length(replace(:txt,',',''))+1
10 )
11 select * from data;
TOKEN
----------------------------------
a
bb
ccc
d
e
f
6 rows selected.
Too complex for Java Programmer
January 13, 2006 - 2pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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,770
95,77096,77097,147129646,147129670,147129684,147129696,147129718,147129734,147129754,606842501,14712
9776,147129788,147129796,147129802,147129850,147129862,147129864,147129866,147129868,606841856,60684
1857';
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%
Followup January 13, 2006 - 2pm Central time zone:
educate the java programmer. Hey, if they can do Java, they better be able to get their head
around a little SQL.
Use either one though.
Is there a way to apply this to another query?
January 27, 2006 - 7pm Central time zone
Reviewer: Gary from St. Louis, MO
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 ) )
Followup January 28, 2006 - 12pm Central time zone:
ops$tkyte@ORA10GR1> create table t ( x varchar2(20) );
Table created.
ops$tkyte@ORA10GR1> insert into t values ( '1,2,3' );
1 row created.
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> select *
2 from table( str2tbl( (select x from t) ) )
3 /
COLUMN_VALUE
------------------------------
1
2
3
just need one more set of ()'s
Thanks!
January 29, 2006 - 6pm Central time zone
Reviewer: Gary from St. Louis, MO
In hindsight, that makes sense. I was beating my head against a wall trying to get it to work and
probably tried everything BUT the additional enclosing parens. :-)
Thanks!!
To reduce JDBC network traffic
March 8, 2006 - 3pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
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%
Followup March 9, 2006 - 12pm Central time zone:
I don't understand the question?
It's a suggestion
March 10, 2006 - 6pm Central time zone
Reviewer: Charlie Zhu from Victoria, BC Canada
No question here.
An enhancement, you only need to pass one Variable/Parameter to JDBC call.
{code}
PreparedStatement pstat = null;
String l_string, l_sql;
l_string = "535657837,535657838,238732767,238732768,238732769,238732770";
l_sql = "WITH sq " +
"as " +
"( " +
" SELECT substr(x, instr(x,',',1,level)+1, instr(x,',',1,level+1) - instr(x,',',1,level) -1) token
" +
" FROM (select ','||l_str||',' x, l_str from (select ? l_str from dual)) " +
" CONNECT BY level <= length(l_str)-length(replace(l_str,',',''))+1 " +
") " +
"select /* */ " +
"a.listingsid, a.bsacode " +
"from abelisting.Listsbyrsrchattrs a, sq " +
"where a.listingsid = To_Number(sq.token) " ;
pstat = conn1.prepareStatement(l_sql);
pstat.setString(1, l_string);
ResultSet rset = pstat.executeQuery();
{code}
Maybe, not exactly match the theme, but...
March 16, 2006 - 2pm Central time zone
Reviewer: Dmytro from Kyiv, Ukraine
...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?
Wow, helped a lot!..
March 17, 2006 - 5am Central time zone
Reviewer: Dmytro from Kyiv, Ukraine
Approach with application context is even much more flexible, than our open for ... using
statements. Before, I need to use dbms_sql to do queries with variable parameter lists and after
that, have a lot of problems, transforming table-type into refcursor, because our client-side
developers want no other types, than refcursors. :)
Thanks. Cool as always ;)
Confused about this query's behaviour??
April 5, 2006 - 5pm Central time zone
Reviewer: Maverick from USA
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
But Query builds correctly
April 6, 2006 - 10am Central time zone
Reviewer: Maverick
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,
Followup April 7, 2006 - 3pm Central time zone:
5 select count(*) into v_count from emp_char
6 where empno in v_emp
v_emp is a scalar thing - NOT A SET, it is a single string. a string that contains the values
'1234', '2345'
it is NOT a set
it is a scalar value
that is all.
Compensate amount
April 11, 2006 - 4am Central time zone
Reviewer: Debasish
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
Followup April 11, 2006 - 2pm Central time zone:
wow, that is a bad design isn't it.
no create
no inserts
no look
Brute force....
April 11, 2006 - 2pm Central time zone
Reviewer: Lars Stampe Villadsen from Copenhagen, Denmark
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...
Followup April 11, 2006 - 7pm Central time zone:
oh we can do it with a self join - but
no create
no inserts
no look
Thank you Tom
September 5, 2006 - 1pm Central time zone
Reviewer: Lorenzo from Madrid, Spain
I have some version of oracle 9i and the CAST is needed.
Great post.
I've passed this technique to a coworker
September 8, 2006 - 5pm Central time zone
Reviewer: Mark Brady from Baltimore, MD USA
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?
Followup September 9, 2006 - 12pm Central time zone:
it is "archaic" - it was the original 8.0 syntax. it is not necessary.
"THE" keyword
September 11, 2006 - 9am Central time zone
Reviewer: Paul James from The Hague, Netherlands
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?
Followup September 11, 2006 - 10am Central time zone:
you need to use the "TABLE" syntax in place of the "archaic" "THE" syntax.
ops$tkyte%ORA10GR2> select * from all_users
2 where user_id in ( select *
3 from THE ( select cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) from dual ) )
4 /
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM 5 30-JUN-05
ops$tkyte%ORA10GR2> select * from all_users
2 where user_id in (select * from TABLE( str2tbl( '1, 3, 5, 7, 99' ) ) )
3 /
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYSTEM 5 30-JUN-05
"THE" keyword - part 2
September 11, 2006 - 9am Central time zone
Reviewer: Paul James from The Hague, Netherlands
Finally found a reference for "THE".
See:
http://www.unix.org.ua/orelly/oracle/prog2/ch19_05.htm
type cast in a string
October 25, 2006 - 3pm Central time zone
Reviewer: sara from nj
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
type cast in a string
October 26, 2006 - 10am Central time zone
Reviewer: sara from nj
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';
Followup October 26, 2006 - 12pm Central time zone:
I don't know what you mean, I cannot actually, well, you know - RUN your example, so I cannot help
you debug it. If you gave a SHORT CONCISE yet 100% complete (did I mention SHORT, as small as
POSSIBLE).
all i see is YOU ARE NOT USING BIND VARIABLES - STOP THAT RIGHT NOW, use binds.
open p_cursor
for 'select ...... :string ....' USING p_con_id;
varyi9ng in list
March 7, 2007 - 4am Central time zone
Reviewer: Herbert from Netherlands
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.
Followup March 7, 2007 - 10am Central time zone:
because the database KNOWS that the context function should work like a bind variable.
hence sys_context is called ONCE per query and "bound in" in effect.
Where as:
where x = your_pkg.your_function
might have your_pkg being invoked many millions or billions of times. Sys_context is known to the sql parser, your function is not. it is more efficient.
is there way to create index on object columns?
March 7, 2007 - 2pm Central time zone
Reviewer: Yong from USA
I have client use object table type as
SQL> desc swc_ypub_file_instance
Name Null? Type
----------------------------------------- -------- ----------------------------
INSTANCE_ID NOT NULL NUMBER
FILE_ID NOT NULL NUMBER
INSTANCE_STATUS_CD NOT NULL VARCHAR2(10)
PUBLISH_USER_ID NOT NULL VARCHAR2(20)
PUBLISH_DATE NOT NULL DATE
CREATE_USER_ID NOT NULL VARCHAR2(20)
CREATE_DATE NOT NULL DATE
UPDATE_USER_ID NOT NULL VARCHAR2(20)
UPDATE_DATE NOT NULL DATE
AVAILABLE_FOR_DOWNLOAD_IND VARCHAR2(1)
OBJECT_ID NUMBER
FILE_PATH SWC_ADMIN.FILE_PATH_TABLE
ATTR_VALUE SWC_ADMIN.ATTR_VALUE_TABLE
SQL> desc SWC_ADMIN.ATTR_VALUE_TABLE
SWC_ADMIN.ATTR_VALUE_TABLE TABLE OF SWC_ADMIN.ATTR_VALUE_TYPE
Name Null? Type
----------------------------------------- -------- ----------------------------
ATTR_VALUE_ID NUMBER
ATTR_ID NUMBER
ATTR_LOCALE_CD VARCHAR2(8)
ATTR_SEQ_NUM NUMBER
CREATE_USER_ID VARCHAR2(20)
CREATE_DATE DATE
UPDATE_USER_ID VARCHAR2(20)
UPDATE_DATE DATE
ATTR_VALUE_TEXT VARCHAR2(4000)
is there way to create index on attr_id and attr_value_text
for query like this?
select TABLE(FILE_INST.attr_value)
from swc_ypub_file_instance file_inst
where inst_attval.attr_id = 204
AND inst_attval.attr_value_text ='280831063'
/
Loop in PL/SQL
March 14, 2007 - 10pm Central time zone
Reviewer: A reader from Singapore
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
Followup March 15, 2007 - 9am Central time zone:
ops$tkyte%ORA10GR2> declare
2 type array is table of varchar2(2000);
3 l_data array := array( 'x','y','z');
4 begin
5 for i in 1 .. l_data.count
6 loop
7 dbms_output.put_line( 'processing ' || l_data(i) );
8 end loop;
9 end;
10 /
processing x
processing y
processing z
PL/SQL procedure successfully completed.
Thank you!
March 15, 2007 - 9pm Central time zone
Reviewer: A reader from Singapore
creating type outside package
April 4, 2007 - 2am Central time zone
Reviewer: Lakshmi from india
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?
Followup April 4, 2007 - 10am Central time zone:
because myTableType does not exist in SQL, it exists in plsql - plsql is a layer on TOP of sql.
create the type as I have demonstrated.
working in someother case ...
April 4, 2007 - 1pm Central time zone
Reviewer: Lakshmi from india
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
Followup April 4, 2007 - 3pm Central time zone:
you are fetching into a host variable, SQL never sees your plsql table, it is a program variable being retrieved into.
Variable LIKE list
January 15, 2008 - 1am Central time zone
Reviewer: Duke Ganote from the bluegrass region of Ohio, USA
I had a situation where someone had added columns to our datawarehouse table, but not yet to a
datamart table (in the same database). I wanted to see what stored PL/SQL used those new columns.
I found koms' question on a variable LIKE list
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061#39386992947284
and AndersH's comment on a LIKE-list thereafter.
Since I'm querying data dictionary tables, I assume your preferred solution of text indexing
doesn't apply.
So my approach was similar to AndersH's:
SQL> create table datawarehouse_table ( x number, new_column_name number );
Table created.
SQL> create table datamart_table ( x number );
Table created.
SQL> create procedure etl_procedure is
2 begin
3 update datawarehouse_table set new_column_name = 1;
4 end; -- just a lame demo of ETL
5 /
Procedure created.
SQL> select s.name, s.line
2 , t.column_name
3 from user_source s
4 , ( select column_name
5 from user_tab_columns
6 where table_name = 'DATAWAREHOUSE_TABLE'
7 MINUS
8 select column_name
9 from user_tab_columns
10 where table_name = 'DATAMART_TABLE'
11 ) t
12 where UPPER(s.text) like '%'||t.column_name||'%'
13 /
NAME LINE COLUMN_NAME
------------------------------ ---------- -----------------
ETL_PROCEDURE 3 NEW_COLUMN_NAME
or, using REGEXP_LIKE instead of ESCAPE_
January 15, 2008 - 8am Central time zone
Reviewer: Duke Ganote
drop procedure etl_procedure
/
create or replace procedure etl_procedure is
begin
update datawarehouse_table set new_column_name = 1;
end; -- %newbcolumnbname%
/
select s.name, s.line
, t.column_name
from user_source s
, ( select column_name
from user_tab_columns
where table_name = 'DATAWAREHOUSE_TABLE'
MINUS
select column_name
from user_tab_columns
where table_name = 'DATAMART_TABLE'
) t
where REGEXP_LIKE( UPPER(s.text) , t.column_name )
/
NAME LINE COLUMN_NAME
------------------------------ ---------- ----------------
ETL_PROCEDURE 3 NEW_COLUMN_NAME
Something strange???
April 23, 2008 - 12am Central time zone
Reviewer: Tim
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?
Followup April 28, 2008 - 9am Central time zone:
when comparing string literals, they are considered "char" types.
ops$tkyte%ORA10GR2> select * from dual where '1' = '1 ';
D
-
X
ops$tkyte%ORA10GR2> select * from dual where '1' = ' 1';
no rows selected
when a char(n) is compared to a char(m) (different lengths) the lesser of the two is (by ANSI definition) promoted to the other longer length
hence:
'1' = '1 '
is identical to
'1 ' = '1 '
and
'1' = ' 1'
is identical to
'1 ' = ' 1'
so, "one<blank>" = "one<blank>" but "one<blank>" is NOT equal to "<blank>one"
Thanks
May 1, 2008 - 3am Central time zone
Reviewer: Tim
Thanks
May 1, 2008 - 3am Central time zone
Reviewer: Tim
Using a refcursor with a varying in list
December 2, 2008 - 3pm Central time zone
Reviewer: A reader
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.
Followup December 9, 2008 - 9am Central time zone:
ops$tkyte%ORA10GR2> create or replace type myTableType as table of number
2 /
Type created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure test_proc( p_inputs in myTableType, p_cursor in out
sys_refcursor )
2 as
3 begin
4 open p_cursor
5 for 'select * from all_users where user_id in (select * from TABLE(:x) )' using
p_inputs;
6 end;
7 /
Procedure created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> variable x refcursor
ops$tkyte%ORA10GR2> exec test_proc( myTableType( 0, 5, uid ), :x )
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> print x
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
SYS 0 30-JUN-05
SYSTEM 5 30-JUN-05
OPS$TKYTE 481 24-NOV-08
Using a refcursor with a varying in list
December 10, 2008 - 10pm Central time zone
Reviewer: A reader
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?
Followup December 11, 2008 - 7am Central time zone:
it might make sense to use a SINGLE cardinality that represents the best guess as to the average amount of elements.
What I'm trying to say is "do not use cardinality 1, cardinality 2, .... cardinality N" - just use "cardinality 12" if 12 is about the average.
else the default number of rows will be based on the block size - I have an 8k block size so 8168 is the 'guess' used for me
ops$tkyte%ORA10GR2> declare
2 l_data sys.odcinumberlist := sys.odcinumberlist(1,2,3);
3 begin
4 for x in (select * from dual, table(l_data))
5 loop
6 null;
7 end loop;
8 for x in (select * from table(dbms_xplan.display_cursor))
9 loop
10 dbms_output.put_line( x.plan_table_output );
11 end loop;
12 end;
13 /
SQL_ID 0bgvv3nyvwc27, child number 0
-------------------------------------
SELECT * FROM DUAL, TABLE(:B1 )
Plan hash value: 3363747227
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 35 (100)| |
| 1 | NESTED LOOPS | | 8168 | 32672 | 35 (3)| 00:00:01 |
| 2 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| 00:00:01 |
| 3 | COLLECTION ITERATOR PICKLER FETCH| | | | | |
-------------------------------------------------------------------------------------------
PL/SQL procedure successfully completed.
Replacing IN list with WITH clause causes an error
January 29, 2009 - 6am Central time zone
Reviewer: Lise from Scotland
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
Followup January 30, 2009 - 2pm Central time zone:
i cannot even get it to go that far!
probably because I don't have a table named my_tables :(
nor the types
no my_columns....
Here are the details
February 5, 2009 - 4am Central time zone
Reviewer: Lise from Scotland
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
Followup February 5, 2009 - 10am Central time zone:
appears to be 9i specific, works in 10g. It is happening with the double reference to my_columns in the scalar subquery
ops$tkyte%ORA9IR2> DECLARE
2 lt_data_types t_varchar_table := t_varchar_table('NUMBER', 'DATE');
3 ln_count NUMBER;
4 rec SYS_REFCURSOR;
5 BEGIN
6
7 WITH
8 data_types as
9 (SELECT COLUMN_VALUE FROM TABLE(CAST(lt_data_types AS t_varchar_table))),
10 my_columns AS
11 (SELECT table_name
12 FROM user_tab_columns, data_types
13 WHERE data_type = data_types.COLUMN_VALUE),
14 my_tables AS
15 (SELECT TO_CHAR((SELECT COUNT(*)
16 FROM my_columns m
17 WHERE m.table_name = 'Temp1')) "Val"
18 /* ,TO_CHAR((SELECT COUNT(*)
19 FROM my_columns a
20 WHERE a.table_name = 'Temp1')) "Val2" */
21 FROM dual)
22 SELECT COUNT(*) into ln_count
23 FROM my_tables;
24 dbms_output.put_line('Got ' || TO_CHAR(ln_count));
25 END;
26 /
Got 1
PL/SQL procedure successfully completed.
you would have to code by the way:
open cursor for ....
FETCH cursor into ....
close cursor
you would not use a select into with an explicit cursor. You would use an implicit cursor to "into"
Joining two tables where matching column in one table is a comma separated list
March 9, 2009 - 5pm Central time zone
Reviewer: A Reader from NY, USA
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
Followup March 10, 2009 - 2am Central time zone:
.... where the data in the matching column of one the tables (test) is a comma separated list ....
yuck, can you say "will never perform reasonably in our lifetime" ?
You understand that indexing isn't going to help, this will always be a full scan of at least the table with the CSV (comma separate values) list?
can we PLEASE fix this - you should never store a CSV list like that, this will never return reasonable results performance wise.
Can I do this in pure SQL? Sure.
Would I do this in pure SQL? Never.
Bind in list and Oracle's sql parsing
July 12, 2009 - 1am Central time zone
Reviewer: Vijay from San Diego
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
Followup July 14, 2009 - 5pm Central time zone:
http://tkyte.blogspot.com/2006/06/varying-in-lists.html
... Is it because the data-type of the terminal parameter is not known in the
inlist & oracle cannot allocate space upfront upfront?
...
nothing like that at all.
where column IN ({set})
A set is either
a) a discrete set, you supply it, like "where x in (1,2,4,5)" - there is nothing to "parse" here, there are 4 distinct ELEMENTS, it is not a string to be parsed, there is no terminator concept
b) a result set, you give a query
I only see one sql statement there at the end - there is no "last select", there is only ONE sql statement. It happens to have the keyword select in it many times, but there is only ONE sql statement there to be parsed and executed.
varying dynamic column list in query
August 7, 2009 - 12pm Central time zone
Reviewer: Michael from Switzerland
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
Followup August 7, 2009 - 2pm Central time zone:
not going to happen
A plsql pipelined function returns a collection of object types.
The object type is static, compiled into the database.
It has a fixed number of attributes.
Only if your customer is willing to receive a collection variable (like an array) with the numbers in it - fixing the number of columns needed to be "one" - could this work.
|