SQL & PL/SQL :: Query From Multiple Databases
Apr 14, 2010
I have a query that queries an Oracle 10g database see below
SELECT PLOGDBY, COUNT(PLOGDBY) AS "Calls Logged"
FROM LCC_TPROBLEM WHERE
(PLOGDBY = 'WOODWARD_A' OR
PLOGDBY = 'BASSETT_A' OR
PLOGDBY = 'MISTRY_R' OR
PLOGDBY = 'MEADOWS_K' OR
PLOGDBY = 'SKINNER_R' OR
PLOGDBY = 'DAVIS_Z' OR
PLOGDBY = 'BLOUNT_L' OR
[Code]...
This gives me the result:
PLOGDBY Calls Logged
DAVIS_Z 11
MEADOWS_K 26
MISTRY_R 47
PATEL_RI 53
SKINNER_R 21
THANDI_D 58
WOODWARD_A 30
I also have a query that queries a SQL Server database:
Select Agent, SUM([acd calls which have rung the agent])As CallsRung
FROM Dashboard_stats
Where Date = DATEADD(DAY,DATEDIFF(DAY,'20000101',GETDATE())-1,'20000101')
Group by Agent
This gives me the result:
Davis_Z22
Meadows_K31
Mistry_R54
Patel_R65
Skinner_R35
Thandi_D89
Woodward_A34
What I want to do is write a query that returns the result:
Query 1(Oracle database) as a percentage of Query 2 (SQL Server database)
Results would be:
Davis_Z50%
Meadows_K83%
Mistry_R87%
Patel_R81%
Skinner_R60%
Thandi_D65%
Woodward_A88%
View 4 Replies
ADVERTISEMENT
Nov 23, 2010
We are getting a consultant to upgrade an Oracle 9i installation to 11g R2. The current installation has 6 different databases installed on the same server. Each database is a different customer so for reasons of security we have requested that this be split into 6 virtual machines with one database per machine.
The consultant suggested that they could install the 11g database once and then just make copies (which would all have the same instance name. We are told that the TNS names can be configured so clients are directed to the right database.
View 3 Replies
View Related
Jun 16, 2013
while using top command it shows multiple processes usage.Actually we have 3 databases in the same server.how to find out which database process and user session hogging the cpu resources
View 2 Replies
View Related
Dec 5, 2010
I have Oracle 11g installed on AIX and I want to install another 11gR2 single instance in a different oracle home, will it be possible?
View 3 Replies
View Related
Oct 20, 2011
I have created a procedure which i am executing in one database, in between the procedure i need to connect to diffrent database, how can i do it..
example procedure:
I Started executing procedure in DATABASE1 and i want to switch to DATABASE2
CREATE OR REPLACE PROCEDURE TEST_CONN IS
BEGIN
--SQL STMTS
CONNECT USERNAME/PASSWORD@DATABASE2
--SQL STMTS
END;
/
View 3 Replies
View Related
May 15, 2013
if multiple databases on one 2 nodes RAC is supported ? Explain me the inconvenient to have multiple databases on RAC ?
View 14 Replies
View Related
Oct 28, 2010
ORACLE DBA
EXPERTS
�I can install multiple versions of Oracle databases 10GR2 and 11GR2 in a single node using a unique Oracle ASM single instance?
View 6 Replies
View Related
Mar 27, 2012
I have the task to migrate the total databases(Exact copy to be moved to another server).The current server is going for format.After I did the following steps I am getting the tablespaces(databases)-4 sizes same ,but I am facing issue like some default tablespaces i.e temp,system are not matching.
temp tablespace
***************
current server - 4.0(approximately)
Migrating server - 160 MB
System tablespace
*****************
current server - 580 MB
Migrating server - 220 MB
Also I checked the tables are also matching for the 4 databases.Also Provide the solution or method which is correct.
steps done for migrating(By me)
********************************
EXPORTING DATA USING DATAPUMP
*********************************
1 From command prompt MKDIR 'c:oraclexeapp mp';
2 From SQL prompt conn system/kotak;
3 create or replace directory dmpdir as 'c:oraclexeapp mp';
4 grant read,write on directory dmpdir to kotak;
5 From command prompt
expdp system/kotak@xe full=Y directory=dmpdir dumpfile=xe.dmp logfile=expdpxe.log;
IMPORTING DATA USING DATAPUMP
*****************************
in another server machine
1 From SQL prompt conn system/kotak;
2 create or replace directory dmpdir as 'c:oraclexeapp mp';
3 grant read,write on directory dmpdir to kotak;
4 From command prompt set ORACLE_SID=xe;
5 impdp system/kotak@xe full=Y directory=dmpdir dumpfile=xe.dmp logfile=impdpxe.log;
IN OUR PROCESS we created the below tablespaces and user before IMPORTING created 4 tablespaces
1. kotak
2. kotakdb
3. wired_data
4. ferrari
Created Users which are there in 219 server
1. KOTAK
2. KOTAKDB
3. FC_80
4. DEMOINTERNETBANK
5. DEMOINTERNETBANKDB
6. CREDITCARD
View 1 Replies
View Related
Jun 10, 2013
I am trying to retrieve info from multiple DBs and insert into a central DB via DB LINKS.The links are retrieved via a cursor.
However I keep coming up against 'PL/SQL: ORA-00942: table or view does not exist'..how to handle db_links using a cursor in a pl/sql block? The code is as follows:
DECLARE
db_link_rec VARCHAR2(30);
CURSOR db_link_cur IS
SELECT DB_LINK
from MESSAGING_PROD_LIST;
BEGIN
OPEN db_link_cur;
LOOP
FETCH db_link_cur INTO db_link_rec;
EXIT when db_link_cur%NOTFOUND;
[code]....
View 1 Replies
View Related
Jun 3, 2011
I have a result of Query like this:
Col1 Col2 Col3
T1 15 20
T1 18 19
T1 5 20
T2 15 20
T2 18 19
And I just need to display only the T's that would meet the condition Col2 in (15,18) and Col3 in (20,19).
I try with a condition where col2 in (15,18) and col3 in (20,19) and that works, but I don't need to display T1, because T1 has a Col2=5 and Col3=20, I just have to display T2 that just satisfies all my conditions, Col2 in (15,18) and Col3 in (20,19). It's something like a vertical search.
View 2 Replies
View Related
Dec 17, 2012
Table-Name
ID Status description Tracking ID
1 Strat Frog 1
2 Start Dog 2
3 Process Frog 1
4 Completed Dog 2
5 Start Rabbit 3
6 Error Frog 1
7 Stop Rabbit 3
8 Start Elephant 4
9 process Elephant 4
10 Start Human 5
11 Stop Human 5
12 Start Butterfly 6
13 completed Butterfly 6
14 start lion 7
15 error lion 8
16 complted lion 8
17 start tiger 9
18 error tiger 9
select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss')
And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));
Result should be " Frog and Elephant and tiger"
Start of every record(descrpition --status is Start)
End of every record ( status is stop or done or completed)
status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )
Note:
tracking IDs may change up on error
View 2 Replies
View Related
Dec 17, 2011
I've tried for pivot query feature of Oracle 11g, but I'm trying for pivot result on multiple column.
Herewith I'm displaying my try on single column pivot query.
SQL> select * from
2 (select deptno,job,sal
3 --,comm
4 from emp)
5 pivot (sum(sal) as payment for job in('CLERK','SALESMAN','MANAGER'))
6 order by 1;
[code]....
I've tried this one also, but it didn't seems to be working.
SQL> select * from
2 (select deptno,job,sal,comm
3 from emp)
4 pivot (sum(sal) as payment_sal,sum(comm) as payment_comm for job in('CLERK','SALESMAN','MANAGER'))
5 order by 1;
[code]....
I want result like below.
DEPTNO PAYMENT 'CLERK'_PAYMENT 'SALESMAN'_PAYMENT 'MANAGER'_PAYMENT
---------- ------- --------------- ------------------ -----------------
10 SAL1300 2450
20 SAL1900 2975
30 SAL 950 5600 2850
30 COMM 2200
is it possible to have multiple column pivot query.
View 2 Replies
View Related
Mar 5, 2010
I need to eliminate the blank spaces based on below conditions Consider name column with a value as
Input : "sa c h in Te nd ulka r" where "Sachin" is first name and "Tendulkar" is last name. there is more than 1 space between sachin and tendulkar (here its not displaying properly)
Condition :Second name is seperated from first name with more than 1 spaces and others are with 1 black space. I need to get result as Output:"sachin Tendulkar" ( there should be 1 blank space between first and last name in result.)
View 3 Replies
View Related
Apr 10, 2012
how does this query execute? what kind of a query is this called?
mysql> select ename,(select dname from dept where deptno=e.deptno ) as dname -> from emp e;
+--------+------------+
| ename | dname |
+--------+------------+
| SMITH | RESEARCH |
| ALLEN | SALES |
| WARD | SALES |
| JONES | RESEARCH |
| MARTIN | SALES |
| BLAKE | SALES |
| CLARK | ACCOUNTING |
| SCOTT | RESEARCH |
| KING | ACCOUNTING |
| TURNER | SALES |
| ADAMS | RESEARCH |
| JAMES | SALES |
| FORD | RESEARCH |
| MILLER | ACCOUNTING |
+--------+------------+
14 rows in set (0.00 sec)
View 8 Replies
View Related
May 25, 2013
I need to run the below query and display the result every one hour.
Below code giving result only once.
I think my method is wrong.
SQL> declare
2 counter number :=0;
3 item varchar(300);
4 BEGIN
5 FOR item IN (
[Code]...
PL/SQL procedure successfully completed.
View 12 Replies
View Related
Nov 19, 2012
I have following queries:-
#select name from v$database;
#select log_mode from v$database;
#select count(*)"INVALID_OBJECTS" from dba_objects where status='INVALID';
#select count(*) "INVALID_N/A_INDEXES" from dba_indexes where status!='VALID';
#select count(*)"Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID';
#select count(*) "Broken Jobs" from dba_jobs where broken!='Y';
#select count(*) "Block Corruption" from v$database_block_corruption;
i want a table which can be generated just by select cmd and it will list the result of all the above queires as follow:-
DB_NAME ARCH_MOD INV_OBJ INV_IDX INV_TRG B_JOB BLK_CRP
---------- -------------------------------------- -------------------------------------- ---------- ---------- ---------- ----------
PROD NOARCHIVELOG 0 86 6 3 0
I mean to say i want multiple select queries into 1 table (note:- i m not saying to create a tables and then insert,update(using select from other tables), its just a sheel script that will fetch these record into a txt file)
View 7 Replies
View Related
Sep 6, 2012
I have a sql query as below :
select order_number,
(select decode(hcp.contact_point_purpose,'ABC',hcp.email_address,'CDE',hcp.email_address,null)
from hz_contact_points,
hz_parties hz
WHERE hz.party_id=hcp.owner_table_id) Email
FROM oe_order_headers_all h
WHERE h.order_number='102'
....................
..............
Actually the problem i am facing is the inner select query is returning multiple row , so my main query is erroring out, i need to capture the multiple row.
In the above example the inner decode statement returning two mail address, I need to capture that, but while executing the whole query it is erroring out as saying single query returns multiple values. capture multiple values
View 3 Replies
View Related
Feb 6, 2013
i am trying to generate a SUM from the individual schemas to put on this report but i can't seem to get it right...
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=WINDOWS-1252">
<meta name="generator" content="SQL*Plus 11.2.0">
<TITLE>Launch page </TITLE></head>
<body>
<table border='1' width='90%' align='center' summary='Script output'>
[code].....
View 1 Replies
View Related
Oct 1, 2013
i need a query to get dates for last 7 days and each dates should be in one row... but select sysdate from dual..gives one row...
Expected Output Dates: 01-oct-201330-sep-201329-sep-201328-sep-201327-sep-201326-sep-2013
View 3 Replies
View Related
Feb 16, 2011
I am new to SQL and I am just wondering if there is a solution to a problem I am having.I am using the piece of code below.Essentially what I am doing is selecting a field from a table and ordering that field in descending order. Using the Row_Number feature I then specify which Row I want to return.
Every day the row I will want is the Count of field1 for that day divided by 100 minus 1. This returns a single value of field1 and a single value of R.
I perform this operation every day. The only fields I change every day are the dates and the value of R. I use a seperate piece of SQL code to calculate R each day.
My problem is I have to often populate historical tables with this data. I can only run the code once for each day and for each value of R. Is there anyway I can alter this code such that it can return multiple values of field1 over several dates?The only way I can think of is to repeat the code multiple times using UNION but I am hoping there is a more efficient way.
SELECT *
FROM (SELECT Field1,
ROW_NUMBER() OVER (ORDER BY field1 desc ) R
FROM table
WHERE date >= TO_DATE ('20110215', 'YYYYMMDD')
AND date < TO_DATE ('20110216', 'YYYYMMDD')
)
WHERE R = 1227
--Note: 1227 = (count(field1)/100)-1
View 5 Replies
View Related
Jul 18, 2010
in 10g report builder i have written this query
----------------------------------------------------
SELECT DISTINCT(A.TRANS_NUM)
, A.POST_CD
, A.TRANS_DT
, A.EFF_DT
, A.TRANS_TYPE
, ( SELECT DES FROM SMT_CODE_LIST WHERE CD = A.TRANS_TYPE) DTRANS_TYPE
, A.TIME_STAMP
[code]....
this query returns multiple row. how to get distinct row.
View 5 Replies
View Related
Jan 27, 2012
Actully i am updating two table of data.. but below error message came..
update (select ename, dname
from emp e, dept d
where e.deptno = d.deptno
and empno = 7788)
set ename = 'X', dname = 'Y'
/
Error at line 1
ORA-01776: cannot modify more than one base table through a join view
View 10 Replies
View Related
Mar 8, 2012
I have been given a task to produce an ad-hoc report based on the following conditions (I will give you the structure of the table and details in the table below the requirements)
Requirements: Adhoc Report for Audit on CIT Income Allocation
1. Select from the RETURNS table:
•Id > 3600000 and
•Prog_program_cd = '01' and
[code]...
3.If there are multiple entries from the same account and tax year, only retain the latest record: From the records selected in step 2, if there are multiple records with the same Acct_Id and Period_end_date, only retain the record with the most recent Status_date (i.e. MAX value on the date).
4. Using the results from step 3, link to the applicable RETURN_LINE_ITEMS table where:
•RETURNS.Id = RETURN_LINE_ITEMS.Rtrn_Id
5. From the selected return on RETURN_LINE_ITEMS table, retrieve records where (value on Sch 000 Line 062 > 500,000) and (value on Sch 000 Line 066 < value on Sch 000 Line 062) :
•Sched_nbr = '000', and Litm_line_item_nbr = '062', and Active_ind = 'Y', get Revise_val_amt as 'ab_taxable_income'
•Sched_nbr = '000', and Litm_line_item_nbr = '066', and Active_ind = 'Y', get Revise_val_amt as 'amt_taxable_in_ab'
[code]...
So when you run the scripts above, the tables are created (I have tested it in TOAD) Now let's feed data into both the tables
RETURNS table:
insert into RETURNS
VALUES
(3602448 , '01' ,288151,3876821,9345370 , 403232531, 1, 'TAXPAYER' ,12/4/2008 ,'N', 'Y','N','ASSESSED', 2/18/2009,'SANDEEP',1/2/2008,1/2/2009,
'PRINTED','2/18/2009, 6043073664,'PAPER', 'NOTICE','N');
[code]...
View 15 Replies
View Related
Feb 24, 2011
We recently migrated from 9i to 11g.
One UPDATE query runs for 2min and it opens multiple oracle sessions. Almost 40 sessions. Once the UPDATE is done, the sessions close.
When monitoring V$SESSION, found that the newly opened sessions have a PROGRAM value like "oracle@server (P001)"
The UPDATE query is as below. The query works fine, no issue with performance.
SET t1.text =
( SELECT text
FROM table2 t2
WHERE t1.col1 = t2.col1
AND t1.col2 = t2.col2
AND ROWNUM < 2
[code].......
View 13 Replies
View Related
Oct 22, 2011
Our Team is planning to find a new architecture for our new project. In which we have to fire query to multiple database and then we have to collect all responses from them.(Suppose there are 10databases on which we have to fire query)
I searched a lot,the only thing I got is...It could be possible only through Database link(DbLink),Is there any other way to fire query on distributed databases...?
View 20 Replies
View Related
Oct 20, 2012
These are the tables I'm working with:
SQL> desc custinfo;
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTID VARCHAR2(4)
CUSTNAME VARCHAR2(18)
CUSTADR VARCHAR2(12)
CUSTCITY VARCHAR2(10)
CUSTSTATE CHAR(2)
CUSTZIP VARCHAR2(5)
CAPACITY NUMBER(3)
HOUSECODE VARCHAR2(2)
[code]...
I don't understand why the custid is the same for each customer, and why it's selecting every customer and not just those with more than 150 gallons ordered.
For this one use the oil tables that you set up and use a subquery. Select the minimum average fall use from the house table. Then show all customers whose number of gallons delivered times two is greater than the minimum.
View 4 Replies
View Related
May 9, 2013
this is procedure to send email to multiple receipents
CREATE OR REPLACE PROCEDURE mail ( subject IN VARCHAR2,recievers VARCHAR2)
IS
sender VARCHAR2(30) := 'mailid';
[Code]...
i got procedure sucessfully created
execute mail('hi','mail id1,maildi2');
when i execute the procedure i get the following error
Error at line 1
ORA-29279: SMTP permanent error: 554 5.5.1 Error: no valid recipients
ORA-06512: at "SYS.UTL_SMTP", line 17
ORA-06512: at "SYS.UTL_SMTP", line 98
ORA-06512: at "SYS.UTL_SMTP", line 271
ORA-06512: at "SUPPLIER.MAIL", line 47
ORA-06512: at line 1
View 7 Replies
View Related
May 10, 2012
can we delete multiples table through the single query?
suppose we have 2 table first one is emp and second is client
i want delete all data from emp and client through the single line query
View 1 Replies
View Related
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?
View 4 Replies
View Related
May 13, 2013
Using Oracle 11gR2 on windows 7 client. I have a question on calculating sum() on multiple columns on different columns and store the results in a view. Unfortunately I could not post the problem here as it keeps on giving error "Sorry, this content is not allowed", without telling where or what it is! So I had to post it in the stack-overflow forum, here is the link: [URL] .........
View 6 Replies
View Related
