FW: Problem with DBMS_SQL - Long and probably annoying

From: Kimberly Smith <kimberly.smith_at_gmd.fujitsu.com>
Date: Wed, 31 Oct 2001 09:50:15 -0800
Message-ID: <F001.003B9751.20011031100021@fatcity.com>

Thanks to those who replied. Of course the answer was simple but for some reason I thought the code was right in the whole quote thing. I did need to have quotes get passed in on the third and forth parameter.

I plan on rewriting the code anyway as I have not found a single reason as to why the original developer used DBMS_SQL instead of just using a cursor. Some people like to do things the hard way I guess. This gets us over the hump until I get time to redo it.

-----Original Message-----
Sent: Wednesday, October 31, 2001 6:34 AM To: ORACLE-L_at_fatcity.com
Cc: kimberly.smith_at_gmd.fujitsu.com

Kimberly,

Taking your code, passing the parameters you did, and taking a look at the SQL statement that is built, you end up with this in your WHERE CLAUSE:

AND a.shift IN A AND a.equipment_id IN 1408

If shift is a varchar2 column, which by passing the value of A I assume it is, you need delimiters on it. This is also assuming that equipment_id is numeric. If not, the same type of approach needs to be applied to it as well.

Try the following invocation of the package and see what happens:

eqhistpackage_kim.tool_performance('29-Aug-2001 00:00','30-Aug-2001 00:00','''A''','1408', dongle );

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Kimberly Smith
INET: kimberly.smith_at_gmd.fujitsu.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 Oct 31 2001 - 11:50:15 CST