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 RepliesI want to add column to table which has huge amount of data and fill with data from another table. What is the best way to do it? Is it faster to use CTAS instead of ALTER TABLE ADD COLUMN?
View 2 Replies View RelatedIve got a look up table and i want it to display text instead of just the ID number, how would i do that? so for example if
1 = Hello
2 = Bye
it would display "Hello" instead of "1"
I want to have sum of Time Stamp Values in a Table. i.e. Group by Query
example : Select Name, Sum(Timestamp column) from Emp group by name;
OR
I have Time values in character column of a table. i.e. Table TT column t1 char(5) values '03:02'
Above mentioned values in more than one line, i want to get the sum of all the values and it should be a time value.
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.
Ive got loads of new entries that need to be entered into a table if they're not in there already.
Is there a quick way of doing this rather than having to add the entries manually using some kind of tool rather than using manual sql statements(would take a very long time since theres 800 new entries).?
I have requirement on table type. without using bulk or %rowtype is there any possible to create table type with two or more columns.
I got with single column,but I am unable to create with multiple columns.
DECLARE
TYPE T1 IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
T T1;
CNT NUMBER:=0;
BEGIN
FOR I IN 1..100 LOOP
CNT:=CNT+1;
[code]....
below query.
1) How to add a new column to the existing table's particular position, instead of atlast.
2) I created a table without mentioned the datatype size as below Create table dummy (name char, age number). Then what is the default size will be allocated for those column's?
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..
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.
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;
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]...
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 RelatedI 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.
I want to create a table, whose first column , patient_id, should be a sequence.
View 9 Replies View Relatedis it Possible to Assign the Sequence Name During the Table Creation.
View 1 Replies View RelatedI 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 RelatedI 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>
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;
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 ??
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 RelatedCheck 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?
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.
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 Relatedi 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]....
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 Relatedi'm new to oracle environment.how can i specify NONCLUSTERD INDEX on Primary cloumn during table creation.By default it will create clusterd index but i need non-clusterd index on it.
I'm using following stmt to create normal primary constarint during table creation,
CONSTRAINT PKFORM_PROPS PRIMARY KEY (FORM_PROPS_PK) USING INDEX TABLESPACE DB123_INDEX
how can i change the above query, so that it should create NONCLUSTERED INDEX on Primary key column.
I want to put one check box to check all the check boxes.how can i do this?
View 5 Replies View RelatedI have a stored proc SP_INSERT_TRAINEES.Here sTraineeNo is provided as input which has count of trainee needs to be inserted in table aaa_foc.user_profile. The sequence is used to generate ids as :
CREATE SEQUENCE AAA_FOC.TRAINEE_ID INCREMENT BY 1 MINVALUE 0 MAXVALUE 999 NOCACHE NOCYCLE NOORDER
I want the values for ids to be inserted as 001,002,003,.......010,011,................099,100,101,............999.So that the values in the table would be like TRAINEE001,002.......
I have tried to use LPAD to it but the values are getting insertes as TRAINEE1,2,3...........
The code is given below:
CREATE OR REPLACE PROCEDURE AAA_FOC9.SP_INSERT_TRAINEES
(sTraineeNo IN NUMBER,
nReturned_O OUT NUMBER)
IS
ln_insert_cnt PLS_INTEGER :=0;
nSequence NUMBER:=0;
[code]......
Static Tables Creation In oracle & Diff Between Static table ,Dynamic table
View 2 Replies View Related