RE: Literal SQL and sys.dual

This is the code you submitted later yesterday and the error I am getting during compile is shown below. I am wondering how you got it to work. I tried it this on 8i/Solaris and 9i/NT. SQL CREATE OR REPLACE PACKAGE BODY My_Dynamic_Stuff IS 2 -- 3 PROCEDURE eval_this (pi_string IN VARCHAR2)

RE: Literal SQL and sys.dual

For the 3rd time I am sending this code, Mohammad, it wouldn't be a bad idea to try to resolve the code yourself. CREATE OR REPLACE PACKAGE BODY My_Dynamic_Stuff IS v_result NUMBER; -- PROCEDURE eval_this (pi_string IN VARCHAR2) IS BEGIN RTRIM(pi_string,';') || '; end;'; END

Re: Literal SQL and sys.dual

Raj, and for the 3rd time the version you post, wouldn't compile. Look below at what you posted. Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, April 04, 2002 11:54 AM For the 3rd time I am

RE: Literal SQL and sys.dual

12:25 PM To: Jamadagni, Rajendra Subject: RE: Literal SQL and sys.dual Thanks for your help and sending the code three times. I know it can be a bother. However, Can you just tell me in a yes/no, if this is the code you are sending me? The code I have received it is shown below

RE: Literal SQL and sys.dual

an opinion is an art! -Original Message- From: Mohammed Shakir [mailto:[EMAIL PROTECTED]] Sent: Thursday, April 04, 2002 12:25 PM To: Jamadagni, Rajendra Subject: RE: Literal SQL and sys.dual Thanks for your help and sending the code three times. I know it can

RE: Literal SQL and sys.dual

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,

RE: Literal SQL and sys.dual

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 +

RE: Literal SQL and sys.dual

TRy something like this ... CREATE OR REPLACE PACKAGE My_Dynamic_Stuff IS v_result NUMBER; -- PROCEDURE eval_this (pi_string IN VARCHAR2); END My_Dynamic_Stuff; / CREATE OR REPLACE PACKAGE BODY My_Dynamic_Stuff IS -- PROCEDURE eval_this (pi_string IN VARCHAR2) IS BEGIN

RE: Literal SQL and sys.dual

Thanks a million, I am going to try it and let you know how it works out. I can see the result I am looking for. Shakir --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: TRy something like this ... CREATE OR REPLACE PACKAGE My_Dynamic_Stuff IS v_result NUMBER; -- PROCEDURE

RE: Literal SQL and sys.dual

Hmmm there is a small error The package body should be as follows CREATE OR REPLACE PACKAGE BODY My_Dynamic_Stuff IS -- PROCEDURE eval_this (pi_string IN VARCHAR2) IS BEGIN RTRIM(pi_string,';') || '; end;'; END eval_this; -- END My_Dynamic_Stuff; / this of course assumes that you are

RE: Literal SQL and sys.dual

This code looks the same as what you submitted earlier. The package body does not compile either. I am not sure what you are trying to do with the RTRIM function. I see that you will get for the first example 1 + 1 + 1 +1; end; but then what? I have tried to compile the package body on

Re: Literal SQL and sys.dual

Mohammed Shakir wrote: I am working on a database optimization project. My Shared pool is filled with SQL like select 0 from dual; select 1/1 from dual; select 1 - 1/(2 + 2) from dual; I tried to use the bind variables to minimize the literal SQL. However I need a different SQL

RE: Literal SQL and sys.dual

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

Re: Literal SQL and sys.dual

Why don't you do: variable1 := 1+ 1; instead of select (1+1) into variable1 from dual? Igor Neyman, OCP DBA [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 02, 2002 2:03 PM I am working on a database

RE: Literal SQL and sys.dual

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

Re: Literal SQL and sys.dual

The programmer does not know what he has to calculate ahead of time. The formulas are stored in the table. He takes the data out from the table with the formula creates the string of data and operator. Then adds: create string: sql_string := '1 '; sql_string := sql_string || '+ '; sql_string :=

Re: Literal SQL and sys.dual

You are right. There are at least twelve modules that I have identified so far. Some are easy and others are like speghatti. Learning the code, fixing and testing will require couple of months. I have to leave this project with in a month and I have SQL code that needs optimization. I have a huge