SQL & PL/SQL :: ORA-00955 / Name Already Used By Existing Object
Mar 15, 2012
During import , tables were imported but index was not imported due to ORACLE home was full.after import i was trying to create the the same index as it was not imported i am getting below error.
The "error ORA-00955: name is already used by an existing object"
My index name is GL01PK . I am trying to verify if the index with above name is present or not , using following query.
select OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID ,OBJECT_TYPE from dba_objects where object_name like '%GL01%'
below is the output which shows that GL01PK index is not present.
GLOT1 GL01 150943 TABLE
FNSONLC3 GL01 147240 TABLE
FNSONLC3 GL01_13042011 148184 TABLE
FNSONLC3 GL01_07052011 148195 TABLE
how can i create the index here with the same name GL01PK
View 3 Replies
ADVERTISEMENT
Sep 26, 2012
While practicing with Triggers, the following error was encountered. An Object Type and an object Table are created.
create or replace
type typPerson is object
(id number,
firstname varchar2(30),
lastname varchar2(30)
[code].........
I executed the below insert statement, and I got the following error.
SQL> insert into person_obj_tab values (10,'Object1','From Trigger');
insert into person_obj_tab values (10,'Object1','From Trigger')
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4153
Session ID: 136 Serial number: 305
[code]......
SQL> insert into person_obj_tab values (10,'Object','Original');
1 row created.Question:
1) Trigger of version 1 did not report any error during compilation, but during DML execution, hangs for sometime and gives the above error.
2) Whether direct assignment of Objects of greater size is possible inside triggers built on object Tables?
3) Suppose an object contain multiple attributes (say more than 20), then how to assign them inside a trigger?
View 1 Replies
View Related
Jan 25, 2013
I have a problem with executing oracleCommand.ExecuteReader() method. Whatever I try it always returns null and it won't create OracleData reader object. I'm using ODAC 1120320_x64 for .net 4.0 and timesten112241.win64. Don't sure what to do. Debugger is showing strange thing in OracleConnection object : ConnectionState = Closed, but output of ttStatus shows connection to TimesTen data store and ExecuteNonQuery() command works just fine with or without (in or out) parameters. But when I try to execute some query with multile output such as select *, I can't get any result.
I also have a strange problem with connection.Open() When I execute Open() i throws AccessViolationException that can be handled with [Handle ProcessCorruptedStateExceptions] attribute, but connection is established after that and my application works fine until I try to instance OracleDataReader object.
Here is the code:
OracleCommand select = null;
OracleDataReader reader = null;
select = new OracleCommand(selectStmt, connection);
select.CommandType = CommandType.Text;
try
{
reader = select.ExecuteReader(); // this line throws NullReferenceException
if (reader.HasRows)
{
[code]....
Just to mention, I tried it with different queries (pl/sql, plane sql, stored procedure) and all of them works fine in SQL Developer, but not in app.
View 10 Replies
View Related
Dec 24, 2012
Is there any query to find the dependent object details for any object. Like if mview is built on a table, then i should be able to find the table name with out checking code of the mview. similar way for view and functions or procedures etc...
View 5 Replies
View Related
Dec 19, 2011
I'm am getting an error that says that a role doesn't exist when I can cleary see it in DBA_ROLES. I can successfully grant other roles in this session. I am using Oracle 11.1 on RAC.
GRANT "CONNECT" TO BAKERD
Error at line 1
ORA-01919: role 'CONNECT' does not exist
select * from dba_roles where role = 'CONNECT'
[code]....
View 5 Replies
View Related
Mar 29, 2013
I have an Type-object typeObj1 that consists another Type-object typeObj2. this def has another Type-object typeObj3. how to access variable declared inside typeObj3. I have syntax below for each Type.
CREATE OR REPLACE TYPE typeObj1
AS OBJECT
(
SYSTEM_IDENTIFER VARCHAR2(50),
PROCESS_TYPE VARCHAR2(50),
abc typeObj2
)
/
[Code]...
/I have tried to access the type-object in where clause in following way
FROM TABLE(CAST(I_typeObj1 AS typeObj1)) ITTPRC,
......
Where
.......
AND (ADDKEY.ADDTN_INFO_KEY_TYP_NM IN (SELECT ADDTN_INFO_KEY_TYP_NM FROM TABLE(ITTPRC.abc)))
AND (ADTINF.ADDTN_RQST_TYP_VAL_DT IN (SELECT ADDTN_RQST_VAL_DT FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VAL_NUM IN (SELECT ADDTN_RQST_VAL_NUM FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
AND (ADTINF.ADDTN_RQST_TYP_VALUE IN (SELECT ADDTN_RQST_VALUE FROM TABLE( ITTPRC.def)) OR ITTPRC.def IS NULL )
In this way i am able to access the variable inside typeObj3. But problem is i am getting error "ORA-01427 single-row subquery returns more than one row" when i pass more that one typeObj2.
I passed the values like this in proc execution.
T_T_A_I_V := typeObj3('asdasd',NULL,NULL),
typeObj3('String654',NULL,NULL),
typeObj3('abcdef',NULL,NULL));
T_T_A_I_I := typeObj2('CampusCode',T_T_A_I_V),
typeObj2('PlanNumber',T_T_A_I_V);
What i have done is removed typeObj3 from typeObj2, variables defined in typeObj3 are added in typeObj2 then i got ride of above error. is it correct
View 4 Replies
View Related
Nov 28, 2011
I am trying to update an existing trigger in my Prod DB, but it may be possible that while recreating the trigger,existing trigger might be under any transaction.
So what could be the possible scenarios? Will I be throwing any error like ORA-0054: resource busy or any other error or what? If it does what could be possible escape ways apart from downtime.
View 17 Replies
View Related
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
Mar 13, 2012
want to create a PL/SQL procedure, update_id(id_emp in number), that gives a new id_emp (id_emp=y) to an existing employee (id_emp=x).So before updating the EMP table, we have to :
1- create a new row on EMP(with id_emp=y) that has the same informations of the employee (id_emp=x),
2- update all tables that contains the id_emp column (update <TAB> set id_emp=y where id_emp=x),
3- delete employee (id_emp=x).
The problem is in step 2 : it creates a lot of locks and makes the DB unusable.To deal with this problem, I thought for many solutions, but the problem is how to implement them correctly and efficiently.
Before executing step 2, we have to ensure (through a marker I guess) that all the tables that have the id_emp column, are managed by our session, and any other acces (through SELECT, UPDATE, DELETE, INSERT statment) from another session will be rejected since we have a marker on that table.
When step 2 ends, we release all the tables from the markers.
My simple question : how to achieve this ?
View 12 Replies
View Related
Nov 10, 2011
how to add a auery to my procedure.
View 12 Replies
View Related
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
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
Jul 15, 2013
I need to know how to modify the spfile - I know how to create it from scratch (from pfile to spfile), but need to modify an existing spfile.
View 3 Replies
View Related
Jun 2, 2013
we are providing a salary column. from that salary column we need to calculate bonus with the condition as bonus=(salary+10%). how to execute this.
View 5 Replies
View Related
Apr 9, 2013
I would like to know how to create an associative array from an existing array plus some columns using other tables using a SQL query, bulk collect to a new array.
For example:
1. array 1 is created like select emp_no, emp_name, dept_no bulk collect into l_emp from emp;
2. array 2 needs to be created from array 1 along with the table department and the output should have all columns of array 1 plus dept_name, loc_no from department table.
3. I know this can be done using a simple join between 2 tables but for some reason I would like to create it as explained above.
4.i wanted them as associate arrays and not object collection.
View 2 Replies
View Related
Nov 8, 2010
In my organization, I have a table and in that there is a column named "code".I want to restrict some insertion to that particular column. suppose that code column values are 12 and 1245 then i cant insert the value 12,1245, 1 ,124 and so on but i can insert 2 ,123,15,12456 and so on.
that means the new values should not be any substring of the existing data from left. making that column primary key and then I had a logic to compare the existing value which are longer than the new value and then to perform this.But dont know how to make it happen correctly.
View 2 Replies
View Related
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
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
Jul 8, 2010
I want to add some new columns in an existing table that has over 10 millions rows, but not at the end of existing columns, my requirement is such i want new columns between existing existing columns. Is it possible.....
View 16 Replies
View Related
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
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
Jun 5, 2011
There is a existing form B. From a form A I need to call form B on button press. Problem is form B has many tabs,It should open in the third tab when it is being opened from form A.
View 1 Replies
View Related
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
Oct 31, 2013
I have 2 tables that doesn't have primary keys. These 2 tables have same number of rows. I want to create a new table from getting some columns from table1 and some columns from table 2. I want to combine first row from table1 and first row from table2.
Below is example
TABLE1
ACOL1 ACOL2 ACOL3
A1 A2 A3
B1 B2 B3
C1 C2 C3
TABLE2
BCOL1 BCOL2 BCOL3
11 12 13
21 22 23
31 32 33
COMBINED_TABLE
ACOL1 BCOL2 BCOL3
A1 12 13
B1 22 23
C1 32 33
I tried below query but no luck. It gives below error:
Query : create table COMBINED_TABLE AS select a.ACOL1, b.BCOL2, b.BCOL3 from (select ACOL1,rownum from TABLE1) a, (select BCOL2, BCOL3, rownum from TABLE2) b WHERE a.rownum = b.rownum
Error : ORA-01747:"invalid user.table.column, table.column, or column specification"
View 7 Replies
View Related
Jul 5, 2012
Today I found myself in a situation where I needed to compress an already existing, partitioned index. First I issued an alter table to rebuild and compress the whole index:
SQL> ALTER INDEX MY_INDEX REBUILD COMPRESS;
ERROR at line 1:ORA-14086: a partitioned index may not be rebuilt as a wholeSo next I tried to rebuild compress one of the partitions:
SQL> ALTER INDEX MY_INDEX REBUILD PARTITION PART1 COMPRESS;
ERROR at line 1:ORA-28659: COMPRESS must be specified at object level first
Turns out that there is no way you can do a rebuild compress on partitioned indexes. I did not realize that until I stumbled across document 312843.1 on Metalink. It says the only way to compress a partitioned index is to drop it and recreate it (through 11.2).
My question is do we have any new feature in Oracle 11gR2 to rebuild partition indexes?
View 2 Replies
View Related
May 9, 2012
How can I modify the check constraint without dropping the already existing check constraint.
Example: alter table table_1 add constraint const_chk check (ATTRIBUTE7 in
('READ','UNREAD');
Want to add some TENTATIVE value for the already existing one. I could do that by dropping the constraint
ALTER TABLE table_1 DROP CONSTRAINT const_chk and modifying it later as
alter table table_1 add constraint const_chk check (ATTRIBUTE7 in
('READ','UNREAD','TENTATIVE');
Is it possible to do that with out dropping the constraint?
View 2 Replies
View Related
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
View Related
Nov 2, 2012
I am getting the following error only for the ""first execution"", even the package is in "valid" state. If the execute the same package again "second execution", it runs ok.
usually we complie all the "invalid objects" in the schema when we move the code to TEST or PRE-PROD env.
but i am getting the following error, even if the package is in "valid state".
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "TEST_OWNER.TEST_PKG" has
been invalidated
ORA-04065: not executed, altered or dropped package
"TEST_OWNER.TEST_PKG"
ORA-06508: PL/SQL: could not find program unit being called:
"TEST_OWNER.TEST_PKG"
ORA-06512: at line 2I have gone through the following thread, blu explained the cause of this error,
but do we get this error even when the package is in "valid" state too?
Re: When should be package invalidated?
View 4 Replies
View Related
Oct 4, 2013
I have an procedure with four in parameters in it. Now, I want to add an out parameter to it. The issue is the procedure is called from many other procedures and triggers. Since we can't set default value to out parameter as we can for in parameter, how we can achieve the requirement without having to update all the places where this procedure is being called.
View 3 Replies
View Related
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
