Fetching Data Older Than 2 Hours

Mar 11, 2013

I am fetching data which are older than 2 hours from now. TRANSACTION_TIME is varchar2 field in "MM/DD/RRRR HH:MI:SS PM" format.

SELECT * FROM TRANSACTION_DETAILS
WHERE TO_DATE(TRANSACTION_TIME,'MM/DD/RRRR HH:MI:SS PM') <(SYSDATE - 2/24);

Is there anything wrong in the query.

View 2 Replies


ADVERTISEMENT

Forms :: 6i - One Data Block Based On Table / Fetching Data

Nov 3, 2011

I am working on forms 6i. I have one data block based on table EMP.

Datablock-A: Empno, Ename, Sal

I have FIND Screen: Empno, Ename, FIND Button.

I can search the data through FIND Screen and populate data in Datablock A.

I am using below logic to search data through FIND Screen.

When-Button-Pressed(FIND Button): calling 'EXECUTE_QUERY'.

In Pre-query trigger of DatablockA:

copy(:FIND.EMPNO,'BLOCKA.EMPNO');
copy(:FIND.ENAME,'BLOCKA.ENAME');

It's working fine. But below case is failing.

Let us say, if i enter empno 10 (which is not there in database) in FIND Screen --> press FIND Button, it's showing up 'QUERY CAUSED NO RECORDS', Till this point it's working fine;. But after this, if i press CTR+F11 in block A, it's not pulling records. only this case it's not pulling records.

But if i enter something else in FIND Screen, if it returns any data, then if i press CTR+F11,it's pulling all records.

why it's failing to pull records if i try to query data in first case only.

View 2 Replies View Related

SQL & PL/SQL :: Displaying 24 Hours Data

Dec 27, 2010

I have a table which will the following type of data

"COL1""COL2"
1001"27-DEC-2010 02:00:00"
1002"27-DEC-2010 07:00:00"
1003"27-DEC-2010 09:00:00"
1004"27-DEC-2010 02:00:00"
1005"27-DEC-2010 12:00:00"

Here you can see that we have data for 27th Dec 2010 02,07,09 and 12 hours. I want a query which will show the full 24 hours data even if it doenst have any records. like the following,

COL1 COL2
0 2010122701
1001 2010122702
1004 2010122702
0 2010122703
0 2010122704
0 2010122705
0 2010122706
1002 2010122707

View 2 Replies View Related

SQL & PL/SQL :: Fetching BLOB Data

Jun 20, 2012

I have two different database servers where I need to migrate table data from one schema to another schema in batch wise for eg say 100 rows. I used BULK COLLECT with LIMIT. But to access BLOB data from table I have facing errors. What could be other approache to do the same.

here pc_work is a table containing BLOB data in sourse schema. I am fetch data from this table to table t1_test_work using dblink but not working

[
declare
type array is table of test_work%ROWTYPE;
L_DATA array;
cursor C is select * from pc_work@prpctrg;
begin
open C;
LOOP

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: Fetching The Data From Table?

May 29, 2011

here i have a table called cn_wghmtdt_trn and some table columns are shift_date(date),net_wt(nothing but crushing weight or cane wt),crop_type_code(rotoon or plant),shift_code(its like 1/2/3 but its default 1)

and we have fetch the

1)today crushed weight

2)shift crushed weight

3)till date cane crushed,

4)total rotoon crushed,

View 5 Replies View Related

Fetching Data - Need Link Between Tables

Jun 16, 2011

In my select statement i am fetching the data from

OE_ORDER_HEADERS_ALL,OE_ORDER_LINES_ALL,WSH_DELIVERY_DETAILS,WSH_SERIAL_NUMBERS.

I need appropriate links for that tables.

View 2 Replies View Related

Time Consumption In Fetching The Data

Oct 24, 2010

The below query take time to fetch the data about 25 seconds for 9 rows.

select uctb.member_code As Member_ID,
((uct.price * uct.quantity * i.multiplier) ) As Traded_Value_In_Lacs,
sum(uct.price * uct.quantity) As Total_No_of_Trades,
sum(uct.quantity) As Total_Volume_Of_Trades
[code]......

View 2 Replies View Related

Fetching Data From Table In A Loop From Range Of Row?

Mar 23, 2012

I have dcs_sku table .The record count is 50 thousand in that table.My requirement is to fech every row,create an xml out of it and post the data to some third party.As the count is very huge,I can't select the entire record and do the operation at a time.way which I will run the sql query in a loop,which will fetch 1st from rown 1 to row 1000,next 1001 to 2000,2000 no 'n' row...

I tried the below query:

select * from dcs_sku where rownum between 1 and 200...This gave me the 1st 200 rows and worked fine.

but the moment I changed the query to :

select * from dcs_sku where rownum between 201 and 300:::No result was coming up.

View 1 Replies View Related

Forms :: Select Not Fetching Data Within A Cursor

Feb 8, 2011

SELECT
contgrp_num,
cpgrp_desc,
strategy_id,
perftyp_cd,
stiertyp_id_calcbase,
stiertyp_id,
cpgrp_flg_aggr,
pgrptyp_cd

[Code]..

The above query is not returning any value. But, when I put this within a begin-end block, its fetching data.

why its not returning any data without the begin-end block.

The entire code has an outer begin-end block within which the cursor is also defined.

View 1 Replies View Related

Forms :: Fetching Data - Retrieve Username

Sep 7, 2010

I have a detailed block with a user ID column. The user name is not available in the block. Therefore, I have created a non-database column to retrieve the user name into it.

Here is the code I've used.

PROCEDURE get_details IS

iLoop number := 1;

CURSOR c is

SELECT FULL_NAME
FROM GRP_EMPLOYEE
WHERE EMPLOYEE_NUMBER = :USER_ID;

[Code]...

However, it retrieves the first record only.

The results are displayed like this.

Loop: Result:

1 Full_Name
2 Empty
3 Empty
4 Empty

View 2 Replies View Related

PL/SQL :: Fetching Data From Table Using Date Condition

Nov 30, 2012

I have a table structure and data as below.

create table production
(
IPC VARCHAR2(200),
PRODUCTIONDATE VARCHAR2(200) ,
QUANTITY VARCHAR2(2000),
PRODUCTIONCODE VARCHAR2(2000),
MOULDQUANTITY VARCHAR2(2000));

[Code].....

Now here i want to fetch data having condition as

PRODUCTIONDATE  >= Monday of current week

so i would skip only first two rows and will have to get all rows.

I tried using below condition but it would give not give data for 2013 values.

to_number(to_char(to_date(PRODUCTIONDATE,'yyyymmdd'),'IW')) >= to_number(to_char(sysdate, 'IW'))

View 5 Replies View Related

SQL & PL/SQL :: Unable To Query Table - Fetching Data Timing Out?

May 1, 2011

User complaints fetching data from table A is timing-out ( as per application standards). I decided to collect latest statistics(dbms_stats) on index used by query which is running for more than 12 hours.not.It is partition table and got close to 10 millions records.

1) I checked dba_waiters - no rows
2) I checked v$locked_object/v$locks - no rows
3) I'm unable to run explain-plan for any criteria on that table
4) I'm unable to open table data-tab in Toad/ Sql developer.
5) I'm unable to query a single partition; no error but it keeps on running.

View 8 Replies View Related

PL/SQL :: Remove Records That Are Older Than 2013

Nov 5, 2013

I am trying to remove records that are older than 2013.

delete from reports where rep_date<16-01-13;
My table:  

SQL> desc reports Name          Null?    Type ----------------------------------------- -------- ----------------------------   REP_ID                                    NOT NULL NUMBER(5) SL_ID                                              NUMBER(2) REP_DATE                                           TIMESTAMP(0) SOURCE                                             VARCHAR2(3200) HEADING                                            VARCHAR2(3200) REPORT                                             CLOB  SQL> 

View 7 Replies View Related

Windows / .NET :: Using Older ODBC Drivers With 11G

Sep 13, 2012

the need to use 9i Oracle ODBC drivers with 11g? What issues might be raised with this? Our application is using a very old technology and has issues, in certain functional areas, with 11g ODBC, and we found by reverting the driver to 9.0.0.2 it works.

View 6 Replies View Related

Server Administration :: Moving Tables From Older To New DB?

Mar 23, 2013

I have problem with moving old DB to the new (the same DB 10.2.0 in Win 2003, first in 32 bit, second in 64 bit). I want move DB from 32 to 64 bit. Problem is that all objects in old DB were created in SYSTEM schema by SYS. I can't export that objets (with data) because impdp nor imp don't touch this objects (tables with indexes). I can't use export import procedure. I'm looking for another method to transfer data, which will be the best and the fastest? Maybe files copy on OS? I suppose it will be problems with configuration files, database have other tablespaces.

View 13 Replies View Related

Recover Tablespace (Database) To Older State

Jan 30, 2013

i have the following problem:

At a test database instance of Oracle 11g (11.2.0.3.0) at CentOS 6 i've imported a tablespace from another instance to test my backup strategy.

First i've done a fullbackup with RMAN:
----------
# rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
----------
Then i thought to test the database i could delete something and restore and recover the database from the backup. So i deleted a table in the imported tablespace:
----------
# sqlplus / as sysdba
SQL> drop table TESTTABLE;
----------
After that i would start the restore and recover process by restoring and recovering the tablespace in which I deleted the table with RMAN. At first i've set the tablespace offline:
----------
SQL> alter tablespace TESTTABLESPACE offline immediate;
----------
Then I go to RMAN and start the restoring:
----------
RMAN> restore tablespace TESTTABLESPACE;
----------
Now there is a problem when i execute the recover command. The recover command includes the recovering from the redolog files, right? As I dropped the table from the tablespace this operation was written in the redolog files. So when i make a :
----------
RMAN> recover tablespace TESTTABLESPACE;
----------
The dropped tablespace would be dropped also, because the last redolog file told RMAN to drop the table. So i think I have to restore and recover my tablespace from an older timestamp, so RMAN would ignore the dropping of the table in the redolog file. Is that right so?

And when it is right how can i restore a tablespace from an older date so RMAN would ignore the dropping?

View 4 Replies View Related

Automatic Storage Management :: CMD Delete Files Older Than N Days

Oct 24, 2012

in ASM disk group,

(under one directory)

ASMCMD> ls -lt

...(thousands of log files looks like, for example, log_19.26718.790635667),

how can I delete those files older than 10 days in one command line?

View 5 Replies View Related

Recovery Manager (RMAN) :: Duplicate Restoring Older Version Of File Than Expected?

Feb 28, 2013

We are running 11.2.0.3.2.

In my catalog for the "source" database (rman target db), I have the backupsets for a full database backup ended at Feb. 7, 03:43:37. These are online backups. So, there are archived redo logs being generated while it runs and the following archived redo logs finished at Feb. 7, 04:00:24.

We duplicate databases all the time. So, this is not a new concept for us. The one thing that has changed is that we now back up to disk (using the flashback recovery area) and then later on, initiate a backup to tape. Prior to this go-live, we did all of our backups directly to tape. The catalog does not seem confused. It knows it needs to go to tape because it's beyond the retention for disk backups. The only problem is that it is going to the backup prior to the backupset I want, only for a couple of files.

In the past, when all went directly to tape, we would do a set until time 'Feb. 7, 03:43:37' and it would automatically restore the backupset that finished then and apply archived redo logs as necessary to make a consistent copy. Now, if I use the same model, it's going to a backup set from the prior date for 3 particular files. If I change the time to when the archived redo logs ended their backup, 04:00, it still goes back to the day before, but only for 2 files.

I can list a backup of each specific file and see that the file is in the backupset for which I expect RMAN to pull. How can I figure out a date/time to go back to if not using the method of reviewing the catalog entries and timestamps?

View 4 Replies View Related

Backup & Recovery :: RMAN Script Delete The Backup Pieces Older Than 2 Days

Jan 8, 2013

Is this script is enough to delete the backup pieces older than 2 days.

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
configure device type disk parallelism 1 backup type to compressed backupset;

run
{
allocate channel d1 type disk ;
backup filesperset 5 format '/u06/backup/EBSDEV/EBSDEV_data_t%t_s%s_p%p' database ;
sql 'alter system archive log current' ;
backup filesperset 20 format '/u06/backup/EBSDV3/EBSDV3_arch_t%t_s%s_p%p' archivelog all delete input ;

[Code]....

View 1 Replies View Related

Fetching Last N Records?

Jun 5, 2012

oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

CREATE TABLE MACHINE_M (
IDVARCHAR(20)NOT NULL,
NAMEVARCHAR(20)
)

[Code]....

Now when there records in READING_DWR are 1 lakh, i am getting the o/p in 10 sec.
However when there are bulk records, it is taking 3 to 4 min.

Is there any way to improve my query performance?

View 1 Replies View Related

SQL & PL/SQL :: How To Compare Hours

Jan 10, 2011

I want to write a function that gets:

1. event_date (dd/mm/yyyy)
2. event_start_time (??/??/???? HH24:MI)
3. event_end_time (??/??/???? HH24:MI)

I got a table called EVENTS that got 2 fields:

1. event_date (dd/mm/yyyy HH24:MI)
2. event_end_date (??/??/???? HH24:MI)

and want to check if there is an event in my EVENTS table that occurs in the same dd/mm/yyyy as the input, and can disturb the input event times. means:

input.event_start_time is between EVENTS.event_date
and EVENTS.event_end_date
and
input.event_end_time is between EVENTS.event_date
and EVENTS.event_end_date

but to compare only the hours here! (HH24:MI)
because the date (dd/mm/yyyy) is checked before..

I don't know how to cut only the hours out of the date and compare them, and don't know how to write the whole function.

View 2 Replies View Related

SQL & PL/SQL :: Calculating The Business Hours

Aug 15, 2011

I have a field in Customers table called shipeddate....

I wnat to check the number of hours an item which has a shipeddate is in the store room to the current datetime...

But the business hrs of the store room are from 8am-5pm..

So when a shipped date is 4pm on MOnday

and i am checking on 9 am Tuesday the number of hrs shud be 1(4-5 of Monday)+1(8-9 of tuesday) =2hrss..

How can i achieve this...

View 2 Replies View Related

SQL & PL/SQL :: Fetching Records From Cursor

Jun 21, 2012

I have created one global temporary table in which I inserted 2 rows.

I am fetching the rows by using following cursor :

declare
cursor c1 is
select TTD_TRV_MODE
from global_tra_trv_dtl;
v_trv_mode varchar2(10);
Begin
open c1;

[code]....

But instead of 2 rows , 3 rows are getting fetched.

View 6 Replies View Related

SQL & PL/SQL :: How To Get Correct Minute Between Two Hours

Oct 22, 2012

i am using one query but not getting correct minutes.

here is my query:

v_Interval:= to_timestamp(v_temphrs,'HH24:MI:SS')-to_timestamp(v_outpunch1,'HH24:MI:SS');
v_TotalHrsMin1 := extract(hour from v_interval) * 60 + extract(minute from v_interval);

here v_interval datatype is "interval day to second" and v_temphrs datatype is varchar2 and value is : 12:00:00 and v_outpunch1 datatype is varchar2 and value is: 06:10:00
and v_totalHrsMin1 datatype is number.

here i should get value 370.
but i am getting value 350.

View 3 Replies View Related

SQL & PL/SQL :: Calculating Working Hours?

May 8, 2010

I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. reduce the execution time of this query or if there is any other way to calculate working hours

The following query take 63.499 sec

SELECT sql_calc_found_rows gstime,
MAX(stoptime) AS mx,
MIN(starttime) AS mn,

[Code].....

View 6 Replies View Related

PL/SQL :: Fetching Date Less Than 3 Months

Sep 12, 2012

How can I fetch a date less than 3 months, if date is '31-Mar-2011' ?

View 3 Replies View Related

PL/SQL :: Convert Number To Hours

Jan 1, 2013

I want to convert below MS-SQL query in oracle 10g.

for eg:

Select numasdate,
Cast(numasdate / 60 as Varchar) + ' hours ' +
Cast(numasdate % 60 as Varchar) + ' minutes'
as [TotalHoursAndMinutes]
From
#SampleTable

Output:

9436 157 hours 16 minutes
537 8 hours 57 minutes
9323 155 hours 23 minutes
12525 208 hours 45 minutes

View 8 Replies View Related

PL/SQL :: Generate Hours Range

Oct 4, 2012

I have a table with a date field. This field storage dates with hours like this:

01-08-2012 8:30:00
01-08-2012 8:15:00
01-08-2012 9:30:00
01-08-2012 9:40:00
02-08-2012 8:30:00
02-08-2012 9:30:00
02-08-2012 9:34:00
...

I have to generate a report with the day and the frequency :

__Day_______ Hour Range__CountRecords
WEDNESDAY 8 - 9 2
WEDNESDAY 9 - 10 2
THURSDAY 8 - 9 3

The block of the hour must be 1 hour (8-9,9-10 and so on)

how generate the hours range.

My database oracle version is 8i. (I know that is very old but I can't change because It´s a legacy system).

View 5 Replies View Related

SQL & PL/SQL :: Cursor Not Fetching Values

Apr 21, 2011

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have a cursor in my procedure. When I OPEN, FETCH, it doesnt give me any values. But instead of cursor,if I use the sql , used in the same cursor, then i am getting the value.

DECLARE
l_vin CLM_MAIN.vin%TYPE;
l_part CLM_MAIN.vin%TYPE;
l_clm_id CLM_MAIN.vin%TYPE;

[Code]....

Clm_main has a UNIQUE constrains, of VIN, PART. So when i OPEN the cursor, its not finding clm_id , eventhough it exist in the table. so it takes it as claims_cur%NOTFOUND, an tries to INSERT in the table. But since the record with that VIN and PART already exists, it throws exception that ORA-00001: unique constraint (CLM_MAIN_UK) violated.

View 25 Replies View Related

PL/SQL :: Fetching A Record From A Table?

Apr 11, 2013

I wanted to fetch a records from a table with no. of records and segment_name,owner,size of the table.So, i wrote a query like,

select owner,segment_name,bytes from dba_segments where segment_name='EMP';

then it is working but when i use count(*) for no.of records means how many records this table contains that time, it is showing an error.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved