RE: Literal SQL and sys.dual

From: Mohammed Shakir <mshakir08816_at_yahoo.com>
Date: Tue, 02 Apr 2002 19:18:19 -0800
Message-ID: <F001.0043971F.20020402191819@fatcity.com>

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" <Rajendra.Jamadagni_at_espn.com> 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: mshakir08816_at_yahoo.com

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: ListGuru_at_fatcity.com (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).

Received on Tue Apr 02 2002 - 21:18:19 CST