Hi Tom,

your solution is OK if the ratio of Names and Values is 1:1.
But I cannot use it for conditions like [AREA] IN ('AA','BB','CC').

BTW, why CURSOR_SHARING=FORCE is not working here? It is for SQL only?

Thanks, Jan

"you do not NEED (nor even truly desire) 'AA', 'BB', 'CC' to be bind variables! "

For the moment, I agree with you, but not forever, since the number of rules is going to increase, there could be plenty of rules with the same "frame", but not values, e.g.:

[AREA] IN ('AA','BB','CC')
[AREA] IN ('BB','AA','CC')
[AREA] IN ('CC','DD','AA')

In this case, I would like to have in SQL_AREA just one statment.


Jan

So, what are you going to do about "[age] > 15" and anything else with "binds"

I suppose you could:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace context my_ctx using parse_procedure
  2  /

Context created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure
  2                          parse_procedure( p_names in dbms_sql.varchar2s,
  3                                           p_values in dbms_sql.varchar2s,
  4                                           p_rule in varchar2 )
  5  as
  6      l_rule  long;
  7      l_yn    varchar2(1) default 'N';
  8  begin
  9      for i in 1 .. p_names.count
 10      loop
 11          dbms_session.set_context( 'my_ctx', p_names(i), p_values(i) );
 12          dbms_output.put_line('set ' || p_names(i) || ' = '||p_values(i));
 13      end loop;
 14
 15      l_rule := replace( p_rule, '[', 'sys_context( ''my_ctx'', ''' );
 16      l_rule := replace( l_rule, ']', ''')' );
 17
 18
 19          execute immediate 'select decode( count(*), 1, ''Y'', ''N'' )
 20                           from dual
 21                          where ' || l_rule into l_yn;
 22
 23      dbms_output.put_line( 'answer is ' || l_yn );
 24      dbms_output.put_line( '-------------------' );
 25  end;
 26  /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> drop table t;

Table dropped.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( rule_id int, condition varchar2(255) );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1, '([AGE]>20 AND [SEX]=''M'') OR [STAFF_CATEGORY]=''MGR'' ' );

1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;

Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter system flush shared_pool;

System altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set cursor_sharing=force;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> declare
  2      l_names dbms_sql.varchar2s;
  3      l_values dbms_sql.varchar2s;
  4  begin
  5      l_names(1) := 'age';
  6      l_values(1) := '15';
  7      l_names(2) := 'sex';
  8      l_values(2) := 'M';
  9      l_names(3) := 'staff_category';
 10      l_values(3) := 'MGR';
 11
 12      for x in ( select * from t )
 13      loop
 14          dbms_output.put_line( 'processing "' || x.condition || '"' );
 15          parse_procedure( l_names, l_values, x.condition );
 16
 17          l_values(3) := 'XX';
 18          parse_procedure( l_names, l_values, x.condition );
 19      end loop;
 20  end;
 21  /
processing "([AGE]>20 AND [SEX]='M') OR [STAFF_CATEGORY]='MGR' "
set age = 15
set sex = M
set staff_category = MGR
answer is Y
-------------------
set age = 15
set sex = M
set staff_category = XX
answer is N
-------------------

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select sql_text from v$sql where sql_text like 'select decode%';

SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
select decode( count(*), :"SYS_B_00", :"SYS_B_01", :"SYS_B_02" )                          from dual                        where (
sys_context( :"SYS_B_03", :"SYS_B_04")>:"SYS_B_05" AND sys_context( :"SYS_B_06", :"SYS_B_07")=:"SYS_B_08") OR sys_context( :"SYS_B_
09", :"SYS_B_10")=:"SYS_B_11"


<b>obviates technically the need to use sys_context -- however, I'll betcha the replace is lots easier then parsing and finding exact names and substituting in the values! so you might keep sys_context anyhow

But -- bottom line -- you have a FINITE number of rules -- maybe 1000, maybe more, but FINITE.  You can size a shared pool to hold them all.  The use of bind variables is to prevent an INFINITE number of statements from flooding the pool</b>
 

Thanks Tom for your spent time,

yes, it will be finite number of rules, and by watching and estimating statistics about parsed rules,
it will be not more then 100-200 unique rules, surely less then 1000. Then it is propably not worth of such microtunning.

just one more academic question, propably out of the original one. I compared SQL with PLSQL inside of EXECUTE IMMEDIATE:

----------------------
DECLARE
t NUMBER:=dbms_utility.get_time;
v VARCHAR2(1):='N';
v_con VARCHAR2(10):='1=0';
BEGIN

FOR i IN 1 .. 100000 LOOP
EXECUTE IMMEDIATE
'BEGIN IF '||v_con||' THEN :1:=''Y''; END IF;END;' USING OUT v;
END LOOP;

dbms_output.put_line('PL/SQL, Condition met? = '||v||' Time: '||TO_CHAR(dbms_utility.get_time-t));
t:=dbms_utility.get_time;

FOR ii IN 1 .. 100000 LOOP
EXECUTE IMMEDIATE
'SELECT DECODE(COUNT(*),1,''Y'',''N'') FROM DUAL WHERE '||v_con INTO v;
END LOOP;

dbms_output.put_line('SQL, Condition met? = '||v||' Time: '||TO_CHAR(dbms_utility.get_time-t));
END;
-----------------------

PL/SQL, Condition met? = Y Time: 1088
SQL, Condition met? = Y Time: 1181
PL/SQL, Condition met?='N' = Time: 1059
SQL, Condition met? = N Time: 878


I see here that SQL without I/O is faster (878:1059) then PL/SQL. Is it because PL/SQL is slower
or because PL/SQL is used in Dynamic SQL (I mean - Dynamic Sql was born for SQL)?

When I do the same test in Static PL/SQL block, result are totaly different (because of switch between SQL and PL/SQL?):

----------------------
DECLARE
t NUMBER:=dbms_utility.get_time;
v VARCHAR2(1):='N';
v_con VARCHAR2(10):='1=0';
BEGIN

FOR i IN 1 .. 100000 LOOP
IF 1=0 THEN v:='Y'; END IF;
END LOOP;

dbms_output.put_line('PL/SQL, Condition met? = '||v||' Time: '||TO_CHAR(dbms_utility.get_time-t));
t:=dbms_utility.get_time;

FOR ii IN 1 .. 100000 LOOP
SELECT DECODE(COUNT(*),1,'Y','N') INTO v FROM DUAL WHERE 1=0;
END LOOP;
dbms_output.put_line('SQL, Condition met? = '||v||' Time: '||TO_CHAR(dbms_utility.get_time-t));
END;
---------------------

PL/SQL, Condition met? = N Time: 7
SQL, Condition met? = N Time: 559

Thanks and Regards, Jan

Hi Tom

I prepare a dynamic insert as select statement with a few (0 - 150) bind variables in the where clause. My code for execute statement look like this:

IF i_bind_index = 1 THEN EXECUTE IMMEDIATE i_statement USING v(1);
ELSIF i_bind_index = 2 THEN EXECUTE IMMEDIATE i_statement USING v(1),v(2);
ELSIF i_bind_index = 3 THEN EXECUTE IMMEDIATE i_statement USING v(1),v(2),v(3);
...
...
ELSIF i_bind_index = 149 THEN EXECUTE IMMEDIATE i_statement USING v(1),v(2),v(3),v(4),v(5),v(6),v(7),v(8),v(9),v(10),v(11),v(12),v(13),v(14),v(15),v(16),v(17),v(18),v(19),v(20),v(21),v(22),v(23),v(24),v(25),v(26),v(27),v(28),v(29),v(30),v(31),v(32),v(33),v(34),v(35),v(36),v(37),v(38),v(39),v(40),v(41),v(42),v(43),v(44),v(45),v(46),v(47),v(48),v(49),v(50),v(51),v(52),v(53),v(54),v(55),v(56),v(57),v(58),v(59),v(60),v(61),v(62),v(63),v(64),v(65),v(66),v(67),v(68),v(69),v(70),v(71),v(72),v(73),v(74),v(75),v(76),v(77),v(78),v(79),v(80),v(81),v(82),v(83),v(84),v(85),v(86),v(87),v(88),v(89),v(90),v(91),v(92),v(93),v(94),v(95),v(96),v(97),v(98),v(99),v(100),v(101),v(102),v(103),v(104),v(105),v(106),v(107),v(108),v(109),v(110),v(111),v(112),v(113),v(114),v(115),v(116),v(117),v(118),v(119),v(120),v(121),v(122),v(123),v(124),v(125),v(126),v(127),v(128),v(129),v(130),v(131),v(132),v(133),v(134),v(135),v(136),v(137),v(138),v(139),v(140),v(141),v(142),v(143),v(144),v(145),v(146),v(147),v(148),v(149);
ELSIF i_bind_index = 150 THEN EXECUTE IMMEDIATE i_statement USING v(1),v(2),v(3),v(4),v(5),v(6),v(7),v(8),v(9),v(10),v(11),v(12),v(13),v(14),v(15),v(16),v(17),v(18),v(19),v(20),v(21),v(22),v(23),v(24),v(25),v(26),v(27),v(28),v(29),v(30),v(31),v(32),v(33),v(34),v(35),v(36),v(37),v(38),v(39),v(40),v(41),v(42),v(43),v(44),v(45),v(46),v(47),v(48),v(49),v(50),v(51),v(52),v(53),v(54),v(55),v(56),v(57),v(58),v(59),v(60),v(61),v(62),v(63),v(64),v(65),v(66),v(67),v(68),v(69),v(70),v(71),v(72),v(73),v(74),v(75),v(76),v(77),v(78),v(79),v(80),v(81),v(82),v(83),v(84),v(85),v(86),v(87),v(88),v(89),v(90),v(91),v(92),v(93),v(94),v(95),v(96),v(97),v(98),v(99),v(100),v(101),v(102),v(103),v(104),v(105),v(106),v(107),v(108),v(109),v(110),v(111),v(112),v(113),v(114),v(115),v(116),v(117),v(118),v(119),v(120),v(121),v(122),v(123),v(124),v(125),v(126),v(127),v(128),v(129),v(130),v(131),v(132),v(133),v(134),v(135),v(136),v(137),v(138),v(139),v(140),v(141),v(142),v(143),v(144),v(145),v(146),v(147),v(148),v(149),v(150);

My code is pretty fast and all bind variables are perfect bound, but is there a easier (and shorter) way to implement it. Many thanks for your answer.

Kind Regards
Marc

This is a perfect example of when you want to use DBMS_SQL and not native dynamic sql:

ops$tkyte@ORA920> create or replace package dyn_insert
  2  as
  3      type array is table of varchar2(4000);
  4
  5      procedure do_insert( p_tname in varchar2,
  6                           p_cnames in array,
  7                           p_values in array );
  8
  9  end;
 10  /

Package created.

ops$tkyte@ORA920> create or replace package body dyn_insert
  2  as
  3
  4  g_last_stmt long;
  5  g_cursor    number := dbms_sql.open_cursor;
  6
  7  procedure p ( p_str in varchar2 )
  8  is
  9     l_str   long := p_str;
 10  begin
 11     loop
 12        exit when l_str is null;
 13        dbms_output.put_line( substr( l_str, 1, 250 ) );
 14        l_str := substr( l_str, 251 );
 15     end loop;
 16  end;
 17
 18  procedure do_insert( p_tname in varchar2,
 19                       p_cnames in array,
 20                       p_values in array )
 21  is
 22      l_stmt  long;
 23      l_rc    number;
 24  begin
 25      l_stmt := 'insert into ' || p_tname || ' ( ' || p_cnames(1);
 26      for i in 2 .. p_cnames.count
 27      loop
 28          l_stmt := l_stmt || ', ' || p_cnames(i);
 29      end loop;
 30      l_stmt := l_stmt || ') values ( :bv1';
 31      for i in 2 .. p_cnames.count
 32      loop
 33          l_stmt := l_stmt || ', :bv' || i;
 34      end loop;
 35      l_stmt := l_stmt || ')';
 36      if ( l_stmt <> g_last_stmt or g_last_stmt is null )
 37      then
 38          p( l_stmt );
 39          g_last_stmt := l_stmt;
 40          dbms_sql.parse( g_cursor, g_last_stmt, dbms_sql.native );
 41      end if;
 42
 43      for i in 1 .. p_values.count
 44      loop
 45          dbms_sql.bind_variable( g_cursor, ':bv' || i, p_values(i) );
 46      end loop;
 47      l_rc := dbms_sql.execute( g_cursor );
 48  end;
 49
 50  end;
 51  /

Package body created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> drop table t;

Table dropped.

ops$tkyte@ORA920> create table t ( x int, y int, z int );

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> exec dyn_insert.do_insert( 't', dyn_insert.array( 'x', 'y', 'z' ), dyn_insert.array( 1, 2, 3 ) );<b>
insert into t ( x, y, z) values ( :bv1, :bv2, :bv3)

thats shows we built a new statment and parsed it..</b>

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> exec dyn_insert.do_insert( 't', dyn_insert.array( 'x', 'y', 'z' ), dyn_insert.array( 4, 5, 6 ) );

PL/SQL procedure successfully completed.

<b>the lack of output here (no insert statement) shows we are reusing that cursor-- no parse, it'll be better when you have tons of inserts to do</b>

ops$tkyte@ORA920> exec dyn_insert.do_insert( 't', dyn_insert.array( 'z', 'y', 'x' ), dyn_insert.array( 4, 5, 6 ) );
insert into t ( z, y, x) values ( :bv1, :bv2, :bv3)

<b>different insert, so a parse for this one...</b>

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> select * from t;

         X          Y          Z
---------- ---------- ----------
         1          2          3
         4          5          6
         6          5          4

ops$tkyte@ORA920>
 

Hi Tom

I do the same technique for open a cursor:

IF i_bind_index = 1 THEN OPEN io_daten FOR i_statement USING v(1);
ELSIF i_bind_index = 2 THEN OPEN io_daten FOR i_statement USING v(1),v(2);
ELSIF i_bind_index = 3 THEN OPEN io_daten FOR i_statement USING v(1),v(2),v(3);
...
ELSIF i_bind_index = 150 THEN OPEN io_daten FOR i_statement USING v(1),v(2),v(3),v(4),v(5),v(6),v(7),v(8),v(9),v(10),v(11),v(12),v(13),v(14),v(15),v(16),v(17),v(18),v(19),v(20),v(21),v(22),v(23),v(24),v(25),v(26),v(27),v(28),v(29),v(30),v(31),v(32),v(33),v(34),v(35),v(36),v(37),v(38),v(39),v(40),v(41),v(42),v(43),v(44),v(45),v(46),v(47),v(48),v(49),v(50),v(51),v(52),v(53),v(54),v(55),v(56),v(57),v(58),v(59),v(60),v(61),v(62),v(63),v(64),v(65),v(66),v(67),v(68),v(69),v(70),v(71),v(72),v(73),v(74),v(75),v(76),v(77),v(78),v(79),v(80),v(81),v(82),v(83),v(84),v(85),v(86),v(87),v(88),v(89),v(90),v(91),v(92),v(93),v(94),v(95),v(96),v(97),v(98),v(99),v(100),v(101),v(102),v(103),v(104),v(105),v(106),v(107),v(108),v(109),v(110),v(111),v(112),v(113),v(114),v(115),v(116),v(117),v(118),v(119),v(120),v(121),v(122),v(123),v(124),v(125),v(126),v(127),v(128),v(129),v(130),v(131),v(132),v(133),v(134),v(135),v(136),v(137),v(138),v(139),v(140),v(141),v(142),v(143),v(144),v(145),v(146),v(147),v(148),v(149),v(150);

Is DBMS_SQL also a good solution for open cursor ? Thanks for your answer.

Kind regards
Marc

Tom,

Thanks a lot for your earlier timely responses. Here is a problem we are facing currently.

We are using Oracle 8i (8.1.7.0). It takes 1 min or more when we execute a query ( inside a package ) dynamically using bulk collect & context like the one given below.

BEGIN
EXECUTE IMMEDIATE REPLACE( 'BEGIN
SELECT BULK COLLECT
INTO :outtab
FROM mytable
WHERE col1 = SYS_CONTEXT( "myctx", "col1_value" );
END;',
'"',
''''
)
USING OUT mycollection;
END;


But when we execute the same as STATIC query in PL/SQL like the one given below, it takes 30 secs.

BEGIN
SELECT BULK COLLECT
INTO :outtab
FROM mytable
WHERE col1 = pl_sql_variable_value;
END;

The only difference I can see is we are using dynamic query & context in one case, and in the other we are issuing it as static query.

Could you please help us with your inputs/comments/suggestions on this.

Thanks.. Muralee

Hi Tom,
Please see the attached EditPlus file with two functions.

Both are similar Functions USING Execute Immediate - AND also "INTO" clause IN their dynamic SQL formation....
BUT the CALL TO FIRST Function error outs ........ WITH message .....
"ORA-01744 inappropriate INTO
Cause: The INTO clause may not be used in a subquery.
Action: Check the syntax, place the INTO clause in the top-level query, and retry the statement. "

If we COMMENT the INTO clause OF FIRST FUNCTION AND THEN call it - It works Fine !!!
I want TO know why doesnt it WORK WITH INTO clause - WHILE the second Function works Fine WITH INTO Clause.
There IS no such difference IN both the Functions.

All the tables used in the Func are eithier Pub/Pvt Syn or View. Do u need any rights/privs for Execute immediate?
Kindly let me know what can/needs to be done .
Do let me know if you need any more clarification.

----------------------------------------------

Just in case if u r NOT able to open the File - here is the what it contains

/* First Function FN_GET_CHASSIS_QUANTITY */
FUNCTION FN_GET_CHASSIS_QUANTITY ( p_contract_number IN DW_KS_SVC_HEADERS.CONTRACT_NUMBER%TYPE
,p_svc_header_id IN DW_KS_SVC_HEADERS.SVC_HEADER_ID%TYPE
,p_contract_status IN DW_KS_SVC_HEADERS.CONTRACT_STATUS%TYPE
,p_install_loc IN DW_KS_INSTALL_BASE.INSTALL_LOC%TYPE
,p_item_list IN LONG
,p_expd_parts_flag IN VARCHAR2
,p_termd_parts_flag IN VARCHAR2
,p_zero_item_flag IN VARCHAR2
,p_cat_a_flag IN VARCHAR2
) RETURN DW_KS_SVC_DETAILS.QUANTITY%TYPE
AS
l_chassis_quantity DW_KS_SVC_DETAILS.QUANTITY%TYPE;
l_boolean BOOLEAN := FALSE;
l_con_query VARCHAR2(6000);
l_con_select VARCHAR2(200);
l_con_tables_2 VARCHAR2(400);
l_con_conds_2 VARCHAR2(5000);
l_con_tables_4 VARCHAR2(400);
l_con_conds_4 VARCHAR2(5000);
l_con_conds_list_4 VARCHAR2(5000);
p_svc_line_conds VARCHAR2(200);
p_cp_status_conds VARCHAR2(1000);
l_cat_a_cond VARCHAR2(100);
l_det_prod_join VARCHAR2(100);
l_zero_item_cond VARCHAR2(100);
l_position NUMBER;

BEGIN
DBMS_OUTPUT.PUT_LINE('In CAT A SUM list ');
l_boolean := Pk_Ks_Bi_Csd_Report.FN_VALID_STATUSES( p_contract_status
,p_expd_parts_flag
,p_termd_parts_flag
,p_svc_line_conds
,p_cp_status_conds);
DBMS_OUTPUT.PUT_LINE('after valid status');
l_con_select := 'SELECT SUM(SDTLS.QUANTITY) ' ||
'INTO :l_chassis_quantity ';
l_con_tables_2 := 'FROM DW_KS_SVC_HEADERS SHDRS ' ||
' ,DW_KS_SVC_DETAILS SDTLS ' ||
' ,CDW_PRODUCTS PRODS ';
l_con_conds_2 := 'WHERE shdrs.SVC_HEADER_ID = ''' || p_svc_header_id || ''' ' ||
'AND shdrs.CONTRACT_NUMBER = ''' || p_contract_number || ''' ' ||
'AND shdrs.CONTRACT_STATUS = ''' || p_contract_status || ''' ' ||
'AND shdrs.SVC_HEADER_ID = sdtls.SVC_HEADER_ID ' ||
'AND shdrs.CONTRACT_NUMBER = sdtls.CONTRACT_NUMBER ' ||
p_cp_status_conds || ' ' ||
'AND PRODS.PRODUCT_ID = SDTLS.PRODUCT_ID ' ||
'AND PRODS.CSM_ITEM_TYPE IN (''CHASSIS'') ';
l_con_tables_4 := ' ,DW_KS_INSTALL_BASE INBS ';

l_con_conds_4 := 'AND to_number(SDTLS.INSTALL_INSTANCE_ID) = INBS.INSTANCE_ID ' ||
'AND SUBSTR(inbs.INSTANCE_STATUS,1) IN (''Latest-INSTALLED'',''?DMY'') ' ||
-- 'AND substr(inbs.instance_status,1) = ''Latest-INSTALLED'' ' ||
'AND INBS.INSTALL_LOC = ''' || p_install_loc || ''' ';

l_con_conds_list_4 := 'AND to_number(SDTLS.INSTALL_INSTANCE_ID) = INBS.INSTANCE_ID ' ||
'AND SUBSTR(inbs.INSTANCE_STATUS,1) IN (''Latest-INSTALLED'',''?DMY'') ' ||
-- 'AND substr(inbs.instance_status,1) = ''Latest-INSTALLED'' ' ||
'AND INBS.INSTALL_LOC IN (' || p_install_loc || ') ';


l_cat_a_cond := ' AND prods.PRODUCT_CATEGORY = ''A'' ';
l_det_prod_join:= ' AND sdtls.product_id = prods.product_id ';

l_zero_item_cond := ' AND (sdtls.maint_list_price <> 0 OR sdtls.maint_net_price <> 0 ) ';


IF p_item_list IS NOT NULL THEN
IF p_item_list <> 'ALL' THEN
l_con_conds_2 := l_con_conds_2 ||
'AND PRODS.PRODUCT_ID IN (' || p_item_list || ') ';
END IF;
END IF;

IF p_zero_item_flag = 'N' THEN
l_con_conds_2 := l_con_conds_2 || l_zero_item_cond;
END IF;

IF p_cat_a_flag = 'Y' THEN
l_con_conds_2 := l_con_conds_2 || l_cat_a_cond;
END IF;


IF p_install_loc IS NULL THEN
l_con_query := l_con_select || l_con_tables_2 || l_con_conds_2;
ELSE
BEGIN
SELECT INSTR(p_install_loc,'''', 1) INTO l_position FROM DUAL;
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_position := 0;
END;
IF l_position = 0 THEN
l_con_query := l_con_select || l_con_tables_2 || l_con_tables_4 ||
l_con_conds_2 || l_con_conds_4;

ELSE
l_con_query := l_con_select || l_con_tables_2 || l_con_tables_4 ||
l_con_conds_2 || l_con_conds_list_4;
END IF;
END IF;
BEGIN
-- execute immediate 'insert into cib_temp values (:id, :sys_date, :text)' using 111112,sysdate,l_con_query;
DBMS_OUTPUT.PUT_LINE('after insert');
COMMIT;
END;
BEGIN
EXECUTE IMMEDIATE l_con_query INTO l_chassis_quantity;
DBMS_OUTPUT.PUT_LINE('after execute immediate');
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_chassis_quantity := 0;
RETURN l_chassis_quantity;
END;

RETURN l_chassis_quantity;

END FN_GET_CHASSIS_QUANTITY;
/* First Function FN_GET_CHASSIS_QUANTITY */

/* Second Function */
FUNCTION FN_GET_CON_EARLIEST_DATE ( p_contract_number IN DW_KS_SVC_HEADERS.CONTRACT_NUMBER%TYPE
,p_svc_header_id IN DW_KS_SVC_HEADERS.SVC_HEADER_ID%TYPE
,p_contract_status IN DW_KS_SVC_HEADERS.CONTRACT_STATUS%TYPE
,p_expd_parts_flag IN VARCHAR2
,p_termd_parts_flag IN VARCHAR2
) RETURN DW_KS_SVC_DETAILS.END_DATE%TYPE
AS
l_min_end_date DW_KS_SVC_DETAILS.END_DATE%TYPE := NULL;
l_boolean BOOLEAN := FALSE;
l_con_query VARCHAR2(1000);
p_svc_line_conds VARCHAR2(200);
p_cp_status_conds VARCHAR2(1000);
BEGIN

DBMS_OUTPUT.PUT_LINE('In con earliest date ');
l_boolean := Pk_Ks_Bi_Csd_Report.FN_VALID_STATUSES( p_contract_status
,p_expd_parts_flag
,p_termd_parts_flag
,p_svc_line_conds
,p_cp_status_conds);
DBMS_OUTPUT.PUT_LINE('after valid status');
l_con_query := 'SELECT MIN(sdtls.END_DATE) ' ||
'INTO :l_min_end_date ' ||
'FROM dw_ks_svc_headers shdrs, ' ||
' dw_ks_svc_details sdtls ' ||
'WHERE shdrs.SVC_HEADER_ID = ''' || p_svc_header_id || ''' ' ||
'AND shdrs.CONTRACT_NUMBER = ''' || p_contract_number || ''' ' ||
'AND shdrs.CONTRACT_STATUS = ''' || p_contract_status || ''' ' ||
'AND shdrs.SVC_HEADER_ID = sdtls.SVC_HEADER_ID ' ||
'AND shdrs.CONTRACT_NUMBER = sdtls.CONTRACT_NUMBER ' ||
p_cp_status_conds || ' ' ;
-- 'GROUP BY shdrs.CONTRACT_NUMBER ';
DBMS_OUTPUT.PUT_LINE('before insert');
BEGIN
-- execute immediate 'insert into cib_temp values (:id, :sys_date, :text)' using 111112,sysdate,l_con_query;
DBMS_OUTPUT.PUT_LINE('after insert');
COMMIT;
END;
BEGIN
EXECUTE IMMEDIATE l_con_query INTO l_min_end_date;
DBMS_OUTPUT.PUT_LINE('after execute immediate');
EXCEPTION
WHEN NO_DATA_FOUND THEN
l_min_end_date := NULL;
RETURN l_min_end_date;
END;

RETURN l_min_end_date;
END FN_GET_CON_EARLIEST_DATE;
/* Second Function */

/* Call to First procedure */
DECLARE
A DW_KS_SVC_DETAILS.QUANTITY%TYPE;
BEGIN
a := Pk_Ks_Bi_Csd_Report.FN_GET_CHASSIS_QUANTITY ( '1000520' ,
'47441033' ,
'ACTIVE' ,
NULL ,
'ALL' ,
'N' ,
'N' ,
'N' ,
'N'
);
DBMS_OUTPUT.PUT_LINE('The output : '||a);
END;
/* Call to First procedure */

/* Call to Second procedure */
DECLARE
A DW_KS_SVC_DETAILS.END_DATE%TYPE;
BEGIN
a := Pk_Ks_Bi_Csd_Report.FN_GET_CON_EARLIEST_DATE ('1000520' ,
'47441033' ,
'ACTIVE' ,
'N' ,
'N'
) ;
DBMS_OUTPUT.PUT_LINE('The output : '||a);
END;
/* Call to Second procedure */

hi tom, Thanks u verymuch for this artical. It is very very inforomative..

I am doing a similar thing in my pl/sql program.
I have one set of data and two structures.
one is stage and another is production.

I have all the data in stage. and I have to move it to production. making sure that all the CONSTRAINTS are met.

ok now,
1.) I can write one update stmt to update all changed rows using dynamic update stmt.

2.) I can write loop to update all changed rows by creatin dynamic update stmt for each row (dbms_sql example abouve.)

I know the case 1 will be faster as it is only one stmt.
but it will fail if at least one record is not meeting all constraint and i will loose the whole upate.

where as in case 2 I can catch the exception. and continue insert/update with the next record . and I can insert rest of related data.

based on these requirements, do u think that case 2 is better approach ?


another thing is how can i write

do_update like do_insert procedure about as I will have to update on PK columns ?

also where we are passing table of varchar2's can I pass the sqlstmt that fatches the data for the VALUES and some how use it smartly ?

please advice

but there are curcular relationships
for instance,


user table,

user_code varchar2(11),
user_mgr_code varchar2(11),
...

so letus say in staging user magnager wasa inserted first.,
and in prod. tables letus say user is inseerted first and trying to validate the user_mgr_code not found YET. and it failed rolling back all the inserts for that table.


this is a very simple senerio. in the system it gets more complex (broker to trdaer specific relations then broker to its comapny and broker might been also a bond broker etc..)

apperantly there are no bad rows in STAGINg because if it is bad we don't want to use that in prod.
the prblem is the order of the row inserted. if it gets inserted in "proper order" it is fine else not.

can you still suggest me which case from above described cases would be good for this operation ?

hi tom,
I want to know if the sys_context function works in a procedure.
for eg. in the following example i am trying to get the ip address but it does not returns me anything .

------------------------Fucntion---------------------------
PROCEDURE refund_mail(recepiant in varchar2)
IS
c utl_smtp.connection;
CURSOR c1 is
select payment_gateway_name,count(*)total from ir_refund where refund_status in(1,4) and payment_gateway_name is not null group by
payment_gateway_name ;
mesg varchar2(255);
new_line varchar2(255);
addr varchar2(255);

Begin
new_line := chr(13) || chr(10);


c := utl_smtp.open_connection('210.210.72.87');

utl_smtp.helo(c,'210.210.72.87');
utl_smtp.mail(c,'rahul@irctc.co.in');
utl_smtp.rcpt(c,recepiant);
utl_smtp.open_data(c);
utl_smtp.write_data(c,'Subject : Refund Mail'||new_line);

execute immediate 'select sys_context(''USERENV'',''IP_ADDRESS'') from dual' into addr;
utl_smtp.write_data(c,'The machine IP is :'||addr||new_line);
utl_smtp.write_data(c,'Refund Status as on '||sysdate||new_line);
utl_smtp.write_data(c,addr);
dbms_output.put_line('the ip_address of the machine is ' ||addr);
utl_smtp.write_data(c,addr);
utl_smtp.write_data(c,'Paymnet_gateway Total'|| chr(13) || chr(10));
FOR i in c1 LOOP
mesg := ' '||i.payment_gateway_name ||' '||i.total;
utl_smtp.write_data(c,mesg);
utl_smtp.write_data(c,new_line);
END LOOP;
utl_smtp.close_data(c);
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
BEGIN
utl_smtp.quit(c);
EXCEPTION
WHEN utl_smtp.transient_error OR utl_smtp.permanent_error THEN
NULL; -- When the SMTP server is down or unavailable, we don't have
-- a connection to the server. The quit call will raise an
-- exception that we can ignore.
END;
raise_application_error(-20000,
'Failed to send mail due to the following error: ' || sqlerrm);
END;

-----------------------------------------------------------

Now i want to know why the execute immediate is not returning me the ip_address? and is their other way of doing it.

Note : the procedure gives the desred result except for the execute_immediate statement.

Hi Tom,
Your answer was most accurate. I was not connected over sqlnet and hence was not getting the ip_address.
I did never know this .
One more thing that you mention in your reply ,
Never use dynamic sql until essential. Could you just tell me why? and what is better , to use execute immediate or dbms_sql.
Thanks for your reply and the time.


Thanks and regards,
Rahul.

Hi Tom,
I have one more question, ok now that i know that i need to have a sqlnet connection established to get the ip_address. I tried to add a host command as follows

host ('sqlplus user/passwd@connstring');

but i get the following error

PLS-00201: identifier 'HOST' must be declared

if i dont enclose the string in quotes it gives me error for the string contains the white space.

I have searched the site for a related problem and found one post but the solution provided is to create our own java function to do the activity.
Also let me know if their is any other way of doing this.

Hi Tom,

I try to create a procedure which is to be used to refresh the snapshot by running 'execute dbms_job.run(job_num)'
but received errors while executing it, the job number is 21.  I want to grant the execute privilege to another user, so he can do the snapshots refresh on demand himself.  He only has select privileges on the referred objects.

SQL> create procedure watson.refresh_test
  2  as
  3  begin
  4  execute immediate 'execute dbms_job.run(21)';
  5  end;
  6  /

Procedure created.

SQL> exec refresh_test;
BEGIN refresh_test; END;

*
ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "WATSON.REFRESH_TEST", line 4
ORA-06512: at line 1

I don't know where is the problem, could you pl shed some light?

thanks,
 

execute is a sqlplus'ism.  it is not sql

SQL> exec foo

is really

SQL> begin foo; end;


you don't want or need to do that dynamically.

just

...
begin
  dbms_job.run(21);
end;
/
...... 

Tom,

that works.

thank you very much.

TOm,

I want to have the materialized view refreshed at exact 9PM everyday, how to set it up?

CREATE materialized view data_view_table
PCTFREE 10
PCTUSED 40
MAXTRANS 255
REFRESH complete with rowid
START WITH sysdate
NEXT TRUNC(SYSDATE) + (21/24)
AS SELECT * FROM studymetadata_view
/

gives wrong time in the job.

thanks,

Tom -

Our front end java application passes any number of parameters to a db package to form a dynamic sql query.

I have implemented sys_context to bind the variables to the query.

Performance has been adversely affected by sys_context.

What I have done is take the query generated by the package and ran it through tkprof. I then took the query and replaced the sys_contex with the actual values of the parameters that were passed in.

Here is the original query w/sys_context:

select distinct r.code,r.descr,r.initialbookingdatetime,rcst.lastname,
ria.lastname,ra.agcyname,raa.lastname,ris.startdatetime sdt,rc.sourcecode,
r.status
from
resv r ,resvcustomer rcst ,resvitinsegment ris ,resvinternalagent ria ,
resvagcy ra ,resvagcyagent raa ,resvcomp rc
where r.id = rcst.resvid(+)
and r.id = ris.resvid(+)
and r.id = ria.resvid(+)
and r.id = ra.resvid(+)
and r.id = rc.resvid(+)
and ra.id = raa.resvagcyid(+)
and ( ris.startdatetime in (select min(startdatetime)
from resvitinsegment sris
where sris.resvid = r.id)
or
ris.startdatetime is null)
and ( ra.sequence = (select min(sequence)
from resvagcy ra
where ra.resvid = r.id)
or
ra.sequence is null)
and ( raa.sequence = (select min(sequence)
from resvagcyagent
where resvagcyid = ra.id)
or
raa.sequence is null)
and ( rc.sequence = (select min(sequence)
from resvcomp rc
where rc.resvid = r.id)
or
rc.sequence is null)
and ( ria.sequence = (select min(sequence)
from resvinternalagent
where resvid = r.id)
or ria.sequence is null)
and ( rcst.sequence = (select min(sequence)
from resvcustomer
where resvid = r.id)
or rcst.sequence is null)
and r.initialbookingdatetime between trunc(to_date(sys_context( 'RESVSUM_CTX','INITBOOKDT' ))) and
(trunc(to_date(sys_context( 'RESVSUM_CTX','INITBOOKDT' )))+
to_number(sys_context( 'RESVSUM_CTX', 'INITBOOKDTWIN' ))-1/86400)
and r.id in (select drg.resvid
from resvguest drg
where 1=1
and upper(drg.lastname) like sys_context( 'RESVSUM_CTX', 'RGLNAME' ))

*-*-*-*-*-*-*-
plan w/sys_context:

Rows Row Source Operation
------- ---------------------------------------------------
2 SORT UNIQUE (cr=2664 r=63 w=0 time=136552 us)
9 FILTER (cr=2664 r=63 w=0 time=136392 us)
56 FILTER (cr=2646 r=63 w=0 time=135224 us)
56 MERGE JOIN OUTER (cr=2646 r=63 w=0 time=134945 us)
24 SORT JOIN (cr=2578 r=0 w=0 time=45445 us)
24 NESTED LOOPS OUTER (cr=2578 r=0 w=0 time=44885 us)
20 NESTED LOOPS OUTER (cr=2522 r=0 w=0 time=44265 us)
10 NESTED LOOPS OUTER (cr=2462 r=0 w=0 time=43544 us)
10 NESTED LOOPS OUTER (cr=2434 r=0 w=0 time=43209 us)
6 NESTED LOOPS OUTER (cr=2416 r=0 w=0 time=42887 us)
5 NESTED LOOPS (cr=2407 r=0 w=0 time=42704 us)
803 TABLE ACCESS BY INDEX ROWID RESV (cr=806 r=0 w=0 time=11352 us)
803 INDEX RANGE SCAN RESV_BOOKDT_IDX (cr=4 r=0 w=0 time=2040 us)(object id 24352)
5 TABLE ACCESS BY INDEX ROWID RESVGUEST (cr=1601 r=0 w=0 time=26173 us)
795 INDEX RANGE SCAN RESV_GUES_RESV (cr=811 r=0 w=0 time=12135 us)(object id 21524)
2 TABLE ACCESS BY INDEX ROWID RESVINTERNALAGENT (cr=9 r=0 w=0 time=121 us)
2 INDEX RANGE SCAN RESV_INTE_AGEN_RESV (cr=7 r=0 w=0 time=73 us)(object id 21553)
10 TABLE ACCESS BY INDEX ROWID RESVAGCY (cr=18 r=0 w=0 time=227 us)
10 INDEX RANGE SCAN RESV_AGCY_RESV (cr=12 r=0 w=0 time=108 us)(object id 21449)
4 TABLE ACCESS BY INDEX ROWID RESVAGCYAGENT (cr=28 r=0 w=0 time=223 us)
4 INDEX RANGE SCAN RESV_AGCY_AGEN_RESV_AGCY (cr=24 r=0 w=0 time=140 us)(object id 21452)
20 TABLE ACCESS BY INDEX ROWID RESVCOMP (cr=60 r=0 w=0 time=550 us)
20 INDEX RANGE SCAN RESV_COMP_RESV (cr=40 r=0 w=0 time=282 us)(object id 21459)
8 TABLE ACCESS BY INDEX ROWID RESVCUSTOMER (cr=56 r=0 w=0 time=424 us)
8 INDEX RANGE SCAN RESV_CUST_RESV (cr=48 r=0 w=0 time=279 us)(object id 21505)
56 SORT JOIN (cr=68 r=63 w=0 time=88942 us)
10504 TABLE ACCESS FULL RESVITINSEGMENT (cr=68 r=63 w=0 time=39005 us)
2 SORT AGGREGATE (cr=6 r=0 w=0 time=216 us)
5 TABLE ACCESS BY INDEX ROWID RESVITINSEGMENT (cr=6 r=0 w=0 time=176 us)
5 INDEX RANGE SCAN ALT_RESV_ITIN_SEGM_ALTE (cr=4 r=0 w=0 time=105 us)(object id 21771)
2 SORT AGGREGATE (cr=4 r=0 w=0 time=77 us)
2 FIRST ROW (cr=4 r=0 w=0 time=58 us)
2 INDEX RANGE SCAN (MIN/MAX) ALT_RESV_AGCY_ALTE (cr=4 r=0 w=0 time=48 us)(object id 21692)
0 SORT AGGREGATE (cr=0 r=0 w=0 time=0 us)
0 FIRST ROW (cr=0 r=0 w=0 time=0 us)
0 INDEX RANGE SCAN (MIN/MAX) ALT_RESV_AGCY_AGEN_ALTE (cr=0 r=0 w=0 time=0 us)(object id 21695)
2 SORT AGGREGATE (cr=4 r=0 w=0 time=69 us)
2 FIRST ROW (cr=4 r=0 w=0 time=50 us)
2 INDEX RANGE SCAN (MIN/MAX) ALT_RESV_COMP_ALTE (cr=4 r=0 w=0 time=40 us)(object id 21702)
1 SORT AGGREGATE (cr=2 r=0 w=0 time=41 us)
1 FIRST ROW (cr=2 r=0 w=0 time=31 us)
1 INDEX RANGE SCAN (MIN/MAX) ALT_RESV_INTE_AGEN_ALTE (cr=2 r=0 w=0 time=25 us)(object id 21764)
1 SORT AGGREGATE (cr=2 r=0 w=0 time=34 us)
1 FIRST ROW (cr=2 r=0 w=0 time=24 us)
1 INDEX RANGE SCAN (MIN/MAX) ALT_RESV_CUST_ALTE (cr=2 r=0 w=0 time=20 us)(object id 21734)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
2 SORT (UNIQUE)
9 FILTER
56 FILTER
56 MERGE JOIN (OUTER)
24 SORT (JOIN)
24 NESTED LOOPS (OUTER)
20 NESTED LOOPS (OUTER)
10 NESTED LOOPS (OUTER)
10 NESTED LOOPS (OUTER)
6 NESTED LOOPS (OUTER)
5 NESTED LOOPS
803 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'RESV'
803 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESV_BOOKDT_IDX' (NON-UNIQUE)
5 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'RESVGUEST'
795 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESV_GUES_RESV' (NON-UNIQUE)
2 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'RESVINTERNALAGENT'
2 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESV_INTE_AGEN_RESV' (NON-UNIQUE)
10 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'RESVAGCY'
10 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESV_AGCY_RESV' (NON-UNIQUE)
4 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'RESVAGCYAGENT'
4 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESV_AGCY_AGEN_RESV_AGCY' (NON-UNIQUE)
20 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESVCOMP'
20 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESV_COMP_RESV' (NON-UNIQUE)
8 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESVCUSTOMER'
8 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESV_CUST_RESV' (NON-UNIQUE)
56 SORT (JOIN)
10504 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'RESVITINSEGMENT'
2 SORT (AGGREGATE)
5 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESVITINSEGMENT'
5 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'ALT_RESV_ITIN_SEGM_ALTE' (UNIQUE)
2 SORT (AGGREGATE)
2 FIRST ROW
2 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
'ALT_RESV_AGCY_ALTE' (UNIQUE)
0 SORT (AGGREGATE)
0 FIRST ROW
0 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
'ALT_RESV_AGCY_AGEN_ALTE' (UNIQUE)
2 SORT (AGGREGATE)
2 FIRST ROW
2 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
'ALT_RESV_COMP_ALTE' (UNIQUE)
1 SORT (AGGREGATE)
1 FIRST ROW
1 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
'ALT_RESV_INTE_AGEN_ALTE' (UNIQUE)
1 SORT (AGGREGATE)
1 FIRST ROW
1 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
'ALT_RESV_CUST_ALTE' (UNIQUE)

*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
The sql that was run w/hardcoded values:

select distinct r.code,r.descr,r.initialbookingdatetime,rcst.lastname, ria.lastname,ra.agcyname
,raa.lastname,ris.startdatetime sdt,rc.sourcecode,r.status
from resv r ,resvcustomer rcst ,resvitinsegment ris ,resvinternalagent ria
,resvagcy ra ,resvagcyagent raa ,resvcomp rc
where r.id = rcst.resvid(+)
and r.id = ris.resvid(+)
and r.id = ria.resvid(+)
and r.id = ra.resvid(+)
and r.id = rc.resvid(+)
and ra.id = raa.resvagcyid(+)
and ( ris.startdatetime in (select min(startdatetime)
from resvitinsegment sris
where sris.resvid = r.id)
or
ris.startdatetime is null)
and ( ra.sequence = (select min(sequence)
from resvagcy ra
where ra.resvid = r.id)
or
ra.sequence is null)
and ( raa.sequence = (select min(sequence)
from resvagcyagent where resvagcyid = ra.id)
or
raa.sequence is null)
and ( rc.sequence = (select min(sequence)
from resvcomp rc
where rc.resvid = r.id)
or
rc.sequence is null)
and ( ria.sequence = (select min(sequence)
from resvinternalagent
where resvid = r.id)
or
ria.sequence is null)
and ( rcst.sequence = (select min(sequence)
from resvcustomer
where resvid = r.id)
or
rcst.sequence is null)
and r.initialbookingdatetime between trunc(to_date('01-jan-2004')) and
(trunc(to_date('01-jan-2004'))+to_number('365')-1/86400)
and r.id in (select drg.resvid
from resvguest drg
where 1=1
and upper(drg.lastname) like 'PYL%' )

Plan where I hard coded the values for the variables:
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT UNIQUE (cr=87 r=0 w=0 time=2420 us)
1 FILTER (cr=87 r=0 w=0 time=2260 us)
8 FILTER (cr=79 r=0 w=0 time=1704 us)
8 NESTED LOOPS OUTER (cr=79 r=0 w=0 time=1658 us)
4 NESTED LOOPS OUTER (cr=55 r=0 w=0 time=1302 us)
2 NESTED LOOPS OUTER (cr=43 r=0 w=0 time=1135 us)
2 NESTED LOOPS OUTER (cr=39 r=0 w=0 time=1063 us)
2 NESTED LOOPS OUTER (cr=31 r=0 w=0 time=929 us)
1 NESTED LOOPS (cr=28 r=0 w=0 time=841 us)
5 NESTED LOOPS (cr=16 r=0 w=0 time=500 us)
5 TABLE ACCESS BY INDEX ROWID RESVGUEST (cr=4 r=0 w=0 time=194 us)
5 INDEX RANGE SCAN RESVGUEST_NAME_FB_IDX (cr=2 r=0 w=0 time=125 us)(object id 22457)
5 TABLE ACCESS BY INDEX ROWID RESV (cr=12 r=0 w=0 time=237 us)
5 INDEX UNIQUE SCAN PK_RESV_PRIM (cr=7 r=0 w=0 time=78 us)(object id 21587)
1 TABLE ACCESS BY INDEX ROWID RESVITINSEGMENT (cr=12 r=0 w=0 time=291 us)
11 INDEX RANGE SCAN RESV_ITIN_SEGM_RESV (cr=7 r=0 w=0 time=119 us)(object id 21561)
2 TABLE ACCESS BY INDEX ROWID RESVCOMP (cr=3 r=0 w=0 time=55 us)
2 INDEX RANGE SCAN RESV_COMP_RESV (cr=2 r=0 w=0 time=30 us)(object id 21459)
2 TABLE ACCESS BY INDEX ROWID RESVAGCY (cr=8 r=0 w=0 time=98 us)
2 INDEX RANGE SCAN RESV_AGCY_RESV (cr=6 r=0 w=0 time=60 us)(object id 21449)
0 TABLE ACCESS BY INDEX ROWID RESVAGCYAGENT (cr=4 r=0 w=0 time=39 us)
0 INDEX RANGE SCAN RESV_AGCY_AGEN_RESV_AGCY (cr=4 r=0 w=0 time=31 us)(object id 21452)
4 TABLE ACCESS BY INDEX ROWID RESVINTERNALAGENT (cr=12 r=0 w=0 time=122 us)
4 INDEX RANGE SCAN RESV_INTE_AGEN_RESV (cr=8 r=0 w=0 time=64 us)(object id 21553)
8 TABLE ACCESS BY INDEX ROWID RESVCUSTOMER (cr=24 r=0 w=0 time=257 us)
8 INDEX RANGE SCAN RESV_CUST_RESV (cr=16 r=0 w=0 time=142 us)(object id 21505)
1 SORT AGGREGATE (cr=2 r=0 w=0 time=54 us)
1 FIRST ROW (cr=2 r=0 w=0 time=34 us)
1 INDEX RANGE SCAN (MIN/MAX) ALT_RESV_AGCY_ALTE (cr=2 r=0 w=0 time=29 us)(object id 21692)
0 SORT AGGREGATE (cr=0 r=0 w=0 time=0 us)
0 FIRST ROW (cr=0 r=0 w=0 time=0 us)
0 INDEX RANGE SCAN (MIN/MAX) ALT_RESV_AGCY_AGEN_ALTE (cr=0 r=0 w=0 time=0 us)(object id 21695)
1 SORT AGGREGATE (cr=2 r=0 w=0 time=86 us)
1 FIRST ROW (cr=2 r=0 w=0 time=28 us)
1 INDEX RANGE SCAN (MIN/MAX) ALT_RESV_COMP_ALTE (cr=2 r=0 w=0 time=23 us)(object id 21702)
1 SORT AGGREGATE (cr=2 r=0 w=0 time=34 us)
1 FIRST ROW (cr=2 r=0 w=0 time=25 us)
1 INDEX RANGE SCAN (MIN/MAX) ALT_RESV_INTE_AGEN_ALTE (cr=2 r=0 w=0 time=19 us)(object id 21764)
1 SORT AGGREGATE (cr=2 r=0 w=0 time=38 us)
1 FIRST ROW (cr=2 r=0 w=0 time=26 us)
1 INDEX RANGE SCAN (MIN/MAX) ALT_RESV_CUST_ALTE (cr=2 r=0 w=0 time=21 us)(object id 21734)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (UNIQUE)
1 FILTER
8 NESTED LOOPS (OUTER)
8 NESTED LOOPS (OUTER)
4 NESTED LOOPS (OUTER)
2 NESTED LOOPS (OUTER)
2 NESTED LOOPS (OUTER)
2 NESTED LOOPS
1 NESTED LOOPS
5 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'RESVGUEST'
5 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESVGUEST_NAME_FB_IDX' (NON-UNIQUE)
5 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'RESV'
5 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PK_RESV_PRIM' (UNIQUE)
5 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'RESVITINSEGMENT'
1 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESVITINSEG_STARTDTTM_IDX' (NON-UNIQUE)
11 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESVCOMP'
2 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESV_COMP_RESV' (NON-UNIQUE)
2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESVAGCY'
2 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESV_AGCY_RESV' (NON-UNIQUE)
2 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESVAGCYAGENT'
0 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESV_AGCY_AGEN_RESV_AGCY' (NON-UNIQUE)
0 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESVINTERNALAGENT'
4 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'RESV_INTE_AGEN_RESV' (NON-UNIQUE)
4 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'RESVCUSTOMER'
8 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'RESV_CUST_RESV'
(NON-UNIQUE)
8 SORT (AGGREGATE)
1 FIRST ROW
1 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
'ALT_RESV_AGCY_ALTE' (UNIQUE)
1 SORT (AGGREGATE)
0 FIRST ROW
0 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
'ALT_RESV_AGCY_AGEN_ALTE' (UNIQUE)
0 SORT (AGGREGATE)
1 FIRST ROW
1 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
'ALT_RESV_COMP_ALTE' (UNIQUE)
1 SORT (AGGREGATE)
1 FIRST ROW
1 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
'ALT_RESV_INTE_AGEN_ALTE' (UNIQUE)
1 SORT (AGGREGATE)
1 FIRST ROW
1 INDEX GOAL: ANALYZED (RANGE SCAN (MIN/MAX)) OF
'ALT_RESV_CUST_ALTE' (UNIQUE)


**************************

My question is - are there performance tradeoff's to using sys_context? Why does the optimizer choose a different execution plan with sys_context?

This issue is causing me great pain and my mgmt. is telling me to concatenate the variables in - ie - no binds!

Help!!

Ben

i can't tell you if this exact query is popular or not (i dont have numbers). the pkg. can qualify based on 100+ different parameters - in any combination.

that was just a random test case i created to check performance. it was causing problems so i investigated the behavior.

i raised the issue about trashing the shared pool. as usual the requirements outweigh the dba's concerns.

i am capturing the parameters passed each iteration to try to identify "popular requests" - but without that would it be better to forego binding and just concat the values into the query?

this is a v9.2 db on solaris

hi tom -

the frequency of searches. i am told "often". call center type operations. my guess is every 3 seconds?

i set the cursor sharing as you suggested. the plan did not change.

through more testing - i noticed that the combination of LIKE and sys_context - cause the execution plan issues i am experiencing. if i execute the query with '=' the plan is efficient. i do have function based indexes on the searchable columns.

perhaps user expectation needs to be set accordingly when using LIKE functionality.

that is to be expected -- with "=" it knows "a single value", with like - all bets are off.

consider:

ops$tkyte@ORA9IR2> create table t as select * from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2> create index t_idx on t(object_name);
 
Index created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.gather_table_stats( user, 'T', method_opt=> 'for all indexed columns size 254', cascade => true );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where object_name like sys_context('myctx', 'x');
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=1396 Bytes=139600)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=39 Card=1396 Bytes=139600)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=251)
 
 
<b>that is the generic best guess (1,396 rows) and it is really very close to the "generic" answer, the general answer.</b>


ops$tkyte@ORA9IR2> select * from t where object_name = sys_context('myctx', 'x');
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=200)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=2 Bytes=200)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=2)
 
<b>that is 2 for reasons obvious to the optimizer -- object_name is almost unique, where object_name = VALUE is known to return 1 or 2 rows

Using literals however:</b>
 
 
ops$tkyte@ORA9IR2> select * from t where object_name like 'ACC%';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=200)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=4 Card=2 Bytes=200)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=2)
 
 
 
ops$tkyte@ORA9IR2> select * from t where object_name like 'jav%';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=40 Card=2638 Bytes=263800)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=40 Card=2638 Bytes=263800)
 
 
<b>we get somewhere between 2 and 2600 -- the plans flip flop

the following is pure "hack", don't like it but it will make the like a tad more selective (apparently)</b>:
 
ops$tkyte@ORA9IR2> select * from t 
    where object_name like substr( sys_context('myctx', 'x'),1,1)||'%' and
  2                        object_name like sys_context('myctx', 'x');
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=39 Card=70 Bytes=7000)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=39 Card=70 Bytes=7000)
   2    1     INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=3 Card=251)
 
 
 
 

any guess as to how many systems are held together by "hacks"? ;)

thanks

Tom,

"procedure do_insert( p_tname in varchar2,
p_cnames in array,
p_values in array )";

How can we write procedure "do_update" to uodate rows based on same no. of paramters as we have in above.

I have a situation where I was not aware of the into clause (how many values will come). If I use maxium variable then getting the error message "ORA-01007: variable not in select list", when it actually returns only less values in into clause.

How to find out the into clause values (OUTPUT values - how many?) dynamically? .

Part of my coding:
-- using replace for changing input values
select replace(c1.expression,':DOC_NO' ,t_sono) into t_exp from dual;
Begin
Execute immediate t_exp into t_amount1, t_amount2, t_amount3, t_amount4 ;
<< How to put dynamically t_amount1, 2, 3 & 4, since how many output values will come is NOT known>>
exception
<< exceptions >>
end;
else
<< giving error - saying input values not replaced fully >>

Hi Tom,
I am getting an error when I execute the following procedure.

SQL>  create or replace procedure p2(x in number,y out varchar2)
  2   as
  3   begin
  4   execute immediate 'select ename from emp where empno = :empno'
  5   using in x,out y;
  6   end;
  7  /

Procedure created.

SQL> var enm varchar2(30)
SQL> exec p2(7900,:enm)
BEGIN p2(7900,:enm); END;

*
ERROR at line 1:
ORA-01006: bind variable does not exist 
ORA-06512: at "APPS.P2", line 4 
ORA-06512: at line 1 


ENM                                                                             
--------------------------------                                                
                                                                                
 

you tried to bind two variables to a statement that takes ONE.


ops$tkyte@ORA10GR1>  create or replace procedure p2(x in number,y out varchar2)
  2   as
  3   begin
  4   execute immediate 'select ename from emp where empno = :empno' INTO y using x;
  5   end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA10GR1>
ops$tkyte@ORA10GR1> variable enm varchar2(30)
ops$tkyte@ORA10GR1> exec p2( 7900, :enm );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR1> print enm
 
ENM
--------------------------------
JAMES
 
 

Hi Tom,
I do wanted to post this as a new Question But site was giving some error..

So here it is …
Description
-----------
As I read at most of the places that “EXECUTE IMMEDIATE” must only be used when you know the exact no of Bind variables .
So I thought of using DBMS_SQL instead of Execute Immediate ( case#2) , it only works for Only when Output variable is of Scalar variable type ( Number or varchar) NOT for OBJECT TYPE.
HERE is the err while using Object_type as Out

PLS-00306: wrong no or types of arguments in call to DEFINE_COLUMN

-------------------------------------------------------

Facts
--------------------------------------------------------
1> TYP_IDS is a table type objectL Create or Replace Type TYP_IDS as Table of NUMBER )
2> TheDesc can have string for execute function as
a. B_pkg.operate(A_pkg.getUniv('249’, :inAsofDt), A_pkg.getUniv ('247’, :inAsofDt)
b. OR C_pkg.doThis(A_pkg.getthis('22’, :inAsofDt))
In both of the cases return typ is always TYP_IDS, same Bind Variable ‘:inAsofDt’ BUT with different Frequency.
-------------------------------------------------------
Case#2
------------------------------------------------------------Procedure…..
IS
theDesc VARCHAR2(2000);
theSQL VARCHAR2(2000);
theUniv TYP_IDS;
theCursor NUMBER;
DUMMY NUMBER;
theUniv TYP_IDS:= TYP_IDS();
theCursor NUMBER;
DUMMY NUMBER;

BEGIN
SELECT description
INTO theDesc
FROM abc_mst
WHERE name =inNameOfUniversePassed;

theSQL := ' select ' || theDesc || ' from dual ';
theCursor := dbms_sql.open_cursor;

DBMS_SQL.PARSE(theCursor, theSql, dbms_sql.native);
DBMS_SQL.DEFINE_COLUMN(theCursor, 1, theUniv);
DBMS_SQL.BIND_VARIABLE(theCursor, ':inAsofDt', inAsofDt);

dummy := dbms_sql.execute(theCursor);
dummy := DBMS_SQL.FETCH_ROWS(theCursor);
DBMS_SQL.COLUMN_VALUE(theCursor, 1, theUniv);
DBMS_SQL.close_cursor(theCursor);
COMMIT;

RETURN theUniv;

END;

Tom,
I found a very bad code in our interface.
The DB1’s interface CMI run against with 100 remote databases for financial processes.
The following code is the problem, no bind variables used.
l_get_db_name.db_link_name is the database link (100 of them)
and i_var.ps_id is the post_id of the remote database (hundreds from each database)

Both l_get_db_name.db_link_name and i_var.ps_id are obtained from the cursors.

How can the code be corrected so that the bind variables will be used for the db link and ps_id?
Thank you so much.
Trinh


BEGIN
l_sql_string := 'BEGIN :i_var := IFACE.cost_pulled@' ||
l_get_db_name.db_link_name || '(' || i_var.ps_id ||'); COMMIT; END;';
EXECUTE IMMEDIATE l_sql_string USING OUT l_iface_process_no;
EXCEPTION
WHEN OTHERS THEN
.....
END;

Hello,
IF i have execute immediate or ref cursor
l_sql_string='select emp from emps where emp=:p_emp'
EXECUTE IMMEDIATE l_sql_string USING l_emp;
AS i know execute immediate doesn't make parsing an i thought that 'using l_emp' and 'emp=:p_emp' are used for parsing, so why i can't do

l_sql_string='select emp from emps where emp=||p_emp'
EXECUTE IMMEDIATE l_sql_string;
Thank you
GOSH

May you explain me if is it wrong to put into execute immediate ||p_emp instead of :p_emp.
Tnank's
GOSH

Hi,Tom,
I am confused because i know that EXECUTE immediate in comparison to static sql statement every time executes
parsing and on the other hand :x using p_emp avoid parsing.
isn'it a contradiction?
Explain me please , i think i miss something here
Thank you
GOSH

I read about soft parse and hard parse,
so if i run static sql with bind var a few times no soft and hard parse will be executed, on the other hand if i
run execute immediate 'sql..' just a soft parse will be executed all the times? And if the answer is "YES" why in execute immediate soft parse will be executed?

Thanks
JOSH

Why in static sql like
select * from emp where emp_no=var_emp (in pl/sql code)
that executed a few times soft parse will not executed?

But soft parse makes only a sintacs checks so why to run execute immedite few times is not recommended, does soft parse also heavy?
GOSH

and hard parse only when current query is not in cashe,so isn't it have to be also for static sql?

If i use execute immediate 'SELECT ...' 110 times IN LOOP
will it take much more time than STATIC SQL IN THIS LOOP
Thank you
LE.

I am doing something similar to the example above where different conditions would be parsed dynamically and evaluated. Your suggestion was to use dbms_session.set_context as the binding mechanism. I wrote a small procedure to try this with my specific requirements, but from simple timing, the set_context version takes longer to execute than the no-binding version. Am I doing something wrong?

create or replace context test_ctx using test_proc
/
create or replace
PROCEDURE TEST_PROC(numLoops in number default 10,
sSql in varchar2) is
stime date;
etime date;
diff number;

arr1 dbms_sql.number_table;
arr2 dbms_sql.number_table;
result varchar2(1);
sqltext varchar2(500);
begin
dbms_output.put_line(substr('Value of numLoops='||numLoops,1,255));

sqltext := sSql;

stime := sysdate;
for j in 1..numLoops loop
for i in 1..10 loop
arr1(i) := i+j;
arr2(i) := i+j+10;
end loop;

for i in 1 .. 10
loop
dbms_session.set_context( 'test_ctx', 'a'||i, arr1(i) );
end loop;
for i in 1 .. 10
loop
dbms_session.set_context( 'test_ctx', 'b'||i, arr2(i) );
end loop;

sqltext := replace( sqltext, '[', 'sys_context( ''test_ctx'', ''' );
sqltext := replace( sqltext, ']', ''')' );

execute immediate sqltext into result;
--dbms_output.put_line(substr('Value of result='||result,1,255));
end loop;
etime := sysdate;
diff := (etime - stime) * 60 * 60 * 24;
dbms_output.put_line(substr('Using context: value of diff='||diff,1,255));

stime := sysdate;
for j in 1..numLoops loop
for i in 1..10 loop
arr1(i) := i+j;
arr2(i) := i+j+10;
end loop;

FOR i IN arr1.FIRST .. 10
LOOP
sqltext := REPLACE (sqltext, '[a' || i || ']', round(nvl(arr1(i),0),8));
END LOOP;

FOR i IN arr2.FIRST .. 10
LOOP
sqltext := REPLACE (sqltext, '[b' || i || ']', round(nvl(arr2(i),0),8));
END LOOP;
--dbms_output.put_line(substr('Value of sqltext='||sqltext,1,255));
execute immediate sqltext into result;
--dbms_output.put_line(substr('Value of result='||result,1,255));
end loop;
etime := sysdate;
diff := (etime - stime) * 60 * 60 * 24;
dbms_output.put_line(substr('Without bind variables: value of diff='||diff,1,255));
dbms_session.clear_context( 'test_ctx', 'test_Proc');
end;
/

Here is a sample call to the procedure:

begin
test_proc(5000,'select decode(sign([a4]+[a5]+[a6]+[a7]+[a8]-5),1,decode(sign([b4]+[b5]+[b6]+[b7]+[b8]),1,''Y'',''N''),''N'') from dual');
end;

And the output was:
Value of numLoops=5000
Using context: value of diff=6
Without bind variables: value of diff=3

Thanks for any help.

you do realize you parsed the same sql over and over?  and all of them were sys_context() !!!!!!!   

(you assigned ssql OUTSIDE the loop, it got replaced ONCE and was constant after that!)


not very real world....  I assume in real life the inputs would have varied?

so, lets break this apart and use runstats on it to measure elapsed time AND more *importantly* latching.....


This is needed in case you want to run this more than once (in order to hard parse which is what would really happen as people passed different inputs in there...)


ops$tkyte@ORA9IR2> alter system flush shared_pool; 
System altered.
 
ops$tkyte@ORA9IR2> create or replace context test_ctx using test_proc
  2  / 
Context created.
 
ops$tkyte@ORA9IR2> create or replace
  2  PROCEDURE TEST_PROC(numLoops in number default 10,
  3                      sSql     in varchar2)
  4  is
  5    arr1       dbms_sql.number_table;
  6    arr2       dbms_sql.number_table;
  7    result  varchar2(1);
  8    sqltext varchar2(500);
  9  begin
 10    for j in 1..numLoops
 11    loop
 12      for i in 1..10
 13      loop
 14        arr1(i) := i+j;
 15        arr2(i) := i+j+10;
 16      end loop;
 17
 18      for i in 1 .. 10
 19      loop
 20        dbms_session.set_context( 'test_ctx', 'a'||i, arr1(i) );
 21      end loop;
 22      for i in 1 .. 10
 23      loop
 24        dbms_session.set_context( 'test_ctx', 'b'||i, arr2(i) );
 25      end loop;
 26
 27      sqltext := sSql;  <b><<<=== this was your mistake, you replaced it ONCE</b>
 28      sqltext := replace( sqltext, '[', 'sys_context( ''test_ctx'', ''' );
 29      sqltext := replace( sqltext, ']', ''')' );
 30
 31      execute immediate sqltext into result;
 32    end loop;
 33  end;
 34  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace
  2  PROCEDURE TEST_PROC2(numLoops in number default 10,
  3                      sSql     in varchar2)
  4  is
  5    arr1       dbms_sql.number_table;
  6    arr2       dbms_sql.number_table;
  7    result  varchar2(1);
  8    sqltext varchar2(500);
  9  begin
 10    for j in 1..numLoops loop
 11      for i in 1..10
 12          loop
 13        arr1(i) := i+j;
 14        arr2(i) := i+j+10;
 15      end loop;
 16
 17      sqltext := sSql; <b><<<=== moved here to vary in the inputs!</b>
 18      FOR i IN arr1.FIRST .. 10
 19      LOOP
 20        sqltext := REPLACE (sqltext, '[a' || i || ']', round(nvl(arr1(i),0),8));
 21      END LOOP;
 22
 23      FOR i IN arr2.FIRST .. 10
 24      LOOP
 25        sqltext := REPLACE (sqltext, '[b' || i || ']', round(nvl(arr2(i),0),8));
 26      END LOOP;
 27      execute immediate sqltext into result;
 28    end loop;
 29  end;
 30  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_start
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> begin
  2  test_proc(5000,'select decode(sign([a4]+[a5]+[a6]+[a7]+[a8]-5),1,decode(sign([b4]+[b5]+[b6]+[b7]+[b8]),1 ,''Y'',''N''),''N'') from dual');
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_middle
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> begin
  2  test_proc2(5000,'select decode(sign([a4]+[a5]+[a6]+[a7]+[a8]-5),1,decode(sign([b4]+[b5]+[b6]+[b7]+[b8]),1 ,''Y'',''N''),''N'') from dual');
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_stop(1000)
Run1 ran in 305 hsecs
Run2 ran in 377 hsecs
run 1 ran in 80.9% of the time

<b>slightly different than your results and....</b>


 
  1  select count(case when sql_text not like '%sys_context%' then 1 end),
  2         sum(case when sql_text not like '%sys_context%' then sharable_mem end),
  3  count(case when sql_text like '%sys_context%' then 1 end),
  4         sum(case when sql_text like '%sys_context%' then sharable_mem end)
  5* from v$sql where sql_text like 'select decode(sign(% from dual'
ops$tkyte@ORA9IR2> /
 
COUNT(CASEWHENSQL_TEXTNOTLIKE'%SYS_CONTEXT%'THEN1END)
-----------------------------------------------------
SUM(CASEWHENSQL_TEXTNOTLIKE'%SYS_CONTEXT%'THENSHARABLE_MEMEND)
--------------------------------------------------------------
COUNT(CASEWHENSQL_TEXTLIKE'%SYS_CONTEXT%'THEN1END)
--------------------------------------------------
SUM(CASEWHENSQL_TEXTLIKE'%SYS_CONTEXT%'THENSHARABLE_MEMEND)
-----------------------------------------------------------
                                                 5000
                                                      52076141
                                                 1
                                                      13698


that is quite a difference.


Actually, I would prefer to use binds here if you can.

store your "formula" as:

decode(sign(:a4+:a5+:a6+:a7+:a8-5),1......


and use dbms_sql to bind by name a1, a2, a3, .... (catching exceptions when you try to bind a variable that isn't actually used).


one hard parse -- cache the cursor and reuse it over and over and over and over!


 

Yes....my (not so smart) mistake. I will give dbms_sql a swing and see how that compares. Thanks for your time and patience.

The following is very simplistic simulation of what I'm trying to achieve. Basically, I have a whole bunch of SQL statements stored in a table. At runtime, I retreive the SQL statements from the database table and run them in the PL/SQL code. These SQL statements fall into two categories: the ones with two bind variables and the ones with three bind variables. The ones with three bind variables repeat the same bind variable twice (see l_sql_text2; bind variable x).

The problem is that when I retrieve the SQL statements from the database, I do not know which category a particular SQL statement falls into. If I use EXECUTE IMMEDIATE construct, I need to know ahead of time how to do the binding by using 'USING' keyword.  Is there anyway in a dynamic SQL that allows me to parameterize the cursor?  This way I don't really need to know if the SQL statement has two or three bind variables. Is it possible to do this?


SQL> CREATE TABLE x (
  2  col1  VARCHAR2(12),
  3  dummy VARCHAR2(1)
  4  );

Table created.

SQL> 
SQL> 
SQL> DECLARE
  2     l_sql_text1 VARCHAR2(100);
  3     l_sql_text2 VARCHAR2(100);
  4  
  5  BEGIN
  6     DELETE FROM x;
  7  
  8     l_sql_text1 := 'INSERT INTO x ' ||
  9                    'SELECT ''l_sql_text2'', dummy FROM dual WHERE 1 = :x AND 2 = :y';
 10     l_sql_text2 := 'INSERT INTO x ' ||
 11                    'SELECT ''l_sql_text3'', dummy FROM dual WHERE 1 = :x AND 2 = :y AND 1 = 

:x';

 12  
 13     EXECUTE IMMEDIATE l_sql_text1 USING 1, 2;
 14     EXECUTE IMMEDIATE l_sql_text2 USING 1, 2, 1;
 15  END;
 16  /

PL/SQL procedure successfully completed.

SQL> 
SQL> SELECT * FROM x;

COL1         D
------------ -
l_sql_text2  X
l_sql_text3  X

SQL> 
SQL> ROLLBACK;

Rollback complete.

 

It would be nice if there was a function of dbms_sql to return the no of binds in a statement, in a similiar way we retrieve the number of columns.

I guess using length(str)-length(replace(str,':','')) is a nice little work around.

Tom,
We are currently having 2 test schemas in a database. Let's say "Main" Schema and "User" Schema.
We are writing all our procedures in "Main" Schema. But, want our users to test in "User" Schema. So we have created sperate set of tables and procedures in "User" Schema. So when we execute our procedures we do not mess up users test cases (data) and Vice Versa.
But this way whenever we write or create new procedures, we need to do so in "Users" Schema too. and maintanance is another extra work. Whenever we do changes in "Main" Schema we need to update "Users" Schema. So i thought of changing the procedures, so that whenever we execute these procedures they write data into respective schema tables.and we can have only one set of procedures (in "Main"Schema and grant execute to "Users" Schema)


Eg:- if i login as "Main" and execute this procedures, data is updated/inserted/deleted from this Schema tables, and if i login as "User" , it does the same in User schema. But i need to change regular, simple DML's to Dynamic DML's. Will there be any significant affect in performance? We are talking about Tons of procedures and tables in future?

in procedure, i can do an "Execute immediate 'Insert into '||schema.tablename"
instead of "insert into tablename"

2)And i'm also using many sys_context's in those procedures created in "Main" Schema. How are they going to be affected if i create just one set of procedures? Can I use these context's in other Schema's too?

Any ideas , you can suggest, will be of much help.

Thanks,

Thanks for your response. But, if i install those procedures in two schemas seperately, then maintaining them is a bit of problem. Don't you think? When ever i change one set of procedures /packages, i've to do the same in other schema?

HTML DB has the concept of collections to persistently store session-specific data. One of the APIs to manage that has the signature

HTMLDB_COLLECTION.UPDATE_MEMBER (
p_collection_name => collection name,
p_seq => member sequence number,
p_c001 => member attribute 1,
p_c002 => member attribute 2,
p_c003 => member attribute 3,
p_c004 => member attribute 4,
p_c005 => member attribute 5,
p_c006 => member attribute 6,
p_c007 => member attribute 7,
...
p_c050 => member attribute 50);

i.e. the number of arguments to the API are variable, it defaults the rest of them to NULL.

I have a PL/SQL table of varchar2 with upto 50 rows that I want to pass as arguments to the upto 50 parameters above.

How can I do this?

if l_array.count=1 I would like to call the above API with
HTMLDB_COLLECTION.UPDATE_MEMBER (
p_collection_name => collection name,
p_seq => member sequence number,
p_c001 => l_array(1));

If l_array.count=2, I would like to call it with
HTMLDB_COLLECTION.UPDATE_MEMBER (
p_collection_name => collection name,
p_seq => member sequence number,
p_c001 => l_array(1)
p_c002 => l_array(2)
);

and so on.

Any ideas? Thanks

I cant pad the array to 50 because the number of elements in my array determine the number of "columns" in the collection. In a subsequent query region, I build a SELECT statement that uses only those columns ie.
select c001,c002,....c00N from htmldb_collections where ...

Here is what I ended up doing

l_sql := 'begin htmldb_collection.update_member(''MY_COLLECTION'',to_char(:seq)';

for i in 1..l_array.count loop
l_sql := l_sql||',''';
l_sql := l_sql||l_array(i);
l_sql := l_sql||'''';
end loop;
l_sql := l_sql||');end;';

execute immediate l_sql using l_seq_id;

Is this OK? Would it use bind variables?

Thanks

As it turns out, there is a API htmldb_collection.update_member_attribute() that lets you pass in a attribute # (1 thru 50) and update just that. So I can just do

for i in 1..l_array.count loop
htmldb_collection.update_member_attribute('MY_COLLECTION',l_seq_id,i,l_array(i));
end loop;

Thanks

[The OTN forums must have just come up, they were horribly broken the last day or so, showing a ugly Java error stack on the main home page forums.oracle.com, really quite a disgrace!]

I have a table with 20 columns like
create table t
(
pk int,
c1 varchar2(4000),
...
c20 varchar2(4000)
)

I have a string of colon delimited column names like
pk:age:height:weight

The table contains the PK and the data for column names as per the string above.

How can I loop thru the table and create/execute a UPDATE statement that updates those columns in a pre-specified table?

Of course using bind variables as much as possible?

Thanks

t.pk contains the pk
t.c1 contains data for the 'age' column
t.c2 contains data for the 'height' column
t.c3 contains data for the 'weight' column

How can I loop thru the table and create/execute a UPDATE statement that updates those columns in a pre-specified table?

for rec in (select * from t) loop
construct a update statement like
update mytab set
age=rec.c1,
height=rec.c2,
weight=rec.c3
where pk=rec.pk;
end loop;

Of course using bind variables as much as possible?

The UPDATE statement above would need to be dynamic since the column names and number of columns to update come from that colon delimited string.

Help? Thanks

OK I guess I can build a string like

l_sql := update (select t.* from mytab,t
where mytab.pk=t.pk)'
loop over my column headings string and build the

age=c1
height=c2

But what about bind variables? Wont this generate a unique string every time?

Thanks

Also, I kind of want to do it row-by-row for the usual reason...I dont want bad data in 1 row to cause the entire update to fail. I want to let as much go thru as possible and show a report with Success or sqlerrm on a per-row basis.

Thanks

Tom,

I tried to execute the following pl/sql code using Bind variables, Basically the purpose of the code is, for the input location_id (PK) ID, i need to find all the child tables matching that location id.

I am trying to use Bind variables, it seems not working.
Can you help. I am getting the error as below :

Errors :
=========

@tmp
Enter value for tabname: LOCATION
old 9: where table_name = UPPER('&tabname')
new 9: where table_name = UPPER('LOCATION')
declare
*
ERROR at line 1:
ORA-01722: invalid number
ORA-06512: at line 20


No errors.
20 execute immediate l_query into l_fk_id using i.column_name;
21 exception
22 when no_data_found then
23* dbms_output.put_line('Location id not found in : '||i.table_name||' ,'||i.column_name) ;
========================


PL/SQL CODE:
============

declare
cursor c1 is
select a.table_name, (select column_name from user_cons_columns where table_name=a.table_name and
constraint_name=a.constraint_name) column_name
--, a.constraint_name, a.r_constraint_name
from user_constraints a
where (a.r_constraint_name) in ( select constraint_name
from user_constraints
where table_name = UPPER('&tabname')
and constraint_type in ( 'P', 'U' ) )
;
l_query varchar2(1000);
l_location_id number(11) ;
l_fk_id number ;
begin
for i in c1 loop
dbms_output.put_line('Table name : '||i.table_name||','|| ' Column name : '||i.column_name) ;
l_query := 'select count(*) from '||i.table_name|| ' where :col1=1498553';
begin
execute immediate l_query into l_fk_id using i.column_name;
exception
when no_data_found then
dbms_output.put_line('Location id not found in : '||i.table_name||' ,'||i.column_name) ;
end;
dbms_output.put_line('Location id : '|| to_char(l_fk_id)|| ' found in : '||i.table_name||' ,'||i.column_name) ;
end loop;
end;
/
show errors

Besides, I cant really use your "update a join" technique because my source table "t" is really the view htmldb_collections and I get the key-preserved error and I really cant get around it.

Is there a way to rewrite your update statement as a regular update without the update a join part?

Thanks

Tom,

<< l_query := 'select count(*) from '||i.table_name|| ' where :col1=1498553';
begin
execute immediate l_query into l_fk_id using i.column_name;

you seem to be comparing a string to a number. why?
>>

No, In fact :i.col1 (column name) is a number datatype. No strings are allowed to be stored.

It should compare as "LOCATION_ID=1498553". Probably :i.col1 is treated as a "data value" rather than "column name" while substituting and raising "INVALID number" error. Could it be ?

But if i code it without bind variables, its working fine.

======
l_query := 'select '||i.column_name||' from '||i.table_name|| ' where '||i.column_name||'='||l_location_id||' and ROWNUM=1';
begin
execute immediate l_query into l_fk_id ;
dbms_output.put_line('Location id : '|| l_fk_id|| ' FOUND in : '||i.table_name||' ,'||i.column_name) ;
======

I am on 9.2.0.5.

Tom,

The purpose is, for the given PK value, i need to find only the child tables that matches the PK value. Thats it.

To make the code generic, i used 'execute immediate' with dynamic sql string.

1. I tried with "Bind variables" approach and it makes sense after seeeing your response, it should not work. agree.

2. I tried with out "Bind variables" but using execute immediate as coded below. It is working. This code is executed manually on demand not as part of the application.

Question 1:
-----------
You mean, even this approach is not the right way of doing it ?

======
l_query := 'select '||i.column_name||' from '||i.table_name|| ' where
'||i.column_name||'='||l_location_id||' and ROWNUM=1';
begin
execute immediate l_query into l_fk_id ;
dbms_output.put_line('Location id : '|| l_fk_id|| ' FOUND in :
'||i.table_name||' ,'||i.column_name) ;
======

<<<
can you tell us really what you are trying to accomplish, it look suspiciously
like "do it yourself integrity" which

a) cannot be done by you without locking tables or lots of rows (eg: doesn't
scale very well)

b) is a total waste of your time, since the database does it.

>>

Question 2: Can you please explain this. Sorry, i don't get it. I don't lock any table/rows explicitly. .

Tom:

Thank you for your help in advance. How to make the code work. 
error msg:
14:26:17 SQL> EXEC TEST_VAR_PROC.LOAD_DATA;
BEGIN TEST_VAR_PROC.LOAD_DATA; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'LOAD_VAR1' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06512: at "CDC.TEST_VAR_PROC", line 17
ORA-06512: at line 1

My code is:

CREATE OR REPLACE PACKAGE TEST_VAR_PROC IS

   PROCEDURE LOAD_VAR1;
   PROCEDURE LOAD_DATA;

END TEST_VAR_PROC;
/


CREATE OR REPLACE PACKAGE BODY TEST_VAR_PROC AS
PROCEDURE LOAD_VAR1 IS
BEGIN
   dbms_output.put_line ( ' execute successfully !!!');

EXCEPTION
   WHEN OTHERS THEN
      NULL;
END LOAD_VAR1;

PROCEDURE LOAD_DATA IS 
GV_SQL VARCHAR2(500);
V_TABLE_NAME VARCHAR2(35) := 'VAR1';
BEGIN
            GV_SQL := 'BEGIN LOAD_'||V_TABLE_NAME||'; END;';
           EXECUTE IMMEDIATE GV_SQL;
END LOAD_DATA;

END TEST_VAR_PROC;
/


 

Tom:
Thank you for the helps.
I have a code like this, and it works fine. However, it is kind of stupid. There are more then 4000 veriable ( V_IND1 .. V_IND999, V_PCT1 .. V_PCT999 ....).

I believe there should have a more efficient way to code it. Thank you in advance.

CREATE OR REPLACE PROCEDURE TEST_LOOP IS

V_IND1 VARCHAR2(1);
V_PCT1 NUMBER;
V_EFF1 NUMBER;
V_END1 NUMBER;

V_IND2 VARCHAR2(1);
V_PCT2 NUMBER;
V_EFF2 NUMBER;
V_END2 NUMBER;

V_IND3 VARCHAR2(1);
V_PCT3 NUMBER;
V_EFF3 NUMBER;
V_END3 NUMBER;

V_IND4 VARCHAR2(1);
V_PCT4 NUMBER;
V_EFF4 NUMBER;
V_END4 NUMBER;

CURSOR CUR_TEST IS
SELECT AD1.CIP_IND_1, AD1.CIP_PCT_1, AD1.CIP_EFF_DT_1, AD1.CIP_END_DT_1,
AD1.CIP_IND_2, AD1.CIP_PCT_2, AD1.CIP_EFF_DT_2, AD1.CIP_END_DT_2,
AD1.CIP_IND_3, AD1.CIP_PCT_3, AD1.CIP_EFF_DT_3, AD1.CIP_END_DT_3,
AD1.CIP_IND_4, AD1.CIP_PCT_4, AD1.CIP_EFF_DT_4, AD1.CIP_END_DT_4
FROM TEST_TABLE AD1;
BEGIN
OPEN CUR_TEST;
LOOP
FETCH CUR_TEST INTO V_IND1, V_PCT1, V_EFF1, V_END1,
V_IND2, V_PCT2, V_EFF2, V_END2,
V_IND3, V_PCT3, V_EFF3, V_END3,
V_IND4, V_PCT4, V_EFF4, V_END4
EXIT WHEN CUR_TEST%NOTFOUND;

IF (V_IND1 IS NOT NULL OR V_PCT1 != 0 OR V_EFF1 != 0 OR V_END1 != 0 ) THEN
V_SUB_SEQ := 1;
DBMS_OUTPUT.PUT_LINE (' V_SUB_SEQ IS :'|| V_SUB_SEQ);
DBMS_OUTPUT.PUT_LINE (' V_IND1 IS :'|| V_IND1);
DBMS_OUTPUT.PUT_LINE (' V_PCT1 IS :'|| V_PCT1);
DBMS_OUTPUT.PUT_LINE (' V_EFF1 IS :'|| V_EFF1);
DBMS_OUTPUT.PUT_LINE (' V_END1 IS :'|| V_END1);
END IF;

IF (V_IND2 IS NOT NULL OR V_PCT2 != 0 OR V_EFF2 != 0 OR V_END2 != 0 ) THEN
V_SUB_SEQ := 2;
DBMS_OUTPUT.PUT_LINE (' V_SUB_SEQ IS :'|| V_SUB_SEQ);
DBMS_OUTPUT.PUT_LINE (' V_IND1 IS :'|| V_IND1);
DBMS_OUTPUT.PUT_LINE (' V_PCT1 IS :'|| V_PCT1);
DBMS_OUTPUT.PUT_LINE (' V_EFF1 IS :'|| V_EFF1);
DBMS_OUTPUT.PUT_LINE (' V_END1 IS :'|| V_END1);
END IF;

IF (V_IND3 IS NOT NULL OR V_PCT3 != 0 OR V_EFF3 != 0 OR V_END3 != 0 ) THEN
V_SUB_SEQ := 3;
DBMS_OUTPUT.PUT_LINE (' V_SUB_SEQ IS :'|| V_SUB_SEQ);
DBMS_OUTPUT.PUT_LINE (' V_IND1 IS :'|| V_IND1);
DBMS_OUTPUT.PUT_LINE (' V_PCT1 IS :'|| V_PCT1);
DBMS_OUTPUT.PUT_LINE (' V_EFF1 IS :'|| V_EFF1);
DBMS_OUTPUT.PUT_LINE (' V_END1 IS :'|| V_END1);
END IF;

IF (V_IND4 IS NOT NULL OR V_PCT4 != 0 OR V_EFF4 != 0 OR V_END4 != 0 ) THEN
V_SUB_SEQ := 4;
DBMS_OUTPUT.PUT_LINE (' V_SUB_SEQ IS :'|| V_SUB_SEQ);
DBMS_OUTPUT.PUT_LINE (' V_IND1 IS :'|| V_IND1);
DBMS_OUTPUT.PUT_LINE (' V_PCT1 IS :'|| V_PCT1);
DBMS_OUTPUT.PUT_LINE (' V_EFF1 IS :'|| V_EFF1);
DBMS_OUTPUT.PUT_LINE (' V_END1 IS :'|| V_END1);
END IF;
END LOOP;
CLOSE CUR_TEST;
COMMIT;
END TEST_LOOP;
/

I found this posting with the same problem as mine.
you said:

-- Followup:
'begin TEST_VAR_PROC.LOAD_' || v_table_name || '; end;'

pretend that exeucte immediate is a function call to another package (it is
conceptually). you are not in the scope of YOUR package when you call it. --

Great, so how do I pass a variable to an 'execute immediate' statement (which you basically always have to do)?

Hi Tom,

sorry, my example should have been more specific. I have a 'criteria' table, updated by the user, which looks like this:

tlb_criteria
column operator value
EMP.EMPNO IN (123, 456)

I am reading the values into v_column, v_operator, and v_value

I need to set a flag to TRUE if EMP.EMPNO IN (123, 456), but without hardcoding column, operator, and value.

If I write:
EXECUTE IMMEDIATE 'begin '|| v_flag ||' := ' ||v_column || ' ' || v_operator || ' ' || v_value; end;' using out v_flag;

I get an error that v_column, v_operator, and v_value do not exist (I am not sure of v_flag either).

In your answer, if I understood correctly, you say to use bind variables. I can't get the syntax to work.

I appreciate your help.

I've replicated (I think precisely) your original solution and run a SQL_TRACE/tkprof. With the results as pasted below.
Why is the
begin if (sys_context( 'my_ctx', 'AGE')>30 AND  sys_context( 'my_ctx', 'SEX')=
  'M') OR sys_context( 'my_ctx', 'STAFF_CATEGORY')='MGR'  then :1 := 'Y'; end 
  if; end;

Shown as being parsed twice? This corresponds to the variable data and would imply that a parse in being done for
each set or data elements being passed to the rule. 

12:46:55 SQL> @c:\tomtest
12:47:03  21  /
processing "([AGE]>30 AND  [SEX]='M') OR [STAFF_CATEGORY]='MGR' "
set age = 15
set sex = M
set staff_category = MGR
answer is Y
-------------------
set age = 15
set sex = M
set staff_category = XX
answer is N
-------------------

PL/SQL procedure successfully completed.

12:47:04 SQL> select * from t
12:57:18   2  /

   RULE_ID
----------
CONDITION
--------------------------------------------------------------------------------
         1
([AGE]>30 AND  [SEX]='M') OR [STAFF_CATEGORY]='MGR'


12:57:19 SQL> 

TKPROF: Release 9.2.0.1.0 - Production on Wed Oct 19 12:54:43 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: fra02_ora_23107.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

alter session set sql_trace=true


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 66  (FRACREL)
********************************************************************************

 declare
     l_names dbms_sql.varchar2s;
     l_values dbms_sql.varchar2s;
 begin
     l_names(1) := 'age';
     l_values(1) := '15';
     l_names(2) := 'sex';
     l_values(2) := 'M';
     l_names(3) := 'staff_category';
      l_values(3) := 'MGR';
       for x in ( select * from t )
      loop
          dbms_output.put_line( 'processing "' || x.condition || '"' );
          parse_procedure( l_names, l_values, x.condition );
          l_values(3) := 'XX';
         parse_procedure( l_names, l_values, x.condition );
      end loop;
  end;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.01       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.01       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 66  (FRACREL)
********************************************************************************

select * 
from
 t 


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          8          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          8          0           1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 66  (FRACREL)   (recursive depth: 1)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   TABLE ACCESS (FULL) OF 'T'

********************************************************************************

begin if (sys_context( 'my_ctx', 'AGE')>30 AND  sys_context( 'my_ctx', 'SEX')=
  'M') OR sys_context( 'my_ctx', 'STAFF_CATEGORY')='MGR'  then :1 := 'Y'; end 
  if; end;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           3
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           3

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 66  (FRACREL)   (recursive depth: 1)
********************************************************************************

SELECT sys_context(:b2,:b1) 
from
 sys.dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0         12          0           4
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        9      0.00       0.00          0         12          0           4

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 66  (FRACREL)   (recursive depth: 2)

Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   TABLE ACCESS (FULL) OF 'DUAL'

********************************************************************************

BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 66  (FRACREL)



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.01       0.00          0          0          0           0
Execute      3      0.00       0.00          0          0          0           2
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      0.01       0.00          0          0          0           2

Misses in library cache during parse: 0


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        4      0.00       0.00          0          0          0           0
Execute      7      0.00       0.00          0          0          0           3
Fetch        6      0.00       0.00          0         20          0           5
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       17      0.00       0.00          0         20          0           8

Misses in library cache during parse: 0

    7  user  SQL statements in session.
    0  internal SQL statements in session.
    7  SQL statements in session.
    2  statements EXPLAINed in this session.
********************************************************************************
Trace file: fra02_ora_23107.trc
Trace file compatibility: 9.00.01
Sort options: default

       1  session in tracefile.
       7  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       7  SQL statements in trace file.
       6  unique SQL statements in trace file.
       2  SQL statements EXPLAINed using schema:
           FRACREL.prof$plan_table
             Default table was used.
             Table was created.
             Table was dropped.
      86  lines in trace file.










 

I thought the purpose of the original solution was to allow a set of rules to be parsed only once (per rule), regardless of how much (rows of '15', 'M', 'MGR' ) data was thrown at it.

Is it that after the 'rule' has been hard-parsed once subsequent parses are soft-parses? We're building a system modelled on the example with (potentially) 100's of rules each of which are tested against many thousands of rows. I'm a bit concerned that 100's times 1000's of parses (even soft-parses) doesn't augur well for scalability & performance.


Thanks for your time.

I've been trying to modify the parse_procedure to verify that a table of data meets data rules. Here's what I've come up with so far.


/*************************************/
create or replace procedure run_parse_procedure (v_table_name VARCHAR2)as

l_rowcount NUMBER;
l_names dbms_sql.varchar2s;
l_values dbms_sql.varchar2s;

begin

select max(column_id) INTO l_rowcount from user_tab_columns where table_name = v_table_name;

l_rowcount := l_rowcount + 1;

for i in 1..l_rowcount LOOP

select column_name into l_names(i) from user_tab_columns where table_name = v_table_name and column_Id = i;
dbms_output.put_line( 'l_names("' || i || '"}' || l_names(i)|| '"' );
END loop;

for x in ( select * from t)
loop
dbms_output.put_line( 'processing "' || x.condition || '"' );
parse_procedure( l_names, l_values, x.condition );

parse_procedure( l_names, l_values, x.condition );
end loop;
end;


/*************************************/

Here's where I'm stumped. I'm not sure how to assign the column values from the table to l_values and have them pass in to the procedure correctly.

Example using Emp table:

lnames(1):= EMPNO
lnames(2):= ENAME
lnames(3):= JOB
lnames(4):= MGR
lnames(5):= HIREDATE
lnames(6):= SAL
lnames(7):= COMM
lnames(9):= DEPTNO

l_values(1):= 7369
l_values(2):= SMITH
l_values(3):= CLERK
l_values(4):= 7902
l_values(5):= 12/17/1980
l_values(6):= 800
l_values(7):= 20

This seems like it would be slow when you're talking 100,000 records. I thought of a passing a record, but I'd like to use the same procedure for more than one table with different structures.

thanks.

I have a table of formulae
refID Formula Condition
1 Average average>7
2 Param1*(Average)+Param2 average<7
3 Cd*sqr(2*32.2)*(7-0.2*x)*(x^1.5)
4 Param1*(Average^Param2)+(Param2*Average)+Param3
5

I need to check condition and then depending on the condition I need to execute formula. I tried dynamic sql but bind variables is not right approach.
How can I handle this?

I've taken your advice

/*****************************
dbms_sql would be useful to

a) reduce the amount of parsing (since you only call parse when the statement
changes - parse once, execute many)

b) bind by name a varying number of inputs.

/******************************

and it works great. My problem is that it takes forever for it to run. I'm processing a table of > 100,000 rows with 56 columns. Its the base table for a data collection application. There are minimally about 100 rules that apply to the data. I'm sure I missed something in your example that is causing it to go slow, but it takes around 7 hours to run. Here's my code:
/**************************************/

CREATE OR REPLACE PROCEDURE run_parse_procedure (
v_table_name VARCHAR2,
v_query VARCHAR2
)
AS
l_rowcount NUMBER;
l_names DBMS_SQL.varchar2s;
l_values DBMS_SQL.varchar2s;
l_thecursor INTEGER DEFAULT DBMS_SQL.open_cursor;
l_columnvalue VARCHAR2 (2000);
l_status INTEGER;
l_colcnt NUMBER DEFAULT 0;
l_cnt NUMBER DEFAULT 0;
p_table_name VARCHAR2 (50) := UPPER (v_table_name);
BEGIN
SELECT MAX (column_id)
INTO l_rowcount
FROM user_tab_columns
WHERE table_name = p_table_name;

FOR i IN 1 .. l_rowcount
LOOP
SELECT column_name
INTO l_names (i)
FROM user_tab_columns
WHERE table_name = p_table_name AND column_id = i;
END LOOP;

/********************************************************************/
DBMS_SQL.parse (l_thecursor, v_query, DBMS_SQL.native);

FOR i IN 1 .. 255
LOOP
BEGIN
DBMS_SQL.define_column (l_thecursor, i, l_columnvalue, 2000);
l_colcnt := i;
EXCEPTION
WHEN OTHERS
THEN
IF (SQLCODE = -1007)
THEN
EXIT;
ELSE
RAISE;
END IF;
END;
END LOOP;
l_status := DBMS_SQL.EXECUTE (l_thecursor);

LOOP
EXIT WHEN (DBMS_SQL.fetch_rows (l_thecursor) <= 0);
l_rowcount := DBMS_SQL.last_row_count;

FOR i IN 1 .. l_colcnt
LOOP
DBMS_SQL.COLUMN_VALUE (l_thecursor, i, l_values (i));

END LOOP;

l_cnt := l_cnt + 1;

parse_procedure2 (l_names, l_values);
-- DBMS_OUTPUT.PUT_LINE('run parse_procedure count(' || l_cnt ||')');

END LOOP;

DBMS_SQL.close_cursor (l_thecursor);

commit;
END;

/*****************************************
CREATE OR REPLACE PROCEDURE parse_procedure (
p_names IN DBMS_SQL.varchar2s,
p_values IN DBMS_SQL.varchar2s
)
AS
p_rule LONG;
l_rule LONG;
l_yn VARCHAR2 (1) DEFAULT 'N';
p_rule_id VARCHAR2 (6);
p_rec_count NUMBER := 0;
p_stuid_sk NUMBER;
BEGIN
FOR i IN 1 .. p_names.COUNT
LOOP
DBMS_SESSION.set_context ('my_ctx', p_names (i), p_values (i));
-- DBMS_OUTPUT.put_line ('set ' || p_names (i) || ' = ' || p_values (i));
END LOOP;

p_stuid_sk := p_values (1);

FOR x IN (SELECT *
FROM t)
LOOP
p_rec_count := p_rec_count + 1;
p_rule := x.condition;
p_rule_id := x.rule_id;
l_rule := REPLACE (p_rule, '[', 'sys_context( ''my_ctx2'', ''');
l_rule := REPLACE (l_rule, ']', ''')');

EXECUTE IMMEDIATE 'begin if '
|| l_rule
|| ' then :1 := ''Y''; end if; end;'
USING OUT l_yn;

IF l_yn = 'Y'
THEN
DBMS_OUTPUT.put_line ('processing: ' || x.condition);
DBMS_OUTPUT.put_line ( 'parse Procedure lnames: '
|| p_names (1)
|| ' '
|| p_values (1)
|| ', '
|| p_names (2)
|| ' '
|| ' lvalues: '
|| p_values (2)
);
DBMS_OUTPUT.put_line ('answer is ' || l_yn);
DBMS_OUTPUT.put_line ('-------------------');

l_yn := 'N';

END IF;
END LOOP;

END;
/

I've tried running it with TKProfs (with only 390 records) and it appears to be parsing like crazy....I'm heading over to my DBA's cubie, but was wondering if you could give me any ideas?

I didn't put the entire report output in here, but it would appear to me (and my DBA) that I'm parsing for every rule and every record and that is what is making it so slow. Right now there are 390 data records and 87 rules, but you can see that it is parsing 53680 times when it selects sys_context from dual. I'm not sure where that number comes from, but it looks like its (390 * 87 * 2ish). Anyway I'm going to try to step through it again and figure out what's happening, but it seems that its passing through the right variables....

thanks for your help


TKPROF: Release 9.2.0.3.0 - Production on Tue Apr 18 11:00:45 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Trace file: db241_ora_26352.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

/* Formatted on 2006/04/18 11:39 (Formatter Plus v4.8.7) */
ALTER SESSION SET sql_trace = TRUE

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer goal: CHOOSE
Parsing user id: 109
********************************************************************************

select o.*, NVL(d.debuginfo, 'F') DEBUGINFO, nvl(p.AUTHID, 'CURRENT_USER') authid
from (
Select object_name, object_type, decode(status, 'VALID', 'V', 'I') status, last_ddl_time, object_id, created
from sys.user_objects
where 1=1
and object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY')
) o, sys.all_probe_objects d
,(SELECT object_name, AUTHID
FROM sys.user_procedures
where 1=1
GROUP BY object_name, AUTHID) p
where p.object_name (+) = o.object_name
and o.OBJECT_ID = d.object_id (+)
And D.Owner (+) = :own
And O.Object_Name = D.Object_Name (+)
And ((d.object_type is null) or (d.object_type in ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY')))
order by 2, 1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.03 0.03 0 1450 0 48
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.03 0 1450 0 48

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 109

Rows Row Source Operation
------- ---------------------------------------------------
48 SORT ORDER BY (cr=1450 r=0 w=0 time=32090 us)
48 MERGE JOIN OUTER (cr=1450 r=0 w=0 time=31791 us)
48 SORT JOIN (cr=931 r=0 w=0 time=21808 us)
48 FILTER (cr=931 r=0 w=0 time=21501 us)
48 MERGE JOIN OUTER (cr=931 r=0 w=0 time=21411 us)
48 SORT JOIN (cr=185 r=0 w=0 time=3505 us)
48 VIEW (cr=185 r=0 w=0 time=3176 us)
48 UNION-ALL (cr=185 r=0 w=0 time=3111 us)
48 FILTER (cr=185 r=0 w=0 time=2926 us)
48 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=185 r=0 w=0 time=2839 us)
289 INDEX RANGE SCAN I_OBJ2 (cr=5 r=0 w=0 time=721 us)(object id 37)
0 TABLE ACCESS BY INDEX ROWID IND$ (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN I_IND1 (cr=0 r=0 w=0 time=0 us)(object id 39)
0 FILTER (cr=0 r=0 w=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID LINK$ (cr=0 r=0 w=0 time=0 us)
0 INDEX RANGE SCAN I_LINK1 (cr=0 r=0 w=0 time=0 us)(object id 113)
48 SORT JOIN (cr=746 r=0 w=0 time=17700 us)
271 VIEW (cr=746 r=0 w=0 time=16830 us)
271 SORT UNIQUE (cr=746 r=0 w=0 time=16481 us)
301 FILTER (cr=746 r=0 w=0 time=12597 us)
321 NESTED LOOPS OUTER (cr=481 r=0 w=0 time=8403 us)
289 NESTED LOOPS (cr=187 r=0 w=0 time=4026 us)
1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 r=0 w=0 time=36 us)
1 INDEX UNIQUE SCAN I_USER1 (cr=1 r=0 w=0 time=18 us)(object id 44)
289 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=185 r=0 w=0 time=3584 us)
289 INDEX RANGE SCAN I_OBJ2 (cr=5 r=0 w=0 time=818 us)(object id 37)
110 INDEX RANGE SCAN I_IDL_CHAR1 (cr=294 r=0 w=0 time=2792 us)(object id 121)
2 INDEX RANGE SCAN I_IDL_CHAR1 (cr=157 r=0 w=0 time=799 us)(object id 121)
53 TABLE ACCESS BY INDEX ROWID IND$ (cr=108 r=0 w=0 time=808 us)
53 INDEX UNIQUE SCAN I_IND1 (cr=55 r=0 w=0 time=367 us)(object id 39)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
48 SORT JOIN (cr=519 r=0 w=0 time=9764 us)
42 VIEW (cr=519 r=0 w=0 time=9517 us)
42 SORT GROUP BY (cr=519 r=0 w=0 time=9461 us)
101 NESTED LOOPS OUTER (cr=519 r=0 w=0 time=9033 us)
101 NESTED LOOPS OUTER (cr=519 r=0 w=0 time=8592 us)
101 NESTED LOOPS (cr=519 r=0 w=0 time=8033 us)
289 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=185 r=0 w=0 time=2149 us)
289 INDEX RANGE SCAN I_OBJ2 (cr=5 r=0 w=0 time=522 us)(object id 37)
101 TABLE ACCESS BY INDEX ROWID PROCEDUREINFO$ (cr=334 r=0 w=0 time=4379 us)
101 INDEX RANGE SCAN I_PROCEDUREINFO1 (cr=292 r=0 w=0 time=3479 us)(object id 116)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 r=0 w=0 time=226 us)
0 INDEX UNIQUE SCAN I_OBJ1 (cr=0 r=0 w=0 time=81 us)(object id 36)
0 TABLE ACCESS CLUSTER USER$ (cr=0 r=0 w=0 time=195 us)
0 INDEX UNIQUE SCAN I_USER# (cr=0 r=0 w=0 time=72 us)(object id 11)

********************************************************************************

Select CREATED, LAST_DDL_TIME, OBJECT_ID, STATUS, TIMESTAMP
from sys.user_objects
where object_name = :n
and object_type = :t

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 3 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 3 0 1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 109

Rows Row Source Operation
------- ---------------------------------------------------
1 VIEW (cr=3 r=0 w=0 time=160 us)
1 UNION-ALL (cr=3 r=0 w=0 time=154 us)
1 FILTER (cr=3 r=0 w=0 time=119 us)
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 time=114 us)
1 INDEX RANGE SCAN I_OBJ2 (cr=2 r=0 w=0 time=67 us)(object id 37)
0 TABLE ACCESS BY INDEX ROWID IND$ (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN I_IND1 (cr=0 r=0 w=0 time=0 us)(object id 39)
0 FILTER (cr=0 r=0 w=0 time=1 us)
0 TABLE ACCESS BY INDEX ROWID LINK$ (cr=0 r=0 w=0 time=0 us)
0 INDEX RANGE SCAN I_LINK1 (cr=0 r=0 w=0 time=0 us)(object id 113)

********************************************************************************

Select object_type
from
all_objects where owner = :OWNER and object_name =:Proc and object_type in
('PACKAGE', 'PROCEDURE', 'FUNCTION')


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 10 0 2
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 10 0 2

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 109

Rows Row Source Operation
------- ---------------------------------------------------
1 FILTER (cr=5 r=0 w=0 time=167 us)
1 NESTED LOOPS (cr=5 r=0 w=0 time=156 us)
1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 r=0 w=0 time=53 us)
1 INDEX UNIQUE SCAN I_USER1 (cr=1 r=0 w=0 time=26 us)(object id 44)
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 time=81 us)
1 INDEX RANGE SCAN I_OBJ2 (cr=2 r=0 w=0 time=45 us)(object id 37)
0 TABLE ACCESS BY INDEX ROWID IND$ (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN I_IND1 (cr=0 r=0 w=0 time=0 us)(object id 39)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)

********************************************************************************

Select *
from
all_arguments where object_id = ( select object_id from
all_objects where owner = :OWNER and object_name =:Proc
and object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION'))order by
Object_Name, Overload, Sequence


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 10 0 0
Fetch 2 0.00 0.00 0 20 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 30 0 4

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 109
********************************************************************************

select TEXT
from
SYS.USER_SOURCE where NAME=:name and TYPE=:type order by LINE


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 14 0 146
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 14 0 146

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 109

Rows Row Source Operation
------- ---------------------------------------------------
73 SORT ORDER BY (cr=7 r=0 w=0 time=1597 us)
73 VIEW (cr=7 r=0 w=0 time=1305 us)
73 UNION-ALL (cr=7 r=0 w=0 time=1208 us)
73 TABLE ACCESS BY INDEX ROWID SOURCE$ (cr=7 r=0 w=0 time=855 us)
75 NESTED LOOPS (cr=6 r=0 w=0 time=518 us)
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 time=108 us)
1 INDEX RANGE SCAN I_OBJ2 (cr=2 r=0 w=0 time=58 us)(object id 37)
73 INDEX RANGE SCAN I_SOURCE1 (cr=3 r=0 w=0 time=206 us)(object id 119)
0 FILTER (cr=0 r=0 w=0 time=1 us)
0 NESTED LOOPS (cr=0 r=0 w=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=0 r=0 w=0 time=0 us)
0 INDEX RANGE SCAN I_OBJ2 (cr=0 r=0 w=0 time=0 us)(object id 37)
0 FIXED TABLE FIXED INDEX X$JOXFS (ind:1) (cr=0 r=0 w=0 time=0 us)

********************************************************************************

select text
from
view$ where rowid=:1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 5 0.00 0.00 0 0 0 0
Fetch 5 0.00 0.00 0 12 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 12 0 5

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY USER ROWID OBJ#(62) (cr=1 r=0 w=0 time=50 us)

********************************************************************************

Select b.object_type, b.owner, b.object_name
FROM
all_objects a, public_dependency p, all_objects b WHERE a.owner = :owner
and a.object_name = :name and a.object_type = :type and a.object_id =
p.object_id and p.referenced_object_id = b.object_id and a.owner <>
'SYS' and a.owner <> 'SYSTEM' and b.owner <> 'SYS' and b.owner <>
'SYSTEM'


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 60 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.02 0.02 0 60 0 5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 109

Rows Row Source Operation
------- ---------------------------------------------------
5 FILTER (cr=60 r=0 w=0 time=1094 us)
8 NESTED LOOPS (cr=60 r=0 w=0 time=1064 us)
13 NESTED LOOPS (cr=37 r=0 w=0 time=712 us)
13 NESTED LOOPS (cr=9 r=0 w=0 time=379 us)
1 NESTED LOOPS (cr=5 r=0 w=0 time=190 us)
1 TABLE ACCESS BY INDEX ROWID USER$ (cr=2 r=0 w=0 time=67 us)
1 INDEX UNIQUE SCAN I_USER1 (cr=1 r=0 w=0 time=38 us)(object id 44)
1 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=3 r=0 w=0 time=95 us)
1 INDEX RANGE SCAN I_OBJ2 (cr=2 r=0 w=0 time=48 us)(object id 37)
13 TABLE ACCESS BY INDEX ROWID DEPENDENCY$ (cr=4 r=0 w=0 time=163 us)
13 INDEX RANGE SCAN I_DEPENDENCY1 (cr=3 r=0 w=0 time=87 us)(object id 127)
13 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=28 r=0 w=0 time=262 us)
13 INDEX UNIQUE SCAN I_OBJ1 (cr=15 r=0 w=0 time=130 us)(object id 36)
8 TABLE ACCESS CLUSTER USER$ (cr=23 r=0 w=0 time=281 us)
13 INDEX UNIQUE SCAN I_USER# (cr=2 r=0 w=0 time=53 us)(object id 11)
0 TABLE ACCESS BY INDEX ROWID IND$ (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN I_IND1 (cr=0 r=0 w=0 time=0 us)(object id 39)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID IND$ (cr=0 r=0 w=0 time=0 us)
0 INDEX UNIQUE SCAN I_IND1 (cr=0 r=0 w=0 time=0 us)(object id 39)
0 FIXED TABLE FULL X$KZSPR (cr=0 r=0 w=0 time=0 us)

********************************************************************************

DECLARE
V_TABLE_NAME VARCHAR2(200);
V_QUERY VARCHAR2(200);
BEGIN
V_TABLE_NAME := 'ME_STU_PSL_FY06_ALL';
V_QUERY := 'SELECT * FROM ME_STU_PSL_FY06_ALL';
SSC_CLC.RUN_PARSE_PROCEDURE6 ( V_TABLE_NAME, V_QUERY );
COMMIT;
END;

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 1 93.38 90.95 0 0 0 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 93.40 90.97 0 0 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 109
********************************************************************************

SELECT column_name
FROM user_tab_columns
WHERE table_name = :b1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.00 0 223 0 53
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 223 0 53

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 109 (recursive depth: 1)
********************************************************************************

SELECT *
FROM
ME_STU_PSL_FY06_ALL


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 391 0.05 0.04 0 397 0 390
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 393 0.05 0.04 0 397 0 390

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 109 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
390 TABLE ACCESS FULL ME_STU_PSL_FY06_ALL (cr=397 r=0 w=0 time=11708 us)

********************************************************************************

select *
from
t


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 390 0.01 0.01 0 0 0 0
Fetch 33930 1.41 1.43 0 35880 0 33540
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 34321 1.42 1.45 0 35880 0 33540

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 109 (recursive depth: 1)
********************************************************************************

begin if sys_context( 'my_ctx3', 'ADDINSTRORGID') IS NOT NULL AND
sys_context( 'my_ctx3', 'LASTGRADE') NOT IN ('09', '10' , '11', '12','SU',
'AW','AA', 'PG','AD') then :1 := 'Y'; end if; end;


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 390 0.08 0.07 0 0 0 0
Execute 390 0.12 0.13 0 0 0 390
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 780 0.20 0.21 0 0 0 390

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 109 (recursive depth: 1)
********************************************************************************

SELECT sys_context(:b2,:b1)
from
sys.dual


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 53608 2.67 2.36 0 0 0 0
Fetch 53608 4.66 4.71 0 160824 0 53608
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 107217 7.33 7.07 0 160824 0 53608

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 109 (recursive depth: 2)
********************************************************************************

begin if sys_context( 'my_ctx3', 'ADMINSTAT') ='02' AND sys_context(
'my_ctx3', 'LASTGRADE') NOT IN ('09', '10' ,'11','12','SU', 'AW', 'AA',
'PG', 'AD') then :1 := 'Y'; end if; end;

I have tried and I keep getting:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at "SSC_CLC.PKG_PARSE_PROCEDURE", line 21
ORA-06512: at "SSC_CLC.PKG_PARSE_PROCEDURE", line 136
ORA-06512: at line 11

Running PKG_PARSE_PROCEDURE.RUN_EDIT_CHECK

line 136==> pu_edit_check (l_names, l_values, p_rule_cur);


PKG_PARSE_PROCEDURE.PU_EDIT_CHECK

line 21==> DBMS_SESSION.set_context ('my_ctx', p_names (i), p_values (i));


THis is what I used to create the context:

create or replace context my_ctx using pkg_parse_procedure.pu_edit_check

thanks!

Hi,
Is there any way to make the table name and column name also dynamic? For example, I have v_sql = 'select col1 from tab1'
and i have two more parameters namely v_Table_name and V_Column_name.
To execute the following statement, I need to declare the v_variable. Since the table name and columns names are dynamic, I'm not able to define the into clause.

execute immediate v_sql into v_Variable
Thanks
Shankar Ram

The purpose of this procedure is to check the sql syntax which the user builds on the fly for one of my dynamic query screens.

CREATE OR REPLACE FUNCTION CHECK_SQL_SYNTAX(pin_sql_value IN VARCHAR2)
RETURN VARCHAR2 AS
V_VALUE VARCHAR2(2000);
v_errormessage VARCHAR2(2000);
BEGIN
FOR I IN 1..1 LOOP
execute immediate pin_sql_value into v_value;
IF v_value IS NOT NULL THEN
v_errormessage := 'SUCCESS';
END IF;
END LOOP;
RETURN(v_errormessage);
EXCEPTION
WHEN OTHERS THEN
v_errormessage := substr(sqlerrm,1,2000);
RETURN(v_errormessage);
END;

begin
-- Call the function
:result := check_sql_syntax(pin_sql_value => 'SELECT MAILSTATUS FROM EMAIL_CODE where rownum = 1');
end;

The above function returns ORA-01036 Error. So I thought this is because of the “into” clause and trying to resolve the problem.

Thanks
Shankar Ram

Yes. I understand the adverse effects of dynamic SQL. But the question is, is there any other way to check the syntax and the privilege of the user for a given SQL?
Thanks
Shankar Ram

Hmmmmm. Thanks for pointing out something which is very very important.
Yes, I do understand the serious side effectes of letting the user do something which they are not intended to do. I covered all that portion by giving them only the options which we provide from the application. They cannot construct their own sql.
Even though it looks like they can pick whenever they want, actually they will be forced to USE(mix and match) the combimations which we provide.
The SQL wont get executed if they give something else other than what we provide them.
And moreover all the values which they pass are binded. we don't use the contactination at all when we build these kind of ad-hoc queries.
But I don't want to hard code to check for the sql syntax which they build. For example they have option to choose open parenthesis and close parenthesis. If I want to check the syntax before they execute the sql, what am I supposed to do? Please let me know is there is any workaround with out using execute immediate to check for the sql syntax.

Thank you very much
Shankar Ram

I'm in a situation where I need to apply the following (having inherited some far from ideal code that needs modification).

I have a complex dynamically generated SQL statement stuctured in a similar fashion to this...

SELECT *
FROM
(
SELECT some_columns, a_col*func(:1) score
FROM...
WHERE ...
UNION
SELECT some_columns, a_col*func(:2)
FROM...
WHERE ...
UNION
SELECT some_columns, a_col*func(:3)
FROM...
)
WHERE func(:n) > 0
ORDER BY score


etc where the number of UNIONS is data dependant and returns multiple rows. I wish to execute an OPEN - LOOP - FETCH - END LOOP - CLOSE on the resultset.

In all these UNION'ed SELECTs the parameter is the same (a passed user defined TYPE). DBMS_SQL.BIND_VARIABLE does not appear to (as far as I can see) support this (user defined TYPEs). Is there any other approach I can take to avoid the

IF union_count = 0 THEN
OPEN v_curs FOR v_sql_stmt USING v_type;
ELSIF union_count = 1 THEN
OPEN v_curs FOR v_sql_stmt USING v_type, v_type;
ELSIF union_count = 2 THEN
OPEN v_curs FOR v_sql_stmt USING v_type, v_type, v_type;

...when I don't know how many UNIONs I will have.

I am currently on 8.1.7 but we plan to migrate to 10g by the end of the year but plans don't always come to reality ;-)

Type was collection of

CREATE OR REPLACE TYPE room_config_type_x as object(room_config_no NUMBER,
no_of_adults NUMBER,
no_of_children NUMBER,
no_of_infants NUMBER,
allocated CHAR(1));

CREATE OR REPLACE TYPE
room_config_tab_x IS table OF room_config_type_x;

I've adopted the approach of passing the information via a transaction based temporary table. Is this the best option? Are there any pitfalls to doing this?

Hello Sir,

I am trying to execute the following query dynamically from a package PCK_TOPUP however getting the error as shown below
INSERT INTO DDL02.po031@DBLINK_TOPUP_ON_LIFEDEV (UNIQUE_ID, MAINT, POLREF, STA, INRTYP, INRREF, BENTYP, BENREF, CSH_70, GTDCSH, CSH_65, ADJGTDCSH, CSH_60, ACMPRM, UNPIND, CRF_NO, NRA, ANNOPT, PP_VARPRM, PP_CSHINT, LTEIND, AUDSTF_NO, AUDAPLCDE, AUDUPD_ID, AUDUPDDTE, AUDUPDTME) (SELECT * FROM DDL01.po031 WHERE polref = sys_context('TOPUP_CTX','polref') AND (polref, inrtyp, inrref, bentyp, benref) NOT IN (SELECT polref, inrtyp, inrref, bentyp, benref FROM DDL02.po031@DBLINK_TOPUP_ON_LIFEDEV WHERE polref = sys_context('TOPUP_CTX','polref') ))

Error : Error message - ORA-02070: database does not support in this context

This package PCK_TOPUP is in a local schema and is not in DDL01 or DDL02. The context TOPUP_CTX is also defined in this local schema for package PCK_TOPUP. What could be the reason of getting this error ??

Regards
Kishore

Thanks for your response. If I define the context as globally accessible, will it work??

Regards
Kishore

Tom,
It would be great, if you can provide a 'Send URL via Email' link for each page. That would be very helpful.

Thanks for a great site!
Regards,
Rizwan

Hello Sir

How do the applications that load data from one schema to another, rather one schema (database 1) to another schema (database 2) work. I have an application that reads data from a source schema and loads data it into a target schema using a DBLINK. There is one procedure which does most of the inserts (around 90% (160 inserts into different tables)). This procedure has an INSERT INTO SELECT statement in it. Since for different tables we have different columns in the where clause of select statement, we create the where clause on the fly and need to use the bind variable. I tried to use sys_context as mentioned in my previous query above, however it doesn't work. Moreover for each policy that is loaded the code parses all the queries again that may have got executed before for some other policy between the same environments. Could you please suggest me a way of implementing this most efficiently by avoiding the redundant parses.

Also in the two cases mentioned below can we say that one of them will always work better than the other. If so which one ?

Case 1: Dynamic Sql statement that is always soft parsed
Case 2: Static Sql statement that is always hard parsed

Regards
Kishore

We have basically different environments ex DEV, SYS, UAT, PRE etc (each separate database). Within these we have schemas. Thus in order to get something from UAT schema to the SYS schema or DEV schema we have created an application called TOPUP. It will read an input policy and load all its relevant data from source to destination. The way you have mentioned will not work for our case since we have one procedure which call 180 times (total number of tables). Thus the table from which data is to be selected and the table where the data is to be loaded along with the where clause will change 180 times. The procedure is as shown below.
PROCEDURE p_insert_policy_details (pv_source_database_id IN policy_requests.src_db_id%TYPE
,pv_target_database_id IN policy_requests.db_id%TYPE
,pv_target_dblink_name IN dblink_controls.link_name%TYPE
,pv_table_name IN VARCHAR2
,pv_unique_keys IN VARCHAR2
,pv_where_condition IN VARCHAR2
)
IS

/**********************************************************************************************
Description: This procedure inserts the records into the tables of the target database
which are retrieved based on requested policy.
**********************************************************************************************/

lv_insert_string VARCHAR2(4000);

BEGIN

gv_table_name := pv_table_name;
lv_insert_string := 'INSERT INTO '
|| pv_target_database_id
|| '.'
|| pv_table_name
|| pv_target_dblink_name
|| ' ('
|| f_get_column_names (pv_owner_id => pv_source_database_id
,pv_table_name => pv_table_name)
|| ') (SELECT * FROM '
|| pv_source_database_id
|| '.'
|| pv_table_name
|| ' WHERE '
|| pv_where_condition
|| ' AND ('
|| pv_unique_keys
|| ') NOT IN (SELECT '
|| pv_unique_keys
|| ' FROM '
|| pv_target_database_id
|| '.'
|| pv_table_name
|| pv_target_dblink_name
|| ' WHERE '
|| pv_where_condition
|| ' ))';

EXECUTE IMMEDIATE lv_insert_string;

END p_insert_policy_details;

What is the best way to write such applications.

Really sorry for giving half information which has lead to going into loops. This is the last one.

Actually it is a daily process to upload policies from one environment to other and thus data refresh won't serve our purpose.

Also we need bind variables because if I have to upload 4 policies from DB1.schema1 to DB2.schema1 then there will be in total

180 (distinct tables) * 4 = 720 inserts
and thus 720 hard parses.

I wanted to limit the hard parses to 180. Thus if only the literals are changing but data is going to the same table the hard parses should not happen. Have mentioned sort of pseudo code below

for i in 1..total policies
loop
db_name = '';
table_name = '';
where_clause = '';
call insert_record(db_name, table_name, where_clause)
end loop;

insert_record(db_name, table_name, where_clause)
{
execute immediate
insert into DB1.schema_A.table_name select * from DB2.schema_B.table_name || where_clause;
}

Regards
Kishore Negi

you are simply great Tom

this helped to solve the problems

Ankit Vaid

Hello Tom,

My goal is to obtain a varray to collect the data from the varrays stored on a table, as efficiently as possible.

I created a type:
create or replace type "int_v" as varray(999999) of number(10,0);
and a table:
create table mytab ( id number,vcol int_v );

I inserted some data in my table:
insert into mytab (id, vcol) values (1, int_v(10,11,12,13,14,15,16,17,18,19));
insert into mytab (id, vcol) values (2, int_v(20,21,22,23,24,25,26,27,28,29));

And created a procedure:

create or replace procedure test ( vaID int_v )
is
type rec is table of int_v index by pls_integer;
vaux rec;
vfinal int_v;
saux string(1000);

begin

saux := 'select val from ( select 1 val from dual where 1 = 0';

for i in 1..vaID.Count
loop

select t.vcol
into vaux(i)
from mytab t
where t.id = vaID(i);

saux := saux || ' union all select p.column_value val from table(:v) p';

end loop;

saux := saux || ')';
execute immediate saux
bulk collect into vfinal
using vaux(1), vaux(2);

for i in vfinal.first..vfinal.last
loop dbms_output.put_line(vfinal(i));
end loop;

end test;

Later, I inserted new columns in my table:
insert into mytab (id, vcol) values (3, int_v(30,31,32,33,34,35,36,37,38,39));
insert into mytab (id, vcol) values (4, int_v(40,41,42,43,44,45,46,47,48,49));
insert into mytab (id, vcol) values (5, int_v(50,51,52,53,54,55,56,57,58,59));
insert into mytab (id, vcol) values (6, int_v(60,61,62,63,64,65,66,67,68,69));

I would like to adapt my procedure to handle this new situation. How may i do something like:
using vaux(1), vaux(2), .........
?

Best regards,
Isabel

The goal is to create a varray with all elements from several other varrays.
In the example given, there will be a final varray with 70 elements (10..69).
The goal is also to avoid a loop by position (extending and setting), since there will be up to 45.000 of values on each varray. The problem is that I don't know how many varrays there will be (also up to 45.000 - worst case).

Since we're working with Oracle 9i, there's no chance on using MULTISET UNION operator.

As so, I thought on creating a 'union all' routine for undetermined number of varrays...

Tom,

Having read your book on when to use DBMS_SQL and NDS (and REF_CURSORS)I have hit a bit of a dilemma.

I have a database table which holds SQL_STATEMENT, it has a child table which holds SQL_STATEMENT_BINDS which are part of a definition. The SQL statement generates an XMLTYPE using sys_xmlagg.

The eutopia is to execute the sql with the binds and return into an XMLTYPE.

Using 'EXECUTE IMMEDIATE' I can execute the SQL within a PL/SQL package and return into a local variable l_xml defined as xmltype. However the bind support for execute immediate isn't great.

Using DBMS_SQL, and looping for each record in SQL_STATEMENT_BINDS we can build up the SQL however, it appears that DBMS_SQL does not provide support for XMLTYPE.

I've searched metalink, and cannot find any issues relating to this.

Do you know of any workarounds?

I can use .getClobVal() for the XML - but it kind of defeats the object as we don't get implicit XML validation, and we need to manually add the method call to each piece of SQL in STATEMENT_SQL.

Hi Tom thanks for your feedback, perhaps "Not Great" was the wrong combination of words ;-)

Just meant that we don't have the advanced bind features available that we have in DBMS_SQL ie. in DBMS_SQL I can loop around my BIND table and apply the binds once by name. Using execute immediate I have to bind each occurence of the variable. ie. Execute Immediate 'SQL' Using bind1,bind2.... etc

We have gone down the CLOB output route using DBMS_SQL, it just means our developers have to remember to add getClobVal() to all the SQLXML output. Would be nice to have xmltype support out of DBMS_SQL.

Good to see the website back up!

In your reply to "Marc from Switzerland" on "Bind variables in exectue immediate" (for inserts), I found solution to the problem that I was facing. Thanks a lot !!..

One question though.. In the example, you have done a "g_cursor number := dbms_sql.open_cursor;".. but there is no dbms_sql.close_cursor in the code..
When does automatic closure happens ?

Thanks in Advance
Karthik

...
ops$tkyte@ORA920> create or replace package body dyn_insert
  2  as
  3
  4  g_last_stmt long;
  5  g_cursor    number := dbms_sql.open_cursor;
  6
  7  procedure p ( p_str in varchar2 )
....
 36      if ( l_stmt <> g_last_stmt or g_last_stmt is null )
 37      then
 38          p( l_stmt );
 39          g_last_stmt := l_stmt;
 40          dbms_sql.parse( g_cursor, g_last_stmt, dbms_sql.native );
 41      end if;
....

Hi Tom,

Thanks alot for your reply.

But in our current project we have almost 60 packages having code like this. And each time we add a fact column we have to go and changes these type of SQL's.

Generic approach for such situations are not recommended?

Is it because of performance impact or not a good practice ?

Thanks & regards
Rupali.

Hi Tom,

Thank you very much for your reply.

We have around 60 packages having code like mentioned above, and each month we have to add new fact column in more than 100 tables. And then go and change the complete code. Thats the reason we thought of a generalized approach where in we will keep a parameter table which will keep track of newly added fact columns. Existing packages will read this table and build dynamic query at run time and will get executed using "Execute immediate".

Are you saying this is not a good approach ? Is it because of performance impact or because of code complexity ?

Can you please give your valuable view on it?

Thanks & regards
Rupali

Hi Tom,

Thank you very much for your reply.

We have around 60 packages having code like mentioned above, and each month we have to add new fact column in more than 100 tables. And then go and change the complete code. Thats the reason we thought of a generalized approach where in we will keep a parameter table which will keep track of newly added fact columns. Existing packages will read this table and build dynamic query at run time and will get executed using "Execute immediate".

Are you saying this is not a good approach ? Is it because of performance impact or because of code complexity ?

Can you please give your valuable view on it?

Thanks & regards
Rupali

Hi Tom,

Sorry taking you back to question from "Marc from Switzerland" on Bind Variables.

What if instead of bind values from variables we are getting values from single record array.

For example if the code is like

Create or replace procedure Update_incentive(p_emprec emp%rowtype) AS
BEGIN
MERGE INTO Emp
USING dual
ON (
a.empid = p_emprec.empno
)
WHEN MATCHED THEN
UPDATE SET
a.sal = p_emprec.sal
WHEN NOT MATCHED THEN
INSERT
(empno, deptno, sal)
VALUES
(p_emprec.empno, p_emprec.deptno, p_emprec.sal);

END ;

The above procedure is called from another component which is populating values for columns of emp.

Suppose we want to make this as a dynamic procedure for handling any further column addition in EMP table like HRA, BONUS.

Is it possible to handle this requirement using the solution you gave to Mark ?

Believe me thanks its not a new question.

Thanks & Regards
Rupali

Hi Tom,

I am trying to use rank function inside a stored procedure like

Insert into table
select ...rank()...
from tabl2.

But its giving error while compiling.

I had to use execute immediate 'insert into select ...rank()..etc' andit works.

Is there any restrictions on rank function can't be used inside the procedure or something?

Thanks,
DK

please help me the the pdf file is not been open when the file attachment is more than 32 kb.

It would be very nice if Oracle makes it possible to bind one by one.

For instance:

create or replace procedure search_person( p_age in number
, p_name in varchar2)
l_sql varchar2(4000);
l_bindingcontext bindingcontext;
begin
l_sql := 'select * from persons where 1 = 1 ';

if p_age is not null then
l_sql := l_sql||' and age = :age ';
l_bindingcontext.add(':age',p_age);
end if;

if p_name is not null then
l_sql := l_sql||' and name = :name ';
l_bindingcontext.add(':name',p_name);
end if;

execute immediate l_sql using context l_bindingcontext;
end;
/


When I bind a SQL or PL/SQL statement in Delphi I can bind one by one, so why not in PL/SQL? In Delphi I have enormous flexibility to concat SQL or PL/SQL statements and still bind properly and easily. Why not in PL/SQL?

Using sys_context provides a solution but it is a difficult one.

We want to use dynamic sql in a procedure that returns a ref cursor so we have to use execute immediate instead of dbms_sql, haven't we?

I understand that Oracle 11 gives the possiblity to convert an dbms_sql-cursor into a ref cursor, doesn't it?

We know generate procedures for searching in a table (TAPI) or a view (VAPI).

The T is for Table, the V is for View.

We work with stored PL/SQL procs in our new product line build with C# so we can't concat SQL or PL/SQL statements like I used to in Delphi.

We use a method very similar to the method of Darko Egersdorfer from South Africa in your link http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279 .

We can also check for IS NOT NULL, IS NULL, >= , > , < , <= and BETWEEN.

We can also combine this search with collections:

if p_referentienummer.count > 0 then
l_base := l_base||', ( select/*+ cardinality (tab 10) */ distinct tab.nr from table(cast(:p_referentienummer as bvv_number_table)) tab) tbnrs ';
l_where := l_where||l_waar||' referentienr = tbnrs.nr';
else
l_where := l_where||l_waar||':p_referentienummer is not null';
end if;


We are happy with our generated TAPIs en VAPIs.

I will try if I can achieve the same result with dbms_sql in Oracle 11.

We are happy with our generated TAPIs en VAPIs. ->

We are happy with our generated TAPIs and VAPIs.

Thank you Tom, I made it work in Oracle 11:

example:


drop table emps;

prompt table scott.emp is used

create table emps as select empno,job,sal,deptno from emp;

drop type bvv_2number_table;
drop type bvv_2numbertype;

create or replace TYPE bvv_2numbertype
AS OBJECT
( nr number(20,10)
, nr2 number(20,10)
)
/

create or replace TYPE bvv_2number_table
AS TABLE OF "BVV_2NUMBERTYPE"
/


CREATE OR REPLACE
PROCEDURE EMP_SEARCH( cp_empno in emp.empno%type
, cp_job in emp.job%type
, cp_numbers in bvv_2number_table)
is
type t_emp is table of emps%rowtype;
l_emp t_emp;
l_sql varchar2(4000);
l_cursornr number := dbms_sql.open_cursor;
l_cursornr_exe number;
l_refcursor sys_refcursor;
begin
l_sql := 'select * from emps where 1=1 ';

if cp_empno is not null then
l_sql := l_sql || ' and empno = :empno ';
end if;
if cp_job is not null then
l_sql := l_sql || ' and job = :job ';
end if;
if cp_numbers is not null and cp_numbers.count > 0 then
l_sql := l_sql || ' and (sal,deptno) '||
' in (select nr,nr2 from table(cast(:numbers as bvv_2number_table)) ) ';
end if;

l_sql := l_sql||' order by 1 ';

dbms_output.put_line('SQL statement: '||l_sql);
dbms_output.put_line(chr(10));

dbms_sql.parse(l_cursornr,l_sql,dbms_sql.native);

if cp_empno is not null then
dbms_sql.bind_variable(l_cursornr,'empno',cp_empno);
end if;
if cp_job is not null then
dbms_sql.bind_variable(l_cursornr,'job',cp_job);
end if;
if cp_numbers is not null and cp_numbers.count > 0 then
dbms_sql.bind_variable(l_cursornr,'numbers',cp_numbers);
end if;

l_cursornr_exe := dbms_sql.execute(l_cursornr);

l_refcursor := dbms_sql.to_refcursor(l_cursornr);
fetch l_refcursor bulk collect into l_emp;
close l_refcursor;

if l_emp is not null and l_emp.count > 0 then
dbms_output.put_line('Selected rows from table emps: ');
dbms_output.put_line('------------------------------ ');
for i in l_emp.first..l_emp.last loop
dbms_output.put_line(l_emp(i).empno||' '||l_emp(i).job||' '||
to_char(l_emp(i).sal)||' '||to_char(l_emp(i).deptno));
end loop;
end if;

end;
/

We can test procedure emp_search:

declare
l_number_table bvv_2number_table := bvv_2number_table();
begin
emp_search(null,'CLERK',l_number_table);
dbms_output.put_line(chr(10));
l_number_table.extend;
l_number_table(l_number_table.last) := bvv_2numbertype(800,20);
emp_search(null,'CLERK',l_number_table);
end;
/

The output will be:

SQL statement: select * from emps where 1=1 and job = :job order by 1

Selected rows from table emps:
------------------------------
7369 CLERK 800 20
7876 CLERK 1100 20
7900 CLERK 950 30
7934 CLERK 1300 10

SQL statement: select * from emps where 1=1 and job = :job and (sal,deptno) in (select nr,nr2
from table(cast(:numbers as bvv_2number_table)) ) order by 1

Selected rows from table emps:
------------------------------
7369 CLERK 800 20

It's great that you can bind a collection!

Hi Tom,

Here is something which I want to achieve :

FOR j IN 4 .. (l_col_cnt-3)
LOOP
l_col_name := l_desc_tab(j).col_name;
dbms_sql.column_value( l_cursor_handle, j,l_column_value );
l_var:= 'sel_orders_valid_rec.:l_col_name_var := :l_column_value_var' ;

execute immediate l_var using l_col_name,l_column_value ;

END LOOP;
Asssume that the query is parsed and the Columns are also defined using DBMS_SQL.DEFINE_COLUMNS proecdure.

sel_orders_valid_rec is a record variable which is TABLE%ROWTYPE and I want to assign the value l_column_value dynamically in the COLUMN of the record variable the column name of which is obtained in l_col_name variable.

Is it possible through Execute immediate?...I get error when I try to run the above code in my procedure.

Am I doing something wrong here?

Could you please help me on this?

Thanks in advance.
Ram

Hello Mr. Tom,
Straight to the Point
create table COUNTER
(
X NUMBER,
Y VARCHAR2(10)
)
INsert into counter values (1,'a');
INsert into counter values (100,'b');
INsert into counter values (100,'d');
commit;
--------------PL/SQl Block Start------
-- Created on 1/18/2008 by 142037
declare
-- Local variables here
i integer(2) :=0;
x varchar2(500):='select count(0) from counter';
y varchar2(500):= ' where y in (:test)';
test varchar2(50) := '''a''';
full_query varchar2(500);
begin
-- Test statements here
full_query := x||y;
dbms_output.put_line(full_query);
execute immediate full_query
into i
using test;
dbms_output.put_line(test);
dbms_output.put_line(i);
end;
------------PL/SQL Block End------------
The value that i expect in variable i is 1 whereas what I get is 0.
Also in execute immediate can we specify IN condition as in can the final query be
select anything from something where column_value in ('X','Y') and stuff.

Thanks,
Bhushan

Hi Tom,
I got the partial answer for the above query.
However the IN condition still does not seem to work, here is the slightly modified PL/SQL Block.
The count i get it zero however i would expect it to return 2.

-----------------pl/SQL Block Start
-- Created on 1/18/2008 by 142037
declare
-- Local variables here
i number(2) :=0;
test varchar2(50) := 'a'||','||'b';
x varchar2(500):='select count(0) from counter';
y varchar2(500):= ' where y in (:ex)';

full_query varchar2(500);
begin
-- Test statements here
full_query := x||y;
dbms_output.put_line(full_query);
execute immediate full_query
into i
using test;
dbms_output.put_line(test);
dbms_output.put_line(i);
end;

------------pl/sql Block End

That doesn't work!

Read the link that Tom has given carefully. Your 'using test' binds a varchar2 not a list of varchar2s.

Tom,

Is it possible to use dynamic database link in a procedure? For e.g. in the below procedure I do not want to hard code the database link name. Can I fetch it from some setup table and use it below?

Thanks in advance.

/****************************************/
create procedure p_load_data is
cursor c_src_tab is
select * from src_tab@srcdblink;
begin
for crec in c_src_tab loop
insert into tgt_tab <.....>;
end loop;
end;
/

How? Do I have to make the for loop dynamic or define the cursor in the declare section dynamic? I tried various ways in the declare section where the cursor is declared but no luck.

Thanks.

I'm sure I'm missing something completely obvious, but I can't quite get around it.

csopera10g:opera\ops$csopera10g\administrator> create or replace procedure check
_valid( p_str in varchar2 )
  2  as
  3      odds     number;
  4      codds    number;
  5      evens    number;
  6      l_exec   long := 'select substr( l_str, length(l_str), 1 )';
  7      len      number;
  8      l_cursor sys_refcursor;
  9      l_output varchar2(4000);
 10      l_str    long := p_str;
 11  begin
 12      select length(l_str) into len from dual;
 13      codds := case when mod(len,2) = 1 then trunc(len/2)+1
 14                    when mod(len,2) = 0 then len/2
 15              end;
 16      for i in 1..codds-1 loop
 17          l_exec := l_exec || ' || substr( l_str, length(l_str)-' || i*2 || '
, 1 )';
 18      end loop;
 19      l_exec := l_exec || ' from dual';
 20      dbms_output.put_line(l_exec);
 21      open l_cursor for l_exec;
 22      fetch l_cursor into l_output;
 23          if ( l_cursor%found )
 24      then
 25          dbms_output.put_line( l_output );
 26      end if;
 27      close l_cursor;
 28      odds := 2*l_output;
 29  end;
 30  /

Procedure created.

csopera10g:opera\ops$csopera10g\administrator> exec check_valid(12345)
select substr( l_str, length(l_str), 1 ) || substr( l_str, length(l_str)-2, 1 )
|| substr( l_str, length(l_str)-4, 1 ) from dual
BEGIN check_valid(12345); END;

*
ERROR at line 1:
ORA-00904: "L_STR": invalid identifier
ORA-06512: at "OPS$CSOPERA10G\ADMINISTRATOR.CHECK_VALID", line 21
ORA-06512: at line 1

The idea is to check the validity of the "number" (which may or may not be a number) by, among, other things, taking the odd-number-positioned digits in reserve order, doubling them, & then comparing to the check digit. Other checks for length & so on have been stripped so I can list the shortest possible code.

What are my eyes to tired to see after 8 hours of staring at the screen?

 12      select length(l_str) into len from dual;

len := length(l_str);

l_len := length(p_str);

 10      l_str    long := p_str;

ops$tkyte%ORA10GR2> create or replace procedure check_valid( p_str in varchar2 )
  2  as
  3       l_output varchar2(4000);
  4       l_idx    number := 1;
  5  begin
  6      for i in 1 .. ceil(length(p_str)/2)
  7      loop
  8          l_output := substr(p_str,l_idx,1) || l_output ;
  9          l_idx := l_idx + 2;
 10      end loop;
 11      dbms_output.put_line( 'input = ' || p_str || ', output = ' || l_output );
 12  end;
 13  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec check_valid( '12345' );
input = 12345, output = 531

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec check_valid( '123456' );
input = 123456, output = 531

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec check_valid( '1234567' );
input = 1234567, output = 7531

PL/SQL procedure successfully completed.

Hello Tom,
Please help calrify some doubts and confirm if my understanding is correct.
1) Dynamic Sql is preferred in a Package/Procedure whenever one uses a Select Statement and the value of the parameter in the where clause keeps changing. FOr eg: we have a
for x in (select a,b,c from temp_table) loop
select d,e,f into m,n,o from main_table where a=x.a and b=x.b and c=x.c
-- do some processing
end loop;
so in the above statement instead of writing the select it is preferred
execute immediate 'select d,e,f from main_table where a=:a and b=:b and c=:c' into m,n,o using x.a,x.b,x.c;
Right?
2) Is there a way of parsing the bind variables in the Procedural language plainly using the select ONLY i.e no execute immediate no dbms_sql?
Also, happened to go through this page, do you have any comments on this?
http://www.adp-gmbh.ch/ora/bad_dynamic_sql.html

Cheers!!!
Bhushan

@Bushan

My Oracle knowledge is dwarfed by Tom's Oracle knowledge but I think I can answer this one.

I strongly believe that your understanding is wrong.

You should use execute immediate of dbms_sql only in exceptional cases.

Static sql is faster than execute immediate or dbms_sql. Static sql is always properly binded and you have compile time validation of your code!

One can use dbms_sql.to_refcursor to convert a dbms_sql-cursor into a ref cursor (Oracle 11). It works fine in PL/SQL.

But I encounter error ora-01001 when I want to use this ref cursor in my C# code, normally I can use ref cursors in my C# code.

I have read somewhere that I can't use converted ref cursors outside PL/SQL, or can I?

If I can't I hope this will change.

Yes i agree static SQL is faster than Dynamic SQL, however when the value of the parameters passed is changing how do we bind the variables in a Static SQL..like we do in Dynaming using the 'Using Variable_name'.

@Bhushan

If you use static SQL you always bind the variables properly. Please stop worrying!!

It doesn't matter if you change the value of the parameters.

Your code:

for x in (select a,b,c from temp_table) loop
select d,e,f
into m,n,o
from main_table
where a=x.a
and b=x.b
and c=x.c;
-- do some processing
end loop;

Is fine and fast code! Using dbms_sql or execute immediate will make it slower.

Read here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7832114438832

for x in (select t.a,t.b,t.c, m.d, m.e, m.f 
            from temp_table t, main_table m
           where t.a = m.a
             and t.b = m.b
             and t.c = m.c )   /* using outer join if and only if necessary! */
loop
  -- do some processing
end loop;

@RC
Thanks a Lot!!!

Cheers!!!
Bhushan