Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Problem with DBMS_SQL - Long and probably annoying

Problem with DBMS_SQL - Long and probably annoying

From: Kimberly Smith <kimberly.smith_at_gmd.fujitsu.com>
Date: Tue, 30 Oct 2001 16:50:07 -0800
Message-ID: <F001.003B8B76.20011030162519@fatcity.com>

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

--**************************************************************************
***

  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

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

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

    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;


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). Received on Tue Oct 30 2001 - 18:50:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US