Questions tagged [ora-00942]
Table or view does not exist. Cause: You tried to execute an SQL statement that references a table or view that either does not exist, that you do not have access to, or that belongs to another schema and you didn't reference the table by the schema name.
86
questions
66
votes
8
answers
24k
views
Why doesn't Oracle tell you WHICH table or view does not exist?
If you've used Oracle, you've probably gotten the helpful message "ORA-00942: Table or view does not exist". Is there a legitimate technical reason the message doesn't include the name of the missing ...
- 267k
55
votes
12
answers
141k
views
Spring Batch Framework - Auto create Batch Table
I just created a batch job using Spring Batch framework, but I don't have Database privileges to run CREATE SQL. When I try to run the batch job I hit the error while the framework tried to create ...
- 691
37
votes
5
answers
172k
views
ORA-00942: table or view does not exist (works when a separate sql, but does not work inside a oracle function)
When I have a sql statement like select * from table1, it works great, but as soon as I put it into a function, I get:
ORA-00942: table or view does not exist
How to solve this?
- 16.9k
17
votes
1
answer
36k
views
Accessing another user's table within an Oracle Stored Procedure
I'm writing a stored procedure to copy data from one user's table to another schema. Basically, it is a series of INSERT .. SELECT statements such as this:
INSERT INTO GESCHAEFTE
SELECT *
FROM ...
- 13.1k
15
votes
10
answers
83k
views
Getting an exception ORA-00942: table or view does not exist - when inserting into an existing table
I am getting below exception, when trying to insert a batch of rows to an existing table
ORA-00942: table or view does not exist
I can confirm that the table exists in db and I can insert data to ...
- 843
10
votes
2
answers
23k
views
Is there a good workaround to the Oracle IMPDP REMAP_SCHEMA issue with triggers (ORA-39083, ORA-00942)?
One can use the Oracle data pump import tool (IMPDP.EXE) to import one schema into another using the REMAP_SCHEMA option. However there is an issue in that triggers are not properly remapped. This ...
- 1,264
9
votes
3
answers
8k
views
Oracle PLSQL - Declare a cursor on a non-existing table
I want to declare a cursor on a table that does not exist. Of course, my procedure doesnt compile.
This table is a temporary table, and is created by a pre process. It will exist on runtime, but at ...
- 44.5k
9
votes
5
answers
29k
views
ORA-00942: table or view does not exist : How do I find which table or view it is talking about
We're running a java/hibernate app going against ORACLE 10g in TESTING. Once in a while, we're seeing this error:
ORA-00942: table or view does not exist
Is there a way to find out which table/...
- 13.4k
7
votes
3
answers
10k
views
Oracle Create View issue
I am logged in as the dba account and I want to create a view in User1's schema, but selecting data from User2's.
I used the following query:
CREATE OR REPLACE VIEW User1.NewView (Column1) AS
SELECT ...
- 2,345
7
votes
2
answers
8k
views
Can I create Foreign Keys across Databases?
We have 2 databases - DB1 & DB2.
Can I create a table in DB1 that has a relation with one of the tables in DB2?
In other words, can I have a Foreign Key in my table from another database?
I ...
- 1,951
6
votes
2
answers
23k
views
Oracle dba_data_files table doesn't exist
I've been trying to access the dba_data_files table to see whether the autoextend is turned on for my data files. However, even though I'm using Oracle 10g, this table seems to not exist:
Connected ...
- 190k
6
votes
3
answers
7k
views
Can't select from dba_tab_cols from within stored procedure (PL/SQL)
I'm trying to SELECT from the dba_tab_cols view from within a stored procedure. It's not working and I don't know why.
If I execute the following SQL as a query:
SELECT t.data_type FROM dba_tab_cols ...
- 5,425
6
votes
4
answers
13k
views
How to access Oracle system tables from inside of a PL/SQL function or procedure?
I am trying to access information from an Oracle meta-data table from within a function. For example (purposefully simplified):
CREATE OR REPLACE PROCEDURE MyProcedure
IS
users_datafile_path ...
- 1,114
5
votes
3
answers
14k
views
As System in Sqlplus, how do I query another user's table?
According to select name from system_privilege_map System has been granted:
SELECT ANY TABLE
...and lots of other * ANY TABLES.
Plainly running
select * from the_table;
select * from the_table;
....
Pete Brisco
5
votes
3
answers
16k
views
dba_jobs_running: table or view does not exist when trying to access from procedure
Simply querying running jobs using something like
select * from dba_jobs_running;
works fine when executed in my sqldevelopers SQL console.
However, it does not work, when having exactly the same ...
- 4,512
5
votes
3
answers
8k
views
Oracle SELECT statement not working - ORA-00942
Hopefully a simple question.
error_reporting(E_ALL);
ini_set('display_errors', '1');
$c = oci_connect('whatmyusrnameis', 'whatmypwdis', 'host');
if ($c) {
echo 'connection'...
- 11.8k
4
votes
5
answers
27k
views
Facing an error : table or view does not exist
I am using insert statement and trying to insert data into the database table. I am using stored procedures.
But I am getting this error while doing so.
Message: ORA-00942: table or view does
...
- 3,802
4
votes
2
answers
8k
views
Oracle errors handling
I have such code:
DECLARE
e_not_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(e_not_exist, -942);
car_name VARCHAR2(20);
BEGIN
select name_of_factory into car_name from car where car_id = 1;
...
- 5,958
4
votes
3
answers
8k
views
ORA-00942: Can select from "schema.table" but not "table"?
I experienced an ORA-00942 ("table or view does not exist") when executing
select * from brunch
However, no such problem when executing
select * from joe.brunch
May i know what is the issue here?...
- 23.2k
4
votes
3
answers
13k
views
Multi-Schema Privileges for a Table Trigger in an Oracle Database
I'm trying to write a table trigger which queries another table that is outside the schema where the trigger will reside. Is this possible? It seems like I have no problem querying tables in my ...
- 957
4
votes
2
answers
5k
views
Table or view does not exist - Oracle complains about a comma rather than an actual table or view name
I've never seen this before... I have a query that starts off like this:
with q1 as
(select a.V_ID, a.D_ID, a.C_ID,
case when a.percent > 0 THEN 'Y' ELSE 'N' end L_val,
...
3
votes
3
answers
9k
views
question about pl/sql stored program text
I use TOAD to do my PL/SQL development. In TOAD when i type a procedure name and press f4, I can see this procedure's source code. I think TOAD get the source code from v$sqltext view. To confirm my ...
- 27.7k
3
votes
12
answers
38k
views
ORA-00942 SQLException with Hibernate (Unable to find a table)
I'm trying to run a simple Hibernate application, but I get this error:
org.hibernate.exception.SQLGrammarException: could not execute query
java.sql.SQLException: ORA-00942: table or view does not ...
- 22.3k
3
votes
3
answers
1k
views
Problems When Querying Oracle Database For List Of Tables: "table or view does not exist"
In my program I need to access the schema of an Oracle 11g database. I have been trying to get a list of tables using a query like this:
SELECT t.TABLE_NAME, t.OWNER
FROM ALL_TABLES t
WHERE t.DROPPED ...
- 809
3
votes
5
answers
12k
views
Oracle WITH CLAUSE not working?
I'm trying to use a WITH clause in a query but keep getting the message
ORA-00942: table or view does not exist
I've tried to create a simple query just as an example here:
WITH
test AS
(
...
Dan
3
votes
4
answers
14k
views
ORACLE :Are grants removed when an object is dropped?
I currently have 2 schemas, A and B.
B has a table, and A executes selects inserts and updates on it.
In our sql scripts, we have granted permissions to A so it can complete its tasks.
grant select on ...
- 44.5k
3
votes
4
answers
23k
views
'ORA-00942: table or view does not exist' only when running within a Stored procedure
This should be easy pickin's for a PL-SQL person. Before you mark this question a duplicate, please ensure that while the error message may be common that the underlying problem is the same as a ...
- 24.2k
3
votes
3
answers
10k
views
01031. 00000 - "insufficient privileges" while granting System Privileges to the new user
I created a new connection in Oracle SQL Developer. Under this new connection, I created a new user. Now, I'm trying to grant roles and System privileges to this new user. I get the following error ...
- 847
3
votes
2
answers
4k
views
Java does not run prepare statements with parameter
I am using PreparedStatement to query my table. Unfortunately, I have not been able to do so.
My code is as simple as this:
PreparedStatement preparedStatement = connection.prepareStatement(
"Select ...
- 330
2
votes
3
answers
7k
views
Oracle 9i: Synonymed Table Does Not Exist?
I've created a package that contains a stored procedure that I plan to invoke from a separate application. The stored procedure will return a sorted list of all the views and tables in the schema. To ...
- 16.7k
2
votes
1
answer
2k
views
I'm using Django with a remote Oracle database, and getting a "Table does not exist" error
Whenever I execute a simple query using the Django ORM on a table in the remote Oracle database, I get this error:
>>> from apps.dl.models import Article
>>> Article.objects.using('...
- 14.8k
2
votes
3
answers
2k
views
Problem With Simple PL/SQL Program
Here is my simple PL/SQL program:
DECLARE
CURSOR c1 is
SELECT typ, specifikacia_typu, spz FROM Auta
WHERE (substr(spz, 1, 2) = 'KE' OR substr(spz, 1, 2) = 'KS') AND ROWNUM &...
- 82.4k
2
votes
2
answers
1k
views
Oracle table SELECT rights issue
My application has few tables on Oracle where user XYZ is the schema owner. Tables has been created using XYZ. And I would like to have ABCUSER to have CRUD rights on these tables. I have given the ...
- 1,919
2
votes
2
answers
5k
views
Using DB Links on a PL/SQL script throws "Table not found" error
While trying to create a report using PL/SQL I'm finding myself stuck with the following problem, I created a SQL query that access 2 tables on a remote DB using a DB Link, running the query alone ...
- 3,466
2
votes
1
answer
5k
views
Rebuild index through loop in PLSQL
FOR r IN (select 'DRR_DEV.' ||object_name as obj from dba_objects where object_type = 'INDEX'
and owner ='DRR_DEV')
LOOP
l_sql := 'ALTER INDEX '||r.obj||' REBUILD'||'';
execute ...
- 473
2
votes
2
answers
3k
views
Suppress ORA-00942 errors in ddl create scripts
Say you generate ddl to create all your db tables etc via Hibernate SchemaExport etc. What you get is a script which starts with drop statements at the beginning. Not a problem, as I want this. But ...
- 3,624
2
votes
3
answers
1k
views
Fluent Nhibernate Oracle Identifier Too Long - Alias Name Issue
I've tried to do that.
HasManyToMany<YechidotDoarInGroup>(x => x.Col_yig)
.Table("PigToYig")
.ChildKeyColumn("YIG_GROUP_RECID")
.ParentKeyColumn("PIG_GROUP_RECID");
but I've got:
...
- 3,116
2
votes
2
answers
9k
views
Oracle - Unable to drop tables
This question is related to the one I posted yesterday but with further implications.
The situation is: I'm unable to drop ANY table. Here's an example:
SQL> CREATE TABLE FOO (BAR NUMBER) ...
- 355
2
votes
2
answers
5k
views
Query DBA_TABLES and USER_TABLES in PL SQL cursor
I have a cursor in PLSQL procedure to select from DBA_TABLES and USER_TABLES.
CURSOR c1 IS
SELECT DISTINCT(DB.TABLE_NAME) FROM DBA_TABLES DB, USER_TABLES UT WHERE DB.TABLE_NAME = UT.TABLE_NAME AND ...
- 473
2
votes
0
answers
6k
views
How to change schema name in Crystal report 11?
The report is being designed in Crystal Reports XI.
It uses an ODBC connection to an Oracle database using the CR Oracle ODBC driver 4.1.
I need this report to be easily distributed across 14 ...
vijeta
1
vote
4
answers
1k
views
How does one cheaply validate the existence of a column in a table in another schema with Oracle?
The environment is Oracle 9 & 10. I do not have DBA level access.
The problem is to verify that a specific column exists in a specific table, in another schema.
There are two cases to deal with....
- 28.5k
1
vote
1
answer
2k
views
LEFT JOIN on multiple tables ERROR! (Table or view does not exist)
I have the following SQL Syntax:
SELECT firmenstamm.firmen_id,
firmenstamm.firmenname_1,
kreditorenstamm.kreditorenname_1,
debitor.debitoren_id,
debitor....
- 9,287
1
vote
2
answers
4k
views
Can't drop table that was just created
I created a table named dual2. I've a rows there, and can select from it. When attempting to drop it, it produces this error:
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
...
- 720
1
vote
1
answer
9k
views
Dynamic SQL / Exception Handling -00942 - Oracle PL/SQL [duplicate]
Initially i was trying to create an exception to catch a table that didnt exist (-00942).
Based on the feedback from both Alex and Raj, i amended the code to use EXECUTE IMMEDIATE.
This worked in ...
- 153
1
vote
3
answers
322
views
invalid sql statement in servlet
In login page i make validation that user is allowed to enter system ,
i make methode which validate user:
boolean isValidUser(Connection con,String userName,String pass ){}
it works correctly in ...
- 9,698
1
vote
1
answer
6k
views
How to compile SQL in Oracle SQL Developer?
I have a SQL query that has error.
When I execute it i get ORA-00942: Table or view does not exist.
I don't know which table is giving me the error.
I do know that some SQL Editors offer ...
- 63.4k
1
vote
2
answers
1k
views
Troubleshooting ORA-00942
In the middle of an automated test run last night, one of my twenty worker threads blew out; the application was attempting an INSERT. Springframework reports ORA-00942: table or view does not exist.
...
- 55.2k
1
vote
1
answer
2k
views
Oracle 10: Is it possible to access a public synonym to a table in a stored procedure or package?
I've been trying without success to add a reference to a public synonym in a package or stored procedure in oracle for a while, and I'm wondering if there are solutions to this problem short of ...
- 1,175
1
vote
2
answers
2k
views
Entity Framework 4.0 CTP5 Many to Many relation ship with a help table?
i have a question about EF4 again :) sorry i can't find any thing on the net.
The question is i am using CTP5 Code Only to map (many to many), how do i do it???
But i am doing the old fashion way, ...
- 4,123
1
vote
1
answer
3k
views
Weblogic 10.3, JDBC, Oracle, SQL - Table or View does not exist
I've got a really odd issue that I've not had any success googling for.
It started happening with no changes to the DB, connection settings, code etc.
Problem is, when accessing a servlet, one of ...
- 1,569