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].....
I have following schemas in my database scott,sh,hr...I created a triger on a schema scott with the following method
1. sqlplus / as sysdba
2. CREATE TABLE LOGIN_AUDIT_INFO_ALL (
operation VARCHAR2(30),
obj_owner VARCHAR2(30),
object_name VARCHAR2(30),
sql_text VARCHAR2(64),
attempt_by VARCHAR2(30),
attempt_dt DATE);
3.CREATE OR REPLACE TRIGGER login_audit_prod_schemas
AFTER CREATE OR ALTER OR DROP
ON SCOTT.SCHEMA
[code]....
But it don't fulfill my requirement. trigger is fired when scott perform any action. but my requirment is that trigger should be fired when create action is performed on scott by hr,sh or scott. i also want a single trigger which fulfill the requirement.
The best way to structure my schemas within a single Oracle instance to support multiple project development. At the moment, within an Oracle instance I have Area_Dev and Area1_Test schemas, with the intention that Project Xv1.0 would use Dev schema for development and Test schema for testing.
Lets say I want to start on Project Xv2.0 development while I am still finalizing Project Xv.10 development what is the best way of accommodating that, without creating individual schemas for each project?
Is there any way to run a select command between 2 different schemas without using DB links ?
View 1 Replies View RelatedI have a question related to Oracle Data Pump.
So, I want to export two schemas from database with condition:
1. I want to export scheme_1 with all metadata objects + data.
2. I want to export scheme_2 with only metadata objects.
Oracle version is Oracle EE 10.2.0.4.0, OS - Microsoft Server 2003R2.
As far as I know I can not use parameter EXCLUDE like:
EXCLUDE =TABLES:" IN ('SCHEMA_NAME.TABLE')"
(-but this parameter will give me no tables at all) or I can not use CONTENT=SCHEMA_NAME.METADATA_ONLY, maybe I can use QUERY=where table in (select tablename where schema is .... - but I have tables with same name in both schemas).
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.
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%
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
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.
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.)
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)
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.
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)
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
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
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
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.
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
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]...
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].......
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...?
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.
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
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
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?
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 RelatedOne function returns table metadata in xml form. This works for current schema but not for other schemas. Is it a privilege problem?
my code is..
CREATE OR REPLACE FUNCTION TEST.F_DBEG2
RETURN XMLTYPE
AS
Handle NUMBER;
V_OUTPUT_TOTAL XMLTYPE;
V_CNT NUMBER := 0;
BEGIN
[code]....
but when i select the object of another schema, got output.
SELECT * FROM TEST_NEW.DEPT;
Which privilege i need?
I'm trying to get the Create-Statements of all tables of several schemas:
SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name,'<schmema>')
FROM (SELECT TABLE_NAME, OWNER FROM SYS.ALL_TABLES WHERE owner='<schema>') u
Is there a way to create a loop to handle several schemas listed in a array, for example?
I want to find difference between the objects(tables(columns,constraints), views, sequences) in two schemas. One schema is staging and one is development . In development lot of changes are made , Is there a script or a opensource tool which will bring out the difference in columns, constraints in each table and sequences , views etc.
View 2 Replies View RelatedI am trying to export schemas from 10g to 11g. The NLS_CHARACTERSET for 10g is WE8ISO8859P1 and the NLS_CHARACTERSET for 11g is WE8MSWIN1252. Is it fine or do I need to change the character set, so that I will be able to successfully do the export/import?
View -1 Replies View Related