Hi,

This has reference to my earlier posting about 'order by .... in update statement'.

Lot of guys helped me in with the above posting.

While implementing this I ran into a strage situation. Here it is:

When I run the UPDATE statement from SQL prompt like...


SQL> update prod_analyze_tables set ANALYZE_STATUS = 'to_analyze'
where rowid in
( select rowid from
( select rowid from prod_analyze_tables
where analyzed_on is null
and analyze_status is null
order by company, table_name )
where rownum < 6 ) ;

It excutes fine and says.... 5 rows updated....

This is what i really want. But I need to put this into a file along with some more code.

So a create a file

file c:\abcd contains

------------------------------------------
declare

asdf number ;
begin

update prod_analyze_tables set ANALYZE_STATUS = 'to_analyze'
where rowid in
( select rowid from
( select rowid from prod_analyze_tables
where analyzed_on is null
and analyze_status is null
order by company, table_name )
where rownum < 6 ) ;


end ;

/
--------------------------------------------


I call the file from again SQL like

SQL> @ c:\abcd ;
declare
*
ERROR at line 1:
ORA-00932: inconsistent datatypes
ORA-06512: at line 6

I get the above error..... What the hell I am doing wrong ? I have no clue why it is giving this error.


The PLSQL block containts just one update statement... which ran successfully from SQLPLUS.


The update command is exactly same....I just copied and pasted from the previosly successfully run command.

So why the same statement works in SQLPLUS and fails when I put it in PLSQL..?

Any help towards this will be greately appriciated.... I am sure guys like CHRISRLONG, SLIMDAVE, STECAL will be upin arms to help guys like me...

Thanks,
-Football.