PL/SQL :: Error When Creating A Table / ORA-00957 - Duplicate Column Name
Mar 23, 2013
I'm getting an error as follows .
create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
where
a.CNTCT_KEY = s.CNTCT_KEY and a.SCN_NUM=s.SCN_NUM
and a.ACTVTY_DT = to_date('03/17/2013','mm/dd/yyyy')
and s.SCN_OPEN_DT = to_date('03/15/2013','mm/dd/yyyy')
and a.SRC_SYS_DESC = 'FACET');
create table asgnd_agent_bak as (Select * from ASGND_AGENT a, SCN s
*
ERROR at line 1:
ORA-00957: duplicate column name
View 1 Replies
ADVERTISEMENT
Aug 19, 2008
If i create a table in Oracle 10g it givea me the follwoing Error.. what couls be the error...
ORA-00911: invalid character
create table shopper
(
cShopperId varchar2(30) primary key,
cPassword char(20) not null,
vFirstName varchar2(20) not null,
[code]....
View 1 Replies
View Related
Oct 17, 2013
While creating temp backup table it shows error ORA-00904 'invalid identifier'
CREATE TABLE xxom_valvelink_vlvs_temp AS
SELECT msie.inventory_item_id,FDFC.application_column_name,
flv.tag,
[Code]....
View 10 Replies
View Related
Jul 30, 2012
creating a sql table im getting error " ORA-00907: missing right parenthesis"
CREATE TABLE Campaign
(campaign_id NUMBER(5) PRIMARY KEY,
Account VARCHAR(20) NOT NULL,
SalesLead ID,
SCLead ID,
[code]....
the problem is "ID" and "ID+R", i need these to conect with other sql tables
View 7 Replies
View Related
Mar 3, 2011
I am trying to create two tables and I am getting the "ORA-00911: invalid character" Error. I have been trying to work it out for last past 30 minutes to no success.
CREATE TABLE special_rate
(
rate_id VARCHAR(3),
rate VARCHAR(5),
rate_expiry_date NUMBER(12),
PRIMARY KEY( rate_id ),
FOREIGN KEY( contract_id ) REFERENCES contract(contract_id)
[code]....
View 5 Replies
View Related
Aug 28, 2013
we are trying to create a materialized view (MV) which would access the remote database through db link. Now we need to do update on the local MV so that it should be reflected on the master table.
There is no primary key on this table and we are using "complete refresh" option. since we dont have control over remote database, we are not allowed to create MV log over there.
in this scenario, if i try to create updatetable MV with complete refresh, we are getting below error:
SQL Error: ORA-12013: updatable materialized views must be simple enough to do fast refresh
how to create such MV on this scenario?
our environment is:
Oracle 11.2.0.3
View 1 Replies
View Related
Jun 10, 2011
I am getting error while trying to create a table
SQL>
SQL> CREATE OR REPLACE TABLE CEE_OSPCM_DETAILS
2 (
3 ORD_NBR VARCHAR2(10) NOT NULL,
4 ORD_APPNDX NUMBER(2) NOT NULL,
5 FRCOI_NBR NUMBER,
6 PRINT_NBR VARCHAR2(4),
[code]....
View 3 Replies
View Related
Nov 18, 2011
I have a table with three columns mentioned below :
Col1 Col2 Col3
121
221
331
431
531
622
722
Col1 is the primary key.I need a sql query which will show the data set mentioned below :
Col1 Col2 Col3 Seq
121 1
221 1
331 2
431 2
531 2
622 3
722 3
I need all the 7 records to be displayed.
View 16 Replies
View Related
Jul 31, 2013
I have a scenario where I need to update field in report_input table.It has bind variables in it which will be prompted while running the query.I see that QUERY_VALUE field that need to be updated has length of VARCHAR2(3000).
desc report_input
Name Null Type
----------------- -------- --------------
QUERY_NAME NOT NULL VARCHAR2(64)
QUERY_VALUE NOT NULL VARCHAR2(3000)
[code]....
View 10 Replies
View Related
Feb 6, 2012
below are the create and insert statement, making duplicate row, i mean In table emp_detail, we have row like
ENAMEJOBDEPTNODETAIL
RAJ CS 10 RAJ IS IN CARE OF ROHIT
We need to insert the same record but divide the last column value on the basis of "IN CARE OF" any word after this should come in next new row with the same value for all other column, like
ENAMEJOBDEPTNODETAIL
RAJCS10RAJ IS IN CARE OF
RAJCS10ROHIT
CREATE TABLE "EMP_DETAIL"
("ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"DEPTNO" NUMBER(2,0),
"DETAIL" VARCHAR2(100)
[code]...
View 3 Replies
View Related
Sep 23, 2010
I have a table with multiple columns and I need to retrieve the ones where column a stores duplications for records where column b is discrepant
this is the table
NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12345 +41 22 595 5555
12342 +41 22 595 5500
12340 +41 22 595 5555
the query should return
NAME_ID PHONE_NUMBER
12345 +41 22 595 5555
12340 +41 22 595 5555
The closest I got was with the below, but this also returns duplicates within the same NAME_ID.
select phone_number, name_id
from name_phone
where (phone_number) in
(select phone_number
from name_phone
group by phone_number
having count(*) > 1)
group by phone_number, name_id
order by phone_number
View 3 Replies
View Related
Jun 19, 2012
I have two tables
Table1
Id1 Name1
1 Jack
2 Jack
3 John
Table2
ID2 Name2
NULL Jack
NULL John
I would be assigning ID2 from ID1 based on name match.As Jack has 2 ids when I use the statement
UPDATE TABLE2
set id2 = (select distinct ID1 from table1 where table1.name1=table2.name2);
I get an error message as select statement would return more than one row and the update statement fails completely. with the sql statement to update the ID2 as error when we have duplicate records and continue with the update for other records.
like
table
ID2 Name2
ERROR Jack
View 2 Replies
View Related
Dec 16, 2010
I am trying to write SQL which finds records which are duplicated in more than one column.
Requirement : When ever i have duplicates in Col2 and Col3 both i need that record...
My Source table:
COL1COL2COL3COL4
163kg87
263fh87
1ab23
2ab24
3cd98
4fg87
5xy77
6xy67
Desired Output
COL1COL2COL3COL4
1ab23
2ab24
5xy77
6xy67
For Table generation
CREATE TABLE TEMP_TEST
(
COL1 NUMBER,
COL2 VARCHAR2(10 BYTE),
COL3 VARCHAR2(10 BYTE),
COL4 NUMBER
)
Insert statements
Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values
(163, 'k', 'g', 87);
Insert into TEMP_TEST
(COL1, COL2, COL3, COL4)
Values
[code]....
View 4 Replies
View Related
Mar 8, 2010
I want to avoid duplication on old_nic_no and new_nic_no number in a Table, for this i am using a following trigger
create or replace TRIGGER chk_ip_duplications
BEFORE UPDATE
ON cb_insured_person_bak
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
[Code]...
when i use following command
update cb_insured_person_bak set new_nic_no = '34601-0774284-9' where eobi_no = '4300G043746'
34601-0774284-9 already exist with another eobi_no , i expect error described in rise_application_error clause but i give following error
ORA-04091: Table cb_insured_person_bak is mutating..........
How i can resolve this problem.....
View 15 Replies
View Related
Mar 30, 2013
column sid format 'a5'
column serial# format 'a10'
column mins_running format 'a15'
column sql_text format 'a100'
set linesize 200
set pagesize 30
[Code]..
I am running this code, and the output shows multiple lines.
TRIM(S.SID) TRIM(S.SERIAL#) MINS_RUNNING SUBSTR(Q.SQL_TEXT,1,70)
---------------------------------------- ---------------------------------------- --------------- ----------------------------------------------------------------
700 46592 242.08 Select count(*) as count, case when count(*)>0 then 'FAIL' else
700 46592 242.08 'PASS' end as result
from (SELECT cv.code_value
FROM code_valu
[Code]...
Is there a way to wrap up the column for SQL_TEXT VARCHAR2(64) so that I can 1 row for the output?
View 14 Replies
View Related
Mar 18, 2010
I am working on a assignment, How to prevent duplication with Trigger. I create following compound trigger for this, but it is not doing so.
CREATE OR REPLACE TRIGGER CHK_DUPLICATION FOR INSERT ON TST COMPOUND TRIGGER
R_BCOUNT NUMBER;
R_ACOUNT NUMBER;
D_VAL NUMBER;
BEFORE STATEMENT IS
BEGIN
[code].....
View 12 Replies
View Related
May 13, 2013
I'm going to do some testing, and for that I require to retrieve some data based on a single column e.g test_data_col, which -
1. Has 3 or more count(test_data_col) for a given set of group by columns e.g grp_col1, grp_col2, grp_col3
2. Within the set of rows retrieved, that particular column holds some duplicate values. I don't need the duplicates displayed, just know if duplicates exist or not.
This might explain what I'm trying to do -
grp_col1, grp_col2, grp_col3, test_data_col
1, A, xyz, HELLO
1, A, xyz, HELLO
1, A, xyz, BYE
1, A, xyz, GOODBYE
2, C, pqr, WELCOME
2, C, pqr, GOOD MORNING
2, C, pqr, BAD MORNING
So for condition 1, I do something like this -
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;
In this same query, I want to do something that will tell me if the aggregate COUNT(test_data_col) has any duplicate values within it. Again, displaying the duplicates is not important here.
SELECT COUNT(test_data_col) cnt, grp_col_1, grp_col2, grp_col3,
/*some logic*/ dup_val
FROM test_tab
GROUP BY grp_col_1, grp_col2, grp_col3
HAVING COUNT(test_data_col) >= 3;With the proper coding to replace /*some logic*/, I get following values -
cnt, grp_col_1, grp_col2, grp_col3, dup_val
4, 1, A, xyz, Y
3, 2, C, pqr, N
I just gave dup_val column to explain what I'm trying to achieve.. any other way to know the existence of duplicates in the count aggregate will be fine.My Oracle version is Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
View 2 Replies
View Related
Mar 28, 2013
I have a form which utilizes 2 canvases that the user can toggle between. There is a database column that I would like to have appear on both and be updatable from either place. In my search here first, I found where I could set up a non-database item and copy to it at the point of Post-Query...and that comes close, but I need both columns to not just reflect the db column but be able to update it.
I am about to try using a second trigger to move things from a non-DB column to a DB-column next, but just wondered if there is a better way. When I first compiled with the designer the duplicate column I set up as a DB column also. It only gave me warnings (that I could have lived with) but the ultimate compile my system does outside of the designer calls it an error.
View 7 Replies
View Related
Jun 7, 2006
Oracle 10gR2 (base)
Dataguard, no RAC
no ASM
Performing backups on the physical standby via RMAN. We need to restore our test database, and right now it is equivalent to the production. The DUPLICATE command seemed the best bet. We have controlfile and SPFile both on Auto-backup, and the RMAN on a six day retention with weekly 0 level backups and nightly level 1 cumulative backups.
However, when I run the DUPLICATE it chokes being unable to find a current controlfile nor one in backup, even though we have six days worth of supposedly good (validated) backups. We are not using a catalog, rather the DB controlfile.
The solution to this error is reopen the database and try again. however, the physical standby cannot be opened. It is the standby. What if we move the last backup to the primary database, "register" it there, and try this DUPLICATE?
I have seen nothing on this in my searches, and the Oracle documentation does not address this, though it recommends backup from the Physical Standby.
RMAN-05513 is the error code.
View 10 Replies
View Related
Mar 27, 2013
I have 4 reports on the same page. I have added a checkbox column to each one. On clicking I want to populate a collection with the value of the id of the corresponding row and then have a button acting on the whole list.
My code to get the id of the item that contains the value I want is this
id = 'f02_' + $(this.triggeringElement ).attr('id').substr(4,4);
But there is an f02_0001 for each report, and the code just finds the first one, rather than the one in the context of the report being clicked.
<input name="f02" id="f02_0001" type="hidden" value="2072"/>
....
<input name="f02" id="f02_0001" type="hidden" value="2052"/>
...
etc etc.
All that is ever picked up is 2072
They have different table ids but I don't know how to reference the correct ones in my code.
View 3 Replies
View Related
Sep 18, 2010
I am writing the following PLSQL code-
BEGIN
DBMS_SCHEDULER.CREATE_JOB
(
job_name=>'TRIAL JOB',
job_type=>'PLSQL BLOCK',
job_action=>
[code]........
The new table used above has the following structure
Name Null? Type
STAMP TIMESTAMP(6) WITH TIME ZONE
However I get the following error on execution and am not able to make out why?
BEGIN
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 3
View 4 Replies
View Related
Mar 20, 2008
i have setup the OEM-Dataguard in order to duplicate the DB instance from the primary to the standby DB in the standby server.At first I installed oracle server in Server A (Primary) created DB instance of SCT, in archivelog mode and then I install the oracle binary in Server B (standby) and made 2 servers ORACLE_HOME the same and also I installed OEM agents on the 2 servers and registered successfully to the OEM server.
I then used the dataguard to do the initial backup of the DB in server A and but when I specify the standby DB location, it prompt me the following error.
Error
Examine and correct the following error(s), then retry the operation.
Remote Operation Error - Internal error occured
Add Standby Database: Database Location
Primary Database SCT
Primary Host Ent
Step 3 of 6
Standby Database
* Instance Name The instance name (also referred to as the SID) must be unique on the standby host. Database Storage File SystemRaw Devices Choose whether the database files will be put on a conventional file system or on raw devices.
Standby Host Credentials
Enter the credentials of the user who owns the Oracle installation in the Oracle Home selected below.
* Username
* Password
Standby Database Location
The standby database can be created in any Oracle Home that has been discovered by Enterprise Manager. Only Oracle Homes on hosts that match the operating system of the primary host are shown. Select the Oracle Home in which to create the standby database.
Search For Host
Select Host Oracle Home Oracle Server Version Operating System Operating System Version
Ent /u01/app/oracle/product/10.2.0/db_1 10.2.0.1.0 SunOS 5.10
Ent_standby /u01/app/oracle/product/10.2.0/db_1 10.2.0.1.0 SunOS 5.10
View 2 Replies
View Related
Aug 6, 2010
error while creating a catalog db
error creating db
ORACLE error from recovery catalog database ORA-01552: cannot use system rollback segment for non-system tablespace 'TS'
ORACLE error from recovery catalog database:ORA-00942:table or view doesnt exist
RMAN-00571:--------------------------------------------------------------------------------------------------
RMAN-00569----------------ERROR MESSAGE STACK FOLLOWS-------------------------------------------
RMAN-00571:---------------------------------------------------------------------------------------------------
RMAN-06433:error installing recovery catalog
View 1 Replies
View Related
Feb 25, 2013
I have create the following procedure to create individual jobs dynamically on each schema.
CREATE OR REPLACE PROCEDURE APSP.JOB_TEST_RAM
AS
V_SQL VARCHAR2 (1000);
BEGIN
[Code]....
While executing this procedure I am getting the below error.
PLS-00103: Encountered the symbol "APPS_XX" when expecting one of the following:
View 5 Replies
View Related
Oct 25, 2013
I am getting [Error] PLS-00402 (182: 1): PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names error in my SP.I have created alias for each column and still i am getting the error.
for my_rec_lot in
(SELECT LLP.BOOK_VALUE LLP_BOOK_VALUE,LLP.COMMISSION LLP_COMMISSION,LLP.CURRENCY LLP_CURRENCY,LLP.EXCHANGE_RATE LLP_EXCHANGE_RATE,LLP.EXPENSES LLP_EXPENSES,
[Code].....
View 6 Replies
View Related
Sep 23, 2010
I currently have a 5 column index on a table with over 2 billion records (paritioned on created_date (weekly) that is not very effective.I am contemplating replacing this 5 key index and creating a new single column index made up by hashing of all the 5 five columns.
Is this a wise stratgey? How can I implement this so it is most effective and I dont shoot myself in the foot?
View 4 Replies
View Related
Jan 24, 2012
I have in my database (OLTP-System) a table with about 6000000 records and a zise of about 2GB.
the way to create multi_column indexes on the table?
What are the rule to define the best-position of a column in an index?
index_1(col_1,Col_2,col_3) and not [ (col_1,Col_3,col_2) or (col_2,Col_3,col_1) or (col_2,Col_1,col_3) or (col_3,Col_2,col_1) or (col_3,Col_1,col_2) ] ?
View 4 Replies
View Related
Jan 14, 2011
i have a union all query (two quiries i suppose) that runs fine and returns the results i am after.... when i try to use this query to create a view I get
SQL Command: CREATE OR REPLACE FORCE Failed: Warning: execution completed with warning
CREATE OR REPLACE FORCE VIEW "schema1"."name_of_view" ("column1", "column2", "column3") AS
SELECT b.column1, a.column2, c.column3
[Code].....
View 4 Replies
View Related
Apr 24, 2013
I am trying to create my own error message but when ever i used sp_addmessage it gives error sp2-0734 unknown command biggning... what to do?
View 12 Replies
View Related
Dec 2, 2010
I am creating prim and phy standby on same WIN machine. I have done with all settings. I can do TNSPING to both oracle_sid.I am trying to connect as sysdba to UP the phy standby. But it gives below error:
Enter user-name: sys as sysdba
Enter password:
ERROR:
ORA-12560: TNS:protocol adapter error
So i tried creating password file for phy standby. But gives below error:
Unable to find error file %ORACLE_HOME%\RDBMS\opw<lang>.msb
I am running it from proper dir.
View 4 Replies
View Related