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.
ADVERTISEMENT
SQL & PL/SQL :: How To Insert 100 Records Into Different Tables
Apr 28, 2011i have 100 records in table1,like as we have more 15 tables without data. the issue is how can i insert above table1 data(100 records) into different 15 tables in single sql command.
View 22 Replies View RelatedPL/SQL :: Get Records Count Of All Tables
Sep 26, 2012I am trying to get the record count of all tables using dynamic query. I don't know how to put the value in placeholder. I tried the below code.
SET SERVEROUTPUT ON SIZE 1000000
DECLARE
CURSOR table_list
IS
select OBJECT_NAME from user_objects
where object_type in ('TABLE')
[code].......
SQL & PL/SQL :: Retrieving Records From Tables
Mar 27, 2010I want to retrieve the data from ex.tables. How to get this.
table1:
Account_No, Account_sub_No
1234 1
1234 2
1234 3
2345 4
2345 5
2345 6
2345 7
2345 8
................
Account_no is the primary key
table2:
Account_sub_No, Description
1 Hello
2 Hi
3 No.1
4 great
5 people
.................
8 world
..........................
Account_sub_No is primary key.
Out put:
I want the data like Account_no who is having more than 3 Account_sub_no values.
But in my case need to join these two tables with other tables. join field is Account_no from table1. there are no other fields to join.
Querying Records From Temporary Tables
Feb 2, 2011I have a temporary table (with on commit preserve rows property) which is populated thru insert into command from a procedure. After which, i need to query the records from the populated temp table.
However, my query returns nothing. My procedure works fine cause i tried executing it to populate a regular table and it is ok. However, it shows no output in the temp table cause probably it is creating another session. How do i select the rows from the temp table after populating it from a procedure.
How To Display Unmatched Records In Two Tables
Feb 28, 2008I want to display all records from table 1 (even the null values) that do not match records in Table 2. Below I am creating both tables and I am posting the result query I need.
------------------------------------
CREATE TABLE temp_table1
(
name VARCHAR2(12 BYTE),
last_name VARCHAR2(12 BYTE),
STATE VARCHAR2(2 BYTE),
BIRTH_DATE DATE
);
CREATE TABLE temp_table2
(
name VARCHAR2(12 BYTE),
last_name VARCHAR2(12 BYTE),
STATE VARCHAR2(2 BYTE),
BIRTH_DATE DATE
);
[code].....
The result query need to have 5 rows as shown below
NAME LAST_NAME STATE BIRTH_DATE
------------ ------------ ----- ---------------------
john smith MA 12/1/1979
null null AZ null
null null CT null
null null MA null
null null CT null
SQL & PL/SQL :: List Records Being Deleted From All The Tables
Jan 10, 2011I am executing a script that is deleting some parent records and the corresponding child records as I have used the "on delete cascade" with the Foreign key Constraint.
My question is that can I list the records that are being delete from all the tables i.e. both parent and child tables. Is some thing like spooling can work in this or do I have some other option with which I can see(select) all the deleted records.
SQL & PL/SQL :: Select Records Where Date Is Max From Tables
Oct 6, 2011 I've got 3 tables and I need to select records where date is max from these tables.
select * from the_table_1
----------------------------------
contract_key date_1 saldo
1234 30.9.2011 12:06:50 14,6638
select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 26.9.2011 11:04:02 5,6638
select * from the_table_2
----------------------------------
contract_key date_1 saldo
1234 29.9.2011 17:39:43 2,5438
how to do that ?
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 :: Joins - Find Missing Records Between Two Tables?
Mar 7, 2010I have two tables A with columns a.key, a.location_code, a.status and a.first_name and table B with cols b.key, b.location_code, b.status and b.first_name.
I want to find the missing records between the two tables and as well check whether each column is populated correctly. That is if u take a record with id 1 check if loc_code is same in both the tables and if they are different, insert the key and first record column and second record column into a new table. And similarly if there is no record wiht that particular id in the second table, insert the record.
For missing records in the sense for records which are present in A but not in B, am using
Select a.key_no, a.loc_code, b.loc_code
from A,B
where a.key_no=b.key_no(+)
and b.key_no IS NULL
But the problem is I need to put some constraints on the B table like b.status='Married'and b.loc_code='CA'. When am using this condition in the above query, it's throwing me error saying cannot use outer join operator in and or or.And I could not figure out how to check for the columns being populated correctly between the two tables and at the same time check for missing ones
Forms :: Saving Records In Tables After Updating
Jun 8, 2013I AM WORKING ON FORM I WANT TO SAVE THE RECORD IN TO TABLES TABLE1,TABLE2 AFTER UPDATING THE
TABLE1..BOTH THE TABLE HAVE SAME COLUMNS'.
WHAT IS THE TRIGGER FOR THAT
SQL & PL/SQL :: Merge Two Tables Into One And Knock Out Duplicate Records?
Jul 12, 2006I have two identical tables with same structure and have some duplicates between those two. Finally I want to combine togather and in a single table and knockout the duplicates.
SQL how can we do this in a simple way? each tables has around millions records.
Forms :: Display Records Of Two Tables In One Form
Mar 27, 2013want to display records of two tables in in one form.I have 2 tables. tab1 and tab2
columns in tab1 are code, desc, part, onhand_qty AND
columns in tab2 are code, room_no, row_no, rack, shelf, qty, remarks.
Common column in both the tables is code.
i want to display all the columns of both the tables where tab1.code=tab2.code ,and tab1.onhand_qty is not equal to SUM of tab2.qty
Combine Tables With Union But Limit Number Of Records?
Sep 13, 2007Updated to Add: In a last ditch search, I found my answer with ROWNUM <= 1 in the Where clause. It works and I can go from there with what I want to do.
I have a website that pulls similar information from multiple queries using a Union-based query. I want to only pull 1 record from one section, two from another, and 5 from the third. I've so far found LIMIT but haven't been able to get it to work in that way. Is it possible to limit each query in the union as I am looking to do?
The query is:
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
FROM (so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
WHERE p.storyType = 'alert' AND c.display = 'yes' AND c.startDate <= sysDate AND c.endDate >= sysDate
UNION All
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
[code]....
SQL & PL/SQL :: Performance Tuning For Oracle Tables With Million Of Records
Mar 11, 2013Ways for improving the Table performance which holds million of records for oracle. Currently we have partitioning and indexing but it doesn't seem to work.
View 2 Replies View RelatedSQL & PL/SQL :: Inserting Records Into Target Table From Three Source Tables?
Dec 14, 2012I am trying to insert records into target table from three source tables by using function in a package and I am getting error as follows.
SQL> create or replace
2 PACKAGE
casadm.sis_load_cpl_sis_reb_pgm_hist
3 IS
4 /**********************************************************************
******************
[code]....
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'FN_LOAD1T_CPL_SIS_REB_PGM_HIST' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL & PL/SQL :: Records Inserted In Tables For Database Driven Menu
May 16, 2012I have seen the below blog abut database driven menu in java. uRL..... look into the below tables and its data below.I have concerned inserting data in the tables and particularly about
ROLE_DETAILS,ROLE_MASTER and EM_MENUMAS data.
select
level lvl,
ID, NAME,
lpad(' ', level * 14, ' ') || NAME menu,
parent_NAME,nodetype, page_name from em_menumas
[code].....
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 :: 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?
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 View RelatedSQL & PL/SQL :: Multiple Values Of RULES Tables To Find Records Containing A String Format
Sep 4, 2013Insert into PROFILE
(INSTANCE, PROFILENAME, USER_DATA, UPDATE_DATE)
Values
(138, 'Test A', 'SRC!-1,ARCHIVE_OPT!-1,DATE_FIELD!155,DATE_RULE!1,DISTINCT!1', TO_DATE('01/20/2005 13:35:33', 'MM/DD/YYYY HH24:MI:SS'));
/
Insert into RULES
(ID, NAME)
Values
(155, 'DATE_TEST');
I want a code something of this sort:
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
SQL & PL/SQL :: Oracle 10g - Update Records In Target Table Based On Records Coming In From Source
Jun 1, 2010I am trying to update records in the target table based on the records coming in from source. For instance, if the incoming record is present in the target table I would update them in the target else I would simply insert. I have over one million records in my source while my target has 46 million records. The target table is partitioned based on calendar key. I implement this whole logic using Informatica. Looking at the informatica session log I find that the informatica code is perfectly fine but its in the update part it takes long time (more than 5 days to update one million records). find the TARGET TABLE query and the UPDATE query as below.
TARGET TABLE:
CREATE TABLE OPERATIONS.DENIAL_REGRET_FACT
(
CALENDAR_KEY INTEGER NOT NULL,
DAY_TIME_KEY INTEGER NOT NULL,
SITE_KEY NUMBER NOT NULL,
RESERVATION_AGENT_KEY INTEGER NOT NULL,
LOSS_CODE VARCHAR2(30) NOT NULL,
PROP_ID VARCHAR2(5) NOT NULL,
[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]....
Server Utilities :: Append Tables Content To Existing Tables?
Nov 9, 2010problem on oracle 11gR2 where i have to import data from a source database to an existing table without truncate or drop the target table in the target database.
we have found something called table_exist_action=append in impdp.
SQL & PL/SQL :: Returning List Of Records That Do Not Have Associated Records From A Sub-query
Sep 6, 2010I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
Query for anyone interested:
Select distinct
Project_code,
project_name
from
niku.nbi_project_current_facts nbip,
niku.odf_ca_project cst,
niku.prtask t,
[code]........
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description
from niku.srm_resources res,
niku.pac_mnt_resources pac,
niku.departments dep
where res.unique_name = pac.resource_code
and pac.departcode = dep.departcode
and res.is_active = 1
and description like 'IMS%'
and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be:
1. Identify Support Project
2. Identify Resources allocated to the project team
3. Compare with List of Support Resources
4. If any Resources in that list do NOT appear on the project, then return project.
Maintain Large Tables / Cleanup Data From Our Tables
May 18, 2011I have to cleanup data from our tables (Production Environment) that contain millions of rows. The question is apart from the solution of the partitioned tables what alternative recommended solution suggests Oracle?
To delete these tables by using a cursor PL/SQL block or to import all the database and in the tables that we want to remove the old rows to use the QUERY option of the data pump utility.
I have used both ways and i have to admit that datapump solution is much much faster than the deletion that suffers from I/O disk.The question again is which method from these two is more reliable and less risky for the health of the database.
Implementation Where Data From DB2 Tables Are Moved To Oracle Tables
Sep 3, 2012I came across an implementation where data from DB2 tables are moved to Oracle tables, for BI solutioning, using some oracle procedures called from MS SQL DTS packages which are scheduled jobs.Just being curious, can this be done using OWB or ODI rather than the above detour. I suppose there are some changes being done in those procedures before the data is being loaded into Oracle tables, can't this be done using OWB/ODI? Can it be scheduled too as jobs using OWB/ODI?
View 1 Replies View RelatedSQL & PL/SQL :: Audit Tables For Most Of Production Tables
May 29, 2013In our schema we have corresponding audit tables for most of the production tables
Ex Table name Audit Table
EMP EMP_AU
DEPT DEPT_AU
Audit tables will have all the columns of production table along with audit columns AUDIT_DATE , AUDIT_OPERATION There are few production tables which are not having audit tables.I need to write a script to identify
1) Production tables where corresponding audit table is missing
2) Where there is column difference (In case any column missing in audit table) between Production table and Audit table
SQL & PL/SQL :: How To Get Records For Max Value Only
Sep 21, 2010I am working on the below query:-
SELECT adv_list.advisor_code advisor_code,
adv_list.available_wip, tot_score from(
SELECT advisor.advisor_code advisor_code ,
(wip.wip_limit - SUM (DECODE
(lead_status, 'F1', 1,0)))
available_wip,advisor.tot_score
[Code]...
The below query returns :-
Advisor_code Tot_Score
------------- ---------
00015946 10
00026742 10
00081276 10
00540272 5
My requirement is to get only those records that have the max(Tot_Score).
So I should get:-
Advisor_code Tot_Score
------------- ---------
00015946 10
00026742 10
00081276 10
I am trying to put MAX(TOT_SCORE) in the select list but its not giving the desied result.
I do not want to give it in the where clause as I will have to gain put the whole subquery again.
wherein I can get the MAX tot_score records only with minimum coding.
SQL & PL/SQL :: Get AVG Of All Records
Sep 6, 2013Type Specification :
CREATE OR REPLACE TYPE ArrayCounterSum AS OBJECT
(
-- AUTHOR : CLIVE.GREGORY
-- CREATED : 03-04-2010 14:44:02 14:44:02
-- Modified : S. Glass - Removed read function to increase performance
[code]...
Type Body :
TYPE BODY ArrayCounterSum AS
2
3
4
5
[code]...
So the output will be : 6_10_15_13_14 (Sum of all rows and return as an array)My Goal is to AVG the ROWS and retrun it into array. so what change should I make in above code in order to get the AVG of all records.