Home » SQL & PL/SQL » SQL & PL/SQL » Select from List of numbers...interesting error!
Select from List of numbers...interesting error! [message #189565] Fri, 25 August 2006 02:17 Go to next message
charliebankes
Messages: 11
Registered: June 2006
Junior Member
Got example below off Asktom to select records based on a list of numbers...

It works great until I select from a table with a column called TIMESTAMP, when I get the error ..

****************************************************************
Error on line 0
DECLARE
p_nb_list CONSTANT INTEGER_TT := INTEGER_TT(8,9,10,11);

ORA-06550: line 5, column 9:
PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 11:
PL/SQL: SQL Statement ignored
ORA-06550: line 11, column 24:
PLS-00364: loop index variable 'R' use is invalid
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored
****************************************************************

I know how to fix it - remove the column or create a view but ..

Any ideas why??

is it a bug?? Any workarounds other than what I've sugessted?


CREATE TYPE INTEGER_TT AS TABLE OF NUMBER
/
DECLARE
p_nb_list CONSTANT INTEGER_TT := INTEGER_TT(8,9,10,11);

BEGIN
FOR r IN (
SELECT m_identity,m_nb
FROM trn_hdr_dbf
WHERE m_nb IN ( SELECT column_value
FROM TABLE(p_nb_list) )
)
LOOP
DBMS_OUTPUT.PUT_LINE(r.m_identity || r.m_nb);
END LOOP;
END;
/

Re: Select from List of numbers...interesting error! [message #189603 is a reply to message #189565] Fri, 25 August 2006 04:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Timestamp is a reserved word, and pl/sql really doesn't like selecting from columns that are called reserved words.
Re: Select from List of numbers...interesting error! [message #189811 is a reply to message #189603] Sun, 27 August 2006 23:54 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You should be able to get around it by enclosing with double-quotes and using upper-case.

select "TIMESTAMP" from ...


Ross Leishman
Re: Select from List of numbers...interesting error! [message #190082 is a reply to message #189811] Tue, 29 August 2006 04:22 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actually, I@ve just written a testcase using TIMESTAMP as a column and it seems to work fin in 9.2.0.7.
Previous Topic: Max(count(xxx))
Next Topic: Drop column query
Goto Forum:
  


Current Time: Wed Dec 07 16:49:11 CST 2016

Total time taken to generate the page: 0.06859 seconds