RE: Problem with DBMS_SQL - Long and probably annoying

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 );

You can do it that way, or, you can make the following change in your package:

Old: > 'AND a.shift IN ' || i_shifts || ' ' || New: > 'AND a.shift IN ''' || i_shifts || ''' ' ||

With the change, you can use the original method of invoking the package:

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

But, and this is a big but, this doesn't address the issue of passing multiple values. The fact that an IN is being used makes me think you could possibly be passing in mutliple values. I'm not going to go into the different ways to address this unless you will be passing multiples.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Kimberly
> Smith
> Sent: Tuesday, October 30, 2001 6:25 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Problem with DBMS_SQL - Long and probably annoying
>
>
> There is a Procedure that uses DBMS_SQL that is causing some problems.
> Basically I am having problems debugging it. I got it
> down to the parse statement but see nothing wrong. I have taken the SQL
> statement out of this code and ran it manually and it works so I don't
> understand the error. If someone can give me some ideas to try I would
> appreciate it. Note: This does work from Java as the following:
> int iToolSeq = 5;
> try {
> CallableStatement AddRE = DBConn.prepareCall("begin
> eqhistpackage.tool_performance(?,?,?,?,?); end;");
> AddRE.setString (1, txtStartDate.getText() + " " +
> cmbStartTime.getSelectedItem().toString());
> AddRE.setString (2, txtEndDate.getText() + " " +
> cmbEndTime.getSelectedItem().toString());
> AddRE.setString (3, strShifts.toString());
> AddRE.setString (4, strEquipIDs.toString());
> AddRE.registerOutParameter (5, Types.INTEGER);
> AddRE.execute ();
> iToolSeq = AddRE.getInt(5);
> AddRE.close();
> System.out.println(iToolSeq);
> }
>
> Here is the call:
> declare
> dongle number;
> begin
> eqhistpackage_kim.tool_performance('29-Aug-2001 00:00','30-Aug-2001
> 00:00','A','1408', dongle );
> end;
> Here is the error:
> The following error has occurred:
>
> ORA-00904: invalid column name
> ORA-06512: at "SYS.DBMS_SYS_SQL", line 824
> ORA-06512: at "SYS.DBMS_SQL", line 32
> ORA-06512: at "EH_OWNER.EQHISTPACKAGE_KIM", line 205
> ORA-06512: at line 4
>
>
> Here is the code (minus some client specific stuff). The
> dbms_output lines
> do show up. If I put one after the parse no go.
> CREATE OR REPLACE PACKAGE eqhistpackage_kim
> AS
>
> PROCEDURE tool_performance
> (i_start_date IN varchar2,
> i_end_date IN varchar2,
> i_shifts IN varchar2,
> i_equip_ids IN varchar2,
> o_tool_seq OUT number);
> END;
> /
> CREATE OR REPLACE PACKAGE BODY eqhistpackage_kim
> AS
>
> --****************************************************************
> **********
> ***
> PROCEDURE tool_performance
> (i_Start_date IN varchar2,
> i_End_date IN varchar2,
> i_shifts IN varchar2,
> i_equip_ids IN varchar2,
> o_tool_seq OUT number)
> IS
> --
> -- This routine looks for a match between the measurement name passed in
> -- and an existing measurement name in the EH_MEASUREMENT table. If a
> -- match is found, the measurement code is passed back; otherwise a new
> -- measurement code is computed and passed back and a new row is added to
>
> --****************************************************************
> **********
> ***
>
> v_CursorID number;
> v_SelectStmt varchar2(4000);
> v_Equip_ID EQ_GROUP_MBR.equipment_id%TYPE;
> v_Equip_Type EQ_GROUP_TYPE_MBR.equipment_type%TYPE;
> v_Utilization number;
> v_Prom_Util number;
> v_Availability number;
> v_Downtime_Over_12 number;
> v_MTBF number;
> v_Lots_Proc number;
> v_Wafers_Proc number;
> v_MTTR number;
> v_MTTRv number;
> v_MTTRm number;
> v_MRT number;
> v_MRTv number;
> v_Parts_Wait_Time number;
> v_Parts_Wait_Pct number;
> v_Prod_Time number;
> v_Standby_Time number;
> v_Engineering_Time number;
> v_Sched_Downtime number;
> v_Unsched_Downtime number;
> v_Total_Time number;
> v_Prod_Fail number;
> v_Total_Repair number;
> v_Total_Fail number;
> v_Vendor_Repair number;
> v_Vendor_Fail number;
> v_Eq_Maint_Repair number;
> v_Total_Response number;
> v_Eq_Maint_Down number;
> v_Vendor_Response number;
> v_Total_Down_Incidents number;
> v_Dummy number;
> v_Total_Equip_IDs number;
> v_tool_seq number;
> v_tool_index number;
> v_temp varchar2(1000);
> BEGIN
>
> o_tool_seq := 0;
> v_tool_index := 1;
> v_Total_Equip_IDs := 1;
>
> select tool_perf_seq.NEXTVAL
> into v_tool_seq
> from DUAL;
>
> v_CursorID := DBMS_SQL.OPEN_CURSOR;
> dbms_output.put_line('v_cursorid = ' || v_cursorid);
> -- Place the date range into a temporary variable and write it to a table
> -- that is used to hold the report text.
>
> v_temp := 'Start Date:,'||i_Start_Date||',,End Date:,'||i_End_Date;
> INSERT into tool_perf_report (seq, line_num, text) values(v_tool_seq,
> v_tool_index, v_temp);
>
> -- Build a string containing comma seperated values of each
> column heading.
> -- Write this to the temporary table as well.
>
> v_temp := 'Equipment Type,Equipment ID,Utilization,PROMIS
> Utilization,';
> v_temp := v_temp || 'Availability,Downtime Over 12,MTBF,Lots
> Processed,';
> v_temp := v_temp || 'Wafers Processed,MTTR,MTTRv,MTTRm,MRT,MRTv,';
> v_temp := v_temp || 'Awaiting Parts,Productive Time,Standby Time,';
> v_temp := v_temp || 'Engineering Time,Scheduled Downtime,';
> v_temp := v_temp || 'Unscheduled Downtime';
>
> v_tool_index := v_tool_index + 1;
> INSERT into tool_perf_report (seq, line_num, text) values(v_tool_seq,
> v_tool_index, v_temp);
>
> -- Build a select statement that selects raw and partially computed tool
> -- performance metrics from the equipment bucket table for desired
> equipment
> -- IDs that fall within the start and end times.
>
> v_SelectStmt :=
> 'SELECT b.equipment_type,' ||
> 'a.equipment_id,' ||
> 'sum(standby_time),' ||
> 'sum(productive_time),' ||
> 'sum(engineering_time),' ||
> 'sum(sched_down_time),' ||
> 'sum(unsched_down_time),' ||
> 'sum(shift_total_time),' ||
> 'sum(lots_processed),' ||
> 'sum(wafers_processed),' ||
> 'sum(downtime_over_12_hrs),' ||
> 'sum(waiting_for_parts),' ||
> 'sum(productive_failures),' ||
> 'sum(total_repair_time),' ||
> 'sum(total_failures),' ||
> 'sum(vendor_repair_time),' ||
> 'sum(vendor_failures),' ||
> 'sum(eq_maint_repair_time),' ||
> 'sum(total_response_time),' ||
> 'sum(eq_maint_down_incidents),' ||
> 'sum(vendor_response_time),' ||
> 'sum(total_down_incidents) ' ||
> 'FROM equipment_bucket a, equn b ' ||
> 'WHERE a.equipment_id = b.equipment_id ' ||
> 'AND a.shift_start_date >= TO_DATE(''' || i_Start_Date ||
> ''',''DD-MON-YYYY HH24:MI'') ' ||
> 'AND a.shift_end_date <= TO_DATE(''' || i_End_Date ||
> ''',''DD-MON-YYYY HH24:MI'') ' ||
> 'AND a.shift IN ' || i_shifts || ' ' ||
> 'AND a.equipment_id IN ' || i_equip_ids || ' ' ||
> 'GROUP BY b.equipment_type, a.equipment_id';
> dbms_output.Put_Line('I am here');
> -- Parse the select statement that we just put together and assign
> variables
> -- to the columns in the cursor.
>
>
>
> DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, 2 );
>
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_Equip_Type, 10);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 2, v_Equip_ID, 8);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 3, v_Standby_Time);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 4, v_Prod_Time);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 5, v_Engineering_Time);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 6, v_Sched_Downtime);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 7, v_Unsched_Downtime);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 8, v_Total_Time);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 9, v_Lots_Proc);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 10, v_Wafers_Proc);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 11, v_Downtime_Over_12);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 12, v_Parts_Wait_Time);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 13, v_Prod_Fail);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 14, v_Total_Repair);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 15, v_Total_Fail);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 16, v_Vendor_Repair);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 17, v_Vendor_Fail);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 18, v_Eq_Maint_Repair);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 19, v_Total_Response);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 20, v_Eq_Maint_Down);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 21, v_Vendor_Response);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 22, v_Total_Down_Incidents);
>
> v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);
>
> LOOP
> IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
> EXIT;
> END IF;
>
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_Equip_Type);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 2, v_Equip_ID);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 3, v_Standby_Time);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 4, v_Prod_Time);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 5, v_Engineering_Time);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 6, v_Sched_Downtime);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 7, v_Unsched_Downtime);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 8, v_Total_Time);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 9, v_Lots_Proc);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 10, v_Wafers_Proc);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 11, v_Downtime_Over_12);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 12, v_Parts_Wait_Time);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 13, v_Prod_Fail);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 14, v_Total_Repair);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 15, v_Total_Fail);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 16, v_Vendor_Repair);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 17, v_Vendor_Fail);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 18, v_Eq_Maint_Repair);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 19, v_Total_Response);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 20, v_Eq_Maint_Down);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 21, v_Vendor_Response);
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 22, v_Total_Down_Incidents);
>
>
> END LOOP;
> DBMS_SQL.CLOSE_CURSOR(v_cursorid);
> o_tool_seq := v_tool_seq;
>
>
>
> END;
>
> -- *****************************************************************
> END; -- END OF eqhistpackage BODY
> -- *****************************************************************
> /
>
>
> ________________________________________
> Kimberly Smith
> GMD Fujitsu
> Database Administrator
> (503) 669-6050
>
> --
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

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).