1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

the same sql can not be excute at a different oracle server.

Discussion in 'SQL PL/SQL' started by Ms tao, May 13, 2010.

  1. Ms tao

    Ms tao Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    hello every one,please help me with this problem.
    I got the following kind of sql which can be excuted at my company's oracle server, but can not be excuted at our client's oracle server.

    The error is 'ATTST.SHH_CD' DOES NOT EXISTS AT LINE 14
    but I can excute (select ATTST.SHH_CD FROM SAMPLE_TBL ATTST ) at both oracle server.

    my company's oracle server is Oracle 10.2.0.1
    my client's oracle server is Oracle 10.2.0.4
    looking forward to your anwser.

    example sql:

    LINE
    1 SELECT
    2 (SELECT
    3 ashisys
    4 FROM
    5 (SELECT
    6 ashisys
    7 FROM
    8 (SELECT
    9 'system' as ashisys
    10 FROM
    11 dual
    12 )
    13 WHERE
    14 ashisys = ATTST.SHH_CD -- IT WAS SHOWED 'ATTST.SHH_CD' DOES NOT EXISTS AT THIS LINE
    15 )
    16 WHERE
    17 ashisys = ATTST.SHH_CD
    18 ) AS ashi_val
    19 FROM
    20 SAMPLE_TBL ATTST
    21 WHERE
    22 1=1
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Ome of three situations likely exist:

    1) The table in question does not exist on the client server.
    2) A synonym is missing.
    3) The user account runnnig the SQL has no privileges on the ATTST table.

    Can you describe this table on the client's server while connected as the user running the above mentioned query (SQL> desc attst)? Can the table be accessed if the owner of the table is added to the table name (BOB.ATTST rather than ATTST, for example)? Does a "select owner, table_name from dba_tables where table_name = 'ATTST';" return any data? You have many questions to answer before anyone here can provide you one.
     
  3. Ms tao

    Ms tao Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Dear Zargon
    thank you for your reply.
    but I can excute 'select ATTST.SHH_CD FROM SAMPLE_TBL ATTST ' at both oracle server.
    since the sql I mensioned is a complicated sql with multiple layer,is it possible that this kind of sql can only be excuted by users with special right or role?
    and I also tested the sql at oracle 8 and 9, both failed.

    Best Regards
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Which release of Oracle are you using where this query fails? You report this fails on 8.x and 9.x but don't list the actual versions you've tested (four or five numbers in the format X.X.X.X[.X]). It may be that the the nesting is too deep for earlier releases of Oracle.

    Report, please, the Oracle versions in use, including the release where the query works.
     
  5. Ms tao

    Ms tao Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Dear Zargon:

    thank you for following

    It works at Oracle 10.2.0.1
    failed at Oracle 10.2.0.4

    but I donnot think its the version problem. I'm guessing my client has put some extra constraint on their oracle server...

    Best Regards
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Post a description of SAMPLE_TBL and some sample data, please.
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    No The above syntax will give always Error in Oracle. Even in 11g.

    Just how us the copy-paste from the working and non-working session of Sqlplus.
     
  8. Ms tao

    Ms tao Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Dear zargon
    I think I am getting sure that oracle 10.0.2.4 has forbidden this kind of request.
    and I am trying to write my sql at another way ( in pl/sql function)

    And thank you very much for your support

    Best Regards
     
  9. Ms tao

    Ms tao Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    dear Mr rajavu
    would you please make the statement of 'the copy-paste from the working and non-working session of Sqlplus' more clear?
    I can not understand the meaning very well
    thank you

    Best Regards
     
  10. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Tao, he is asking you to copy what you have done in sql plus and paste it here in the forum so that he can see. :)
     
  11. Ms tao

    Ms tao Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Dear Mr sadik

    thank you very much^-^

    best Regards.
     
  12. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    NO Need to re-write in Pl/sql Function. You can

    either remove the outer Select query
    or change the query as below.

    Code (SQL):
    SELECT (SELECT ATTST.SHH_CD AS ASHISYS
                              FROM DUAL
                              WHERE ATTST.SHH_CD = 'system' ) AS ASHI_VAL
      FROM SAMPLE_TBL ATTST
     WHERE 1 = 1
     
  13. Ms tao

    Ms tao Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Dear Mr rajavu

    thank you for your kindly support.
    but thats a common part which is used at severial sql in my system,
    so I have to make it seperated from other part
    and Its acturally is a very complicated sql.

    thank you again.

    Best Regards