PL/SQL :: Extract Data From Table On Hourly Basis?
Oct 23, 2012
I have a table which have two columns date on hourly basis and response time. I want to pull the previous date's data on hourly basis with the corresponding response time. The data will be loaded to the table every midnight.
eg: Today's date 23/10/2012
I want to pull data from 22/10/12 00 to 22/10/12 23
The below query is pulling the date as required but I am not able to pull the response time.
with a as
(select min(trunc(lhour)) as mindate, max(trunc(lhour)) as maxdate from AVG_HR)
SELECT to_char(maxdate + (level/25), 'dd/mm/yyyy hh24') as dates FROM a CONNECT BY LEVEL <= (1)*24 ;
I have a requirement to extract the data from a table using the UTL file utilities.
My problem is, Say i have a table t1 with column C1,C2, C3, C4, C5. This table t1 gets loaded everyday. i need to pickup the data only that which has changed/inserted in the last load. How can i achieve this ? There is no timestamp in this table.
I need any good suggestion basically I have two different location
1- Factory 2- Head office
I have developed PRODUCTION module in factory and it is working fine and now I want to send data on daily basis to head office therefore I develop a form which will create backup in export format (.dmp) then user will send via email to head office.
Backup file should be save in pre-defined location then user will use a form which I developed for loading data into head office there are two different buttons in this form;
First is used to load data, actually first I load data in a temporary user which creates whenever user will press this button. Second is used to copy data in application user but first it checks if data exists then update otherwise insert.
i create form on which 3 window, 3 Canvas, 3 blocks are there.
1)first block contain 3 items from_date and to_date(nondatabase)and find button
2)second block contains 3 items segment1, quantity and payment_terms
when i enter 2 dates and click on find button 2nd window open. segment1 and quantity between this 2 dates comes from base table like
select segment1,quantity from po_lines_all pll, mtl_system_items mts where pll.ITEM_ID=mts.INVENTORY_ITEM_ID and creation_date between :FROM_DATE and :TO_DATE
and payment_terms should enter manually
3)2nd block contains summary button when i click on it, it save segment1, quantity, payment_terms and open new window which contain segment1 ,qty, payment_terms and when i click on summary button data comes from custom table which group clause means
suppose
item qty terms a 2 2days a 3 2days b 3 3days
then on third form i shows like this a 5 2days b 3 3days
i done all this things but problem is that
suppose once data between 21-aug-2010 and 24-aug-2010 comes on second form , i enter payment_terms and save it. sumaary form also run properly but if another user enter date between 22-aug-2010 and 30-aug-2010 then it will shows data between 21-aug-2010 to 24-aug-2010 from custom table i.e with payment terms as earlier it save and from 25-aug-2010 to 30-aug-2010 it shows data from base table
like
item qty terms date a 2 2days 21aug a 3 2days 22aug b 3 3days 24aug b 4 NULL(ENTER MANUALLY) 26aug c 5 NULL 27aug
I want to adjust 2900 against certain amount from table on fifo basis.
DROP TABLE ABC CREATE TABLE ABC(ID NUMBER PRIMARY KEY,AMT NUMBER); INSERT INTO ABC VALUES(1,1000); INSERT INTO ABC VALUES(2,2000); INSERT INTO ABC VALUES(3,3000);
Can i do it using SQL. I know that it can be done using PL/SQL but i just wanted to confirm if its possible using SQL.
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?
I have a table with a date time column which actually stores the data as and when it is inserted. I need to calculate the time the machine has worked hourly wise. For example the table contains records from 13:00 to 13:15 and 13:45 to 14:00 i should get 30 mins as working time..
I have a table called 'orders' and having date and time of order placed in the field timeplaced. I need to generate a report which is having hourly based number of orders placed . If no order is placed in an hour then it should show 0 for that hour in the result .
I'm a SAP consultant working in SQL on NT platforms. This is the first conversion from Oracle that I have done. My client has provided us with a "Cold" backup of the Oracle dbase on a HD formatted in Unix, I have the partition mounted and I'm able to view the files. I have the ORDATA folder with all the .DBF files.
Q: How do I extract the data from the .DBF files. I need to export to something workable with SQL.
Original database was on Unix, I'm operating on Windows platform.
Im having a problem with writing an appropriate query for a report in my web application. I need it to extract data from three related tables:
CAR( PK CAR_ID INT NOT NULL, TYPE VARCHAR NOT NULL) REPAIR_CENTER( PK REPAIR_CENTER_ID INT NOT NULL, NAME VARCHAR NOT NULL) [code]...
I need the report to display only available cars. Available cars must have these characteristics:
1. if the CAR_REPAIR table is empty, displays all entries from CAR table... 2. if car has multiple entries in the CAR_REPAIR table display only the latest DATE_RETURN if its lower than todays date (SYSDATE), otherwise don't display that car... 3. don't display cars that are in the CAR_REPAIR table and have DATE_RETURN value of NULL
Is there a way where i can extract the data from Oracle Express 6 (OLAP data) into an excel or any other format, so that the same can be loaded onto a MS Sql or a normal Oracle database.
We are getting following error when we are trying to extract data from ASM.
GGS ERROR 500 Oracle GoldenGate Capture for Oracle, ext_1.prm: Getting attributes for ASM file +DATA/testgg/onlinelog/group_1.257.742844671, SQL <BEGIN dbms_diskgroup.getfileattr('+DATA/testgg/onlinelog/group_1.257.742844671', :filetype, :filesize, :lblksize); END;>: (6550) ORA-06550: line 1, column 7: PLS-00201: identifier 'DBMS_DISKGROUP.GETFILEATTR' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignoredNot able to establish initial position for begin time 2011-02-16 16:42:05.
How to extract the data from XML using the xsd file. attached files.
Explanation: first check the EmailMessage tage from order_conf.xml compared with Email.xml(<xsd:element name="EmailMessage">) if exists then go to next node. EmailMessage(exists tag in order xml file) ->next <ns1:emailNotificationype> this tag should be follow under the EmailMessage tag(<xsd:element ref="emailNotificationype">) in Email.xml ->next <ns1:orderNotification> -> check this tag in <xsd:element name="orderNotification"> in Email.xml. -> next <ns1:templateFormatInfo> -> it should follow under <xsd:element name="orderNotification"> in Email.xml. -> next <ns1:templateFormatInfo> -> it should follow these tages <xsd:element name="templateFormatInfo"> <xsd:element ref="templatecode"/> <xsd:element ref="templateversion"/>
select t1.c1 as gr1, t2.c1 as gr2, t1.c2 from test_data t1,test_data t2 where t1.c1<>t2.c1 and t1.c2=t2.c2 and (select count(*) from test_data t3 where t3.c1=t1.c1)= (select count(*) from test_data t4 where t4.c1=t2.c1) order by 1 asc, 2 asc
but I don't find the way to refilter to group the data as expected. The idea is find subsets and show the set of data and values in column c1.
Is it possible for Access to extract data from an Oracle database and upload it directly?
Currently we have a business process where data is being extracted in scheduled queries (30+) to Excel spreadsheets, then manually edited to remove heading lines and imported to an Access database. I see an opportunity to automate a time consuming manual activity by having the Access db extract the data and directly upload it.
A big table of size more than 4 GB from 10g DB needed to be extracted/exported into a text file,the column delimiter is "&|" and row delimiter is "$#".I cannot do it from TOAD as it is hanging while extraction of big table.
In our application, we are allowing user to upload data using excel sheet in UI. We are using PHP script in UI and using SQL Loader to load data from excel sheet to temp_table.
The temp_table has a primary key.
Here my question is , Is there any way to put some batch id for every upload in that table in automatic way ? so that we can easily extract the data by using batch id . we are using Oracle 11g.
