As usual I have inherited the code. The code is not in front of me.
Here is the psuedo code.
sql_string := '1 + 1'
sql_string := 'select ' || sql_string || ' from dual;'
Above string is passed as a parameter to a function that executes
a native dynamic sql:
begin
execute immediate sql_string;
end;
Oracle executes the above query in two steps. First step is the native
dynamic sql and second part itself, and passes SQL to Oracle as:
select 1 + 1 from dual;
And the result is 2. As complete text statement is processed.
Now comes 1 + 2 and the SQL is parsed again and that is non-shared SQL.
The problem is how in this example Oracle sees 1 + 1 as numbers. If I
use bind variable to pass 1 + 1, they are taken as text.
I agree that Programmer did not have to use sys.dual. He has a table
that indicates which record is a value and which record is a token
If token is a + he can add the value. If a token is - (minus) he can
subtract the value when he is getting the data from the table. He did
not have to create the string and pass it to dynamic sql or sys.dual to
process it.
The programmer did not realize that he is executing this and other
statements over a million times and he will be filling up shared pool
with 11K unique (sys.dual) statements. There are 14000 statements (not
sys.dual) use literal sql filling up the shared pool. I think they are
easier to fix as they are part of either value or where clause.
I am not sure if your approach will work as I am doing the same.
Here is my test program which I believe should look similar to yours. I
have a simpler version but it is not with me right now. The results of
the programs are 0 and 1 + 1. whereas I should be getting 0 and 2. The
problem is 1 + 1 is used as string rather than numbers.
declare
l_varchar2 varchar2(50);
l_varchar_in varchar2(50);
begin
begin
l_varchar_in := '0';
execute immediate
'begin
select :l_varchar_str into :l_varchar2
from dual;
end;'
using l_varchar_in, OUT l_varchar2;
dbms_output.put_line(l_varchar2);
end;
begin
l_varchar_in := '1 + 1'; -- I will be getting them as string
execute immediate
'begin
select :l_varchar_str into :l_varchar2
from dual;
end;'
using l_varchar_in, OUT l_varchar2;
dbms_output.put_line(l_varchar2);
end;
end;
--- "Jamadagni, Rajendra" <[EMAIL PROTECTED]> wrote:
> One way to calculate "select (1+1) from dual;" is "myvar := 1+1;".
> Trust me,
> it is more efficient. Most arithmetic functions can be used in
> pl/sql,
> without having to use dual.
>
> Question for you, why do you think you need to go to database, when
> you are
> NOT accessing anything from the database?
>
> Raj
> ______________________________________________________
> Rajendra Jamadagni MIS, ESPN Inc.
> Rajendra dot Jamadagni at ESPN dot com
> Any opinion expressed here is personal and doesn't reflect that of
> ESPN Inc.
>
> QOTD: Any clod can have facts, but having an opinion is an art!
> >
*******************************************************************************1
>
> This e-mail message is confidential, intended only for the named
> recipient(s) above and may contain information that is privileged,
> attorney work product or exempt from disclosure under applicable law.
> If you have received this message in error, or are not the named
> recipient(s), please immediately notify ESPN at (860) 766-2000 and
> delete this e-mail message from your computer, Thank you.
>
>
*******************************************************************************1
>
__________________________________________________
Do You Yahoo!?
Yahoo! Tax Center - online filing with TurboTax
http://taxes.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammed Shakir
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
