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


ADVERTISEMENT

SQL & PL/SQL :: Delete Table If Exist

Jan 18, 2013

delete table if exist

How do we write this code in oracle / sqlplus

View 4 Replies View Related

SQL & PL/SQL :: Two Index Exist On Same Table

Apr 19, 2011

Can there be an impact on performance if below two index exists on the same table:

t_idx(col1,col2, nvl(col7,col6));
t_idx1(col1,col2,nvl(col6,col7));

View 5 Replies View Related

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

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

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 :: How To Check Record Exist In Table

Feb 13, 2013

Usually we check 1 row of data in table and compare if they exist, i want to compare different set of row all together to comapre and take the decisions

eg.

emp table_source
d_key emp_no invol
1 103499 1
1 99262 2
2 103499 1
3 103499 1
3 99262 2

target table

emp_no invol
103499 1
99262 2
103499 1

in the above example i have to check for d_key for set of row exist in target or not, so by above example d_key 1 and 3 has save value in 2 different rows so i want to check if the combination of rows

103499 1
99262 2

as a set appear in source then do nothing else insert so first time d_key 1 both records will insert but for d_key has same set in 2 rows will not be inseted. the number of row can be more than 2 also . so for the given key if we have 3 rows in source then i have to compare all the 3 same set of row in target exist of . in the target i don't have any extra column.

View 3 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

Insert Into Table If Not Exist Otherwise Return Error

May 7, 2011

I am using oracle 10g to create a user register application. what i want is to insert the user information (like Email ,username, password, etc.) into a table. i am able to insert the data into the table but what i want is to check before inserting that same email,and username doesnt exist. if it does it should return some error like the email or username already exist.

View 2 Replies View Related

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 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 :: Check If Table Exist In Scheme - If Not Create As Original DDL

Aug 13, 2013

I have 20 or so tables, partitioned by range, indexed etc...soon these tables will be gone and i have to recreate them with the same definitions. I have to write a procedure(or script, it's up to me) which must:

1- check if table A exist in the scheme
2- if not create the table as the original ddl.

for 1 i've used smth like
-------------------------
SELECT count(*)
INTO a
FROM user_objects
WHERE object_name='A';

[code]....

so my question is how and what is the cleanest and fastest way to do this.? it's a lot DDLs,

View 17 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

PL/SQL :: Code_combination_id Doesn't Exist In Outer Query Table

Aug 20, 2013

I have query in which having some issues with outer join.When I run the inner query, I am getting 121 rows and when i put outer query and run it, I am getting 69 rows coz the code_combination_id does not exist in the outer query table.But even when I say ( + ) , its not giving 121 rows..  

SELECT  DISTINCT t1.* --hr3.NAME prj_org --, p.segment1 prj_no, p.org_id --pt.task_number, pt.task_name,  --paei.expenditure_type, pacdl. acct_ currency_code functional_curr from        (    SELECT DISTINCT gll.ledger_id, gljeh.period_name "Fiscal Period", gll.NAME "Company Name", gljeh.NAME "Journal Name ",  gljeh.je_source "Accounting Document Source",  gljeb.NAME "Journal batch name", gljel. accounted_ dr,glcc.code_ combination_ id,glcc.segment1,glcc.segment2,glcc.segment3,glcc.segment4,glcc.segment5,glcc.segment6,glcc.segment7,glcc.segment8,gljel. accounted_ cr  FROM gl_je_headers gljeh, gl_je_batches gljeb,  gl_je_lines gljel,gl_code_combinations glcc, gl_ledgers gll  WHERE gll.ledger_id =
[code]....

View 5 Replies View Related

How To Enable Oracle Advance Compression For EXIST Partitioned Table

Feb 8, 2013

I have to enable oracle advance compression for existing table which PARTITION BY RANGE then SUBPARTITION BY HASH.

ORacle version: 11.2.0.2.0

View 5 Replies View Related

Restrict Values To English Without Requiring That A Value Exist In Request Table

Jul 8, 2009

I have the following tables:

REQUEST
----------------------
request_id
item_name
unit_of_measure
UOM_CODES_TL ("tl" means translation - this table has the same record 3 times, once for English, once for Spanish, and one for France)
----------------------
unit_of_measure
description
language
[code]....

So what I'm trying to do with the outer join ((+)) is to get the request record, regardless of whether the r.unit_of_measure is null. That works as long as I don't include the underlined portion above. As soon as I do that it only returns results where r.unit_of_measure is not null. That all makes sense to me, but I'm wondering how I can restrict the values to English without requiring that a value exist in the request table.

View 4 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

Application Express :: How To Show Alert When Data Not Exist In Table And Clear Field

Jun 8, 2012

Recently i created a form which contains emp_name,deptno,supervisor_name.For emp_name if i type the emp name (eg:allen) it will show me a list of employees named allen as a select list.For that i used a jquery script on page header

<link rel="stylesheet" href="URL.....
<script type="text/javascript" src="URL....
<script type="text/javascript">
[code]....

and i have created a dynamic action for emp name to fetch the deptno,supervisor_name if select the employee name it is working fine is if i type a emp_name which doesn't exist in the employee table it will show me a alert message employee does not exist and it will clears all the fields.

View 0 Replies View Related







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