-
Inconsistancy between PLSQL & SQLPLUS....strange.
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.
-
You have an extra space after "number" in the declaration section. The semicolon needs to immediately follow the datatype declaration. Also, what is asdf used for? If you don't need it, don't declare it. You don't have to have a DECLARE (if you have nothing to declare) in PL/SQL.
Last edited by stecal; 11-01-2002 at 06:46 PM.
-
Space after 'number' is not true...
I looked into my other code. I have used a space after number and before ';'. It works fine. The reason I am getting this error, I belive is not linked to the space.. I tried without the space also. I still get the same error.
I know that I don't have to use the word 'declare' in a PL/SQL, if there is no need to declare I just used it, thanks for pointig it, though .
If you get a chace try to create a table and try the above update...statemnt from SQLPLUS and from PL/SQL... see the strange behavior....
Thanks for your response.
-Football
-
What happens in PL/SQL when more than one row is returned?
-
I wonder if this is down to the difference between the SQL engines in SQL and PL/SQL in the less recent versions of Oracle.
You might try wrapping the statement in "execute immediate" ...
declare
asdf number ;
begin
execute immediate '
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 ;
/
Note the use of repeated single-quotes (not double quotes) around the literal in the SQL statement string, and the lack of semi-colon at the end of the SQL string before the closing quotes.
-
I have a feeling it has got something to do with incompatibility issues of the ROWID datatype.
-
It works when wraped in 'execute immediate'
Hi SLIMDAVE,
I wraped the update command in execute immediate as you suggested. It works fine.
Thanks for your help.
-Football
-
try this one out
Try assigning a default value to the variable and change the variable name. and one more thing the query with in a query is not supported in PLSQL.
Last edited by nasir; 11-05-2002 at 06:22 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|