Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904: invalid column name--HELP! query is working in pl/sqldev tool but not in shell script
icon8.gif  ORA-00904: invalid column name--HELP! query is working in pl/sqldev tool but not in shell script [message #308019] Thu, 20 March 2008 15:46 Go to next message
r0bert
Messages: 3
Registered: March 2008
Junior Member
Please HELP me identify the culprit in my query "qry_updpenciem".
The sql statemet is NOT working in shell script but it worked in pl/sql dev tool.


Here's my SCRIPT:
---------------------------------
DECLARE
v_order_id number(9);

qry_updpenciem varchar(1600) := 'UPDATE PEN_CIEM SET order_id=order_id*(-1)
where (order_id,external_id_type,trim(external_id)) in
(select order_id,member_inst_id2,trim(member_inst_id3) from order_item
where order_id = :v1 and action_code=7
and (member_inst_id2,member_inst_id3) in
(select external_id_type,external_id from external_id_equip_map@cat
where (account_no,external_id) in
(select account_no,member_inst_id3 from order_item
where order_id = :v1 and member_type=16 and action_code=7
and member_id in
(select external_id_type from arbor.external_id_type_values
where display_value like ''%Outlet ID%''
or external_id_type=2007)
and id_no in
(select id_no from order_item
where order_id = :v1 and member_type=16 and action_code=7
and member_id in
(select external_id_type from arbor.external_id_type_values
where display_value like ''%Outlet ID%'' or external_id_type=2007)
group by id_no
having count(*)=2))
and active_date<>inactive_date))';


CURSOR C_AU_Rejected IS
SELECT DISTINCT co.order_id
FROM customer_order co, order_work_queue owq, order_work_queue_error owqe
/*WHERE co.order_id=owq.order_id*/
WHERE co.order_id=995038046
AND co.order_id_resets=owq.order_id_resets
AND owq.tracking_id=owqe.tracking_id
AND owq.queue_type=1
AND owq.status=60
AND owqe.sql_error LIKE '%ORA-20001: 159003, TRIG: INSERT/UPDATE Failed%'
AND co.order_status<=3 ORDER BY co.order_id;


BEGIN

OPEN C_AU_Rejected;
FETCH C_AU_Rejected INTO v_order_id;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'hh24:mi:ss')||' ----- ARBOR REJECTED ITEMS ('|| TO_CHAR(SYSDATE,'dd-mm-yy') ||') -----');
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'hh24:mi:ss')||' - Total number of REJECTED items found: '||C_AU_Rejected%ROWCOUNT);
LOOP
IF(C_AU_Rejected%NOTFOUND) then
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'hh24:mi:ss')||' - No Arbor Rejected milestones found.');
close C_AU_Rejected;
EXIT;
END IF;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,'hh24:mi:ss')||' - Order No. '|| v_order_id);
EXECUTE IMMEDIATE qry_updpenciem USING v_order_id;
FETCH C_AU_Rejected INTO v_order_id;
END LOOP;

-- COMMIT;

END;
/

Here's the OUTPUT:
-----------------------------------------------
Connected.
16:07:36 ----- ARBOR REJECTED ITEMS (20-03-08) -----
16:07:36 - Total number of REJECTED items found: 1
16:07:36 - Order No. 995038046
DECLARE
*
ERROR at line 1:
ORA-00904: invalid column name
ORA-06512: at line 59


Comment: line 59 in green above
Re: ORA-00904: invalid column name--HELP! query is working in pl/sqldev tool but not in shell script [message #308069 is a reply to message #308019] Fri, 21 March 2008 00:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You used double quotes around your string.
Re: ORA-00904: invalid column name--HELP! query is working in pl/sqldev tool but not in shell script [message #308081 is a reply to message #308019] Fri, 21 March 2008 01:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Next time, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: ORA-00904: invalid column name--HELP! query is working in pl/sqldev tool but not in shell script [message #308141 is a reply to message #308019] Fri, 21 March 2008 09:11 Go to previous messageGo to next message
r0bert
Messages: 3
Registered: March 2008
Junior Member
Frank - Nahh.. i did not use double quote, actually that was two single quotes

Hi All,

My apologies. I should have read the posting guidelines first. I was just so worried and this is my first time to use pl/sql and unaware of the format if embedded in shell script.

Thank you in advance for your help.
Re: ORA-00904: invalid column name--HELP! query is working in pl/sqldev tool but not in shell script [message #308143 is a reply to message #308141] Fri, 21 March 2008 09:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64107
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i did not use double quote, actually that was two single quotes

If you'd format the code, I could see it there you just wasted Frank's time.
OK, I noticed you know it now.
Also, if you use SQL*Plus and copy and paste the session, we can see the line numbers and what exactly happened.
So do it.

Regards
Michel


Re: ORA-00904: invalid column name--HELP! query is working in pl/sqldev tool but not in shell script [message #308146 is a reply to message #308019] Fri, 21 March 2008 09:49 Go to previous message
r0bert
Messages: 3
Registered: March 2008
Junior Member
I tested the query in PL/SQL Dev tool. And I haven't tried that in SQL*Plus.

I can't even run the query now coz I have no connection from my pc at work.. I was just hoping you could at least give me advise and i will try to list down all the possible solutions(from the forum and other sites)so I can recode and run series of test when I get back to work on Monday, 24/03/2008.



Previous Topic: Inheritance & nested tables
Next Topic: CREATE Table Error
Goto Forum:
  


Current Time: Mon Dec 05 04:39:16 CST 2016

Total time taken to generate the page: 0.17011 seconds