RE: Literal SQL and sys.dual

From: Mohammed Shakir <mshakir08816_at_yahoo.com>
Date: Wed, 03 Apr 2002 07:08:33 -0800
Message-ID: <F001.00439E90.20020403070833@fatcity.com>

Thanks for the help.

You are right on the money. However, I am getting the data as strings and I do not know what I will get. I get hundreds of thousands of them and I have no idea what they would look like.

I might get any string. couple of examples:

select 1 + 1 + 1 + 1 from dual
select 1 + (2 - 3)/4 + 5 / 4 + (6 + 7) / ( 5 + 5 + 0)

So my requirement is different. When I use bind variables, either I use a bind variable for each literal and it will work, or use one bind variable for all literals and then all I will get back is one of the above string in the result set.

The best answer is tht programmer should calculate the data as it comes in based on the token he gets and when he/she parses the data coming from the table. This might take place eventually. Right now I am trying to reduce huge library cache and shared pool contentions by elimating literal non-shared SQL.

I come from C/C++/Pro*C development environment and I can not imagine ever using sys.dual to do arithmatic.

Shakir

• "Jamadagni, Rajendra" <Rajendra.Jamadagni_at_espn.com> wrote:
  > The error is in your code, you are explicitly telling oracle that the
  > bind
  > variables are varchar2, so why would Oracle evaluate it?
  >
  > Try this ...
  >
  > DECLARE
  > nnum1 NUMBER;
  > nnum2 NUMBER;
  > nresult NUMBER;
  > BEGIN
  > BEGIN
  > nnum1 := 0;
  > EXECUTE IMMEDIATE
  > USING nnum1, OUT nresult;
  > dbms_output.put_line(TO_CHAR(nresult));
  > END;
  > BEGIN
  > nnum1 := 1;
  > nnum2 := 1;
  > EXECUTE IMMEDIATE
  > 'begin SELECT :1 + :2 INTO :0 FROM dual; END;'
  > USING nnum1, nnum2, OUT nresult;
  > dbms_output.put_line(TO_CHAR(nresult));
  > END;
  > END;
  > /
  >
  >
  > 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!
  > >
  > *********************************************************************2
  >
  > 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 corporate MIS at (860)
  > 766-2000 and delete this e-mail message from your computer, Thank
  > you.
  >
  > *********************************************************************2
  >
  >
  

  ---

Mohammed Shakir
CompuSoft, Inc.
11 Heather Way
East Brunswick, NJ 08816-2825
(732) 672-0464 (Cell)
(732) 257-6001 (Home)

---

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 Wed Apr 03 2002 - 09:08:33 CST