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

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

RE: Problem with DBMS_SQL - Long and probably annoying

From: Kimberly Smith <kimberly.smith_at_gmd.fujitsu.com>
Date: Wed, 31 Oct 2001 02:27:14 -0800
Message-ID: <F001.003B8F64.20011031023518@fatcity.com>

The owner of the package/tables is the one actually doing the execute so permissions is not the issue in this case.

-----Original Message-----
[mailto:Bruce.Reardon_at_comalco.riotinto.com.au] Sent: Tuesday, October 30, 2001 8:13 PM
To: 'ORACLE-L_at_fatcity.com'
Cc: 'kimberly.smith_at_gmd.fujitsu.com'

Kimberly,

A bit of a long shot here - Might it be permissions related?

eg - does the package owner have select on all the columns that are referenced in the package?
I know this isn't an appropriate long term solution, but try granting the package owner "select any table" directly and then see if it works.

HTH,
Bruce Reardon

-----Original Message-----
Sent: Wednesday, 31 October 2001 11:25

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).
Received on Wed Oct 31 2001 - 04:27:14 CST

Original text of this message

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