SQL & PL/SQL :: Receiving ORA-00942 Running View From Different User
May 11, 2010
I have a view in schemaA called viewA for example. The view is quite complex, with multiple joins.I've granted ALL to schemaB to the view, and to all the referenced tables within the view.However, when I run execute the view from schemaB, I get a ORA-00942 table or view does not exist.
SQL> select * from schemaA.viewA;
select * from schemaA.viewA
*
ERROR at line 1: ORA-00942: table or view does not exist
I suspect that Oracle is creating to temporary tables/views internally (or using something that is already there), and that I need to grant access to these temporary objects.
View 25 Replies
ADVERTISEMENT
Nov 23, 2011
An user is running some queries and that's making database hang. I want to view what queries this user is running. Can this be done by session auditing turning on?
View 13 Replies
View Related
May 13, 2011
I am trying to run the following code. The issue i am having is when running it in a function or procedure. (the SELECT statement works on it's own - so why doesn't it would in a procedure?)
SELECT LAST_DDL_TIME from SYS.dba_objects
WHERE object_type='TABLE'
AND OBJECT_NAME = 'CONT_ASSESSMENT'
i get an error saying "PL/SQL: ORA-00942: table or view does not exist"..I a quite new to oracle / SQL.
View 1 Replies
View Related
Sep 14, 2011
I am currently getting an error when I try to access a table from a different schema from my Stored Procedure:
Error: PL/SQL: ORA-00942: table or view does not exist
But when I run the relevant SQL query in SQLPLUSW it is running fine.
cursor cur_prod 3 is
4 select rep.PROD_ID,
5 rep.PROD_NM,
6 rep.PROD_SHIP_DT,
7 from GWIN.T_PROD_SEG rep;
[code]....
View 2 Replies
View Related
Sep 10, 2012
I'm trying to create a procedure but it's giving me errors:
16/3 PL/SQL: ORA-00942: table or view does not exist
4/2 PL/SQL: SQL Statement ignored
3/9 PLS-00341: declaration of cursor 'S_CUR' is incomplete or malformed
63/3 PL/SQL: Item ignored
69/33 PLS-00597: expression 'S_ARRAY' in the INTO list is of wrong type
69/3 PL/SQL: SQL Statement ignored
72/15 PL/SQL: ORA-00942: table or view does not exist
72/3 PL/SQL: SQL Statement ignored
I don't get why its giving table or view does not exists, because when I just execute the SELECT statement, it returned records. weird...
here's my procedure:
create or replace procedure proc_jrp_d_old_77a
as
cursor s_cur is
select /*+ ALL_ROWS */&vtabdate,
cd.cb_cardholder_no,
ac.user_id,
[code]....
View 6 Replies
View Related
Jun 2, 2010
I Need to develop a procedure which inserts data into a table reading from other table. I am executing a insert statement for this.
But im getting below error while compiling the procedure. Quote:Error(54,15): PL/SQL: ORA-00942: table or view does not exist
I am able to execute the insert statement independently. and also able to execute the procedure without this insert.
Created the tables and procedures in the same schema.
View 10 Replies
View Related
Jul 7, 2010
In this scenario(within procedure) 1st cursor return data but 2nd cursor showing error like ORA-00942: table or view does not exit.
declare
cursor r1 is
select table_name,table_partition_name
from abc
where table_partition_name like 'NORMAL_P%'
and base_table_name like 'NORMAL'
and date_column_name like 'C_DATE'
and to_date(to_char(from_date,'dd/mon/yyyy'),'dd/mon/yyyy') between '25-jun-2010' and '27-jun-2010'
order by substr(table_partition_name,19,4);
[code].....
View 3 Replies
View Related
Oct 15, 2012
I have written a function where I am trying to select data from a table located in a different schema. I have asked my DBA to do two things as I am getting Error(14,23): PL/SQL: ORA-00942: table or view does not exist.
a) Grant privileges.
b) Create public synonym.
When I do create the table in the schema where the function is located and compile the function it compiles.
View 4 Replies
View Related
Dec 21, 2010
what is wrong with this:
CREATE OR REPLACE PROCEDURE revoke_object_priviliges_user (
p_username IN VARCHAR2)
AS
l_username VARCHAR2 (30) := upper(p_username);
BEGIN
FOR rec IN (SELECT OWNER,TABLE_NAME,PRIVILEGE,GRANTABLE FROM dba_tab_privs WHERE GRANTEE=l_username)
LOOP
IF (rec.GRANTABLE = 'NO')
[code]....
When i execute it, i get the following errors:
6/12 PL/SQL: SQL Statement ignored
6/61 PL/SQL: ORA-00942: table or view does not exist
8/2 PL/SQL: Statement ignored
8/6 PLS-00364: loop index variable 'REC' use is invalid
View 2 Replies
View Related
Jun 8, 2011
The form actually consists of one block that refers to a table in DB(Datablock). The functionality of the form is to run some logic and then just insert data in the table .This happens on press of a button.
The problem is when I run the form thru' Admin Id it runs successfully but when I run the form through some other user it gives me the error.
The grants on the table are Public. GRANT DELETE, INSERT, SELECT, UPDATE ON XYZ TO PUBLIC;
View 3 Replies
View Related
Jul 28, 2008
I have two schema on the two servers for replication replication is working fine.
i export one schema to another so all the tables exists at both the sites. I am adding objects in the replication group using oracle enterprise manager console.
some of the tables added fine. but some gives me error like.
ORA-23309: object UMESH.PRODUCT_MASTER of type TABLE exists
before adding this objects in the group i do like
SQL> SELECT oname FROM DBA_REPOBJECT ;
ONAME
------------------------------
AC_ACCOUNT
AC_ACCOUNT$RP
AC_ACCOUNT$RP
AC_ACCOUNT_MASTER
AC_ACCOUNT_MASTER$RP
AC_ACCOUNT_MASTER$RP
6 rows selected.
at both the sites. when i add product_master in the group then entries becomes like
SQL> SELECT oname FROM DBA_REPOBJECT ;
ONAME
------------------------------
AC_ACCOUNT
AC_ACCOUNT$RP
AC_ACCOUNT$RP
AC_ACCOUNT_MASTER
AC_ACCOUNT_MASTER$RP
AC_ACCOUNT_MASTER$RP
PRODUCT_MASTER
7 rows selected.
but with the error in and when generate replication support.
SQL> select status,request,message,oname from dba_repcatlog;
STATUS REQUEST
-------------- -----------------------------
MESSAGE
--------------------------------------------------------------------------------
ONAME
------------------------------
ERROR CREATE_MASTER_REPOBJECT
[code]....
sometimes i got error like
ORA-00942: table or view does not exist
when use CREATE_MASTER_REPOBJECT command to create object at master definition site while the the table exists at the master site.but in the same situation other objects are working fine.
View 8 Replies
View Related
Apr 22, 2011
The SELECT at the end of this script fails with "ORA-00942: table or view does not exist". I think I added proper permission via GRANT command.
create table mudd_table (
a number,
b varchar2(10)
);
create user mudd_user identified by mudd_user;
grant select, insert, update, delete on mudd_table to mudd_user;
grant create session to mudd_user;
conn mudd_user/mudd_user@quadoracle;
select * from mudd_table;
View 2 Replies
View Related
Aug 31, 2010
I am receiving an "ORA-00942 Table or View does not exists" error message when I try to run the following SQL statement:
SELECT id_status INTO :ls_id_status
FROM USER_ADMINISTRATION
WHERERTRIM(user_id) = :as_userid;
where :as_userid is an argument passed to the SQL.
Here are some background facts in order to save time regarding actions already taken:
(1) The userid used to login successfully is the same one passed to the above SQL;
(2) I have confirmed that the table does exist - we are using it in an application;
(3) The case of the table name is upper case as seen above and is correct;
(4) The spelling of the table name is correct;
(5) The schema name was prefixed to the table and I still got the same error (not shown above);
(6) The userid that I used is assigned to a role and the role has SELECT permissions to the above table.
View 8 Replies
View Related
Mar 6, 2012
connect user1/user1@dbstring
CREATE TABLE A12
(
A1 NUMBER,
A2 DATE
)
/
GRANT SELECT,UPDATE,DELETE on A12 to USER2
/
DELETE FROM user1.A12
/
--throwing error like ORA-00942: table or view does not exist
connect user2/user2@dbstring
DELETE FROM user1.A12
/
--throwing error like ORA-00942: table or view does not exist
SELECT * FROM user1.A12
/
--no rows returned
Above scenario has happened only for 2 tables out of 1000 tables in my schema.
View 15 Replies
View Related
Jul 22, 2004
i imported a .dmp file but i got this error:
Connected to: Oracle8i Release 8.1.6.0.0 - Production
JServer Release 8.1.6.0.0 - Production
IMP-00003: ORACLE error 942 encountered
ORA-00942: table or view does not exist
IMP-00023: Import views not installed, please notify your DBA
IMP-00000: Import terminated unsuccessfully
Then i run the catexp.sql using SYS user. But i still got that error.
View 20 Replies
View Related
Apr 25, 2012
We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.
This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?
I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....
View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.
View 6 Replies
View Related
Apr 25, 2011
There is a requirement in my database that I want to restrict the user from directly running queries on database from third party tools such as pl/sql developer and toad.
There is a utility in SQL product_user_profile through which this can be done but it is only restricted if you run the query through sql plus. If I want to restrict and (give suppose select,insert) to a user for directly running queries through PL/SQL.
View 1 Replies
View Related
Nov 9, 2011
I have a strange problem when creating a view in user from another user
I have a user called "Cash_tst"
its syntax creation is
-- Create the user
create user CASH_TST
identified by ""
default tablespace CASH
temporary tablespace TEMP
profile DEFAULT
quota unlimited on cash;
-- Grant/Revoke object privileges
grant connect to CASH_TST;
grant dba to CASH_TST;
grant resource to CASH_TST;
-- Grant/Revoke system privileges
grant create any view to CASH_TST;
grant unlimited tablespace to CASH_TST;
I want to create a view
CREATE VIEW TAMER
AS SELECT *
FROM [b]AROFL[/b].RA_CUSTOMER_TRX_LINES_ALL_BEFO
"AROFL" is another user on the same database
when try to create the view "tamer" i got message of
"insufficent privilege"
although i granted "create any view" to the user "cash_tst"
View 12 Replies
View Related
Aug 15, 2013
I have a problem with the code below. I would like to define a variable (exrate1) which I would like to use in another script. Also, the variable should be different for every month(that's why timestamp is between some period).
The problem is that I receive some errors which I don't know haw to fix
View 8 Replies
View Related
Aug 12, 2010
is it possible to view all user that was dropped? how can i know that one user was create by who>? i'm running on multi dba user in my db. are there any history record when we create user?
View 4 Replies
View Related
Feb 9, 2011
We are receiving data from our upstream through text file, they are extracting that text file from their oracle table.In the table in ename colmn some names are having the special character like
Chng
OReilly
We are loading the data from that text file into our oracle tables and the names are loading as it is.Now we are extracting data as text file from our tables which is loaded from or upstream,then the special character is changing as the names are changing as Chng OReilly. We are loading the data from the text file into our tables and the names are loading as
Chng OReilly and i tried to replace the special character with while loading but it is loading as .If I try to change the character with '(Apostrophe) while loading it is changing.
The data type of the column is VARCHAR2(20)
NLS_CHARACTERSET is UTF8
Version Oracle 9i
View 6 Replies
View Related
Feb 9, 2011
We are receiving data from our upstream through text file, they are extracting that text file from their oracle table.In the table in ename colmn some names are having the special character like '
Ch'ng
O'Reilly
We are loading the data from that text file into our oracle tables and the names are loading as it is.Now we are extracting data as text file from our tables which is loaded from or upstream,then the special character ' is changing as ¿the names are changing as Chang Reilly.
We are loading the data from the text file into our tables and the names are loading as Chang O¿Reilly and i tried to replace the special character ¿ with ' while loading but it is loading as ¿.
If I try to change the character ¿ with '(Apostrophe) while loading it is changing.
The data type of the column is VARCHAR2(20)
NLS_CHARACTERSET is UTF8
View 27 Replies
View Related
Jun 30, 2011
how to Refresh materialized view by other user than owner.option is there to give drop any view/table grant to other user. But can't do this as it's a system privileges.
Is there any option to refresh a view without dropping the structure?
View 7 Replies
View Related
Jul 7, 2011
I created the following procedure in my local database.When I executed the procedure SEND_MAIL, recipient is not receiving mail.Do I need any setup in my database?
here is my procedure.
CREATE OR REPLACE PROCEDURE SEND_MAIL (
msg_to varchar2,
msg_subject varchar2,
msg_text varchar2 )
IS
c utl_smtp.connection;
rc integer;
msg_from varchar2(50) := 'Oracle11.2';
mailhost VARCHAR2(30) := '10.137.133.18'; -- local database host
[code]....
View 3 Replies
View Related
Sep 23, 2010
Receiving ORA-29913 and ORA-30653 errors and query does not complete. The queries that receive the error are shown below <> represents substitute your filename; There are actually three different queries that must run to produce an expected output. Those queries run in the order which they appear below:
Query1
select distinct HLQ "highlevel", RESOURCE "RES" from <filename1>
Query2
select distinct ENT.RESOURCE "RES", ENT.ID "PLEX"'
case
when ENT.ACTION is null then nextkey'
else ent.action
end "ACT",
case
when ENT.ACTION is null then ENT.CONDITION_NEXTKEY
end "NRULE",
'TESTSET' "ENTTYPE", replace(My.Owner, '"',' ') "Owner"
from <filename1> ENT, <filename2> ORS
where ENT.f1 = ORS.f2
and ENT.APPLICATION = '<entertypehere>'
Query3
select ENT.ID "AID", ENT.RESOURCE "RES", ENT.ID "PLEX", replace(My.Owner, '"',' ') "Owner", TESTSET' "ENTTYPE" from <filename1> ENT, <filename2> ORS
where APPLICATION = '<entertype>'
and ENT.<f1_column_name> = ORS.<f2_column_name>
Error returned is: "Ora-29913: error in executing ODICEXTTABLEFETCH callout; Ora-30653: reject limit reached"
In the log file of the job that runs these queries, I can also see the following
NULL if (CAS9 = ~) error processing column C5 in row 1 for datafile /home/oracle/DataDir/<filename>_metadata.data
I have checked and it looks like the routine creates ODICEXTTABLEFETCH as it needs it and then throws it away leaving me with Ora-12899 Value too large for column C5 (actual: 13, maximum: 10)
View 2 Replies
View Related
Oct 19, 2012
how is to edit user_sdo_geom_metadata view?
View 6 Replies
View Related
Mar 12, 2009
sample code in OCI in C for receiving records of table in array of structure? Or dynamically storing the result-set in an array..using array of pointers to structure..
View 6 Replies
View Related
Jun 6, 2011
Im facing the problem whenever I try to drop a user. Following thing that I m trying..
system@vahan> drop user knp cascade;
Error at line 1:
ORA-00604: error occured at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
View 4 Replies
View Related
Feb 10, 2011
I am running Oracle 10g on my machine. I have create an ER diagram in Toad Data Modeler, which includes all the keys, contraints etc.I have generated a DDL script which I want to biuld my tables with in Oracle.I have loaded the DDL script using SQL*Plus Worksheet and there are no problems.then tryed to insert some test data into my tables and I keep getting an error code of:
ORA-00942, table or view does not exist.Now I know the tables have been created and also by verifying this with the data dictionary using
select table_name
from user_tables;
TABLE_NAME
=========
Table1
Table2
Table3
.
.
etc
It then displays all 20 of my tables, as above. I am using a Visual Basic front end and I can see all 20 tables listed there also, with attribute names for each table.Also I get the following by typing:
select owner, object_type from
all objects where object name = 'Customer';
OWNER OBJECT_TYPE
------------------ -----------------
SYSADMIN TABLE
View 4 Replies
View Related
Jan 17, 2013
can we change the existing materialized view to normal view? if yes how?
View 2 Replies
View Related
