Inconsistancy between PLSQL & SQLPLUS....strange.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Inconsistancy between PLSQL & SQLPLUS....strange.

  1. #1
    Join Date
    Oct 2002
    Posts
    10

    Angry 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.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    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.

  3. #3
    Join Date
    Oct 2002
    Posts
    10

    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

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    What happens in PL/SQL when more than one row is returned?

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    I have a feeling it has got something to do with incompatibility issues of the ROWID datatype.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  7. #7
    Join Date
    Oct 2002
    Posts
    10

    Smile 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

  8. #8
    Join Date
    Aug 2002
    Posts
    3

    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
  •  


Click Here to Expand Forum to Full Width