I know I am using a outdated Oracle what to do my company are not willing to upgrade because of the economic situtation.
I need generating weekly report.
I have a condition for monthly like this
invoice_date BETWEEN TO_DATE('01-JAN-13 00:00:00', 'dd-mon-yy hh24:mi:ss')
AND TO_DATE('31-JAN-13 23:59:59', 'dd-mon-yy hh24:mi:ss')
now if I want a weekly report Monday - Saturday, How to go about it?
I am working on SQL Developer 1.5.1, i need to prepare hourly record of the activity on the database, for that i have a sql query that gives me the report as per selected columns on hourly basis.I need to prepare 24 reports a day.
Each time i have to go to the query , change the date as per hours like form 22:06:2011 10:00:00 to 22:06:2011 11:00:00 and get the report and export it in excel.
I want to automate the script so that whenever i run the script , it just asks me the date and runs the script 24 times and fetch me the hourly report of whole day.
the query syntax is something like this
Select
............................
...........................
..........................
from
..........................
........................
........................
where
And.......................
And...................
And......................
And req date between to-date( 22:06:2011 10:00:00) And to-date(22:06:2011 11:00:00)
Order by 7,1,2,3,4,5
Is there any possibility that i can automate the script to automatically change the hour itself and generate a report in excel 24 times?
My database is hung while generating the AWR report. I observe the mmon is running for long time. is it okay if i kill, will that have any impact to database.
View 2 Replies View RelatedIm using forms & reports 6i and 10g. I migrated a form from 6i to 10g.The form is working fine, But Im facing problem in report output.When i try to run the report in oracle 10g web layout. It shows error message.
Terminated with error: <br>REP-1247: There exist uncompiled program unit(s). REP-1247: Report contains uncompiled PL/SQL.
When i already compiled the report so many times.
ctrl+k
ctrl+shift+k
ctrl+s
But still got the same error message.
at an Oracle 11gR2 (11.2.0.3.0) Enterprise Edition (fully licensed) with RHEL 6 as OS
I'm trying to generate a statistic, that shows me a weekly or monthly report over the traffic on a tablespace or a schema. Until now, I've worked with the
HIST_VIEW dba_hist_tbspc_space_usage
(I mean there is also one nearly called so for schema), but that view show me only the grow of a tablespace in a timeline of 1 week.
But that would not be the hole traffic. Because SELECT statements will nothing do on the grow of the tablespace but it generates traffic. In that case it wouldn't be so important, if it is more tablespace or schema sided because at this database each tablespace has it's own schema and timeline would be nice if it is weekly or monthly.
I am actually having problems with a report that is generating wrong data in Windows 7 only. I have the same report working correctly on many Windows XP Computers.
View 9 Replies View Relatedquery with which i can achieve following result:- Date,Total space used ,weekly difference.
View 4 Replies View RelatedI need to sum the below daily total to weekly with the week starting day of Saturday Jan 3 2009 for the 52 weeks in 2009. The below query provides me the weekly total with the week starting day MONDAY. But I need the week starting day to be SATURDAY instead of MONDAY.
select to_char(report_date, 'YYYYIW'), sum(total)
from report_table
where to_number(to_char(report_date,'YYYYIW')) >=
to_number(to_char(to_date( '&one_year_ago'),'YYYYIW'))
group by to_char(report_date, 'YYYYIW')
Here is a query I used to generate the Daily Sample Data:
SELECT DISTINCT A.PRODUCT, TO_CHAR(B.BEGIN_DT,'YYYY-MM-DD') as post_date,'RCSL', A.DEPTID, A.ACCOUNT, SUM( A.POSTED_TOTAL_AMT) as Amount_Posted
FROM A, B, C
AND B.BEGIN_DT BETWEEN TO_DATE('&StartDate','YYYY-MM-DD') AND TO_DATE('&EndDate','YYYY-MM-DD')
GROUP BY A.PRODUCT, TO_CHAR(B.BEGIN_DT,'YYYY-MM-DD'), A.DEPTID, A.ACCOUNT
For the below sample data I would need to add data starting 2010-01-02 - 2010-01-08 to get my weekly total.
7777,2010-01-01,RCSL,0170,331062,-85
7777,2010-01-02,RCSL,0170,331063,-190
7777,2010-01-03,RCSL,0170,341413,170.18
7777,2010-01-04,RCSL,0170,347103,-880
7777,2010-01-05,RCSL,0172,331050,-116
7777,2010-01-06,RCSL,0172,331053,-50
7777,2010-01-07,RCSL,0172,331061,-63
7777,2010-01-08,RCSL,0172,331061,-63
I am facing the following issue while generating xml using a sql query. I get the below given table using a query.
CODE ID MARK
==================================
1 4 2331 809
2 4 1772 802
3 4 2331 845
4 5 2331 804
5 5 2331 800
6 5 2210 801
I need to generate the below given xml using a query
<data>
<CODE>4</CODE>
<IDS>
<ID>2331</ID>
<ID>1772</ID>
</IDS>
<MARKS>
[code].....
NOTe: IDS which are distinct needs to be displayed. ALL MARKS should be displayed though there are duplicates.
In the below data, a container is moving from one city to another. 1,2 ,3 can be any number which i want to generate and use as keys to group the cities. Eg: AUH, JEB, CIW belong to the same key=2; SIN, IKT belong to a new group 4. The City where difference between the Seq# is greater than 1 (eg between S8W and AUH), a new group starts.
Conotainer #CitySeqI want this
-------------------------------------------
Container1S8W5251
Container1S8W5261
Container1AUH5362
Container1AUH5372
Container1JEB5382
Container1JEB5392
[code]....
Below is the procedure I created to generate csv file from oracle procedure.
PROCEDURE proc_csv(p_path varchar2 , p_separator varchar2 default ',')
AS
CURSOR c_data
IS
select * FROM product ;
v_file UTL_FILE.file_type;
BEGIN
[code]...
I have two doubts about this procedures
1) In one schema this procedure is generating empty csv file with zero bytes, while in other schem It's working fine, It's showing data. I am unable to make what's the difference.
2) The csv file is not displaying column headings. I want column headings to be displayed on csv file.
Im generating some xml from a table which works fine. Here is an example of what I'm doing.
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
create table "VTEST"(
"PROJECT_NUMBER" VARCHAR2(50 BYTE),
"ORDER_NUMBER" VARCHAR2(50 BYTE),
"PART_NUMBER" VARCHAR2(50 BYTE),
[code]....
so each section would list its unique values.
generating oracle table definitions from XML .xsd files?
View 5 Replies View RelatedI have data like this:
a1 a2 a3 a4
ABC ABC xx zz
ABC xx mm mm
fg ui ok pl
I want to give them unique codes like :
Code for column
a1 Code1
ABCC-1
ABCC-1
fg C-2
then column
a2 Code2
ABC C-1
xxC-3
uiC-4
then column
a3 Code3
xxC-3
mmC-5
okC-6
then column
a4 Code4
zzC-7
mmC-5
plC-8
I can add these `columns in the table Code1 , Code2 etc columns to update.
I have emp table
Empno from_date to_date ScaleBonus_pct Salary
101 31/01/2010 14/02/2010Grade120,10,10,15,25,205000
Reqd. o/p
Emp nofrom_dateScaleEach_WeeK_Sal
10131/01/2010Grade11000(from 20 %X5000
1017/2/2010Grade1500(from 10 %X5000
10114/02/2010Grade1500(from 10 % X5000
10121/02/2010Grade1750(from 15 % X5000
10128/10/2010Grade11250(from 25 % X5000
1017/2/2010Grade11000(from 20 % X5000
we have to split the date by 7 days,should be on Sunday...and if we split by 7 days we have upto 31/01,07/02 and 14/02...but we have to generate 6 days because 20,10,10,15,25,20 (since comma separated value is 6 ).
In weblogic server Axis2 is deployed and it is showing Active.And we have uploaded ODI invoke service also in AXIS2 succesfully and it is showing Active status also...By using the soap its generating the XML with below mentioned error..
some info:- We rae using ODI 10g.
Weblogic 10.3.5.0
XML GENERATING:--_
<xs:element name="inPhasesUptoAndIncludingPostDispatch" nillable="true" type="xs:anyType" />
<xs:element name="key" nillable="true" type="xs:anyType" />
<xs:element name="localPolicyAssertions" nillable="true" type="xs:anyType" />
[code]...
The XML page cannot be displayed Cannot view XML input using XSL style sheet. correct the error and then click the Refresh button, or try again later.
The operation completed successfully. Error processing resource [URL]
</xs:schema></wsdl:types><wsdl:message name="invokeScenarioMessage"><wsdl:part name="part1" element="ns...
Is there any API to generate XSDs from Oracle tables? DBMS_XMLSCHEMA.GENERATESCHEMA accepts only Types and not the Table names. I want to generate XSD from the tables including length, null and default validations.
View 6 Replies View RelatedI have to write a stored procedure/function which has to generate the combination of numbers
For eg: IF I/p is an array of numbers a(i) = [1,2,3]
I want to get various combinations of numbers with these three digits.
writing the stored procedure generating the output for this
I Have got a requirement where we have to generate an xml conforming to a particular XSD.
What i will be having already in place is an XSD document.
We are thinking of making a procedure which will be recieving the set of inputs required for creating that xml.(Data content of that XML.)We would have to make the XML according to that XSD.
Everytime Parsing the XSD is not feasible.We are looking at a solution which would be easy to make the xml from xsd at runtime.These values (DATA) will be available at run time.
I am in urgent need of Generating Random numbers for one of the application .The number once generated is getting stored in table
For this purpose i am using Oracle In-Built function
Quote:Select round(dbms_random.value(1,30)) into a from dual;
Problem is :How to eliminate those numbers to generate which are already stored in Table.
I have a requirement where i have data spread in rows which i have to pivot kind off operation to find the time taken using a Log table.
below is the existing data is present.
col0 col1 col2 col3 col4
30003695/1/2013 8:41Batch Startedproc_unalloc1345
30003705/1/2013 8:41blah proc_unalloc1345
30003715/1/2013 8:46blah proc_unalloc1345
30003725/1/2013 8:46blah proc_unalloc1345
30003735/1/2013 8:56blah proc_unalloc1345
30003745/1/2013 8:57Unalloc Endedproc_unalloc1345
[code].....
Below is what I am trying to find from the above data.
col1Unalloc Alloc Metric
13450.16 1.07 1.3
the result is Hours.Minutes.
I am trying to minus the two dates and find the time taken for a particular section( eg, unalloc ).
I tried using three union queries .
Select block,timestamp From (
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Batch Started' And col3 = 'proc_unalloc' And
Parms Like '1345'
Union
Select 'Unalloc' As Block ,col1,col4 From table_1
Where col2 = 'Unalloc Ended' And col3 = 'proc_unalloc' And
[code].....
I will minus the start and end time for unalloc to get time taken for un alloc. Then i will minus end time of unalloc and end time for alloc to get time taken for alloc... like wise for metric.
generating an xml file using the below data.Table name is T_Data having 4 columns as given below with some data.
Region Divison District Store
---------- ----------------------- ----------- -----------
Northwest Northern California San Jose SJStore1
Northwest Northern California San Jose SJStore2
Northwest Northern California North LA LAStore1
Northwest Northern California North LA LAStore2
Northwest Northern California North LA LAStore3
I want to generate an XML File using SQL/XML Functions and the XML File should look like as given below.
<Region>
<Region Name>Northwest</Region Name>
<Divison>
<Division Name> Northern California </Division Name>
<District>
[code]...
We are using dbms_crypto.hash to generate the MD5 hash value for a CLOB that will be sent to other feeder systems. The feeder systems validate the file by generating the MD5 hash value on their end and compare with the MD5 generated in Oracle and accept the file only if the MD5 hash value matches.
The MD5 file matches between both the systems except in the case where there are special characters in the CLOB. Somehow, MD5 is prefixing  character before any special character and calculating the MD5 value. In our case, our special characters being ®, §, ™, ©....
This is a high priority issue for us and it is holding our UAT. We are using Oracle 11.2.0.2.0.
I am looking for the below one.SQL for generating roles needs to be spooled by automated script.
View 4 Replies View RelatedI want to import schema1 from sourcedb to targetdb. For this I need to generate create sripts and grant scripts for roles related to schema. For example schema1 has role1; rol1 has rol2; rol2 has rol3 and some system privileges and object privileges. How can I generate this script from sourcedb?
View 1 Replies View Relatedi have a select query with four table its generating around 650 rows. and i need to update these 650 rows only.
for example
update ps_po_lining b
set y.recv_req = 'N'
where recv_req in
[Code]....
this query runs but its updating 6000 rows. which is not right. we need to update what ever the select query is retrieving.
I want to know how many archive log generating in One hour at the peak time. We have 6 nodes RAC multiplex 2.
Is there are any query through which I can achieve the above purpose.
Note: As this is a prod instance client is not happy to implement Log Miner utility.
How do i get tkprof trace file in sqldeveloper tool ? I donot have access sql in command mode.
View 5 Replies View Relatedi want to get serial numbers in oracle forms. i have one data block with 10 rows and a non database display item with corresponding length as that of the original data block .
when ever i am executing the form i want to see the serial number generated in the display item and when i inserted a new item or row it should show the corresponding rownum.
eg:
empno name serialno
5 tom 1
4 tinil 2
6 sofy 3
7 john 4
1 albert 5
2 michel 6
i want to get the above answer using forms.
we are inserting a data in table by selecting data from another table.This is part of a very big procedure and the code is like
Loop
SELECTMAX(biz_trn_no) + 1
INTOv_biz_tran_no
FROMnew_business_transactions
WHEREnb_srno = nb_sr_no;
[code]....
Is there any way I can get this functionality without using a sequence? I assume corelated query would be slow and analytic.
