Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00904 Error in Table Function
ORA-00904 Error in Table Function [message #222788] Tue, 06 March 2007 06:40 Go to next message
Jegathees
Messages: 8
Registered: December 2005
Location: Coimbatore
Junior Member
Hi Friends,

I created one table function for simple select query. Function is created. But, while execute it shows 'ORA-00904' Error. The function & error is given below.
-----------------------------------------------------
SQL> CREATE TYPE object_row_type AS OBJECT(
ID VARCHAR2(10),
NAME VARCHAR2(10),
BIRTHYEAR VARCHAR2(4),
BIRTHMONTH VARCHAR2(2),
READFLAG CHAR(1),
);

SQL> CREATE TYPE object_table_type AS TABLE OF object_row_type;

SQL> CREATE OR REPLACE FUNCTION update_objects_newentry
2 RETURN object_table_type PIPELINED IS
3 TYPE ref0 IS REF CURSOR;
4 cur0 ref0;
5 out_rec object_row_type := object_row_type(NULL, NULL, NULL, NULL, NULL
);
6 BEGIN
7 OPEN cur0 FOR 'SELECT * FROM student WHERE readflag = " "';
8 LOOP
9 FETCH cur0 INTO out_rec.ID, out_rec.NAME, out_rec.BIRTHYEAR,out_rec.BI
RTHMONTH, out_rec.READFLAG;
10 EXIT WHEN cur0%NOTFOUND;
11 PIPE ROW(out_rec);
12 END LOOP;
13 CLOSE cur0;
14 RETURN;
15 END update_objects_newentry;
16 /
-----------------------------------------------------------

SQL> SELECT * FROM TABLE(update_objects_newentry);
SELECT * FROM TABLE(update_objects_newentry)
*
ERROR at line 1:
ORA-00904: " ": invalid identifier
ORA-06512: at "SYSTEM.UPDATE_OBJECTS_NEWENTRY", line 7
---------------------------------------------------------
I am not able to find why the error comes ? Could you kindly help me to solve this? (I use Oracle 10g Enterprise Edition Release 10.2.0.1.0)

Thank you.

Kind Regards,
Jegatheeswaran P.



Re: ORA-00904 Error in Table Function [message #222806 is a reply to message #222788] Tue, 06 March 2007 07:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
First: do NOT create objects under the SYSTEM account.
Secondly, you should not use double quotes. Use two single quotes instead.
Re: ORA-00904 Error in Table Function [message #223079 is a reply to message #222788] Wed, 07 March 2007 08:31 Go to previous messageGo to next message
Jegathees
Messages: 8
Registered: December 2005
Location: Coimbatore
Junior Member
Hi Frank,

Thanks for your reply. Then, I want to know how the above function is created under 'SYSTEM' as you told. What is the right procedure to avoid this. Kindly reply.

Regards,
Jegatheeswaran P

Re: ORA-00904 Error in Table Function [message #223085 is a reply to message #223079] Wed, 07 March 2007 08:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We can tell it's created by System, because the error message lists the schema and the object that caused the error:
ERROR at line 1:
ORA-00904: " ": invalid identifier
ORA-06512: at "SYSTEM.UPDATE_OBJECTS_NEWENTRY", line 7


To avoid this, simply log in as a different user when you connect to the database.
Previous Topic: maximum open cursers exceeded
Next Topic: purity level of finction
Goto Forum:
  


Current Time: Thu Dec 08 08:32:46 CST 2016

Total time taken to generate the page: 0.09333 seconds