extend
A reader, February 18, 2004 - 3:33 am UTC
can I extend this to evaluate the following string and return true or false as if it was part of an IF statement.....
'1 IN (1,2,3) AND 1 > -2'
so if I evaluated the above string as an expression I would get a true.
February 18, 2004 - 8:41 pm UTC
sort of.
I used SQL which does not permit booleans (not part of sql, only part of plsql).
what you would do is
'select count(*) from dual where ' || your_where_clause;
and if that returns 1, "true", if it returns 0, "false" -- write a procedure that parses that, binds the output and returns:
return (l_output = 1);
A reader, February 19, 2004 - 4:25 am UTC
Can you possibly show me an example where I can parse and bind the sql ... the string (where_clause) could vary , but always return true/false - for instance it may be '1 IN (1,2,3) AND 1 > -2' or could be '1 > -2'.
i changed your procedure above to do the following to take care of the '1>1' string...
CREATE OR REPLACE package body eval
as
g_theCursor integer default dbms_sql.open_cursor;
function do_it
( p_function in varchar2, p_var in number )
return number
is
l_rowsprocessed number default 0;
l_retval number;
begin
dbms_sql.parse(g_theCursor,
'begin if ' ||
replace(p_function,'in_variable',':in_variable') ||
' then :ret_val := 1; end if; end;',
dbms_sql.native );
dbms_sql.bind_variable
(g_theCursor,':ret_val',l_retval );
begin
dbms_sql.bind_variable
( g_theCursor, ':in_variable', p_var );
exception
when others then
if ( sqlcode = -1006 ) then
null;
else
RAISE;
end if;
end;
l_rowsprocessed := dbms_sql.execute(g_theCursor);
dbms_sql.variable_value
( g_theCursor, ':ret_val', l_retval );
return l_retval;
end do_it;
end eval;
good or bad ?
thanks
February 19, 2004 - 11:05 am UTC
this is what I meant:
ops$tkyte@ORA920PC> create or replace function do_it( p_function in varchar2 ) return boolean
2 is
3 l_cnt number;
4 begin
5 execute immediate
6 'select count(*) from dual where ' || p_function
7 into l_cnt;
8
9 return (l_cnt = 1);
10 end;
11 /
Function created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> begin
2 if ( do_it( '1 IN (1,2,3) AND 1 > -2' ) )
3 then
4 dbms_output.put_line( '1 IN (1,2,3) AND 1 > -2 is TRUE' );
5 else
6 dbms_output.put_line( '1 IN (1,2,3) AND 1 > -2 is FALSE' );
7 end if;
8 if ( do_it( '1 < 0' ) )
9 then
10 dbms_output.put_line( '1 < 0 is TRUE' );
11 else
12 dbms_output.put_line( '1 < 0 is FALSE' );
13 end if;
14 end;
15 /
1 IN (1,2,3) AND 1 > -2 is TRUE
1 < 0 is FALSE
PL/SQL procedure successfully completed.
binding
A reader, February 20, 2004 - 5:00 am UTC
I guess this would be far more efficient if it was binding the values ? Would that be possible considering not knowing how many values in the IN ... it probably is possible with dynamic sql, however if I am calling this function many,many times with a different string, what is my best option?
x=100
for i in 1..100 loop
x := x +1;
p_utils.eval2( i || '>0 AND ' || x || ' in (100,104,110)' );
end loop;
the execute immediate in your example above, would this be a soft parse ?
sorry if I am asking too much .. just in a dilema about how to handle a sticky bit of the system that suprisingly "must perform" !
February 20, 2004 - 9:48 am UTC
two approaches to making this "bind variable friendly"
o application contexts
o cursor_sharing
ops$tkyte@ORA920PC> create or replace function do_it( p_function in varchar2 ) return boolean
2 is
3 l_cnt number;
4 begin
5 execute immediate
6 'select /* LOOK FOR ME */ count(*) from dual where ' || p_function
7 into l_cnt;
8
9 return (l_cnt = 1);
10 end;
11 /
Function created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace context my_ctx using my_proc;
Context created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> create or replace procedure my_proc( p_name in varchar2, p_val in varchar2 )
2 as
3 begin
4 dbms_session.set_context( 'my_ctx', p_name, p_val );
5 end;
6 /
Procedure created.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> declare
2 x number := 100;
3 begin
4 for i in 1 .. 100
5 loop
6 x := x+1;
7 my_proc( 'i', i );
8 my_proc( 'x', x );
9
10 if (do_it( 'sys_context(''my_ctx'',''i'') > 0
11 and
12 sys_context(''my_ctx'',''x'') in (100,104,110)'))
13 then
14 dbms_output.put_line( 'i = ' || i || ' x = ' || x || ' are TRUE' );
15 end if;
16 end loop;
17 end;
18 /
i = 4 x = 104 are TRUE
i = 10 x = 110 are TRUE
PL/SQL procedure successfully completed.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> declare
2 x number := 100;
3 begin
4 execute immediate 'alter session set cursor_sharing=force';
5 for i in 1 .. 100
6 loop
7 x := x+1;
8 if (do_it( i || ' > 0 and ' || x || ' in (100,104,110)'))
9 then
10 dbms_output.put_line( 'i = ' || i || ' x = ' || x || ' are TRUE' );
11 end if;
12 end loop;
13 execute immediate 'alter session set cursor_sharing=exact';
14 end;
15 /
i = 4 x = 104 are TRUE
i = 10 x = 110 are TRUE
PL/SQL procedure successfully completed.
ops$tkyte@ORA920PC>
ops$tkyte@ORA920PC> select sql_text from v$sql where upper(sql_text) like 'SELECT /* LOOK FOR ME */%';
SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
select /* LOOK FOR ME */ count(*) from dual where sys_context('my_ctx','i') > 0 and sys_con
text('my_ctx','x') in (100,104,110)
select /* LOOK FOR ME */ count(*) from dual where :"SYS_B_0" > :"SYS_B_1" and :"SYS_B_2" in (:"SYS_B_3",:"SYS_B_4",:"SYS_B_5")
<b>very relevant question, don't be afraid you are asking too much -- bind variables is something I don't hesitate to talk about :)</b>
But -- on another note -- if the system "must perform" you might consider "static CODE" over dynamic code.
That is, instead of storing strings to be parsed/executed dynamically -- store strings that you run a "code generator against" that creates static procedures that are compiled once. You would regenerate the code after updating the strings. works well if you update strings upon "install" or "infrequent configuration of the system". will work lots better and more performant than doing it on the fly.
binding cont ..
A reader, February 20, 2004 - 5:03 am UTC
appologies, my p_utils.eval2 above is the same as your do_it function
the above techniques
A reader, April 02, 2004 - 3:01 am UTC
the following code
declare
v1 varchar2(500);
begin
select high_value into v1 from user_tab_partitions where table_name = 'PTAB' and rownum = 1;
dbms_output.put_line(v1);
end;
returns me
TO_DATE(' 2004-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
how can I have the above returned as
April 2004
thanks
April 02, 2004 - 10:07 am UTC
declare
v1 varchar2(500);
l_date date;
begin
select high_value into v1 from user_tab_partitions where table_name =
'PTAB' and rownum = 1;
execute immediate 'select ' || v1 || ' from dual' into l_date;
dbms_output.put_line( l_date, 'Month YYYY' );
end;
A reader, April 02, 2004 - 3:25 am UTC
no need to answer the above, I have managed to sort out.
a simple execute immidiate will do.
just using this site gives inspiration !
what about sql injection ?
A reader, August 14, 2007 - 5:12 pm UTC
eval.doit seems to me a hot candidate for sql injection
a possible offender could try to use it to run code he is not granted to
- is this true ?
- if so, can you protect it ?
August 20, 2007 - 12:50 pm UTC
Sure it is true, that is obvious (hopefully). By its very design this is true.
to protect against it, do not do things dynamically at run time. Here, the requirement was to evaluate a string - short of building your own SQL parser and validating all of the inputs conform to whatever rules you designate - in which case you might just as well writing your own evaluation routine as well (if you can parse it, you can compute it)
thank you very much!!!!
A reader, May 17, 2012 - 4:23 am UTC
May 18, 2012 - 2:09 am UTC
great, that leaves only about 43-49 countries to choose from depending on how you count the transcontinental countries :)
Regarding SQL injection
Michael, May 18, 2012 - 4:38 am UTC
Hi Tom,
regarding the SQL injection problem:
Wouldn't it help to create a special db user that doesn't have any privileges and that only owns the package eval?
(The package would, of course, be defined with definer rights.)
Then we grant execute on this package to the real schemas that need to evaluate expressions.
(Of course, PUBLIC privilege might be a problem...)
May 21, 2012 - 7:49 am UTC
The package should be created with AUTHID CURRENT_USER.
In that fashion - it won't be "sql injectable" since it runs with the privileges of the invoker.
But - that assumes the invoker cannot be tricked into running it unintentionally!! (meaning, this package would not be sql injectable, however if the invokers code is sql injectable...... this could be used to fun arbitrary code)
"about 43-49 countries..."
Sokrates, May 18, 2012 - 6:52 am UTC
..., that leaves only about 43-49 countries to choose from depending on how you count the transcontinental countries...I don't get that.
You have to count each transcontinental country, a part of which belongs to Europe, because "A reader from Europe but not Germany and France" could come from that country, no disambiguation here.
So, according to
http://en.wikipedia.org/wiki/List_of_sovereign_states_and_dependent_territories_in_Europe ,
that leaves exactly
50 - 1 /* Germany */ - 1 /* France */
= 48
countries to choose
May 21, 2012 - 7:58 am UTC
It is like I said, depending on how you count the transcontinental countries which some say "belong to europe", some say "belong to asia" and some say "belong to both"
Based on that division, Azerbaijan, Georgia, Kazakhstan, Russia and Turkey have territory both in Europe and Asia.
Many people would not consider those countries to be "in europe", it depends.
will this create context switch?
Mani, January 22, 2014 - 2:06 pm UTC
Hi Tom,
can the below dynamic sql will create context switch between sql and plsql?
Declare
v_txt varchar2(100) := '3 not in (1,2)';
v_val integer := 0;
v_cur varchar2(4000);
begin
v_cur := 'Begin if '||v_txt||' then :v_return := 1; else :v_return := -1; end if; end;';
execute immediate v_cur using Out v_val;
dbms_output.put_line(v_val);
end;
/
Eveluate somethink like cursor alias.table field
André Luiz Boni, August 28, 2019 - 8:15 pm UTC
Hi, Tom
I have this situation for a execute immediate statement:
'select '||rl.field_name||' from '||l_entity||' where '||l_primary||' = '||l_keyvalue;
l_keyvalue is like the value of rl.invoice_id cursor field and I want to pass it to execute immediate.
I tried your code from this post nut not work fine. Do you have any suggestion, please ?
Regards,
André Boni
August 29, 2019 - 10:27 am UTC
What does "not work fine" mean exactly? Precisely what is failing, with what errors, and how can we reproduce this issue?
performance
A reader, January 27, 2021 - 11:09 am UTC
It looks like:
EXECUTE IMMEDIATE 'SELECT '||x||' FROM DUAL' INTO v;
is about twice faster than:
EXECUTE IMMEDIATE 'BEGIN :1 := '||x||'; END;' USING OUT v;
January 27, 2021 - 11:25 am UTC
Care to share your test case proving this?
performance
mik, January 27, 2021 - 6:29 pm UTC
Just wrapped the line into this loop:
declare v number;
begin
for x in 1..10000 loop
EXECUTE IMMEDIATE ...;
end loop;
end;After flushing shared_pool the 'select' took 3.8s, and 'begin' - 6.3s.
January 28, 2021 - 9:46 am UTC
Pretty small for me
SQL> set timing on
SQL> declare
2 v number;
3 begin
4 for x in 1..100000 loop
5 EXECUTE IMMEDIATE 'begin :1 := 123; end;' using out v;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.06
SQL>
SQL> declare
2 v number;
3 begin
4 for x in 1..100000 loop
5 EXECUTE IMMEDIATE 'select 123 from dual' into v;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.94
performance
mik, January 28, 2021 - 4:19 pm UTC
Connor, because you run the same statement in the loop, I run different ones (with x concatenated inside).
January 28, 2021 - 5:53 pm UTC
So show us your complete test case!
performance
mik, February 01, 2021 - 3:43 pm UTC
alter system flush shared_pool
/
declare v number;
begin
for x in 1..10000 loop
EXECUTE IMMEDIATE 'SELECT '||x||' FROM DUAL' INTO v;
end loop;
end;
/
alter system flush shared_pool
/
declare v number;
begin
for x in 1..10000 loop
EXECUTE IMMEDIATE 'BEGIN :1 := '||x||'; END;' USING OUT v;
end loop;
end;
/