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

Error While Creating Table In Oracle 10g?

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

SQL & PL/SQL :: Getting Error While Creating Backup Table?

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

Creating Sql Table Getting Error ORA-00907?

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

Creating Table Error - Invalid Character

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

Error In Creating Update Table Materialized View

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

SQL & PL/SQL :: Creating Table - Missing Or Invalid Option Error

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

SQL & PL/SQL :: Creating Sequence On Duplicate Data Through Query

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

SQL & PL/SQL :: Error While Trying To Update A Column In Table

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

SQL & PL/SQL :: Divide One Of Column Value With Other As Duplicate

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

SQL & PL/SQL :: Duplicate On A Single Column

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

PL/SQL :: Duplicate Values - Getting Error?

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

SQL & PL/SQL :: Finding Duplicate Only Records In More Than One Column?

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

SQL & PL/SQL :: Prevent Duplicate Column Value Using Trigger

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

SQL & PL/SQL :: Duplicate Rows Displayed For A Column

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

SQL & PL/SQL :: Preventing Duplicate Column Value Using Trigger?

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

PL/SQL :: Check For Any Duplicate Values In COUNT() Column

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

Forms :: Duplicate Updatable Column On 2 Canvases

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

Backup & Recovery :: RMAN DUPLICATE Error?

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

Application Express :: Duplicate Column IDs In Reports On Same Page

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

SQL & PL/SQL :: Error With Creating Job

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

Data Guard :: Error Occurred During Initial DB Duplicate To Standby Server?

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

Error While Creating Catalog DB

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

SQL & PL/SQL :: Error In Creating DBMS_SCHEDULER Job?

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

SQL & PL/SQL :: PLS-00402 - Alias Required In SELECT List Of Cursor To Avoid Duplicate Column

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

Creating Index On Hashed Column

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

Creating A Multi Column Index

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

Error Creating View With Union

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

SQL & PL/SQL :: Creating Custom Error Message

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

Error In Creating Password File

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







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