SQL & PL/SQL :: Write Query To Delete Similar Records In Particular Fields (columns) In Different Tables
Jul 17, 2012write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies
ADVERTISEMENT
PL/SQL :: Write Dynamic SQL To Query Unknown Number Of Columns
Jun 6, 2012I need to write a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a simple example query here:
SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS
If I have known the number of columns, e.g. querying two columns: "FIRST_NAME" and "LAST_NAME", I can write a DYNAMIC SQL based on the template in table 8-2 of URL....
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
FIRST_NAME varchar2(200);
LAST_NAME varchar2(200);
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS';
[code]....
However, if I wish to write a dynamical sql to query these two columns for a more general purpose (which should meet the requirement to query different number of columns, e.g. three columns, FIRST_NAME, LAST_NAME, BIRTHDAY instead of two columns FIRST_NAME and LAST_NAME). To do this I first try to query the same two columns but using a different method, following URL.....My code for the same query has error, and I cannot solve it.
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_tab;
l_dummy NUMBER;
l_value NUMBER;
[code]....
Delete Records From Multiple Tables
Jun 27, 2011I have a service that executes a pl/sql function (legacy app) to delete records from multiple tables. This function works fine in development, and has worked fine in production until about a week ago. I'm not a DB guy but the DB guys are trying to say this is an application issue. That may be, be the "insuff privileges" really leads me to believe otherwise.
What is causing this type of Oracle error? Permissions between dev and prod are the same, yet it works in dev but not prod.
ORA-29876: failed in the execution of the ODCIINDEXDELETE routine
ORA-20000: Oracle Text error:
DRG-50857: oracle error in textindexmethods.ODCIIndexDelete
ORA-20000: Oracle Text error:
DRG-10602: failed to queue DML change to column ABSTRACT for primary key AAAfBoAAEAABa62AAA
DRG-50857: oracle error in drexrdml
ORA-01031: insufficient privileges
[code]....
SQL & PL/SQL :: Write Outer Query Which Will Bring Individual Records Which Don't Sum Up To Zero
May 9, 2011I have to write a sub query / build a logic for the below.
There are several accounts which should have a zero balance i.e sum of all the amoutns in that account should be zero. If they are non zero , i have to report which amounts make up non zero balance.
If i have amts as +20 , -20 , -30,-10 i.e the sum is -40 indicating a non zero amount. I need the entire details of the records which makes up non zero sum. So in above case details related to -30 aand -10.
I'm using a sum group clause on several fields at which sum is required to be checked ie. date , account , currency . query that will bring individual records that don't make the sum zero.
Is it possible to write a outer query which will bring individual records which don't sum up to zero.
SQL & PL/SQL :: Delete Bulk Records In Tables From Which Path Can See How Much Space Is Free
May 5, 2010Im a oracle pl/sql developer but I didnt learn oracle as a language. With my sql skills I started working on Oracle.In my project every table has an associated MLOG$ table for it.
For eg.
CREATE TABLE MLOG$_TEST
(
ID VARCHAR2(64 BYTE),
SNAPTIME$$ DATE,
DMLTYPE$$ VARCHAR2(1 BYTE),
OLD_NEW$$ VARCHAR2(1 BYTE),
CHANGE_VECTOR$$ RAW(255)
)
So is MLOG$_TEST oracle's internal table.
Whenever an insert/update/delete happens it is recorded in MLOG$ tables as I/U/D for the priamry key.So if I do a bulk delete of records in tables an entry is made into MLOG$ tables. So deleting old records in a database doesn't free much space.If this is oracle internal table is it advisable to delete from MLOG$ tables too.
My oracle database is mounted on the Linux server.If I delete bulk records in tables from which path I can see how much space is freed.
SQL & PL/SQL :: Get Data From All Similar Tables
Sep 12, 2012I have 3 tables as below. I am looking to get data from these 3 table at a time using a pl/sql block but unable to do. Is there any way that I can get the data in a single query.
PS:All the columns are same in all the 3 tables
table1_COLLECTIONS_a
table1_COLLECTIONS_b
table1_COLLECTIONS_c
PL/SQL :: How To Move Data Between Two Similar Tables
Jul 3, 2012I have two tables with almost the same columns. Table A has 50 columns, Table B has 51 columns, which are defined exactly the same except one more column as LOAD_TIME.
Is there any easy way for me to move the data from Table A to B? the Extra column LOAD_TIME will be set as current system time. All I want is to avoid list all 50 column names. The following SQL has error ORA-00923 error.
INSERT INTO TB_B
SELECT *, sysdate from TB_A;
SQL & PL/SQL :: Retrieving Names Of Tables That Contain Similar Information In Oracle Database?
Nov 16, 2010it's possible to retrieve the names of tables that contain similar information in an oracle database.
View 7 Replies View RelatedSimilar Table Structure Having Similar Indexes?
Sep 24, 2010I do have problem with a table which have same structure of other table with indexes created being the same. Both tables have partions & subpartitions. Let us consider two tables table1 & table2. The problem i face is the index for table1 not being used when its being joined with other respective tables in a query. Whereas for table2 its working perfectly and cost is also less. I have used the explain plan and compared and query timing also takes more time. what might be causing it?
View 2 Replies View RelatedSQL & PL/SQL :: Query To Transpose Quest_id And Ans_id Columns Into Rows For Matching Records
Nov 16, 2012create table ref_tbl (col1 varchar2(10),
col2 varchar2(10),
QUEST_ID_1 varchar2(10),
ANS_ID_1 varchar2(10),
QUEST_ID_2 varchar2(10),
ANS_ID_2 varchar2(10)
);
[code]...
Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records...my output should be like
CUS_IDCOL1COL2QUEST_IDANS_ID
------------------------------------------------
1PMP1234Q101A1234
1PMP1234Q102A2345
1STR2345Q201A2234
2PMP1234Q101A1234
2PMP1234Q102A2345
2STR2345Q201A2234
SQL & PL/SQL :: Insert Multiple Records In Different Tables At A Time (Using Single Query)
Jun 8, 2010 I want to insert multiple record in diff. table by using single query...
how can i di it suppose i hv 3 table table1, table2, table3 i want to insert 2 record on each table respectively..
But i want to do this task by using single query....how can i do it?
PL/SQL :: To Delete Child Records Manually Without Using Oracle Delete Cascade
Oct 9, 2012I have to write a procedure that accepts schema name, table name and column value as parameters....I knew that i need to use metadata to do that deleting manually.
View 9 Replies View RelatedSQL & PL/SQL :: How To Write Single WITH For A Update And Delete
Mar 13, 2012Can I achieve the merge (update-only) and delete in a single SQL statement?
merge into table_4
using
(select table_3.col1 col1,table_3.col2 col2,
from table_1, table_2, table_3
where
[code].....
delete from table_3 where <records identified in the 'using' clause of above merge>;
i.e.
delete from table_3
where (table_3.col1,table_3.col2)
in ( select table_3.col1,table_3.col2,
from table_1, table_2, table_3
where
join conditions.....
other conditions ....
group by table_3.col1, col1,table_3.col2
having count(*) >1 );
What I want is roughly as following :
WITH mywith as ( select table_3.col1,table_3.col2,
from table_1, table_2, table_3
where
join conditions.....
other conditions ....
group by table_3.col1, col1,table_3.col2
having count(*) >1 )
update table_4 set col3='N' where table_4.col1=mywith.col1 and table_4.col2=mywith.col2
delete from table_3 where table_3.col1=mywith.col1 and table_3.col2=mywith.col2;
Write Simple Sql Which Would Delete Data From Last N Months?
Apr 20, 2009am trying to write a simple sql which would delete data from last n months but it will keep the data for the first of each of those month from current sysdate
e.g
Jan 1 - 30 deletes 2 - 30 keeps data for 1st
Feb 1 - 28 deletes 2 - 28 keeps data for 1st
Mar
Apr Current sysdate
May
SQL & PL/SQL :: Delete Table1 Rows Where 3 Fields To Join With Another Table
Aug 13, 2011I need to delete all the registers where the table 1 does join with table 2 in 3 fields... for example:
delete taba1 t1
where t1.campo1 in ( select distinct(tr.campo1)
from tabla1 tr,
tabla2 t2
where t2.error = 0
tr.campo1 = t2.campo1
and tr.campo2 = t2.campo2
[Code]...
SQL & PL/SQL :: Procedure To Delete Records / Count Number Of Records Has Been Deleted
Feb 21, 2011I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS
del_records NUMBER:=0;
BEGIN
DELETE
FROM candidate c
WHERE empid in
(select c.empid
from employee e,
candidate c
where e.empid = c.empid
and e.emp_stat = 'TERMINATED'
);
[code]....
Application Express :: Clearing (or NOT) Form Fields After ARP CREATE Or DELETE Operation
Jan 15, 2013I understand everything -- <gripe> need better Automated Row Fetch (ARF) documentation </gripe>, but I now have CREATE, QUERY, and DELETE working and I can choose to clear the form (or NOT). And I don't claim this is the best or simplest or most-efficient or most straight-forward approach. It's one virtue is that it does seem to work.
I need to be careful state that this involves both Automated Row Processing (ARP) -- which enables INSERT, UPDATE and DELETE. And Automated Row Fetch (ARF) -- which enables SELECT. ARF is found under Page Rendering and ARP is found under page processing. As we have seen, the two are -- or can be -- interdependent. For example, I believe the error I talk about next is caused by ARF trying to fetch the record just deleted by ARP.
Starting from the situation of
1) having the values retained on the screen when CREATE is clicked and the record is added and
2) getting a database error after a DELETE of a row, A solution is the "reset" processes. Under Page Rendering add a "reset page" process to: "Clear Cache for all Items on Pages (PageID,PageID,PageID)" and indicate the page with the database operations and under Conditions: When Button Pressed: choose CANCEL, CREATE, DELETE, QUERY or SAVE. I made two of these - one for CREATE and one for DELETE. To cover both buttons, you need two processes as you can only indicate one button per process.
Now the form (screen) is cleared after the CREATE (by process
1). And since it's also cleared after the DELETE (by process
2), there is no Database error on DELETE.
(The source of that error was ARF trying to retrieve the deleted record because the PK field had the "just deleted" key value. This nulls it and I have ARF set not to attempt a fetch when the key field is null.
So, how about that? Burleson has an example here which adds more detail than I recall seeing before.URL>....
I think the DML Fetch Mode settings he shows are new to the ARP screen in 4.2. I don't see them in 4.1.
SQL & PL/SQL :: Write A SELECT Statement That Returns One Row For Each Customer With Four Columns
Feb 16, 2012I am student of Oracle programming(SQL).Has a question on MIN and MAX functions:
- "Write a SELECT statement that returns one row for each customer with four columns--the customer's first and last name, the date of the customer's most recent order, and the date of the customer's oldest order and the difference between the two dates. Sort the result set so the customers who have been ordering with the company the longest appear first. Use most recent order date as a tie-breaker for the sort."
where orders and order_details,customers are 3 different.this is my query ...but its not working.
SELECT DISTINCT customers.customer_first_name||' '||customers.customer_last_name AS Customer_name,MAX(order_date) AS "Most recent order", MIN(order_date) AS "Oldest order"
FROM customers
INNER JOIN orders
ON customers.customer_id=orders.customer_id
LEFT JOIN order_details
ON orders.order_id=order_details.order_id
LEFT JOIN items
ON order_details.item_id=items.item_id
ORDER BY Customer_name
Select Fields From Across Multiple Tables?
Mar 25, 2011 I have had a google around and can't seem to find an answer as to how do do the following Select statement. i am wanting to Select the following fields from across multiple tables.
(field.table)
CustName.CUST
SalesNo.SALE
SalesDate.SALE
ItemDes.ITEM
Qty.SALEITEM
OrderComplete.SALEITEM
with 2 types of WHERE criteria:
WHERE SalesDate is between 'dateX' AND 'dateY'
and also WHERE OrderComplete = 'Y'
i understand this will require some sort of join in the statement so the keys for the different tables are as follows:
CUST
CustNo - PK
SALE
SalesNo - PK
CustNo - fk
ITEM
ItemNo - PK
SALEITEM
SalesNo -fk
ItemNo - fk (compound PK)
i have had a play around with using some joins & embedded statements
SQL & PL/SQL :: Deleting Duplicate Records Based On Repetition Of Certain Fields
Sep 15, 2011I have the following situation and need support:
create table try_x
(a number PRIMARY KEY,
b NUMBER,
c NUMBER,
f_text VARCHAR2(10));
insert ALL
into try_x values (0,1,1,'abc')
into try_x values (1,1,1,'abc')
into try_x values (2,1,1,'xyz')
into try_x values (3,1,2,'abc')
into try_x values (4,1,2,'abc')
into try_x values (5,1,2,'abc')
into try_x values (6,1,3,'abc')
into try_x values (7,1,3,'abc1')
into try_x values (8,1,3,'abc2')
into try_x values (9,1,3,'abc2')
select * from DUAL;
Although a is the PK, records with similar b,c,f_text are considered redundant and I need to delete all occurrences in the table where b, c, d are redundant and leave the unique ones. So I need the result to look like:
a b c f_text
-----------------
0 1 1 abc
2 1 1 xyz
3 1 2 abc
6 1 3 abc
7 1 3 abc1
8 1 3 abc2
Server Utilities :: Sql Loader Do Not Load Records And Write Them Into Badfile
Oct 18, 2012My loader start and say commit reach logical records 8 as there are 8 records but donot load them and write them into badfile. there is no logs file generation happing so unable to trace.
table
CREATE TABLE ERS_NRT_SRC
(
POLL_ID NUMBER(10) NULL,
TIME_OF_POLL DATE NULL,
SERVICE_DESC VARCHAR2(50 BYTE) NULL,
LOCATION_AGENT VARCHAR2(30 BYTE) NULL,
TRANSACTION_NAME VARCHAR2(50 BYTE) NULL,
[code]...
SQL & PL/SQL :: Build A Stored Procedure Which Compares Specific Fields In 2 Tables
Jul 24, 2013I have table A.Security:
CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 56 USD AB 676
2 b 45 INR AB 143
3 c 32 JPY BA 200
and Table B.Security:
CouponIssue_nameQuantity_typeCurrencySec_TypeSec_alias
1 a 33 USD OB 676
9 b 21 NZD AC 143
8 c 19 GBP CA 200
build a Stored Proc which compares sepecific fields in the 2 tables and reports into a new table as:
A.Security.sec_aliasB.Security.sec_aliastable fieldA-valueB-value Compare
676 676 Security Currency USD USD Match
143 143 Security Currency INR NZD Not-Match
Fields to be compared are:
Quantity_type
Currency
Sec_type
The join between the tables A and B can be made on Sec_alias.Also, this is just a single pair of tables. Further I need to compare 5 more pairs of tables after this and report out of the new table.
SQL & PL/SQL :: Differences Among Records Tables And Tables Of Records
Sep 16, 2010In Oracle database pl/sql 11.2: describe the differences between :
records,
tables, and
tables of records.
Forms :: Auto Populating Records In Other Fields Based On Inventory Number Field
Jul 13, 2011I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.
How to set any triggers for the items to auto populate and i am suppose to finish this work today.
Server Administration :: Possible To Write Triggers On Data Dictionary Tables And Views
Sep 30, 2011Is it possible to create trigger on the various tables and views exists (i.e. dynamic performance views) in data dictionary, when ever any DML operations performs by Oracle it self?
View 6 Replies View RelatedSQL & PL/SQL :: How To Select All Columns From Table Except Those Columns Which Type In Query
Jan 21, 2011I have a two question.
Question 1:How to select all columns from table except those columns which i type in query
Question 2:How to select all columns from table where all columns are not null without type each column name which is in empty data
PL/SQL :: Delete Dupes Based On Multiple Columns
Dec 28, 2012Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
I want to delete dups from a table based on 3 columns
with sample_table as (
select '101' as ID1, '201' as ID2, '4' as weight from dual union all
select '101' as ID1, '201' as ID2, '5' as weight from dual union all
select '105' as ID1, '205' as ID2, '6' as weight from dual union all
select '105' as ID1, '205' as ID2, '6' as weight from dual union all
[code].........
Desired Output
with sample_table as (
select '101' as ID1, '201' as ID2, '5' as weight from dual union all
select '105' as ID1, '205' as ID2, '6' as weight from dual union all
select '110' as ID1, '215' as ID2, '9' as weight from dual
)
select * from sample_table
SQL & PL/SQL :: How To Write Correlated Sub Query
Sep 13, 2010I have employee, location, city tables, I have written following code to use joins
select ename from employee e, location l, city c
where e.c_locationid= l.locationid and l.cityid= c.cityid and c.cityname='XYZ';
The same can be written by using non- correlated sub query as follow..
select ename
from employee
where c_locationid in (select locationid from location where
cityid in (select cityid from city where cityname='XYZ'))
I need to implement the same concept using correlated sub query...
SQL & PL/SQL :: How To Write Column To Row In A Query
Nov 17, 2005How to write column to row...in a SQL query?
For example..
SQL> select empno,deptno from emp where empno = 7369;
EMPNO DEPTNO
---------- ----------
7369 20
7369 10
7369 40
The above output to be written in a single row like given below.
7369 20 10 40
1) Actually it may change dynamically...It may be 2 records for some values and there may be 10 records for some value and different for some other
2) It should be in SQL query only..Not in procedures or functions.
SQL & PL/SQL :: Delete All Records From A Recordset In One Go?
Jul 3, 2010I am selecting around 10000 records in a recordset using an sql query.I need to delete these 10000 records from 10 different tables as each of the table have these records.I can use a FOR Loop for each record one by one to delete these 10K records from each tabel but i was wondering if i can delete there 10K in one go so that the program would Loop Only once rather thatn 10K times.
Mine Delete code looks like:
if x.first is not null then
for i in x.first..x.last loop
delete tbl_A where trade_id = x(i).trade_id;
delete tbl_B where trade_id = x(i).trade_id;
[code]...
Where x is the recordset having around 10K records.Is there any way i can run the loop only once rather than one by one for 10K times.