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) IS 4 BEGIN 5 RTRIM(pi_string,';') || '; end;'; 6 END eval_this; 7 -- 8 END My_Dynamic_Stuff; 9 / Warning: Package Body created with compilation errors. SQL show errors Errors for PACKAGE BODY MY_DYNAMIC_STUFF: LINE/COL ERROR - 5/22 PLS-00103: Encountered the symbol | when expecting one of the following: := . ( % ; The symbol := was inserted before | to continue. SQL --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: There was another email I sent to list, check that out, that has the right code. Raj __ Rajendra JamadagniMIS, 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: [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).
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 eval_this; -- END My_Dynamic_Stuff; / If the mail software is truncating it, I can't help. 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
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 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 eval_this; -- END My_Dynamic_Stuff; / If the mail software is truncating it, I can't help. 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! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman 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).
RE: Literal SQL and sys.dual
Thanks so much for your time and effort. Correct code was found in the attachement. It compiles and runs as you described. You found a very innovative way of converting the string to an expression. I will need to check on whether it creates the Shared or non-shared SQL. That will be the determining factor for us to use it. If it works, you could save my project at least a month of effort. So thanks for help. Shakir --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Here you go Raj __ Rajendra JamadagniMIS, 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! -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 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. Thanks Shakir --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: 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 eval_this; -- END My_Dynamic_Stuff; / If the mail software is truncating it, I can't help. 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 = 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/ ATTACHMENT part 2 application/octet-stream name=MY_DYNAMIC_STUFF.PKS *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: [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).
RE: Literal SQL and sys.dual
I ran the tkprof on the code and found that it is still generating the literal non-shared SQL. Because the code uses dynamic sql. However, you did convert the string to a workable expression that got summed and assigned to the result number variable. Our past programmer did the similar thing. He created the expression as a string, concatenated as 'select ' || my_string || ' from dual;' and passed it as an argument to a function that used a native dynamic SQL to execute it. my_string := 'select ' || '1 + 1 + 2' || ' from dual;'; execute immediate my_string into my_number_variable; So I am back to SQUARE ONE. Thanks however for a great try. Shakir --- Mohammed Shakir [EMAIL PROTECTED] wrote: Thanks so much for your time and effort. Correct code was found in the attachement. It compiles and runs as you described. You found a very innovative way of converting the string to an expression. I will need to check on whether it creates the Shared or non-shared SQL. That will be the determining factor for us to use it. If it works, you could save my project at least a month of effort. So thanks for help. Shakir --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Here you go 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! -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 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. Thanks Shakir --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: 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 eval_this; -- END My_Dynamic_Stuff; / If the mail software is truncating it, I can't help. Raj __ Rajendra JamadagniMIS, 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 = 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/ ATTACHMENT part 2 application/octet-stream name=MY_DYNAMIC_STUFF.PKS *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: [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
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, 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
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 + (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 [EMAIL PROTECTED] 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 JamadagniMIS, 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: [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).
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
RTRIM(pi_string,';') || '; end;';
END eval_this;
--
END My_Dynamic_Stuff;
/
Here is a test ...
SQL get raj_test
1 declare
2my_str varchar2(100);
3 begin
4 dbms_output.enable;
5 my_dynamic_stuff.eval_this('1 + 1 + 1 + 1');
6 dbms_output.put_line ('1 + 1 + 1 + 1 evaluates to : ' ||
to_char(my_dynamic_stuff.v_result));
7 --
8 my_dynamic_stuff.eval_this('1 + (2 - 3)/4 + 5 / 4 + (6 + 7) / ( 5 +
5 + 0)');
9 dbms_output.put_line ('1 + (2 - 3)/4 + 5 / 4 + (6 + 7) / ( 5 + 5 +
0) evaluates to : ' || to_char(my_dynamic_stuff.v_result));
10* end;
SQL raj_test
1 + 1 + 1 + 1 evaluates to : 4
1 + (2 - 3)/4 + 5 / 4 + (6 + 7) / ( 5 + 5 + 0) evaluates to : 3.3
PL/SQL procedure successfully completed.
SQL
HTH some
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
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 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
RTRIM(pi_string,';') || '; end;';
END eval_this;
--
END My_Dynamic_Stuff;
/
Here is a test ...
SQL get raj_test
1 declare
2my_str varchar2(100);
3 begin
4 dbms_output.enable;
5 my_dynamic_stuff.eval_this('1 + 1 + 1 + 1');
6 dbms_output.put_line ('1 + 1 + 1 + 1 evaluates to : ' ||
to_char(my_dynamic_stuff.v_result));
7 --
8 my_dynamic_stuff.eval_this('1 + (2 - 3)/4 + 5 / 4 + (6 + 7) /
( 5 +
5 + 0)');
9 dbms_output.put_line ('1 + (2 - 3)/4 + 5 / 4 + (6 + 7) / ( 5
+ 5 +
0) evaluates to : ' || to_char(my_dynamic_stuff.v_result));
10* end;
SQL @raj_test
1 + 1 + 1 + 1 evaluates to : 4
1 + (2 - 3)/4 + 5 / 4 + (6 + 7) / ( 5 + 5 + 0) evaluates to : 3.3
PL/SQL procedure successfully completed.
SQL
HTH some
Raj
__
Rajendra JamadagniMIS, 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
=
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: [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).
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 on 8i or better version, if you are on pre 8i version, change code appropriately to use dbms_sql package instead of NDS. HTH 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
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 8.1.6/Solaris and 9i/NT and both bomb at concatenated string ( || ) on RTRIM code line. Is this the correct code you are submitting? --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: 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 on 8i or better version, if you are on pre 8i version, change code appropriately to use dbms_sql package instead of NDS. HTH Raj __ Rajendra JamadagniMIS, 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: [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).
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 script for each case. select :b1 from dual; select :b1/:b2 from dual; select :b1 - :b2/(:b3 + :b4) from dual; first one will handle all cases for b1 from 0 to any number which is good. And second sql will handle all the cases for any number for b1 and b2. So I do reduce the literal SQL. However, I need to know ahead of time what type of data I am calculating and then use the appropriate SQL. I think the easy solution would be to use arithmetic. That is to pass the string like ( 1 + 1 / (2 -2) ) to some function that can return me the result of this sting. So I would not be using SQL script, to minimize SQL execution, sys.dual contention or literal SQL filling shared pool and causing both library cache and shared pool. Not to mention saving in CPU processing by not parsing SQL scripts. Anybody, aware of such function in PL/SQL? Is there any other way to do calculations other than 'select 1 + 1 from dual' ? I would really appreciate if you could let me know. Thanks Mohammed Shakir = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) Mohammed, Don't you think, before starting with a PL/SQL function of death, that it *might* be easier to check the code and see where these statements, which are unlikely to be functional requirements, are used? Rewriting the PL/SQL code around them is probably the most efficient way to get rid of them. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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).
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 __ 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
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 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 script for each case. select :b1 from dual; select :b1/:b2 from dual; select :b1 - :b2/(:b3 + :b4) from dual; first one will handle all cases for b1 from 0 to any number which is good. And second sql will handle all the cases for any number for b1 and b2. So I do reduce the literal SQL. However, I need to know ahead of time what type of data I am calculating and then use the appropriate SQL. I think the easy solution would be to use arithmetic. That is to pass the string like ( 1 + 1 / (2 -2) ) to some function that can return me the result of this sting. So I would not be using SQL script, to minimize SQL execution, sys.dual contention or literal SQL filling shared pool and causing both library cache and shared pool. Not to mention saving in CPU processing by not parsing SQL scripts. Anybody, aware of such function in PL/SQL? Is there any other way to do calculations other than 'select 1 + 1 from dual' ? I would really appreciate if you could let me know. Thanks Mohammed Shakir = 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://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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman 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).
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 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 JamadagniMIS, 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).
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 := sql_string || '1'; sql_string := 'select ' || sql_string || ' from dual;' result := execute immediate sql_string; --- Igor Neyman [EMAIL PROTECTED] wrote: 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 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 script for each case. select :b1 from dual; select :b1/:b2 from dual; select :b1 - :b2/(:b3 + :b4) from dual; first one will handle all cases for b1 from 0 to any number which is good. And second sql will handle all the cases for any number for b1 and b2. So I do reduce the literal SQL. However, I need to know ahead of time what type of data I am calculating and then use the appropriate SQL. I think the easy solution would be to use arithmetic. That is to pass the string like ( 1 + 1 / (2 -2) ) to some function that can return me the result of this sting. So I would not be using SQL script, to minimize SQL execution, sys.dual contention or literal SQL filling shared pool and causing both library cache and shared pool. Not to mention saving in CPU processing by not parsing SQL scripts. Anybody, aware of such function in PL/SQL? Is there any other way to do calculations other than 'select 1 + 1 from dual' ? I would really appreciate if you could let me know. Thanks Mohammed Shakir = 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://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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Igor Neyman 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). __ 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).
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 buffer gets problem. So I am running against the time. So without messing up the logic and spend time learning fixing and testing the code, this one seems like a better option for me at this point. I am trying the project to do it right and do it where code needs the proper change even when I am gone. Thanks for yours and every one elses response. Your advices are supporting the ideas I have in the back of my mind to do it right. --- Stephane Faroult [EMAIL PROTECTED] wrote: 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 script for each case. select :b1 from dual; select :b1/:b2 from dual; select :b1 - :b2/(:b3 + :b4) from dual; first one will handle all cases for b1 from 0 to any number which is good. And second sql will handle all the cases for any number for b1 and b2. So I do reduce the literal SQL. However, I need to know ahead of time what type of data I am calculating and then use the appropriate SQL. I think the easy solution would be to use arithmetic. That is to pass the string like ( 1 + 1 / (2 -2) ) to some function that can return me the result of this sting. So I would not be using SQL script, to minimize SQL execution, sys.dual contention or literal SQL filling shared pool and causing both library cache and shared pool. Not to mention saving in CPU processing by not parsing SQL scripts. Anybody, aware of such function in PL/SQL? Is there any other way to do calculations other than 'select 1 + 1 from dual' ? I would really appreciate if you could let me know. Thanks Mohammed Shakir = Mohammed Shakir CompuSoft, Inc. 11 Heather Way East Brunswick, NJ 08816-2825 (732) 672-0464 (Cell) (732) 257-6001 (Home) Mohammed, Don't you think, before starting with a PL/SQL function of death, that it *might* be easier to check the code and see where these statements, which are unlikely to be functional requirements, are used? Rewriting the PL/SQL code around them is probably the most efficient way to get rid of them. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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). = 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: [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).
