Tables not seen

[aldsaldrin]

I have created my tables in a database using a .bat file but when I try to insert data/values into it using toad there is prompt occuring that says that table i just created does not exist but when you look at the schema browser tab the table is there.. Did I miss something? What do you think is the problem? Any help is much appreciated..

Many thanks

[Pilgrim333]

Hi,

Can you query from the table from TOAD and SQL*PLUS? Is the table name you are using in your insert statement correct? No weird chars in the table name? What is the exact table name?

Pilgrim.

[aldsaldrin]

tnx Pilgrim,

There are no wierd characters in the Table name and Im sure that the name is correct. I just tried the same query that I used in Toad in SQL plus and it worked. What do you think is the problem of my Toad?

Many thanks..

[amitpatel66]

You will need to check the following:

1. In Which Schema you created a table?
2. Are you logging in to same schema in TOAD in which you created a table?
3. Try querying that table by appending its owner before table name. Say for

Eg: if you created a table in APPS schema then query a table by saying

Select * from APPS.<table_name>

[Pilgrim333]

Hi,

You are connected in Toad under the same user as the user that you use under SQL*PLUS (and the user that you created the table under)?

Pilgrim.

[aldsaldrin]

@amitpatel66,

1. I created the table in my own schema. When you click the schema browser, the tables I created is there.
2. Yes
3. I tried this:

a.) in SQL Plus: select * from TEST.MyTable; (worked)
b.) in Toad : select * from TEST.MyTable; (did not worked)

@Pilgrim,

Yes I did

I created the table using a .bat file.. What do you guys think is the problem?

Many thanks..

[Pilgrim333]

The only thing i can think of is that the table is created under an other user, and under TOAD you are connecting with another user.

If you look under the schema browser under TOAD, can you view the colums of the table? Can you right click on the table and select data from it?
(It's been a long time since i used TOAD, so don't know what the possibilities of it are)

Pilgrim.

[aldsaldrin]

tnx Pilgrim,

Yes I could view the columns and I could right click the tables..

I logged in @ SQL Plus with this: Username: TEST
Password: test
Host String: MyDatabase

I logged in @ Toad with this: Database: MyDatabase
User/Schema: TEST
Password: test

both of them are the same right? Am I logging in to the same database?

Many thanks..

[amitpatel66]

Please do a small test:

Run this query in both from SQLPLUS and TOAD and post the results:

1. SELECT user from DUAL
2. SELECT * FROM ALL_TABLES WHERE TABLE_NAME ='<Your Table Name>'

Run the above two queries and post back the results. Pelase run from SQLPLUS and TOAD.Hope we find out something here

[Pilgrim333]

The connection looks allright.

The queries from Amit should give some more info. What you also could try is, copying and pasting the insert statement from TOAD into SQL*PLUS and see if that works. (Copy and paste it, so it is the exact statement, don't retype it)

Pilgrim.

[aldsaldrin]

Toad: user = sys
SQL Plus: user = TEST

I typed the code "select user from dual" in both Toad and SQL Plus and here is the result:

Toad: user = sys
SQL Plus: user = TEST

I don't understand the Toad result. TEST is my user/schema. What do you guys think?

Many thanks..

[amitpatel66]

Well here is the catch. Can you notice that in TOAD you are logging in by default as SYS user?

You will need to login as TEST in toad to access that table

If your TOAD is set for automatic login, then you need to relogin to the same database with user TEST and then try querying your table. It should work after you login as user TEST in TOAD

Please post back in case of any issues

[Pilgrim333]

Connection is not allright :D

Pilgrim

[aldsaldrin]

Ok I now I get it, when connecting to Toad I was connected as SYSDBA but now I changed it to Normal and it worked. When trying the code "select user from dual", both Toad and SQL Plus shows the same user. Thank you very much amitpatel and Pilgrim.

[amitpatel66]

Good to hear your issue is resolved.