Objects :: Deleting A Row With Object Column
Jul 4, 2012
I have a table, a superclass and several derived classes.
the table definition looks like this:
/*-------------------------------------------------------------------------------------*/
/* table to store ObjectData for the current session/transaction */
/*-------------------------------------------------------------------------------------*/
create table SessionObjectRegistry(
oid number,
transactid varchar2(200),
sessionid number,
hash number,
[Code]....
ERROR at line 1:
ORA-00600: internal error code, arguments: [kkbnftn2], [], [], [], [], [], [], [], [], [], [], []The db is
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsThe error occurs on 11.2.0.3.0 - 64bit as well.
As a workaround i'm not deleting the rows, but setting the stored objects to null. At the end of the routine i can truncate the table with the objects since truncating works. I'm not the administrator of the database, therefore i may not change any settings.
Unfortunately i wanted to be able to share Objects between Sessions, which is possible with this workaround (simply don't truncate at the end), but leaves a lot of crap lying around.
View 4 Replies
ADVERTISEMENT
May 25, 2011
I was importing one schema from Oracle 10g to 11g using traditional import. I imported as a SYS user, so all the objects created in SYS schema. how can I remove these objects and retain only default SYS objects
View 11 Replies
View Related
Jan 11, 2011
Is it possible that we can know that a particular column of a table has been used in what all data base objects?
i.e. like emp.empno is used in which all procedures/packages/triggers etc.
I would like to know the data dictionary view
View 15 Replies
View Related
Oct 15, 2012
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
I have multiple schemas, all which have tons of objects that point to one column of one table. I tried to look at v$sqltext, and v$sqlarea, but it doesn't seem to show as expected.
Is there a view that I can look at that will show me all the objects that relate to one column?
my situation. Had to change the data structure of this one column. Changed the default value from a Y to an L. I have packages, functions, triggers...etc... that deal with this one column. I need to ensure that I go through EACH one and edit them to reflect the change to the table column. And again, this spans multiple schemas that point back to it.
View 5 Replies
View Related
Jul 22, 2011
I have a table DN_ACTIONS with 9.5 million records. Column DA_OBJECT_NAME is not unique and same object name can have multiple entries. Column DA_ACTION_STATUS can have any values between 1 to 5.
I want to find all the DA_OBJECT_NAME in DN_ACTIONS where all of its entries has DA_ACTION_STATUS=3.
CREATE TABLE DN_ACTIONS
(
DA_ID NUMBER,
DA_OBJECT_NAME VARCHAR2(192 BYTE),
DA_ACTION_STATUS NUMBER(1)
);
best way as it does a self join on big table of 9.5 million.
(select distinct a.DA_OBJECT_NAME from DN_ACTIONS a where not exists ( select distinct DA_OBJECT_NAME from DN_ACTIONS b where a.DA_ OBJECT_NAME = b.DA_OBJECT_NAME and b.DA_ACTION_STATUS != 3))
What are the better ways to rewrite this query?
View 2 Replies
View Related
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
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
Jun 4, 2010
I have a table which has a trigger defined as such on the Main table.
before INSERT OR UPDATE OF USER_NM,COmpany_ID,UPDATED_BY_USER
OR DELETE ON MAIN TABLE OLD AS OLD NEW AS NEW
FOR EACH ROW
When I insert the 1st row (by user Scott)
MAIN TABLE
USER_NM CompnyID UPDATED_BY_USER
----------------------------------------------------------------
ABC1Scott
In the audit table I have this information:
USER_NM CompnyID UPDATED_BY_USER OPERATION_PERFORMED
----------------------------------------------------------------
ABC 1 ScottInsert
When an ADMIN user (other than Scott deletes the above row)
MAIN TABLE:
USER_NM CompnyID UPDATED_BY_USER
----------------------------------------------------------------row deleted....
In the audit table i have 2nd entry as:
USER_NM CompnyID UPDATED_BY_USER OPERATION_PERFORMED
----------------------------------------------------------------
ABC 1 ScottInsert
ABC 1 ScottDELETE
Here the updated_by_user is Scott and not ADMIN, even though I am doing the following in the trigger.
UPDATED_BY_USER := sys_context('USERENV','OS_USER')
I want to show the user who has deleted the record which should be ADMIN.
Since the user deleting may not be the samae as the one who inserted.Any clue?
View 3 Replies
View Related
Feb 11, 2013
I am trying to delete the one month data from the table , which contains the end customer sales data.The total data count in the table is 30 crores. And the data of one month is nearly 10 Crores.I am using oracle 10g , The date field to be used in the condition of delete is indexed.
best way to delete such huge data count.
View 2 Replies
View Related
Oct 19, 2006
After creating all the tables and the constraints, and inputting data to the table.. i want to delete everything. i try using drop table but it doesn't get rid of the constraints.
View 10 Replies
View Related
Jun 30, 2010
looking at my oracle DB schema I see that there are considerable number of tables that appear to be backup(!!) of other tables and don't ask me the reason why would some one create backup/temp tables in production.
All the tables almost occupy 7GB of space and I would like to get ride of them. Before I get ride of them I would like to see when the table was last accessed for any purpose like any DDL/DML statements and any Select statements performed on the temp/backup tables as well. Is this kind of information readily available in any SYS tables or should I write a trigger to get the details going forward.
View 4 Replies
View Related
Dec 20, 2012
I have a table and it's having duplicate records.
for one particular employee, he is having multiple records with the same data in the table
EMPNO ENAME JOB SAL DOB
-------------------------------------------------------
1 A X 100 1956
2 B Y 200 1974
1 A X 100 1956
3 C Z 300 1920
[Code]....
like this am having multiple times the duplicates.
I have written the below query to delete the duplicate records. But it is deleting only one record (if we have 5 duplicates it is deleting only 1 ). But I am looking to delete if we have 5 duplicates need to delete 4 duplicates and keep 1 record in the table.
query which am using to delete the duplicates is
DELETE FROM Table1 a
WHERE ROWID IN (SELECT MAX(ROWID)
FROM Table2 b
WHERE a.ID = b.ID);
it is deleting only one row but I want to delete 4 records out of 5 and keep one record.
View 12 Replies
View Related
Nov 19, 2012
create or replace
Procedure ReadingsPurge
As
v_sql varchar2(500);
v_date date;
p_count NUMBER;
[Code]...
-- Code below drops partitions that are older than the NoOfDays Parameter
OPEN c1;
LOOP
FETCH c1 INTO v_partition_name, v_high_value;
EXIT WHEN c1%NOTFOUND;
[Code]....
Above code is compiling successfully.
After I added the lines makred in the red font, when I tried to execute the stored procedure, I got an error
Error starting at line 1 in command:
execute ReadingsPurge
Error report:
ORA-00933: SQL command not properly ended
ORA-06512: at "CDC_USER.READINGSPURGE", line 30
ORA-06512: at line 1
00933. 00000 - "SQL command not properly ended"
*Cause:
*Action:
View 2 Replies
View Related
May 21, 2010
I have been using MSaccess to do some updating and deleting of data.I have 2 tables, 1 with data, 1 with the criteria to change, 2 colums (FINDWORD and REPLACEWORD).
The main table has lots of data, and the SQL query im using in access looks like this:
UPDATE TABLE1, tblFindReplaceWords SET CleanList.COLUMN-NAME = Replace([TABLE1].[COLUMN-NAME],[tblFindReplaceWords].[FINDWORD],[tblFindReplaceWords].[REPLACEWORD]);
how would I go about doing the same thing in orcale?
View 5 Replies
View Related
Apr 29, 2013
Consider tables A,B,C,D,E,F. all are having 100000++ records Tables B,C,D are dependent on table A (with foreign key constraint). When I am deleting records from all tables, table B,C,D are taking max 30-40 seconds while table A is taking 30-40 mins. All tables are having indexes.
Method I have used:
1. Created Temp table
2. then deleted all records from B,C,D,E,F for all records in temp table for limit of 500.
delete from B where exists (select 1 from temp where b.col1=temp.col1);
3. why it is taking too much time for deleting records in table A.
View 5 Replies
View Related
Jul 5, 2012
I used 'delete input' to delete all the archives backed up.
I am storing archives in dest1 and dest2...i.e, in two locatiions in production.
Although archive in dest1 is getting deleted I am unable to delete those in dest2. how to delete archives in dest2 too.
View 3 Replies
View Related
May 24, 2010
I'm testing a procedure which loads data into my database, and after each test I want to empty some of the tables and reset the sequences. I have this script to do that...
DELETE FROM COM_MERGE;
COMMIT;
DELETE FROM COM_TITLE;
COMMIT;
DELETE FROM COM_ISSUE;
COMMIT;
DELETE FROM COM_PAGE_ELEMENT;
COMMIT;
DELETE FROM COM_ELEMENT;
COMMIT;
DELETE FROM COM_STORY_TITLE;
COMMIT;
BEGIN
COM_RESET_SEQUENCES;
END;
Today I added the call to the sequences procedure to my script, but I have been using the script to delete from tables for a number of days without problem.However today I am finding that when I run the script it works ok the first couple of times, but when I try running it for a third time, it hangs after the second delete (in other words it stops when it gets to the delete from COM_ISSUE). After this happened the first couple of times I stopped the db and restarted it, then the script was ok twice, but again I'm finding that the script hangs. There is no error message, but the script fails to complete.
I didn't know if it was because originally I had one commit at the end of the script, so I added commits after each delete but that didn't solve it.I am using SQL Developer but I have found the same problem when running the script from SQL Plus.This is the definition of the COM_ISSUE table (just in case the table is the source of the problem).There is only one row in COM_ISSUE.
CREATE TABLE "BILL"."COM_ISSUE"
(
"CI_ID" NUMBER NOT NULL ENABLE,
"CI_TITLE" NUMBER NOT NULL ENABLE,
"CI_DATE" NUMBER NOT NULL ENABLE,
"CI_PRICE" NUMBER NOT NULL ENABLE,
"CI_PUBLISHER" NUMBER NOT NULL ENABLE,
CONSTRAINT "COM_ISSUE_PK" PRIMARY KEY ("CI_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536
[code]....
View 9 Replies
View Related
Mar 6, 2012
I have a small question to be clarified. Is there any way to find out the "Applied Archive log files" in DR and "Deleting them through .
View 1 Replies
View Related
Mar 20, 2013
I want to create one alert while deleting the record from the custom form. So i have written the below code in the when-remove-record trigger.
DECLARE
alert_button NUMBER;
BEGIN
IF :XXWIPRELFRM.PRINTED = 'Yes' THEN
alert_button := SHOW_ALERT ('DELETE_ALERT');
[Code]..
When i click on the delete(X) symbol in the form, it is showing alert but when i click on "No" also its removing the record..what i need to write.
View 7 Replies
View Related
Aug 2, 2011
I want to delete records from block in the form.could you explain where(in which trigger) should i write set_block_property.
View 9 Replies
View Related
Oct 13, 2011
I want to delete Master / Detail Data through cursor between date 01-02-2010 till 10-02-2010. Problem is in Detail I dont have date column in detail. But I have to delete Master and Detail record with desire date. I have made a cursor but it delete only detail record i want to delete master record too.
Master Table
M_NO CHAR (12) NOT NULL,
REMARKS VARCHAR2 (200),
CANCEL_YN CHAR (1) NOT NULL,
M_DATE DATE NOT NULL,
PRIMARY KEY ( M_NO ) ) ;
Detail Structure
M_SNO NUMBER NOT NULL,
ACCOUNT_CODE CHAR (19) NOT NULL,
CANCEL_YN CHAR (1) NOT NULL,
M_DESC VARCHAR2 (200),
DB_AMT NUMBER,
CR_AMT NUMBER,
M_NO CHAR (12) NOT NULL,
PRIMARY KEY ( M_SNO, M_NO ) ) ;
create or replace procedure test as
cursor md_cur is
select m_No from master where m_Date
between '01-02-2010' and '10-02-2010';
[code]./....
View 9 Replies
View Related
Feb 13, 2013
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
We are in the process of setting up our backup policy. After the Archived Logs have been backed up, we need to delete them after 7 days. Also the actual files on disk.
RMAN does not delete the Archived Logs from disk.
View 6 Replies
View Related
Sep 29, 2011
How can I delete the duplicate combination of records from the below table.
CREATE TABLE test
(
gidNUMBER(10),
pidNUMBER(10)
);
INSERT INTO test VALUES (10,20);
INSERT INTO test VALUES (20,10);
INSERT INTO test VALUES (25,46);
[code]....
The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.
Expected result after deletion
GID PID
---------- ----------
10 20
25 46
89 64
15 16
19 26
View 5 Replies
View Related
Jun 28, 2010
I have a requirement where i need to retain latest 3 records based on creation date for each customer_id and delete the older records. The customer_ id or contract_number data in the test table are not unique.
Sample Table Script:
CREATE TABLE TEST
(
CUSTOMER_ID VARCHAR2(120 BYTE) NOT NULL,
CONTRACT_NUMBER VARCHAR2(120 BYTE) NOT NULL,
CREATION_DATE DATE NOT NULL
);
[code]...
View 8 Replies
View Related
Jul 1, 2013
when 'where current clause' is used in updating or deleting in loop.... is it required to write commit in the loop or it will auto commit?
View 2 Replies
View Related
Jan 11, 2013
i used item list in my form ,i want to fill this item list ...but i encountered this error 'frm-41075 error deleting group' !!!here is the code :
//when-new-form-instance
declare
errcode number;
group_id RecordGroup;
[code]...
View 3 Replies
View Related
Oct 11, 2010
We uploaded files to application server,using webutil_file_transfer.client_to_as_with_progress Now, we want to delete the file through forms, when user click a button.
we tried this,using HOST command.
DECLARE
CURSOR c_path
IS
SELECT attachpath
FROM PATHTAB
WHERE apid = :parameter.apid;
[code].....
View 10 Replies
View Related
Jan 27, 2011
I've a primary database and a physical standby.Logs are shipping perfectly from primary to standby.The logs that are applied on the standby are getting stored in a mount point(LINUX-/opt2) which consumes more space(160 GB). Will that be right if I go ahead and delete them?
My rman configurations in primary is:
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 4;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
[Code]....
View 2 Replies
View Related
