RE: Problem with DBMS_SQL - Long and probably annoying
Kimberly,
Taking your code, passing the parameters you did, and taking a look at the
SQL statement that is built, you end up with this in your WHERE CLAUSE:
AND a.shift IN A AND a.equipment_id IN 1408
If shift is a varchar2 column, which by passing the value of A I assume it
is, you need delimiters on it. This is also assuming that equipment_id is
numeric. If not, the same type of approach needs to be applied to it as
well.
Try the following invocation of the package and see what happens:
eqhistpackage_kim.tool_performance('29-Aug-2001 00:00','30-Aug-2001
00:00','''A''','1408', dongle );
You can do it that way, or, you can make the following change in your
package:
Old: > 'AND a.shift IN ' || i_shifts || ' ' ||
New: > 'AND a.shift IN ''' || i_shifts || ''' ' ||
With the change, you can use the original method of invoking the package:
eqhistpackage_kim.tool_performance('29-Aug-2001 00:00','30-Aug-2001
00:00','A','1408', dongle );
But, and this is a big but, this doesn't address the issue of passing
multiple values. The fact that an IN is being used makes me think you could
possibly be passing in mutliple values. I'm not going to go into the
different ways to address this unless you will be passing multiples.
Regards,
Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Kimberly
> Smith
> Sent: Tuesday, October 30, 2001 6:25 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Problem with DBMS_SQL - Long and probably annoying
>
>
> 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_shiftsIN 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_shiftsIN 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_Utilizationnumber;
> v_Prom_Util number;
> v_Availability number;
> v_Downtime_Over_12 number;
> v_MTBF number;
> v_Lots_Proc number;
> v_Wafers_Procnumber;
> v_MTTR number;
> v_MTTRv number;
> v_MTTRm number;
> v_MRTnumber;
> v_MRTv number;
> v_Parts_Wait_Timenumber;
> v_Parts_Wait_P
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-
HH24:MI')
'AND a.shift_start_date >= TO_DATE(''' || i_Start_Date ||
''',''DD-MON- HH24:MI'') ' ||
'AND a.shift_end_date <= TO_DATE(''' || i_End_Date ||
''',''DD-MON- 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:[EMAIL PROTECTED]]
Sent: Tuesday, October 30, 2001 8:13 PM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
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_shiftsIN 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_shiftsIN 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_Utilizationnumber;
v_Prom_Util number;
v
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:[EMAIL PROTECTED]]
Sent: Tuesday, October 30, 2001 8:13 PM
To: '[EMAIL PROTECTED]'
Cc: '[EMAIL PROTECTED]'
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_shiftsIN 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_shiftsIN 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_Utilizationnumber;
v_Prom_Util number;
v_Availability number;
v_Downtime_Over_12 number;
v_MTBF number;
v_Lots_Proc number;
v_Wafers_Procnumber;
v_MTTR number;
v_MTTRv number;
v_MTTRm number;
v_MRTnumber;
v_MRTv number;
v_Parts_Wait_Timenumber;
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_Failnumber;
v_Eq_Maint_Repairnumber;
v_Total_Response number;
v_Eq_Maint_Down number;
v_Vendor_Responsenumber;
v_Total_Down_Incidents number;
v_Dummy number;
v_Total_Equip_IDsnumber;
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
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 ();
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_shiftsIN 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_shiftsIN 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_Utilizationnumber;
v_Prom_Util number;
v_Availability number;
v_Downtime_Over_12 number;
v_MTBF number;
v_Lots_Proc number;
v_Wafers_Procnumber;
v_MTTR number;
v_MTTRv number;
v_MTTRm number;
v_MRTnumber;
v_MRTv number;
v_Parts_Wait_Timenumber;
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_Failnumber;
v_Eq_Maint_Repairnumber;
v_Total_Response number;
v_Eq_Maint_Down number;
v_Vendor_Responsenumber;
v_Total_Down_Incidents number;
v_Dummy number;
v_Total_Equip_IDsnumber;
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 sep
RE: Problem with DBMS_SQL - Long and probably annoying
first thing I'd do is to look at line 200 through 210 from user_source to locate the problem. Also I'd put the sql statement as constructed using dbms_output 9you'll have to do multiple calls as your sql might be > 225 characters). Running this SQL in sqlplus usually solves the invalid column problems for me. From my experience it is either a misplaced comma or a misplaced single quote. HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
