Problem while querying..

Hi
I have created a user tempdev1,i have given all the priveleges and he created some tables,it worked fine this morning.Its allowing to connect,but when he is trying to select..
select * from tab; its giving error ORA-00932,inconsistent datatypes?

Thanks
vemulayugandharAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vemulayugandharAuthor Commented:
I will appreciate your help
Thanks
yugandhar
0
sudhi022299Commented:
This is what Oracle manual says:
ORA-00932: inconsistent datatypes
Cause: Either

An attempt was made to perform an operation on incompatible datatypes. For example, adding a character field to a date field (dates may only be added to numeric fields) or concatenating a character field with a long field.
An attempt was made to perform an operation on a database object (such as a table or view) that is not intended for normal use. For example, system tables cannot be modified by a user. Note that on rare occasions this error occurs because a misspelled object name matched a restricted object's name.
An attempt was made to use an undocumented view.
Action: If the cause is

different datatypes, then use consistent datatypes. For example, convert the character field to a numeric field with the TO_NUMBER function before adding it to the date field. Functions may not be used with long fields.
an object not intended for normal use, then do not access the restricted object.
0
vemulayugandharAuthor Commented:
I did not find any errors in alert log file.I found that user did nothing,by looking into v$sqlarea.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

sgantaCommented:
Hi Try to select like this,

SELECT * FROM sys.tab;
0
Mark GeerlingsDatabase AdministratorCommented:
Try:
select table_name from all_tables;

and
select view_name from all_view;

These two selects should be the almost the equivalent of "select * from tab;".
0
vemulayugandharAuthor Commented:
i did try to select connecting as that user,tempdev/tempdev but still its giving the same error.
0
Mark GeerlingsDatabase AdministratorCommented:
If you connect as SYSTEM can you do a select?  Are there other user accounts that work fine?  Can your tempdev (or tempdev1?) user do inserts, updates or deletes?
0
vemulayugandharAuthor Commented:
Yes,i can select as system and also as another user,but cannot select connecting to this user only..
0
Mark GeerlingsDatabase AdministratorCommented:
Do you have a standard script or procedure to create users?  If so, did you leave out a step?

Did you create this account manually in SQL*Plus or did you use a GUI tool like Enterprise Manager?  Can you do any selects as this user?
0
vemulayugandharAuthor Commented:
I created users manually at SVRMGR.
I cannot select any thing as this user.
0
Mark GeerlingsDatabase AdministratorCommented:
If you create users manually in svrmgr or SQL*Plus, you should have (or develop) a script to help with this to make sure that you do not forget a privilege.

If you don't have data in tables owned by this user and if you do not have other objects (packages, procedures, views, etc.) for this user, you may want to drop this user and create a new user, making sure that you give the user the privileges he needs.
0
mshaikhCommented:
As markgeer says; your best bet is to drop and recreate this user and grant the user all the required privelege
0
vemulayugandharAuthor Commented:
My user has created around five tables and wrote some procedures on that table,now how can i drop that user...
0
cadabraCommented:
Consider the following:
1. Use exp to export the user's schema
2. Assuming you have no LONG type columns, you can use the sql*plus copy command, or create table as select to copy tables and their data to another database user (you may need to perform grants first).
3. Use scripts or Enterprise manager to get all definitions of objects in the schema .
4. You can do selects from dba_objects to create a checklist of objects to re-create after dropping and recreating the user and his objects.
0
Mark GeerlingsDatabase AdministratorCommented:
It is simpler than cadabra indicates.
1. Use export to export the users' schema.
2. From SQL*Plus as a DBA, do:
"drop user [username] cascade;"
3. Create the new user and give that account the security it needs.
4. Use import with "fromuser" and "touser" specified to get everything from the previous user's account into the new user's account.

All of this assumes that there is something seriously wrong with your current user's account.  Try creating another new user first, and make sure that that account can do what it should be able to do, before you do a "drop user" with the current account.
0
DarkOraCommented:
The last time I saw this error was when our SQL Server DBA was trying to link an Oracle table into his SQL Server database through an OLE-DB connection.  We discovered that whenever you have a table with a column that is defined as NUMBER with no precision specified (with precision it would be something like NUMBER(12) as opposed to just NUMBER), it will sometimes generate an inconsistent datatype error due to the way OLE-DB maps to the Oracle datatypes.  If the connection is via OLE-DB you might want to look at all the columns listed as NUMBER and modify them to be NUMBER(38) (unless it is one of the Oracle data dictionary tables--it's not very easy to change those).  If the connection is not OLE-DB, please reject this answer and please give some more detail on what application the user is connecting to Oracle with.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.