Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00604,ORA-00904 error (Oracle 9i)
ORA-00604,ORA-00904 error [message #349831] Tue, 23 September 2008 04:29 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
While trying to execute an sql query by removing rule hint I am getting the below error.

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00904: "VW_NSO_2"."$nso_col_1": invalid identifier


Want to know What is the use of this view? Why I am getting the above error?

Regards,
Oli
Re: ORA-00604,ORA-00904 error [message #349836 is a reply to message #349831] Tue, 23 September 2008 04:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is yours not Oracle rdbms standard one.

Regards
Michel
Re: ORA-00604,ORA-00904 error [message #349838 is a reply to message #349831] Tue, 23 September 2008 04:39 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Quote:

ORA-00904: string: invalid identifier
Cause: The column name entered is either missing or invalid.
Action: Enter a valid column name. A valid column name must begin with a letter, be less than or equal to 30 characters, and consist of only alphanumeric characters and the special characters $, _, and #. If it contains other characters, then it must be enclosed in double quotation marks. It may not be a reserved word


Check whether column name is proper .

Quote:
Want to know What is the use of this view ?


You are supposed to know it better ; it is your application Smile

Thumbs Up
Rajuvan.

[Updated on: Tue, 23 September 2008 04:47] by Moderator

Report message to a moderator

Re: ORA-00604,ORA-00904 error [message #349846 is a reply to message #349838] Tue, 23 September 2008 04:52 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
an internal view created for some reason.i.e, VW_NSO_2
The query contains rule hint and works fine when being executed.But when tried removing rule hint I got the above mentioned error.


I checked if the column is valid in length ,using any reserved word etc.

I also tried giving alias to the column names still I am getting the error.


Regards,
Oli
Re: ORA-00604,ORA-00904 error [message #349850 is a reply to message #349831] Tue, 23 September 2008 05:03 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member


Then make the reference in the query as VW_NSO_2."$nso_col_1" ( with double quotes) . Indeed it is a wrong design of database.

Thumbs Up
Rajuvan.
Re: ORA-00604,ORA-00904 error [message #349852 is a reply to message #349846] Tue, 23 September 2008 05:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
an internal view created for some reason.i.e, VW_NSO_2

Internal to you or to Oracle?

Quote:
The query contains rule hint and works fine when being executed

Post the view text including the hint.
Post the referenced table definition.

Regards
Michel
Re: ORA-00604,ORA-00904 error [message #349854 is a reply to message #349850] Tue, 23 September 2008 05:27 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Want to share the information:

http://www.orafaq.com/maillist/oracle-l/2003/12/02/0127.htm

I tried removing the column names and just selecting all columns (select * from..).The problem persists.

If I use rule hint then I can see the result without the above error.

Regards,
Oli

[Updated on: Tue, 23 September 2008 05:27]

Report message to a moderator

Re: ORA-00604,ORA-00904 error [message #349860 is a reply to message #349854] Tue, 23 September 2008 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't want to post the query text view and the hint?
Why?

Regards
Michel
Re: ORA-00604,ORA-00904 error [message #349866 is a reply to message #349860] Tue, 23 September 2008 06:24 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Sorry for not being able to provide the code.




I have a query like this


Select column1,column2...
from tab1 p,tab2 q,tab3 r
where join condition ..
and
Product_id in (select product_id from ....
                  where in_dt between...)
and .....
and ....
and not exists (select distinct 1 from   tab3 cn
                                        where cn.item_cd = p.item_cd  
                                        and status_code in ( select 'C' from dual))


Can not exists be replaced? I tried repacing by not in .But getting internal error!ORA-00600


if i remove
 and not exists (select distinct 1  from tab3 cn
                                        where cn.item_cd = p.item_cd  
                                        and status_code in ( select 'C' from dual)
from the existing query I can see no error.
Regards,
Oli

[Updated on: Tue, 23 September 2008 07:03]

Report message to a moderator

Re: ORA-00604,ORA-00904 error [message #349888 is a reply to message #349866] Tue, 23 September 2008 07:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As we haven't the actual query nor the hint, how can we help?
Why don't you post what is in reality?
Why don't you post this bl... hint?

Regards
Michel
Re: ORA-00604,ORA-00904 error [message #349898 is a reply to message #349888] Tue, 23 September 2008 07:36 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Due to some rule and restrictions imposed,I am not being able to post the query.


As I said, i am not egtting error when I remove the below code from the sql

and [b]not exists[/b] (select distinct 1 from   tab3 cn
                                        where cn.item_cd = p.item_cd  
                                        and status_code in ( select 'C' from dual))

[Updated on: Tue, 23 September 2008 07:37]

Report message to a moderator

Re: ORA-00604,ORA-00904 error [message #349901 is a reply to message #349898] Tue, 23 September 2008 07:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Probably doesn't have to do anything with the problem but - why did you put it this way
and status_code in ( select 'C' from dual)

instead of
and status_code = 'C'
Re: ORA-00604,ORA-00904 error [message #349905 is a reply to message #349831] Tue, 23 September 2008 07:59 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I can understand restrictions on posting actual code. But:

1. What is your Oracle version?

2. Why are you doing a distinct with a not exists?
and [b]not exists[/b] (select distinct 1 from


3. And like Littlefoot said, lose the reference to dual.

4. And if you are removing the rule hint, have you gathered statistics on all the tables in question?


Re: ORA-00604,ORA-00904 error [message #349912 is a reply to message #349898] Tue, 23 September 2008 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Due to some rule and restrictions imposed,I am not being able to post the query

And what restrictions prevent you from posting the hint?

Regards
Michel
Re: ORA-00604,ORA-00904 error [message #349929 is a reply to message #349912] Tue, 23 September 2008 09:07 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member

select /*+ rule */ 
 c.cs_id,c.c4
 e.e1,
 d.upd_dt,
 e.ncr_no,
 e.loc_cd,
 d.status_cd,
 cn.c_id,
 f.t_id,
 o.p_id,
 g.start_dt
			
from table1 o, table2 cn, table3 c, table3 d, table4 e, table5 f, table6 g
            where  o.prod_id = cn.c_id
            and o.prod_id = g.c_id
            and o.sub_prod_id = cn.seq_no
            and o.prod_id = f.c_id
            and o.sub_prod_id = f.seq_no
            and cn.c4 = c.c4
            and c.c4 = d.c4
            and c.pid = e.order_item_id
            and d.upd_dt = max_dt(c.c4)
			AND prod_id in (select c_id from table5 b where b.upd_dt between to_date('01/05/2008',  'MM/DD/YYYY')  and to_date('09/15/2008',  'MM/DD/YYYY')
            and b.t_id in (select t_id from table7
            where start_dt between to_date('01/05/2008', 'MM/DD/YYYY') and to_date('09/15/2008', 'MM/DD/YYYY')))
            
            and  NOT exists (select distinct 1
                                        from table3 ct
                                        where ct.c4 =cn.c4
										and status_cd in ( select 'C' from dual union 
                                        select 'R' from dual union
                                        select distinct e_status_cd from table8 ))
										and o.upd_dt = max_dt(o.prod_id,o.sub_prod_id)


Getting the output when tried to execute using hint.
But when tried removing hint I got the error.

Further,I found that ...

While trying to execute removing below code from the sql I m getting output without error

and  NOT EXISTS (select distinct 1
                                        from table3 ct
                                        where ct.c4 =c.c4
										and status_cd in ( select 'C' from dual union 
                                        select 'R' from dual union
                                        select distinct e_status_cd from table8 ))


Do any changes in the sql help?

Regards,
Oli

[Updated on: Tue, 23 September 2008 09:19]

Report message to a moderator

Re: ORA-00604,ORA-00904 error [message #349946 is a reply to message #349905] Tue, 23 September 2008 10:14 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
smartin wrote on Tue, 23 September 2008 07:59
I can understand restrictions on posting actual code. But:

1. What is your Oracle version?

2. Why are you doing a distinct with a not exists?
and [b]not exists[/b] (select distinct 1 from


3. And like Littlefoot said, lose the reference to dual.

4. And if you are removing the rule hint, have you gathered statistics on all the tables in question?





1. 9.2.0.5.0
2. removing distinct gives same error
3. I have posted the similar query
4. Statistics were gathered.


My concern is can the below code be modified?
select /*+ rule */ 
 c.cs_id,c.c4
 e.e1,
 d.upd_dt,
 e.ncr_no,
 e.loc_cd,
 d.status_cd,
 cn.c_id,
 f.t_id,
 o.p_id,
 g.start_dt
			
from table1 o, table2 cn, table3 c, table3 d, table4 e, table5 f, table6 g
            where  o.prod_id = cn.c_id
            and o.prod_id = g.c_id
            and o.sub_prod_id = cn.seq_no
            and o.prod_id = f.c_id
            and o.sub_prod_id = f.seq_no
            and cn.c4 = c.c4
            and c.c4 = d.c4
            and c.pid = e.order_item_id
            and d.upd_dt = max_dt(c.c4)
			AND prod_id in (select c_id from table5 b where b.upd_dt between to_date('01/05/2008',  'MM/DD/YYYY')  and to_date('09/15/2008',  'MM/DD/YYYY')
            and b.t_id in (select t_id from table7
            where start_dt between to_date('01/05/2008', 'MM/DD/YYYY') and to_date('09/15/2008', 'MM/DD/YYYY')))
            
            and  NOT exists (select distinct 1
                                        from table3 ct
                                        where ct.c4 =cn.c4
										and status_cd in ( select 'C' from dual union 
                                        select 'R' from dual union
                                        select distinct e_status_cd from table8 ))
										and o.upd_dt = max_dt(o.prod_id,o.sub_prod_id)



Removing the below code from the sql gives no error...
and  NOT EXISTS (select distinct 1
                                        from table3 ct
                                        where ct.c4 =c.c4
										and status_cd in ( select 'C' from dual union 
                                        select 'R' from dual union
                                        select distinct e_status_cd from table8 ))

[Updated on: Tue, 23 September 2008 10:19]

Report message to a moderator

Re: ORA-00604,ORA-00904 error [message #349993 is a reply to message #349831] Tue, 23 September 2008 12:14 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
And what about this line? What is that function?
and d.upd_dt = max_dt(c.c4)


Have you captured an explain plan yet and seen where your problematic view name appears?

The whole thing sure sounds like a bug, assuming nothing silly is going on.
Re: ORA-00604,ORA-00904 error [message #350065 is a reply to message #349831] Wed, 24 September 2008 00:14 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

And Moreover , where should we assume the problematic "VW_NSO_2"."$nso_col_1" comes in your query ?

Thumbs Up
Rajuvan.

[Updated on: Wed, 24 September 2008 00:15]

Report message to a moderator

Re: ORA-00604,ORA-00904 error [message #350071 is a reply to message #350065] Wed, 24 September 2008 00:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rajavu1 wrote on Wed, 24 September 2008 07:14
And Moreover , where should we assume the problematic "VW_NSO_2"."$nso_col_1" comes in your query ?

Thumbs Up
Rajuvan.

Like scot stated: you can see that in an explain plan
Re: ORA-00604,ORA-00904 error [message #350100 is a reply to message #350071] Wed, 24 September 2008 02:31 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
While trying to get the plan removing rule hint I am getting the same error I mentioned earlier.


With rule hint for the below code I got the plan below

select  
 c.cs_id,c.c4,
 e.e1,
 d.upd_dt,
 e.ncr_no,
 e.loc_cd,
 d.status_cd,
 cn.c_id,
 f.t_id,
 o.p_id,
 g.start_dt
			
from table1 o, table2 cn, table3 c, table4 d, table5 e, table6 f, table7 g
            where  o.prod_id = cn.c_id and o.prod_id = g.c_id  and o.sub_prod_id = cn.seq_no
            and o.prod_id = f.c_id     and o.sub_prod_id = f.seq_no  and cn.c4 = c.c4
            and c.c4 = d.c4   and c.pid = e.order_item_id
            and d.upd_dt = max_dt(c.c4)
			AND prod_id in (select c_id from table6 b where b.upd_dt between to_date('01/05/2008',  'MM/DD/YYYY')  
			and to_date('09/15/2008',  'MM/DD/YYYY')
			and b.t_id in (select t_id from table8
            where start_dt between to_date('01/05/2008', 'MM/DD/YYYY') and to_date('09/15/2008', 'MM/DD/YYYY')))
            and  NOT exists (select distinct 1
                                        from table3 ct
                                        where ct.c4 =cn.c4
										and status_cd in ( select 'C' from dual union 
                                        select 'R' from dual union
                                        select distinct status_cd from table9 ))
										and o.upd_dt = max_dt(o.prod_id,o.sub_prod_id)



Please make a note that statistics has been gathered for the tables being used in this query.


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------
| Id  | Operation                                |  Name                       | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                             |       |       |       |
|   1 |  FILTER                                  |                             |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID            | TABLE4                      |       |       |       |
|   3 |    NESTED LOOPS                          |                             |       |       |       |
|   4 |     NESTED LOOPS                         |                             |       |       |       |
|   5 |      NESTED LOOPS                        |                             |       |       |       |
|   6 |       NESTED LOOPS                       |                             |       |       |       |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|   7 |        NESTED LOOPS                      |                             |       |       |       |
|   8 |         NESTED LOOPS                     |                             |       |       |       |
|   9 |          NESTED LOOPS                    |                             |       |       |       |
|  10 |           VIEW                           | VW_NSO_1                    |       |       |       |
|  11 |            SORT UNIQUE                   |                             |       |       |       |
|  12 |             TABLE ACCESS BY INDEX ROWID  | TABLE6                      |       |       |       |
|  13 |              NESTED LOOPS                |                             |       |       |       |
|  14 |               TABLE ACCESS BY INDEX ROWID| TABLE8                      |       |       |       |
|  15 |                INDEX RANGE SCAN          | IND2_TABLE8                 |       |       |       |
|  16 |               INDEX RANGE SCAN           | IND_TABLE6                  |       |       |       |
|  17 |           TABLE ACCESS BY INDEX ROWID    | TABLE1                      |       |       |       |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|  18 |            INDEX RANGE SCAN              | PK_TABLE1                   |       |       |       |
|  19 |          TABLE ACCESS BY INDEX ROWID     | TABLE7              	       |       |       |       |
|  20 |           INDEX UNIQUE SCAN              | PK_TABLE7                   |       |       |       |
|  21 |         TABLE ACCESS BY INDEX ROWID      | TABLE6                      |       |       |       |
|  22 |          INDEX UNIQUE SCAN               | PK_TABLE6                   |       |       |       |
|  23 |        INDEX RANGE SCAN                  | PK_TABLE2                   |       |       |       |
|  24 |       TABLE ACCESS BY INDEX ROWID        | TABLE3                      |       |       |       |
|  25 |        INDEX UNIQUE SCAN                 | PK_TABLE3                   |       |       |       |
|  26 |      TABLE ACCESS BY INDEX ROWID         | TABLE5                      |       |       |       |
|  27 |       INDEX UNIQUE SCAN                  | PK_TABLE5                   |       |       |       |
|  28 |     INDEX RANGE SCAN                     | IND_UPD_DT                  |       |       |       |

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
|  29 |   NESTED LOOPS                           |                             |       |       |       |
|  30 |    VIEW                                  | VW_NSO_2                    |       |       |       |
|  31 |     SORT UNIQUE                          |                             |       |       |       |
|  32 |      UNION-ALL                           |                             |       |       |       |
|  33 |       TABLE ACCESS FULL                  | DUAL                        |       |       |       |
|  34 |       TABLE ACCESS FULL                  | DUAL                        |       |       |       |
|  35 |       TABLE ACCESS FULL                  | TABLE9           	       |       |       |       |
|  36 |    INDEX RANGE SCAN                      | IND_TABLE9        	       |       |       |       |
----------------------------------------------------------------------------------------------------



Regards,
Oli
Re: ORA-00604,ORA-00904 error [message #350215 is a reply to message #349831] Wed, 24 September 2008 07:25 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Inclusion of the below code causing problem:


and  NOT EXISTS (select distinct 1
                                        from table3 ct
                                        where ct.c4 =c.c4
										and status_cd in ( select 'C' from dual union 
                                        select 'R' from dual union
                                        select distinct e_status_cd from table8 ))


your suggesion will help..
Regards,
Oli
Re: ORA-00604,ORA-00904 error [message #350223 is a reply to message #349831] Wed, 24 September 2008 07:55 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Still in diognostic mode, I think my suggestions would be, in no particular order:

a) do some searching in metalink for anything similar. 9.2.0.5 is NOT the latest patch version of 9iR2, so it is very possible that a later patch fixes this issue, if it is an issue.

If possible, you could try to simulate the situation in another database of a higher patch or better yet version level. Also I thought I heard of problems in 9i of gathering system statistics or optimizer statistics on tables in the system tablespace. Don't know if you guys did that or not.

b) tweak that not exists subquery...start by simplifying it, even if doing so doesn't get you the answer you are looking for. Just try to get it to "compile" (parse and give you a plan). Even something like "not exists (select 1 from dual);", as well as something that doesn't rely on dual at all.

c) in combination with b, tweak the execution plan the CBO generates for your query (via hints if needed) to get it to try out a few different plans (which admittedly is a challenge since you can't see the plan, but should be doable by guesswork). I've seen bugs where one plan will work fine for a query but another plan will generate an error.

d) you haven't done anything to dual have you? By anything I mean anything...a DBA at a company I was at was fired for that, so believe it or not it does happen...

e) are any of the tables in question not heap organized? For example prior to 10.2.0.4 (I think) there were some nasty bugs related to IOT's (but mostly having to do with hierarchicals)

f) Oh and what about that function call? If all else fails you could try and write it as two queries and piece the results together.
Re: ORA-00604,ORA-00904 error [message #350260 is a reply to message #350223] Wed, 24 September 2008 09:11 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks Smartin for the valuable suggesions. I am working on it.

Making change to
Quote:
not exists (select 1 from dual)
works.

Which compelled me to think that problem persists here in this code only. Can it modified in some way?

and  NOT EXISTS (select distinct 1
                                        from table3 ct
                                        where ct.c4 =c.c4
										and status_cd in ( select 'C' from dual union 
                                        select 'R' from dual union
                                        select distinct e_status_cd from table8 ))




Regards,
Oli
Re: ORA-00604,ORA-00904 error [message #350265 is a reply to message #349831] Wed, 24 September 2008 09:19 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Good, your dual table isn't hosed.

Now that it works, you can capture the CBO version of the explain plan.

Next, slowly (meaning one change at a time) change your not exists query.

Try it next with (select 1 from dual where 1=0)

Then if that works try it with
(select 1 from table3 ct where <something simple>

Etc...

Re: ORA-00604,ORA-00904 error [message #350271 is a reply to message #350265] Wed, 24 September 2008 09:44 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Thanks for all your suggesions....

I am upto here...but response time is too bad
Elapsed: 00:08:53.00!!!!


and  NOT EXISTS (select 1 from table3 ct
                 where ct.c4 =c.c4
		       and status_cd in ('C','R'))


or


and  NOT EXISTS (select 1 from table3 ct
                 where ct.c4 =c.c4
		       and status_cd in (select 'C' from dual union Select 'R' from dual))




upto here..it works.

Getting output without any error...but...problem persists for the inclusion of the rest of the code

Regards,
Oli

[Updated on: Wed, 24 September 2008 10:02]

Report message to a moderator

Re: ORA-00604,ORA-00904 error [message #350294 is a reply to message #349831] Wed, 24 September 2008 10:57 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
FINDINGS:

Below code works fine ...
NOT EXISTS
(SELECT DISTINCT 1
   FROM table3 ct
  WHERE ct.c4  =cn.c4
AND status_cd IN
  ( 
   
   SELECT 'R' FROM dual
    
    UNION
  
  SELECT DISTINCT status_cd FROM table9
  )
)



Below code works fine ...though it can be written by simply status_cd='R'
NOT EXISTS
(SELECT DISTINCT 1
   FROM table3 ct
  WHERE ct.c4  =cn.c4
AND status_cd IN
  ( 
   
   SELECT 'R' FROM dual
    
    
  )
)



Below code works fine ...
NOT EXISTS
(SELECT DISTINCT 1
   FROM table3 ct
  WHERE ct.c4  =cn.c4
AND status_cd IN
  ( 
   
   SELECT DISTINCT status_cd FROM table9
    
    
  )
)


Below code works fine ...
NOT EXISTS
(SELECT DISTINCT 1
   FROM table3 ct
  WHERE ct.c4  =cn.c4
AND status_cd IN
  ( 
   
   SELECT 'R' FROM dual
    
    UNION
  
  SELECT DISTINCT status_cd FROM table9
  )
)


response time is not good but it works...

But ................



Using below code getting the above mentioned error!!!! (still working to find the reason why!)
NOT EXISTS
(SELECT DISTINCT 1
   FROM table3 ct
  WHERE ct.c4  =cn.c4
AND status_cd IN
  ( SELECT 'C' FROM dual
    
    UNION
   
   SELECT 'R' FROM dual
    
    UNION
  
  SELECT DISTINCT status_cd FROM table9
  )
)

any other way?
Your suggesion will help...


Reagrds,
Oli

[EDITED by LF: fixed [code] tags for the last piece of code]

[Updated on: Wed, 24 September 2008 15:08] by Moderator

Report message to a moderator

Re: ORA-00604,ORA-00904 error [message #350517 is a reply to message #350294] Thu, 25 September 2008 04:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you get when you replace the UNIONs with UNION ALLs - just thinking that the internal view may well be doing the sort/unique step - if you remove the need for that, the error may go away.
Re: ORA-00604,ORA-00904 error [message #350616 is a reply to message #349831] Thu, 25 September 2008 10:54 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
For what it is worth, just 2 minutes ago in development, I bombed out with an EOF with a query with the following construct at the end of the where clause (which is a similar construct to the one you had). Note that this is in an 8i database. Gotta be a bug to blow up the way it did.

My solution will be to just write the thing a different way...

		and
			(
				(dcan8, dc$ant) in (select pref_an8_number, pref_an8_type from (<<DRIVER_QUERY_TEXT>>)
				or
				dckcoo = lpad(:l_default_an8,5,0) and dcan8 = :l_default_an8 and dc$ant = '' ''
			)

Previous Topic: What are function we can't use in PL/SQL
Next Topic: oracle not connect throuth scott/tiger
Goto Forum:
  


Current Time: Fri Dec 09 21:09:18 CST 2016

Total time taken to generate the page: 0.10793 seconds