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
ADVERTISEMENT
May 14, 2012
I am trying to recreate a table after I have renamed the original.
DECLARE
v_prd_detail_seq NUMBER;
v_orig_ddl CLOB;
[Code].....
View 6 Replies
View Related
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
Aug 16, 2012
Task is to create application for almost 10,000 PHD Scholors, i want to know which authtication scheme i used so that each Scholor can access his/her data only after authtication (Login Page).
View 4 Replies
View Related
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
Jun 29, 2011
1.How to check a user has 'create table' privilege?
2.how to check a user has privilege to grant 'create table' privilege to other user ?
View 6 Replies
View Related
Jan 4, 2013
I'm new to oracle DB,i've been given access to Oracle as a user. when i try to create a table under my default schema i get the insufficient privileges error.
How do i check from the system views if i have create table privilege under my own schema?
View 2 Replies
View Related
Apr 27, 2010
We can use the clause "CREATE CLUSTER mycluster.." to create a cluster.
How to check if the cluster exist in my database, so I don't have to create the cluster again.
View 1 Replies
View Related
Mar 5, 2012
I have to create a stored procedure having some 10 cursors and i have to display the data's fetched by select statement in cursors using dbms_output.put_line().
for ex:
-------
CREATE OR REPLACE PROCEDURE PROC AS
CURSOR C1 AS SELECT * FROM EMP;
BEGIN
FOR R IN C1
LOOP
[code].....
My question is while creating procedure i want to check if the object exist in database or not.
If EXIST
NO PROBLEM MY CODE CREATE THE PROC AND EXECUTED FINE
IF NOT EXIST
I don't want an exception to be thrown stating "OBJECT DOES NOT EXIST" I wanted PL/SQL engine not to execute the particular select statement itself and continue executing other select statements.
The reason why have such kind of wierd requirement is my program displays all the CEMLI objects in any 11.5.10 instance. I don't know whether they have installed discoverer or not. if they installed it no problem else my program have to eliminate.EXECUTE IMMEDIATE, REF CURSOR becoz i tried it and works fine.but not able to wrap in WRAPPER UTILITY 8.0 versions.
View 14 Replies
View Related
Feb 18, 2013
I have a table EMPLOYEE with columns employee_id and status. I have a requirement that when an employee status is getting changed, then even its linked employee's status also should be changed to the same status value. For this, I need to handle the updating of linked employee's status using a trigger.
Since we get mutating trigger issue when try to update the originating table, I am trying to go with the approach of "in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is an AFTER row trigger that updates a temporary table, and the second an AFTER statement trigger that updates the original table with the values from the temporary table".
But however I am still facing the same issue.
Test case:
CREATE TABLE EMPLOYEE
(
EMPLOYEE_ID VARCHAR2(1),
STATUS NUMBER(9)
);
INSERT INTO EMPLOYEE VALUES ('A',1);
INSERT INTO EMPLOYEE VALUES ('B',1);
commit;
[code]....
Also, any alternate options (rather than using 2 triggers with temp table).
View 7 Replies
View Related
Feb 14, 2012
i am not able to rename a column for original table, once i create a new one with Create table as select i can rename the new table column, but not the original one
SQL*Plus: Release 11.2.0.1.0 Production on Tue Feb 14 13:10:10 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> show user
USER is "WEBSYSTEMD6"
SQL> alter table cbe rename column tnum to tnum_old;
alter table cbe rename column tnum to tnum_old
*
ERROR at line 1:
ORA-00900: invalid SQL statement
SQL> create table cbe_test as select * from cbe;
Table created.
SQL> alter table cbe_test rename column tnum to tnum_old;
Table altered.
SQL>
View 12 Replies
View Related
Mar 28, 2013
what is the the use of views and synonyms if changes made to them affects the original table contents?It is data insecurity know?
View 2 Replies
View Related
Jul 3, 2013
I've moved a package to a new schema and all the packages in the original schema that reference the moved package now fail to compile. The moved package has had a public synonym created and the execute privileges assigned to the original schema by role. what am i missing? Using 11gR2 version 11.2.0.3.0
View 2 Replies
View Related
Aug 11, 2010
I've got a oracle install [non production, but devel] that is a tad screwed up. We moved the box and as a result changed the hostname to match the new naming scheme. Ever since then OracleEM has been somewhat confused. In anycase, I don't want OEM anyways now. Plan is to learn SQLplus.
That being said I've used emctl to shut down dbconsole, but it seems there is something somewhere that keeps restarting 2 processes that like to sit around and take up 100% cpu. I can kill them, they stay dead for a few hours then crop up again.I was able to find this out about them:
[jmacdonald@devoracle ~]$ ps auxwww|grep 2033
oracle 20334 84.1 12.3 994052 255824 ? Rs Aug10 1740:43 ora_j000_orcl
oracle 20336 80.9 14.1 998140 294288 ? Ss Aug10 1674:18 ora_j001_orcl
And then this, which caused me to conlucde its OracleEM:
SELECT sess.process, sess.status, sess.username, sess.schemaname, sql.sql_text
FROM v$session sess,
v$sql sql
WHERE sql.sql_id(+) = sess.sql_id
AND sess.process in (20334,20336)
[code]...
View 6 Replies
View Related
Jul 10, 2012
I have table EMP & DEPT.
Can you give me sample "check" constraint so that when I enter an EMP (with status='active') without matching DEPT entry, the process will fail?
It will allow to enter EMP with status='inactive' only even if no matching DEPT entry. So foreign_key will not apply.
View 21 Replies
View Related
Mar 29, 2013
How can I create a trigger to check if synonym already exists in db and if exists then don't create synonym.
my work: ( this is just like an outline i prepared)
select * from all_synonyms;
declare
s_exists number;
begin
-- check whether the synonym exists
select 1 into s_exists from all_synonyms;
-- an error gets raise if it doesn't
exception when no_data_found then
-- DDL has to be done inside
execute immediate ' create or replace synonym';
end;
/
View 16 Replies
View Related
Aug 23, 2011
how can i create procedure to check username and password.
View 10 Replies
View Related
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
Jan 18, 2013
delete table if exist
How do we write this code in oracle / sqlplus
View 4 Replies
View Related
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
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
Apr 29, 2011
What is the difference between CREATE EXTERNAL TABLE Vs CREATE TABLE .?
Is CREATE EXTERNAL TABLE included in CREATE TABLE?
View 3 Replies
View Related
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
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
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
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
