| 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,
Look closely at the following statement - put more debug statements in this area to show the exact sql statement you are trying to parse.
The SELECT portion looks fine to me - provided that all of the columns do exist in the tables you reference.
My guess is that the problem is in the where clause using near the date or in selection area. Make sure you have your quotes set up correctly. If you have quote mismatch, then the optimizer will not recognize the difference between data and column names, thus giving you the error.
Remember, the statement has to look like a sql statement with all properly ballanced quotes.
Are the following variables being passed in properly structured? Does this properly translate to:
AND a.shift_start_date >= TO_DATE('{i_Start_Date_Value}','DD-MON-YYYY HH24:MI')
'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'') ' ||
Does the value of i_shifts contain a proper IN clause { (5,6,9) } or {
('5','6','7') }
Same for i_equip_ids.
'AND a.shift IN ' || i_shifts || ' ' ||
'AND a.equipment_id IN ' || i_equip_ids || ' ' ||
Hope this helps.
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, October 31, 2001 5:35 AM
To: Multiple recipients of list ORACLE-L
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
-- *****************************************************************
/
________________________________________
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: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us
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 - 07:52:45 CST
 |
 |