February 26, 2010 at 5:19 am
hi
i tried to import data from excel sheet to oracle server. it was done using import wizard in sqlserver. now i got a error i.e. it was created as "Sheet1$" in oracle server. when i use a query to retrieve data from that table i am getting error
SQL Error: ORA-00942: table or view does not exist
can any one help me on this
Thanks
Rock...
February 26, 2010 at 6:15 am
You have to put Sheet1$ like this [Sheet1$], if I remember from the old days. Maybe next time you should change the table name when you use data import/export wizard.
March 1, 2010 at 1:55 am
after changing to [Sheet1$] also i am getting the same error
for ex: select * from test.[Sheet1$];
SQL Error: ORA-00942: table or view does not exist
March 2, 2010 at 10:30 am
The problem is not on the excel side but the table not being found on the Oracle side (I assume the table that is being loaded into).
Out of curiosity, why don't you use oracle tools to load the data from excel? (ie sql loader etc)
David Weil
March 2, 2010 at 12:44 pm
Can you shed some light on the process you are using?
It sounds like you imported the data into MSSQL and either pushed it directly to an Oracle server using DB Link or had a staging table in MSSQL, then pushed it to an Oracle table.
and now you want to Select * from [Sheet1$] and are getting an error? Who is the owner of that object? What user are you using to query SomeOwner.[Sheet1$]? Does that owner have SELECT permission to that object?
Have you considered Oracle External Tables?
You can load the file to the server hosting Oracle. Create the External table using that OS file and query it like any other table.
March 2, 2010 at 7:32 pm
Run the this query
select * from all_tables
on Oracle to verify that the table is there, if so notice the owner and make sure you specify the owner (schema) name in your query if needed, also the table name in Oracle is case sensitive
HTH
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply