Forum Stats

  • 3,727,123 Users
  • 2,245,325 Discussions
  • 7,852,604 Comments

Discussions

ORA-00942 Table does not Exist

929728
929728 Member Posts: 8
edited March 2017 in SQL & PL/SQL

I created a table under my Oracle user from Alteryx, a third-party application.  In SQL Developer I can see the table under my user's schema and I can see all the data and properties.  However I cannot query it even when specifying the user (error: ORA-00942: table or view does not exist).

The following query correctly returned my user name:

select owner,

       object_name

from dba_objects

where object_name = any ('test_spread');

Tagged:
John ThortonAndrewSayerGbenga AjakayeGary_A

Best Answer

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,753 Blue Diamond
    edited March 2017 Accepted Answer

    If Opt_Spread is case sensitive you need to enclose it in double-quotes as :

    select * from "Opt_Spread"

    (I assume that only the "O" and the "S" are upper-case and all the rest of it is lower-case. If not, please adjust the name in the query accordingly)

    Hemant K Chitale

    929728
«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited March 2017
    929728 wrote:I created a table under my Oracle user from Alteryx, a third-party application. In SQL Developer I can see the table under my user's schema and I can see all the data and properties. However I cannot query it even when specifying the user (error: ORA-00942: table or view does not exist).The following query correctly returned my user name:select owner,  object_name from dba_objects where object_name = any ('test_spread');

    Everything is forbidden;  except that which has been explicitly GRANTED.

    No other USER can "see" any table owned by  ALTERYX unless & until after

    GRANT SELECT on TEST_SPREAD TO OTHER_USER;

    is executed by ALTERYX

  • Gbenga Ajakaye
    Gbenga Ajakaye Member Posts: 3,422 Gold Trophy
    edited March 2017

    Are you on 11g or 12c?

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,753 Blue Diamond
    edited March 2017

    >>where object_name = any ('test_spread');

    Would imply that the table name is case sensitive and in lower-case.

    Hemant K Chitale

  • Unknown
    edited March 2017
     However I cannot query it even when specifying the user (error: ORA-00942: table or view does not exist).

    I don't see any query. Are you SURE you posted it?

  • 929728
    929728 Member Posts: 8
    edited March 2017

    Thank you.  I'm currently seeing how to pass that privilege from Alteryx.  I am curious though that I can purge the table my right-clicking on the table even though DELETE was not granted.

  • 929728
    929728 Member Posts: 8
    edited March 2017

    It is 12c. I attempted several simple queries in SQL Developer: SELECT * FROM MyUser.MyTable.  I check & re-checked case sensitivity.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited March 2017
    929728 wrote:Thank you. I'm currently seeing how to pass that privilege from Alteryx. I am curious though that I can purge the table my right-clicking on the table even though DELETE was not granted. 

    It depends.

    to whom does "I" refer; which username?

    assume below was previously done by SYS

    GRANT CREATE TABLE TO ALTERYX;

    then user  ALTERYX did as below

    CREATE TABLE TEST_SPREAD .....;

    now ALTERYX can INSERT, UPDATE, DELETE, ALTER, DROP, SELECT, TRUNCATE, etc. to table TEST_SPREAD; since it owns this object without any other GRANT being issued.

  • 929728
    929728 Member Posts: 8
    edited March 2017

    Thanks for following up for clarification.  I purged the ALTERYX table in SQL Developer under my User's schema as my Oracle User.  For further clarification, the ALTERYX connection string includes my User name & password.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited March 2017
    929728 wrote:Thanks for following up for clarification. I purged the ALTERYX table in SQL Developer under my User's schema as my Oracle User. For further clarification, the ALTERYX connection string includes my User name & password.

    I have no idea what you do or what you see.

    PURGE is NOT a valid SQL statement & meaningless in this discussion.

    >For further clarification, the ALTERYX connection string includes my User name & password.

    HUH? I don't know what above is supposed to mean.

    Please use  COPY & PASTE so we can see what you do & how Oracle responds

    If you refuse to actually show use the SQL you do & how Oracle responds,

    then I'll cease wasting my time trying to guess the problem & solution.

  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,753 Blue Diamond
    edited March 2017

    I just hope that, like the table, the schema name (username) is not case sensitive.

    Hemant K Chitale

  • 929728
    929728 Member Posts: 8
    edited March 2017

    As p6report I can right-click the table in SQL Developer and delete/purge the table.

    These are the SQL Developer queries I tried:

    SELECT * FROM p6report.Opt_Spread

    SELECT * FROM Opt_Spread

    SELECT ID FROM p6report.Opt_Spread

    SELECT Opt_Spread.ID FROM p6report.Opt_Spread

    SELECT ID FROM Opt_Spread

    SELECT Opt_Spread.ID FROM Opt_Spread

    Error starting at line : 1 in command -

    SELECT * FROM p6report.Opt_Spread

    Error at Command Line : 1 Column : 24

    Error report -

    SQL Error: ORA-00942: table or view does not exist

    00942. 00000 -  "table or view does not exist"

    *Cause:   

    *Action:

  • 929728
    929728 Member Posts: 8
    edited March 2017

    I just re-checked cases in both ALTERYX and Oracle and they look good.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited March 2017
    929728 wrote:I just re-checked cases in both ALTERYX and Oracle and they look good.

    Congratulations.

    Please mark this thread as  ANSWERED.

  • 929728
    929728 Member Posts: 8
    edited March 2017

    I just noted that I re-checked the cases.  I attempted to grant Opt_Spread privileges to p6report and I received Vendor Code 17008.  The table doesn't exist under any other User schema but p6report.

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited March 2017
    929728 wrote:I just noted that I re-checked the cases. I attempted to grant p6report and I received Vendor Code 17008. The table doesn't exist under any other User schema but p6report.

    I don't know what you have, what you do, or what you see.

    I need facts; not speculation

  • Kalpataru
    Kalpataru Member Posts: 4,615 Bronze Crown
    edited March 2017

    I think it is due to you have passed the username as lowercase as mentioned by @Hemant K Chitale.

    Try this

    SELECT  owner, object_name  FROM  dba_objectsWHERE  object_name = ANY ('TEST_SPREAD');

    Run the above query and if find the same issue check that grant and paste the exact error here.

    Hope it helps you.

    929728
  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    edited March 2017

    Try this:

    Select * From "test_spread";

    However it is generally considered bad practice to use case sensitive table names.

    929728
  • Kalpataru
    Kalpataru Member Posts: 4,615 Bronze Crown
    edited March 2017

    Dear the OP is selecting from dba_object , i don't get how your query will work and what is about the case sensitive concept comes here.

  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    edited March 2017
    Whenever SqlError Continue;Create Table "xxjktest" As Select 1 n From Dual;Select Count(*) From dba_objects Where object_name='xxjktest';Select Count(*) From dba_objects Where object_name='XXJKTEST';Select * From xxjktest;Select * From "xxjktest";

    Table created.  COUNT(*)----------         11 row selected.  COUNT(*)----------         01 row selected.>> Select * From xxjktest              *Error at line 1ORA-00942: table or view does not exist         N----------         11 row selected.
  • Hemant K Chitale
    Hemant K Chitale Member Posts: 15,753 Blue Diamond
    edited March 2017 Accepted Answer

    If Opt_Spread is case sensitive you need to enclose it in double-quotes as :

    select * from "Opt_Spread"

    (I assume that only the "O" and the "S" are upper-case and all the rest of it is lower-case. If not, please adjust the name in the query accordingly)

    Hemant K Chitale

    929728
  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    edited March 2017

    The OP sad he got a row returned from this query:

    select owner,

           object_name

    from dba_objects

    where object_name = any ('test_spread');

    So it looks like it's all lower case.

    Select * From "test_spread";

    929728
  • BluShadow
    BluShadow Member, Moderator Posts: 40,905 Red Diamond
    edited March 2017
    ORA-01033 wrote:The OP sad he got a row returned from this query:select owner, object_namefrom dba_objectswhere object_name = any ('test_spread');So it looks like it's all lower case.Select * From "test_spread";

    The OP also said in reply #11 that they were selecting from

    SELECT * FROM p6report.Opt_Spread

    So it's correct, if we're assuming the name is case sensitive, to correct that statement as:

    SELECT * FROM p6report."Opt_Spread"

    or suchlike.

    Essentially, the key thing is that, if the database object has been created with case-sensitivity (as some stupid 3rd party tools like to do), then they need to be specified in double quotes to ensure we maintain case sensitivity when referencing them, otherwise oracle will assume UPPERcase for the object.

    Gary_A929728
  • John_K
    John_K Member Posts: 2,498 Gold Trophy
    edited March 2017

    Did you actually read my response in context??! You clearly missed the whole point of what I was showing.

    My first reply was "However it is generally considered bad practice to use case sensitive table names" and asked the OP to run a different query. The user Kalpataru then responded to me asking:

    "i don't get how your query will work and what is about the case sensitive concept comes here."

    So I responded with a demo to show how if you create a table with quotes then you get various results from different queries.

    "By enclosing your object name in double-quotes when you create it, you have made the name case sensitive, and EVERY time you reference it, you will need to enclose the name in double-quotes and match the case exactly as you created it."

    erm - yes - exactly what I was posting an example to show.

    "All of your double-quoting and CamElCase reeks of SQL Server.  You're not in Kansas any more, Toto."

    Yes - I prefer camelcase when formatting. Personal preferences. Who cares?

    929728
  • 929728
    929728 Member Posts: 8
    edited March 2017

    Cases were the problem.   Thank you for your patience Hemant K Chitale.  The table is now OPT_SPREAD and works fine.

This discussion has been closed.