Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Aug 2003
    Posts
    328

    Unanswered: Exported from SQL Server

    I used the export wizard to export a table to Oracle. I am running Oracle 8i. The wizard said the table was created and when I ran a query in Oracle to see if it was in all_tables, it appeared. When I run a query on the table, the table doesn't exist! Any ideas?

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Exported from SQL Server

    Most likely the user you a querying with does not have Select privs to the table you created. Try a user that is granted select on the table, or grant yourself rights.

    You can determine who has privs to the table by

    Select *
    From User_Tab_Privs
    Where Table_Name = 'TABLENAME'

    you can grant privs with

    Grant Select On TABLENAME To USER;

  3. #3
    Join Date
    Aug 2003
    Posts
    328

    Re: Exported from SQL Server

    When I try to grant select privileges, it still tells me that the table does not exist. I exported the table from SQL Server to Scott/Tiger. I tried from Scott/Tiger and from SYS and it still says that the table does not exist.

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Exported from SQL Server

    When you run

    Select *
    From All_Tables
    Where table_name = 'TABLENAME';

    Is SCOTT the owner? If so, when you log on as SCOTT/TIGER and run

    Select *
    from User_Tables;

    does your table appear?

  5. #5
    Join Date
    Aug 2003
    Posts
    328

    Re: Exported from SQL Server

    When I run

    select * from all_tables
    where table_name ='Tablename';

    the data for the table is returned as if the table exists.

    When I run

    Select *
    from Tablename;

    It says that the table doesnt exist

  6. #6
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120
    When you run
    select * from dba_tables
    Who is the OWNER?
    Only the owner will be able to see the table unless a public synonym exists or others have been granted privs.
    connect as whoever is OWNER of the tablename in question.
    If you still get Table or
    View does not exist then ????

  7. #7
    Join Date
    Aug 2003
    Posts
    328

    Re: Exported from SQL Server

    When I exported the data from SQL Server, I set up the connection to connect to Scott/tiger. So I should be able to see it by connecting to scott/tiger. Like I said, I can see the table name in 'all_tables', but when I want to run a query on the table I get the error message. It says the owner is scott. Again, I got no error from the export wizard in SQL Server.
    Last edited by exdter; 10-10-03 at 17:54.

  8. #8
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Exported from SQL Server

    It doesn't matter if you used scott/tiger to import the table if scott has the priveleges to create a table in another schema (owners) area. If you imported the table as DoeJ.MyTable and there is no public synonym for it you will not be able to select against as MyTable. If you do the select against DoeJ.MyTable then you will see it.

    Do this query:[i]
    Select Owner||'.'||Table_Name
    From All_Tables
    Where table_name = 'TABLENAME';[i]

    The result will be the Fully Qualified Name (FQN) of the table.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  9. #9
    Join Date
    Aug 2003
    Posts
    328

    Re: Exported from SQL Server

    The owner I get is scott.tablename. I can't go into it in scott, or sys or anything else.

  10. #10
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Exported from SQL Server

    Try select * from scott.tablename;

    And are you getting
    ERROR at line #:
    ORA-00942: table or view does not exist
    Or No Rows Selected

    Also try DESC scott.tablename;.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  11. #11
    Join Date
    Aug 2003
    Posts
    328

    Re: Exported from SQL Server

    I am getting the ERROR at line #:
    ORA-00942: table or view does not exist
    When I do select * from all_tables where table_name='tablename',
    it shows up.
    Thanks.

  12. #12
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Exported from SQL Server

    Did you try

    select * from scott.tablename;
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  13. #13
    Join Date
    Aug 2003
    Posts
    328

    Re: Exported from SQL Server

    Yes. I tried it from scott, system, and sys.

  14. #14
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Try a

    select * from all_objects where object_name = 'tablename';

    and post the result.

    Or - perhaps the name of the table has funny characters in it, perhaps a tab, an accented char ... i remember a similar problem long times (years) ago.

    al

  15. #15
    Join Date
    Aug 2003
    Posts
    41
    Or, Is the table created within double quotes?

    From scott user, instead of select * from tablename,
    try select * from "tablename";

    I think SQL Server does some funny things like that.

    -Sunil.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •