| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problem with DBMS_SQL - Long and probably annoying
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 ();
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;
--*****************************************************************************
(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: Reardon, Bruce (CALBBAY)
INET: Bruce.Reardon_at_comalco.riotinto.com.au
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 - 01:15:07 CST
 |
 |