Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problem with DBMS_SQL - Long and probably annoying
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 ();
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 -- *****************************************************************Received on Wed Oct 31 2001 - 04:27:14 CST
/
________________________________________ 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).