SQL & PL/SQL :: Accessing V$mystat Gives Table Or View Does Not Exist

Jul 1, 2010

I am using the sid of v$mystat to create a unique filename in my pl/sql procedure.

I have granted access to v$mystat to the user that is accessing it from system user as:

SQL>GRANT SELECT ON V_$MYSTAT TO ar;
Grant succeeded.

SQL> commit;

Commit complete.

now when i login as user ar and do a select on v$mystat it works fine:

SQL> select sid from v$mystat WHERE ROWNUM = 1;

SID
----------
290

However, when i do the same from my PL/SQL procedure it throws an error saying :

SQL> @FILECREATE
53 /

Warning: Function created with compilation errors.

SQL> show errors
Errors for FUNCTION FILECREATE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
22/8 PL/SQL: SQL Statement ignored
22/35 PL/SQL: ORA-00942: table or view does not exist

My PL/SQL function can be found as an attachment.

View 11 Replies


ADVERTISEMENT

SQL & PL/SQL :: Materialized View - Table Or View Does Not Exist

May 2, 2012

I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.

EXEC DBMS_MVIEW.REFRESH('pro_mview','C');

But I am getting the below error.

*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1

I am able to fetch the data from that materialized view(pro_mview).

View 3 Replies View Related

Table Or View Does Not Exist Error?

Apr 25, 2007

I created a table by doing so:

create table Playlist (Artist string, Album string, Track int, Song string, Genre string);

But I get the following error message when trying to perform a query:

SQL> select artist
2 from playlist;
from playlist
*
ERROR at line 2:
ORA-00942: table or view does not exist

I entered data in for the artist, so why doesn't it work?

View 4 Replies View Related

ORA-00942 - Table Or View Does Not Exist

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

SQL & PL/SQL :: ORA-00942 - Table View Does Not Exist

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

SQL & PL/SQL :: ORA-00942 / Table Or View Does Not Exist

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

SQL & PL/SQL :: ORA-00942 - Table Or View Does Not Exist

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

PL/SQL :: ORA-00942 - Table Or View Does Not Exist?

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

ORA-00942 / Table Or View Does Not Exist

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

Client Tools :: Table Or View Does Not Exist

Mar 1, 2010

In toad 9.1.0.62 version, when i open a package in schema browser. It shows table or view doesn't exist.

But the package seems to be in valid state.

I also used other schema's to open that schema packages it opens with no problems.

View 5 Replies View Related

Forms :: ORA-00942 - Table Or View Does Not Exist

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

Replication :: ORA-00942 / Table Or View Does Not Exist

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

SQL & PL/SQL :: Creating Trigger - Table Or View Does Not Exist

Apr 6, 2012

When i try to create a trigger , i ended up with error.

SQL> create or replace
2 TRIGGER LOGON_TRG AFTER LOGON ON DATABASE
3 BEGIN
4 INSERT
5 INTO
6 user_audit_log
7 SELECT
8 user#,

[code]....

Warning: Trigger created with compilation errors.

SQL>
SQL> show error
Errors for TRIGGER LOGON_TRG:
LINE/COL ERROR
-------- -----------------------------------------------------------
2/5 PL/SQL: SQL Statement ignored
17/17 PL/SQL: ORA-00942: table or view does not exist

The command used to resolve the error is

GRANT SELECT ON v_$session TO jack;

Jack user has sysdba privilege. My question is 'sysdba' is a super and special user which has all the privileges in database. Then why does it need SELECT privilege on v$session to user to create the trigger?

View 3 Replies View Related

SQL & PL/SQL :: ORA-00942 - Table Or View Does Not Exist Even After GRANT

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

PL/SQL :: In Line - Table Or View Doesn't Exist?

Jul 26, 2012

I have a SQL query with a construction like:
select a, b
from tablea
,select * from (select a as a from tableb)
where ....

The issue is that in sqldeveloper I don't get any errors and the select works fine.I use the same select in myEclipse report builder and I get an table or view doesn't exist.

View 2 Replies View Related

SQL & PL/SQL :: Create Directory - Table Or View Does Not Exist

Oct 2, 2010

I am trying to do the following-

CREATE DIRECTORY my_dir AS 'd:oraclefiles';
GRANT READ,WRITE ON my_dir TO PUBLIC;

The above throws up an error-

ORA-00942: Table or view does not exist

View 9 Replies View Related

Oracle Store Procedure - Table Or View Does Not Exist

Nov 21, 2011

I have created this store procedure:

create or replace PROCEDURE INSERT_TESTTABLE
(
PrimaryKey IN NUMBER
,One IN VARCHAR2
,Two IN VARCHAR2
,Three IN VARCHAR2
,Four IN VARCHAR2
[code].......

And I get this error: Error(15,13): PL/SQL: ORA-00942: table or view does not exist

View 1 Replies View Related

SQL & PL/SQL :: DELETE Not Working / ORA-00942 - Table Or View Does Not Exist

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

Server Utilities :: ORA-00942 / Table Or View Does Not Exist

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

Server Administration :: Unable To Drop User - Table Or View Does Not Exist

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

SQL & PL/SQL :: Create Materialized View For Accessing Data

Feb 29, 2012

1.As we can create materialized view for accessing data from other schema but same database. will it be effective or it will act as a normal view.

2.Will materialized views can be created in Fast mode for the above scenario?

View 4 Replies View Related

SQL & PL/SQL :: ORA-12003 / Materialized View (xxxx) Does Not Exist

Oct 18, 2013

When I try to drop the table it gives "ORA-12083: must use DROP MATERIALIZED VIEW ..." . When I try to drop the materialized view it gives "ORA-12003: materialized view 'xxxx' does not exist".

Database -Oracle10g
OS- SUSE Linux

View 9 Replies View Related

Accessing Something From Table Not Joined To Others

Jun 17, 2008

I have a schema whereby a table is not joined with other tables.

the info on that table can be gotten manually (by doing a query) and then using that info in another query. so is there a way of getting info from that table?

View 8 Replies View Related

SQL & PL/SQL :: Accessing Dmp Via External Table

Aug 5, 2011

when i am writing dump from external table, it is accessing records from dump.but when i am trying to access other dumps(create thru expdp) it is giving error.the logic i am following is mentioned below-

CREATE OR REPLACE DIRECTORY "DIR_GMS" AS 'D:Gopal_works est_env_files'

GRANT READ ON DIRECTORY dir_gms TO gopal;
GRANT WRITE ON DIRECTORY dir_gms TO gopal;

-- creating dump file in directory

CREATE TABLE emp_ext
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('emp_ext_dmp.dmp')
)
[code]......

i am able to see records.

New point:
-- taking export thru expdb
expdp hr/hr tables=EMPLOYEES directory=DIR_GMS dumpfile=HR_EMP.dmp logfile=expdpEMP.log
then i created one EXTERNAL TABLE TO access it.

CREATE TABLE emp_xt (
EMPLOYEE_ID NUMBER(5),
FIRST_NAME VARCHAR2(50),
LAST_NAME VARCHAR2(50))
ORGANIZATION EXTERNAL (
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY dir_gms
LOCATION ('HR_EMP.DMP')
);

while accessing, it is giving error:

SELECT * FROM EMP_XT

ORA-29913: error IN executing ODCIEXTTABLEOPEN callout
ORA-31619: invalid DUMP FILE "D:Gopal_works est_env_filesHR_EMP.DMP"
ORA-06512: AT "SYS.ORACLE_DATAPUMP", line 19

View 13 Replies View Related

SQL & PL/SQL :: Accessing XML Values From A Table?

Jan 29, 2013

i have a table which has 2 columns.1st column has userId and the other contains an xml data as a link.on clicking that link a new file opens containing the data in xml format.

<fields>
<field key="Public Email">piyush@chand.com</field>
<field key="Location">bangalore</field>
<field key="Website" />
<field key="Birthday">0001-01-01 00:00:00</field>
<field key="Gender">Male</field>
<field key="Language">English</field>
</fields>

i need to access location of a particular userId.How can i do that?

View 6 Replies View Related

SQL & PL/SQL :: Accessing A Table Contains Array In Remote DB?

Jan 30, 2009

How to access (create Synonym and Materialized View) a Table contains an Array of type object which is on Remote Database connecting through DBLink.

View 27 Replies View Related

Replication :: Block Corruption While Accessing Some Table

Mar 16, 2011

The database is running in archivelog mode and we have a standby with Maximum performance.There is no RMAN backup..We have noticed there is block corruption while accessing some tables.Now i would like to know are the corrupted blocks also replicated to the physical standby? Is there a way to recover the data from these corrupted blocks without shutting down the database ?

View 1 Replies View Related

SQL & PL/SQL :: Insert Statement In Trigger Accessing And A Select From A Table

Jun 6, 2010

In a trigger(on update of a table t1) I am trying to write, I am doing an insert on t2 accessing ':new' values of the update on t1.

But in my Insert statement, I am having get one of the column values from another table. How can I write my insert statement in such a way as to insert values contained in ':new' pseudo columns and a select from another table. Below is my insert statement in the trigger :
-------

IF (:old.GROUP_YELLOW <> :new.GROUP_YELLOW) THEN
INSERT INTO TEST.W_THRESHOLD_LOG
(THRESHOLD_LOG_WID, CHANGE_DATE, MEASURE_TYPE_WID, MEASURE_NAME, CUSTOMER_WID, CUSTOMER_NAME, USER_ID, CHANGED_ITEM, PREV_VALUE, NEW_VALUE)
VALUES(TEST.W_THRESHOLD_LOG_SEQ.NEXTVAL, SYSDATE, :new.MEASURE_TYPE_WID, 'Rolling Stabilty' , :new.CUSTOMER_WID, 'Customer1', 'User1', 'GROUP_YELLOW', :old.GROUP_YELLOW , :new.GROUP_YELLOW);
END IF;
-------

In the above code if the hardcoded value 'Customer1' need to be picked from another table,
i.e .

SELECT NAME FROM W_CUSTOMER_DIM WHERE CUSTOMER_WID = THRESHOLD.CUSTOMER_WID

how can I rewrite my query to the above value from the select into my insert statement..?

View 24 Replies View Related

SQL & PL/SQL :: Writing Procedure In User 2 Schema Accessing Table Product - Not Compiled?

Dec 17, 2010

I have got 2 users as user1 and user2.I have used the following statements from user 'user1':

create role GENEVAOBJECTS;
grant select, insert, update, delete on PRODUCT to GENEVAOBJECTS;
grant GENEVAOBJECTS to user2;

In the above statements, product is a table. Now, I could able to access this table from user 'user2'. But however if I write a procedure in user2 schema accessing the table product, then the procedure is not getting compiled.

create or replace procedure test_prc as
v_test number(9);
begin
select product_id into v_test
from PRODUCT where rownum=1;

[code]...

why I cannot access that table from procedure?

View 8 Replies View Related

SQL & PL/SQL :: Table Exist Or Not

Feb 3, 2012

i wrote a program to that takes tablename as input parameter and returns true or false based on table exist or not

the below code works differently when table has data and when table does not have data ? how to improve the below code to make sure my function always retuns true if table exists and false if does not exist regardless of 0 records or more than one record

CREATE OR REPLACE FUNCTION is_tab_present_g (pi_tab_name IN VARCHAR2)
RETURN BOOLEAN
IS
row_cnt NUMBER := 0;
sql_stm VARCHAR2 (4000);
l_tab_name VARCHAR2 (4000);

[Code]....

my verification block

BEGIN
if is_tab_present_g('chk_pk') then
dbms_output.put_line('yessssss');
execute immediate 'drop table chk_pk cascade constraints';
else
dbms_output.put_line('nooo');
end if;
END;
/

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved