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
ADVERTISEMENT
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
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
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
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
Dec 13, 2012
i need to do a loop on a table and export the data in Excel format (so i need a procedure to do it).
write a java class that build this Excel.. oracle procedure loop around the data and every step my Java class write on Excel file.
So, for this i should initialize my java class for example
public class ExcelExporter{
private String fileName;
public ExcelExporter(String fileName){
this.fileName = fileName;
}
}
So, for this issuse i should call the ExcelExporter costructor from my PL/SQL so i can create an object of it and next i use this to populate my Excel.
How its possible to create Java object from PLSQL ? I've seen on the net all procedure call only Java static methods...
View 5 Replies
View Related
Mar 28, 2013
As per Article mentioned in Oracle Base,I have converted non-partitioned table (1 million data) into range-partition table,but,I don't see performance improvement in explain .
View 9 Replies
View Related
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
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
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
View Related
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
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
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
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
Aug 18, 2011
I am trying to pass the concatenated value as parameter to the select statement. But it is not returning the results.
If i pass mytrx(1) := '123' it is working fine. It is failing when i pass more than one value.
Table script
create table xxtesttrx (trx_number varchar2(50));
insert into xxtesttrx
values('123');
insert into xxtesttrx
values('456');
Code
declare
-- declare the table type
TYPE xxtest IS TABLE OF
xxtesttrx.trx_number%Type
INDEX BY BINARY_INTEGER;
[code]......
View 2 Replies
View Related
Mar 6, 2013
I have a schema in which i have 10000 tables and i want to fetch table names from the schema where org_id is not equal to 1,
Note: Here org_id is the column name .
Provide me sql code or procedure to achieve this.
View 2 Replies
View Related
Nov 26, 2006
For e.g.
if my data is as follows
EX_START_DATE EX_END_DATE EX_CHARGE
5/1/2005 5/5/2005 11:59:59 PM 10
5/6/2005 5/7/2005 11:59:59 PM 10
5/10/2005 5/15/2005 11:59:59 PM 10
5/17/2005 5/19/2005 11:59:59 PM 10
5/20/2005 5/25/2005 11:59:59 PM 20
the record
5/6/2005 5/7/2005 11:59:59 PM 10
is in continuation for
5/1/2005 5/5/2005 11:59:59 PM 10 with the same ex_charge
so i need the output as
5/1/2005 5/7/2005 11:59:59 PM 10
5/10/2005 5/15/2005 11:59:59 PM 10
5/17/2005 5/19/2005 11:59:59 PM 10
5/20/2005 5/25/2005 11:59:59 PM 20
and the remaining rows as it is
(Note :date in mm/dd/yyyy format)
View 6 Replies
View Related
Jun 14, 2013
This query that I pasted is working correctly.Let's say a case has 4 owners, it finds me the first owner and show me its address.
However, I want to execute this query for all owners so it should jump the previous owner it found. Lets say for that example that the loop ends at 4.
How may I fix this problem so that the loop do not return always the first owner but it keeps getting 1,2,3,4...also I should increase the sequence value for each situation
SELECT
DECODE(BREINV.NAMEKEY, NULL,'0','1') "BRE_INV",
DECODE(BREINV.NAMEKEY, NULL,' ',BREINV.SEQUENCE) "BRE_NUMINV1",
DECODE(BREINV.NAMEKEY, NULL, ' ', DECODE(BREINV.SEQUENCE,NULL,NULL,RTRIM(ADDINV.FORMATTEDADDRESS,CHR(0))||CHR(13) || CHR(10) ||'Citizen of ' ||SUBSTR(PAY.COUNTRYDESCRIPTION,1,30))) "BRE_NOMINV",
INDIVIDU.FIRSTNAME || ' ' || INV.NAME "BRE_NOMPREINV"
[code].......
View 3 Replies
View Related
Mar 7, 2012
create table stg1(x number, y number);
create table stg2(x number, y number);
create table stg(x number, y number);
I want to insert data from stg1, stg2 into stg
Instead of writing two insert statements, I want to write only one in a for loop to insert data into stg from stg1 and stg2
I tried this
begin
for i in 1..2 loop
insert into stg(x,y) select stgi.x, stgi.y from stgi;
end loop;
end;
it gives me table does not exist error:
so by stgi, i mean it should take stg1, stg2 etc
View 4 Replies
View Related
Oct 8, 2009
how can i write query to take data from DB, if im entering range of dates :
for example between 20-aug-2009 and 1-aug-2009.
But query must be like, if i enter 21-aug-2009 and second date should the start of the month.
for example if i enter second date like 2-jul-2009, first date should start form begining of that month?
View 1 Replies
View Related
Nov 15, 2010
I need to know the counts(into buckets) for each event_date for a given event_id.
I have 4 buckets that I'm trying to calculate
1. Past (from Today)
2. Today
3. > Today but within 3 days in the future
4. More than 3 days in the future
WITH event_data AS
(SELECT '1' event_id, SYSDATE - 1 event_date
FROM dual
UNION ALL
SELECT '1' event_id, SYSDATE
FROM dual
[code]....
Sample Output:
EVENT_IDPASTTODAYWITHIN_3_DAY_WINDOWOUTSIDE_3_DAY_WINDOW
1 111 1
2 102 0
View 3 Replies
View Related
Apr 25, 2012
I have a table where multiple combination of records are store and i want to display data in range format as below, there is any way to group data as below.
create table ot_shop_Rec ( item varchar2(12), it_name varchar2(20),rev number, qty number)
drop table ot_shop_rec
insert into ot_shop_rec values ( '1018001-1001', 'COL',0,10);
insert into ot_shop_rec values ( '1018001-1002', 'COL',0,10);
insert into ot_shop_rec values ( '1018001-1001', 'GRID',0,10);
insert into ot_Shop_rec values ('1018001-1003','COL',0,10);
I WANT THE OUTPUT IN REPORT LIKE
ITEM RANGE DESC QTY REV
1018001-1001 - 1018001-1003 COL 30 0
1018001-1001 GRID 10 0
View 5 Replies
View Related
May 3, 2013
I have in my database table 2 important date columns: StartDate (Not null) and EndDate(Allowed Null).I want to ensure that all records in the table would always create perfect contiues date ranges with no holes inside.Wor example there may not be records [1-may..1-may, 3-may-...] because there would be a hole [2-may...2-may]. Holes are not allowed.
And overlapping is not allowed, for example [1-may..1-may, 1-may-2may, 3-may-...] is not allowed because overlapping occures on day 1-may. Overlapping and holes are not allowed. But it is allowed that table has no records at all. But all DML manipulations with existing records must ensure that overlapping and holes won't occur.How to write such check? How to ensure that data ranges would stay continous with no holes and no overlaps?
View 10 Replies
View Related
Aug 3, 2011
Need to create Sproc(loop) to insert data..We have 2 table:
- BE Table
Name Null Type
---------------------- -------- --------------
BID NOT NULL NUMBER(10)
BUSINESSENTITYTYPEID NOT NULL NUMBER(10)
PARENTID NUMBER(10)
VERSION NOT NULL NUMBER(10)
FULLNAME VARCHAR2(255)
PHONE VARCHAR2(255)
FAX VARCHAR2(255)
[code]....
Whenever the BE table is read it gives more than 80K records from cust_id=1 and so on and becomes a bit slow ..
Now, when we copy the records from BE to CUSTPAYMETHOD (assume 30k). Next time when we read the table it should show us the customerid from 3001based on New Customer's(user created) Customerid.
CUSTID column is logically linked with BID column (FK not enforced) We don't have to pass parameter as we know the cust_id=1,flag for suspended='Y' and Eff_date between date1 and date2..We have accept from/to Period.Probably a loop to insert...This particular SP will run in all env and the id is going to be different how we are going to handle this...
We want to have a procedure. This is to purge BE table.
1) Insert a new parameter CUST_PMNT_MTHD_RETENTION_PERIOD into system_parameter table(which is only a parameter table) -- this can be done outside of Proc.
2)Create a new Customer(user id) through the Customer application and then find out the custID for the new customer -- this can be done outside of Proc.
SP should be for:
- Store Proc will Update the CustID in the CustPayMethod table to the new customer ID (that was created) when the EFFECTIVESTARTDATE older than CUST_PMNT_MTHD_RETENTION_PERIOD(from system_parameter table) days and suspended=’Y’.
- Copy records with custid=1(anonymous user) and startdata older than retention_period and suspended='Y' to Table CUSTPAYMETHOD
Eventually,delete the record from BE table.BusinessEntity(parent) table is information about customer and customerpaymentmethod(child) table is about payment mode(how payment is made by customer).
Assume If BID=1 then we can find Cust_id=1(which can have many records as the payment mode may change because the customer pay by different mode(credit,debit etc).We have to use loop and commit every 200 or 500 records.
FIrst we have to update the table with new cust_id and then insert...
View 1 Replies
View Related
Feb 19, 2010
I want to update column in table 1 based on a substraction of two column, one from the same table and the other from different table. Then update the result of substraction in table 1. Number of rows in two tables are different.
--for r in (( select (table2.y - table1.y as x from table1, table2 where table1.x = c and table2,.x = m))
declare
i number := 1;
c number ;
m number;
[Code]....
View 8 Replies
View Related
Apr 7, 2010
a project I'm working on. I normally work in SQL Server, so I'm a little stuck on this one.
I have a temp table (tmp_stack) with four columns:
Floor [varchar]
Unit [varchar]
Block [number]
BlockStart [number]
BlockEnd [number]
BlockStart and BlockEnd are currently NULL. What I need to do is loop through the table for each Floor and update BlockStart and BlockEnd for each Unit depending on how many blocks they use and how many have been used by prior units on that floor.
For example:
There are three units on Floor #1: 1A, 1B, and 1C.
1A = 5 blocks
1B = 3 blocks
1C = 2 blocks
For 1A, BlockStart should = 1 and BlockEnd should = 5
For 1B, BlockStart should = 6 and BlockEnd should = 8
For 1C, BlockStart should = 9 and BlockEnd should = 10
And everything should reset back to the beginning on successive floors.
In T-SQL, I would use a cursor, and I assume I need to do the same kind of thing in Oracle, but I can't figure out the syntax.
View 8 Replies
View Related
Sep 13, 2010
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
View 1 Replies
View Related
Mar 28, 2013
formatting the data.I want to group the below table data based on the Grade column for a header_data with start_time and end_time displayed in range. I was trying with group by, partitions etc but no luck. I use version 10gr2.
create table rel_data_mf (header_data varchar2(10),start_time varchar2(100),end_time varchar2(100),grade varchar2(1));
--table rel_data_mf data as comma separated values
header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 01:00:00-05:00,U
ENG,2013-03-29 01:00:00-05:00,2013-03-29 02:00:00-05:00,U
[Code]...
--Required output
header_data,start_time,end_time,Grade
ENG,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
ENG,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,A
ENG,2013-03-29 07:00:00-05:00,2013-03-29 10:00:00-05:00,U
MATH,2013-03-29 00:00:00-05:00,2013-03-29 03:00:00-05:00,U
MATH,2013-03-29 03:00:00-05:00,2013-03-29 07:00:00-05:00,B
MATH,2013-03-29 07:00:00-05:00,2013-03-29 13:00:00-05:00,U
View 6 Replies
View Related
Feb 21, 2013
I would like to cretae 3 partitions for a table
partition 1 = < 2011
partition2 = <2012
partition 3 = any other value not staisfying partition 1 and partition 2
i would like to know how to create a partition 3 without specifying the range so that the data which are not coming under the range of other partitions (Partition 1 & 2) will automatically goes to this partition 3.
Also in the above case if i insert data of year 2009 then where will it go , to partition 1 (or) 2 since both of them matches the condition (<2011 and <2012) (or) do i need to use between clause while creating partitions?
View 3 Replies
View Related
Feb 2, 2012
I'm working with Oracle 10g.
I have a table like this;
ID Amount Date
123 5000 Oct-07-2011
123 null Oct-09-2011
124 7000 Oct-14-2011
124 null Oct-17-2011
124 null Oct-24-2011
What I'm trying to do here is loop thruogh the records and update the amount that's null with the previous amount with the same ID.
View 3 Replies
View Related
