PL/SQL :: Creation Of A Table After Checking Whether It Is Existing

Apr 22, 2013

declare
vnum number;
vname varchar2(50):='t1';
begin
begin
select count(*) into vnum from dba_tables where table_name=vname;
dbms_output.put_line('table count '||vnum);
exception

when others then

vnum := 0;
end;
begin
if vnum>0 then
execute immediate 'drop table '||vname;
dbms_output.put_line('table dropped');
end if;
exception

when others then

dbms_output.put_line('table does not exists');
end;
execute immediate 'create table '||vname ||' ( n number)';
dbms_output.put_line('table created');
end;

View 14 Replies


ADVERTISEMENT

SQL & PL/SQL :: Cluster Creation On Already Existing Table

Feb 20, 2012

I have table property and flat , in both of these tables I have cvp_id common colomn .Now I want to add cluster on this colomn so how can I add cluster to table which is already exists.

View 3 Replies View Related

Specify Existing Template For Database Creation

Nov 7, 2013

During the database installation the two nodes after all the prerequisite check and copy of files i am getting the below error message 

"Template4 General_Purpose.dbc does not exist. Please specify an existing template for database creation". 

How can i overcome this error ? I am installing Oracle 11g R2 11.2.0 On Oracle Enterprise Linux (5.1.19) running on [Oracle Virtual Box 4.1.12 ]. Also what are the steps to uninstall the previously installed db_home1 which failed due to this error. 

View 1 Replies View Related

ASM Disk Groups Creation After ASM Instance Creation

Jul 9, 2012

During ASM Disk Groups creation after the ASM instance creation, receive the following error: Disk Group ORAASMGROUP2 already exists. Cannot be created again

The Grid infrastructure was deinstall one time and still the same issue.

View 4 Replies View Related

SQL & PL/SQL :: New Table Creation From Different Databases?

Apr 9, 2013

I have two tables having same name in two different databases.i have to create a new table with these two tables in a new database.

But if one table have 2 columns and another table having three columns I have to create a new table with three columns..

View 6 Replies View Related

SQL & PL/SQL :: External Table Creation

May 23, 2011

i had small query on external tables. i had (.csv) file outside the database. In this file, one column will be added monthly. i need to create the external table dynamically by adding the column for every month.

How the procedure can be created for this requirement.

View 9 Replies View Related

Creating Table From Existing Table In Another Schema?

Jan 4, 2009

I am creating a table from another existing table in another schema. The existing table contains data. When I am using the query- create table m _voucher as select * from ipm.m_voucher,I am getting the whole data of m_voucher but I want empty m_voucher table, so what will be the query to get the empty m_voucher table?

View 4 Replies View Related

Create Temp Table From Existing Table

Sep 2, 2010

I want to create temp table, for this i am using:

CODEcreate global temporary table help_temp
as
select * from help;

but this is creating only the table structure, not copying the table data.

View 5 Replies View Related

Adding Check After Table Creation

May 23, 2009

I created a table but I want to add the Unique check to it as I forgot to apply it to the table when I created it.Is it possible to make the field Unique after having created the table or do I have to drop the table and re-create it?

View 3 Replies View Related

Table Creation Inside Function

Feb 25, 2012

i am trying to create table inside function where in after creating table when am trying to access the table with select statement oracle is throwing error 'Table/view doesnot exist -00942', below is the code snippet

create or replace function example (mkey in varchar2) return varchar2
is
g_key varchar2(100);
l_tbl_ntext exception;
pragma exception_init(l_tbl_ntext , -942);
begin

begin
execute immediate 'select * from example1';

exception
when l_tbl_ntext then
null;
end;
execute immediate 'create table example1(skey varchar2, g_key varchar2) storage(buffer_pool, keep)';
end example;
/

select * from example;

View 8 Replies View Related

Relationship - Table Creation With Keys Looks?

Feb 24, 2007

the relationship on my script.What I want to do is make it possible to assign many courses to a student.But I have read a few books on foreign keys and primary keys, and I still dont get how to go on doing this....

This is how the table creation with the keys looks right now...

 CREATE TABLE CAMPUS (
   CAMPUSID            NUMBER(4) NOT NULL,
   NAME                VARCHAR2(80) NOT NULL,
   CONSTRAINT CAMPUS_PK PRIMARY KEY (CAMPUSID));

[code]...

View 3 Replies View Related

SQL & PL/SQL :: Creation Of Temp Table Within Procedure

Aug 17, 2010

I need to create a temp table within a stored procedure. I want to apply some logic on the table, and then delete it when it is completed in runtime -- all inside the stored procedure.

View 13 Replies View Related

SQL & PL/SQL :: Dynamic Table Creation Using Timestamps?

Nov 28, 2010

I have a requirement to create the table monthly.The tablename will be the o/p of the following select query.

select 'TBR_AND_SECURITY_DEPOSIT'||TO_CHAR(sysdate, 'Monyy') from dual

I am trying to create the table for creating the backup of the existing table TBR_AND_SECURITY_DEPOSIT.

create table (select 'TBR_AND_SECURITY_DEPOSIT'||TO_CHAR(sysdate, 'Monyy') from dual) select * from TBR_AND_SECURITY_DEPOSIT

but it is throwing error as invalid table name.

View 2 Replies View Related

SQL & PL/SQL :: Table Creation With First Column To Be Sequence

May 6, 2010

I want to create a table, whose first column , patient_id, should be a sequence.

View 9 Replies View Related

SQL & PL/SQL :: Assign Sequence Name During Table Creation?

Aug 22, 2011

is it Possible to Assign the Sequence Name During the Table Creation.

View 1 Replies View Related

SQL & PL/SQL :: Nested Table Creation Error

Apr 12, 2010

I am getting compilation error when create nested table type depend on table structure

SQL> CREATE OR REPLACE TYPE typ$nttb$1 IS TABLE OF emp%rowtype
2 ;
3 /

Warning: Type created with compilation errors.

SQL> show error
Errors for TYPE TYP$NTTB$1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/29 PLS-00329: schema-level type has illegal reference to SCOTT.EMP
SQL>

View 5 Replies View Related

Index Creation In Partitioned Table

Jul 20, 2012

Facts:
Oracle 10.1.0.5
AIX 5.3

My table, HMTX have 10 partitions each of one have 6 millions of rows (average). We have 7 partitioned LOCAL indexes in that table.
Every month we load data into a new partition (6 million of rows aprox) and drop the oldest partition in table HMTX.

In order to do that we have a script that contain the next statements:

drop of all indexes
drop index n1;
drop index n...;
drop index n7;

[Code]...

create indexes again with tha same storage and degree parameters
CREATE INDEX hmtx_TST_N1 ON hmtx (campo1, campo2, campo3 .... campo8)
TABLESPACE xxxx
PCTFREE 0
INITRANS 2

[Code]....

My problem is in rhe index creation section, despite use parallel with degree 8 and nologging the index was created in :

Elapsed: 02:43:50.85.

In past months that index was created in :
Elapsed: 01:43:36.94
Elapsed: 04:48:31.24
Elapsed: 00:57:16.28

there are another way in order speed the index creation ?? o another way to disable ths index ??

View 4 Replies View Related

SQL & PL/SQL :: How To Add Row Dependencies In Existing Table

Jul 20, 2012

How to add ROWDEPENDENCIES in an existing table. I mean How to Alter any table to add ROWDEPENDENCIES..

View 2 Replies View Related

SQL & PL/SQL :: Partition Of Existing Table

Nov 30, 2011

We have a transaction table and has 30 million rows. The table is not partitioned till date. We need to create partition on this table. We had an idea of moving this data to a temporary table and create partition[range]on the original table and move the data back.

View 5 Replies View Related

SQL & PL/SQL :: Update Existing Table

Apr 24, 2012

I want to either UPDATE or add a new column with ROWNUMBER() OVER an column on a table

the output will shows like below

empID DeptNo New_column Start_Date
11 7778 1 01-02-2010
11 3400 202-06-2010
25 4444 103-02-2010
25 7775 204-07-2011
25 7777 305-02-2012
30 2223 109-02-2012

the forth one column is in date format and I want to update table with respect to order by the column start_date

View 1 Replies View Related

Dynamic Pivot Table (Partition) Creation

Oct 31, 2010

I have 2 tables , which can be mapped with a common column and the second table's rest of the columns should become like columns(like pivot) and in the second table itself 1 more column called value , this sum of the value becomes a value to the pivot column value. For this I am using CASE structure and I am writing the individual case for each column , but I am sure after some extent case don't work at all and I would like to do it dynamically( when ever the new entry will come into the source table , my proc has to pick automatically the new value and put the new value as part of pivot structure) , so pls share your inputs and if possible provide some sample code.

View 3 Replies View Related

Server Utilities :: Table Creation - SQL Loader?

Aug 2, 2010

Check table creation script

CREATE TABLE "SCOTT"."TEST_USER"
("TX_SID" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"TX_FIRST_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"TX_LAST_NAME" VARCHAR2(30 BYTE) NOT NULL ENABLE,

[code]...

Here i'm loading data into these three tables through sql loader. Here is the control file

OPTIONS (SKIP=1,ROWS=5)
LOAD DATA
INFILE 'C:SQL LOADER DEMO estuser_data_lat.csv'
INTO TABLE TEST_USER

[code]...

Here are the two function which i'm calling from sql loader control file

CREATE OR REPLACE FUNCTION get_role_id(p_role_name VARCHAR2)
RETURN NUMBER IS
lv_role_id NUMBER;
BEGIN

[code]..

i'hv attached the testuser_data_lat.csv file, which is the data file.Command line

C:SQL LOADER DEMO>SQLLDR scott/sc CONTROL=rd_users_control.ctl

Now let me tell u what is happening Whem i'm running the above sqlldr, log is generating saying

Record 1: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")
Record 2: Rejected - Error on table TEST_ROLE, column ID_ROLE.
ORA-01400: cannot insert NULL into ("SCOTT"."TEST_ROLE"."ID_ROLE")

[code]...

But when i remove

INTO TABLE TEST_ROLE(
TX_SID POSITION(1:3) CHAR,
ID_ROLE "get_role_id(:ROLE_NAME)" ,
TX_CREATED_BY CONSTANT "SYSTEM",

[code]...

from Control file, data is getting popupalated in TEST_USER and TEST_TITLE similarly if remove

INTO TABLE TEST_TITLE(
TX_SID POSITION(1:3) CHAR,
ID_TITLE "get_title_id(:TITLE_NAME)" ,

[code]...

from Control file, TEST_USER and TEST_ROLE is getting populated.

Here RD_ROLE_MASTER script

CREATE TABLE RD_ROLE_MASTER (
"ID_ROLE" NUMBER(38,0) NOT NULL ENABLE,
"TX_ROLE_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE

[code]...

Here is RD_TITLE_MASTER script
CREATE TABLE RD_TITLE_MASTER(
"ID_TITLE" NUMBER(38,0) NOT NULL ENABLE,
"TX_TITLE_NAME" VARCHAR2(25 BYTE) NOT NULL ENABLE);

Insert into RD_TITLE_MASTER (ID_TITLE,TX_TITLE_NAME) values (7,'RED_LOB_ESCALATION_L1'); what is the problem?

View 1 Replies View Related

SQL & PL/SQL :: Partition Creation Based On Other Table Values

Feb 12, 2013

i am trying to create Procedure which will create the partitions based on the other table date values one per each day.

CREATE OR REPLACE PROCEDURE PARTITION_TEST(PART_DATE_TABLE IN VARCHAR2, TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2)
AS
V_PART_NM VARCHAR2(20);
V_PART_CNT NUMBER;
V_DATE DATE;
V_SCHEMA_NAME VARCHAR(15);

[Code]..

It is not creating the partitions and even not giving any errors.

View 4 Replies View Related

PL/SQL :: How To Find Out Initial Date Of Creation Of Table

Nov 7, 2013

I have a table which contains some data. But we are dropped and recreated the same table several times. Now I wanted to know that when this table created initially. How to findout the date of creation(very first time). 

View 13 Replies View Related

SQL & PL/SQL :: Table Creation - Facing Error ORA-02438?

Oct 18, 2012

i want to create a table to perform a task when i insert client account details in this table then check the account type is in(S,C,R) and balance should be also check when account type S then balance >=5000, account type C then balance >=10000 and account type R then balance >=5000 but when i run this query then facing error "ORA-02438: Column check constraint cannot reference other columns"

SQL> ed
Wrote file afiedt.buf

1 create table kcb_acc_tab
2 (
3 accno varchar2(20) constraint accno_pk primary key,
4 name varchar2(20) constraint name_nn not null
5 constraint name_chk check((substr(name,1,1) between 'A' and 'Z') and name=upper(name)),

[code]....

View 4 Replies View Related

SQL & PL/SQL :: Table Creation - Import Data In Oracle?

Sep 20, 2012

i have 1 xcel sheet in formatted data i want to import data in oracle table .. how do i do dat??

View 5 Replies View Related

Getting Timestamp For Existing Records In Table?

Nov 15, 2010

I need to get the timestamp for all the existing records in my table...I am having one user definition field, is this possible?

View 4 Replies View Related

SQL & PL/SQL :: Modifying Existing Constraint On Table

Apr 1, 2013

Can we modifying the existing constraint on table?I have table level UNIQUE constraint on 3 columns of table.I need to modify the UNIQUE constraint to 2 columns? Instead of dropping/recreating the constraint, is there any option to modify the existing constraint

Ex

CREATE TABLE TEST_CONST(NUM1 NUMBER , NUM2 NUMBER , NUM3 NUMBER , UNIQUE (NUM1 ,NUM2,NUM3)); ;

SELECT * FROM USER_CONS_COLUMNS UCC WHERE UCC.TABLE_NAME LIKE 'TEST_CONST';

ALTER TABLE TEST_CONST MODIFY CONSTRAINT SYS_C0025132 UNIQUE(NUM1,NUM2);

ORA-00933: SQL command not properly ended

Third statement is not executing fine .

View 1 Replies View Related

SQL & PL/SQL :: Check Existing Entries In Table

Apr 3, 2012

I am abolute new in PL SQL and I am working on following topic:

I have to check if an entry exists in a table.

- if it exists ...... do something
- if not ...... simply go on

I tried the following:

select Count(*)from products p
where p.productNbr = temp_Nbr
Group By t.produkt;

I fetched the result into a variable entries

if entries > 0 .....

MY problem is the following:

If there are entries (entries>0) --> it works
If there are no entries, entries is not 0 but 'no data found' --> crash

I cannot work with Exceptions because this all happens in a Loop which must go on in both cases!

View 14 Replies View Related

SQL & PL/SQL :: Create Partition On Existing Table?

Aug 13, 2010

I need to create partition on the non partition table without dropping a table.

Range partition on stage tables and hash partition on fact tables.

View 4 Replies View Related







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