PL/SQL :: Fetch 1 Lac Rows From Table

Dec 15, 2012

OS : widows 2003

Oracle 9i release 2

I need to Fetch 1 lakh rows from table (all rows) in fastest way.

How to do it?

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Construct Query To Fetch Different Rows From Same Table In Different Columns

May 25, 2013

Lets say I have a table in ORACLE database like:

ACC_ID | ACC_AMT
111 | 10000
111 | 12000
111 | 14000
222 | 25000
222 | 30000
333 | 18000
333 | 27000
333 | 13000
333 | 15000

I want to get the output as:

ACC_ID_1 | ACC_AMT_1 | ACC_ID_2 | ACC_AMT_2 | ACC_ID_3 | ACC_AMT_3
111 | 10000 | 222 | 25000 | 333 | 18000
111 | 12000 | 222 | 30000 | 333 | 27000
111 | 14000 | null | null | 333 | 13000
null | null | null | null | 333 | 15000

I need each different ACC_ID with ACC_AMT in different columns. The table may have other different ACC_ID also, but I will fetch only what I need. What is the best way to do this?

So far I have tried this:

SELECT
(CASE WHEN ACC_ID=111 THEN ACC_ID END) AS ACC_ID_1,
(CASE WHEN ACC_ID=111 THEN ACC_AMT END) AS ACC_AMT_1,
(CASE WHEN ACC_ID=222 THEN ACC_ID END) AS ACC_ID_2,
(CASE WHEN ACC_ID=222 THEN ACC_AMT END) AS ACC_AMT_2,
(CASE WHEN ACC_ID=333 THEN ACC_ID END) AS ACC_ID_3,
(CASE WHEN ACC_ID=333 THEN ACC_AMT END) AS ACC_AMT_3
FROM <TABLE_NAME>

But I am not getting the desired result.

View 22 Replies View Related

SQL & PL/SQL :: Fetch All Rows Into Single Variable

Feb 21, 2012

i want to load all rows of single columns into one variable. After that, that variable will use in any side of the pl sql block.

SET serveroutput ON
DECLARE
CURSOR CUR_DATA
IS
SELECT DAS_SECURITY_CODE FROM SECURITY_TYPE WHERE ASSET_TYPE='DERIVATIVES';
rec_data CUR_DATA%rowtype;

[Code]....

how to resolve this. above query,variable holds last value which fetched from cursor.

View 9 Replies View Related

Reports & Discoverer :: Using Parameter For Maximum Rows To Fetch To Be A Number

Dec 15, 2010

In Report it is possible to set the maximum rows to fetch to be a number e.g. 1+.Is it possible to use a number that is selected from another query? When I try to do this i.e. I put in the field num_lines or :num_lines I just get invalid number

View 10 Replies View Related

SQL & PL/SQL :: Describe Results / Define Output And Fetch Rows Of A Query

Jun 2, 2010

There are several stages for sql processing in 10g2 database concept document.The following stages are necessary for each type of statement processing:

■ Stage 1: Create a Cursor
■ Stage 2: Parse the Statement
■ Stage 5: Bind Any Variables
■ Stage 7: Run the Statement
■ Stage 9: Close the Cursor
Optionally, you can include another stage:
■ Stage 6: Parallelize the Statement

Queries (SELECTs) require several additional stages, as shown in Figure 241:

■ Stage 3: Describe Results of a Query
■ Stage 4: Define Output of a Query
■ Stage 8: Fetch Rows of a Query

Stage 3: Describe Results of a Query The describe stage is necessary only if the characteristics of a query's result are not known; for example, when a query is entered interactively by a user. In this case, the describe stage determines the characteristics (datatypes, lengths, and names) of a query's result.

Stage 4: Define Output of a Query In the define stage for queries, you specify the location, size, and datatype of variables defined to receive each fetched value. These variables are called define variables. Oracle performs datatype conversion if necessary.

I still don't understand what's Stage 3: Describe Results of a Query and Stage 4: Define Output of a Query.

View 2 Replies View Related

Precompilers, OCI & OCCI :: How To Fetch All Rows In A Cursor In Local Variables

Jul 29, 2008

How to fetch all the rows in a cursor in local variables(host variables) in PRO C . /*For x in cur is not working....*/

View 2 Replies View Related

Forms :: ORA-01422 / Exact Fetch Returns More Than Requested Number Of Rows

Aug 18, 2010

ORA-01422: exact fetch returns more than requested number of rows.

I receive this error because i tried to introduce the below coding in a post query of the block.

begin
select supplier_cd into :fin_ex_rev_head.vendor_code
from fin_ex_rev_receipts
where receipt_date
between :keyblock.receipt_date

[code].....

View 10 Replies View Related

Fetch All Information From Big Table For Those Key Present In Small Table

Jun 28, 2012

I am having two tables

Table 1 having 16 cror rows .
Table 2 having 1000 rows

I joined both the tables and fetch all inforamtion from big table for those key present in small table.Join query taking more time to fetch the rows .

View 2 Replies View Related

Forms :: ORA-24374 / Define Not Done Before Fetch Or Execute And Fetch

Mar 24, 2011

FRM-40501: ORACLE error: unable to reserve record for update or delete.

ORA-24374: define not done before fetch or execute and fetch

My master-detail form has single canvas. For both blocks, master and detail, two tables joined together in each. One table to be updated, second table has some info for reference (query only).

I am getting these errors when in detail block the item from LOV is selected for existing record. This does not happen for new record inserted in detail block.

View 1 Replies View Related

OO4O And Wizards :: How To Fetch Columns In Cursor Using FETCH

Nov 1, 2012

create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS

CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;

Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?

View 1 Replies View Related

SQL & PL/SQL :: Fetch Data From Table

May 11, 2011

I want to write a SELECT query on the data which are collected in a PLSQL table which is having 4 columns.

Looping through all the records in the PLSQL table will not get my requirement. Because I need to group the data based on two columns and need to fetch the count of groups.

Is there any way to query that PLSQL table?

View 1 Replies View Related

SQL & PL/SQL :: How To Fetch Name Of Column And Table Name

Nov 30, 2011

how to get name of coloumn and table name which is being modified in trigger

View 8 Replies View Related

Queries Will Fetch Second Highest Sal From Emp Table

Jan 19, 2012

go through both the given queries...

SELECT * FROM EMP A WHERE 1=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE B.SAL>A.SAL);
SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2);

both queries will fetch second highest sal from emp table.which sorting is used by oracle in order by and group by clause.

View 1 Replies View Related

PL/SQL :: Fetch Records From Employee Table

Oct 28, 2012

I am having one table Employee. Employee table having 50 records. I want to fetch 5 records every timeone the query is executed. But it should be like below.

1-5 records
6-10 records
11-15 records
16-20 records
46-50 records

Any one can give the query.

View 5 Replies View Related

PL/SQL :: Fetch DISTINCT Values From Partitioned Table

May 12, 2013

What is the fastest way to fetch DISTINCT values from partitioned table?

1) DISTINCT/UNIQUE
2) GROUP BY
3) PARTITION BY OVER()
4) MAX(ROWID)

Table Definition
CREATE TABLE STG_SOS_SALES_FACT_STUDY
(
  CNTRY_KEY     NUMBER,
  STUDY_ID      NUMBER,
  PRD_KEY_YEAR  NUMBER,
  PRD_KEY_WEEK  NUMBER,
  DATE_FROM     DATE,

[Code]...

-> PARTITION BY RANGE (PRD_KEY_YEAR, PRD_KEY_WEEK)
-> SUBPARTITION BY LIST (CNTRY_KEY)

** Local Partition Indexes
1) CN_SD_CTG_PRD_PRDC_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, CTG_ID, PRDC_KEY)
2) CN_SD_PRD_STR_CTG_IDX = STG_SOS_SALES_FACT_STUDY (PRD_KEY_YEAR, PRD_KEY_WEEK, CNTRY_KEY, STUDY_ID, STR_KEY)#Query:
SELECT DISTINCT PRD_KEY_WEEK, PRD_KEY_YEAR

[Code]...

** Explain Plan:
Plan
SELECT STATEMENT  ALL_ROWSCost: 6,235  Bytes: 629  Cardinality: 37                           
      8 HASH UNIQUE  Cost: 6,235  Bytes: 629  Cardinality: 37                      
         7 CONCATENATION                 
              3 PARTITION RANGE ITERATOR  Cost: 1,985  Bytes: 1,031,900  Cardinality: 60,700  Partition #: 3  Partitions accessed #194 - #207          

[Code]...

Partition #: 7  Partitions determined by Key ValuesThe above query is taking around 6-7 minutes to fetch the data.

View 12 Replies View Related

Forms :: Fetch Record From Table Using Item Code

Jun 7, 2013

i want to fetch the data in my form from table, by using item_code.

eg when i write the item_code its value get matched in same table and fetch the another

columns by item_code.

View 1 Replies View Related

Join Two Oracle Tables For Fast Fetch Of Data From Table?

Jul 2, 2012

join two oracle tables for fast fetch of data from table.

View 1 Replies View Related

SQL & PL/SQL :: Oracle 9i - Block Logic To Fetch Records From Table And Compare

Dec 26, 2011

the following proble.The emp table is having 14 records.

SELECT * FROM emp ORDER BY empno;

EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030

[code]...

The emp table is having 10 records.

SELECT * FROM emp_10 ORDER BY empno;

EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
7566JONES200020

[code]...

I have written the following PL/SQL block logic tofetch the records from the emp table and compare the records with emp_10 table to perform insert if the records are newelse to perform update the existed records in the emp_10 table.

DECLARE
CURSOR tranche_balance_cur
IS
SELECT empno,
ename,
sal,

[code]...

Execution scenario 1:

I have commented insert and update statements in that case I got the following out put.

Inserted Records4
Updated Records10

As per the logic it's giving the correct output because the cursor is fetching 14 records in that already 10 records are existed in emp_10 tableand 4 are new records.so that it's showing the count for inserted records as 10 and updated records as 4.

Execution scenario 2:
I have uncommented insert and update statements in that case I got the following out put.

Inserted Records13
Updated Records1

As per the logic it's not giving the correct output.

I tried with using TRIM function in the comparision logic to avoid spaces.

TRIM(emp_10.empno) = TRIM(tranche_balance_rec.empno)
AND TRIM(emp_10.ename) = TRIM(tranche_balance_rec.ename)
AND TRIM(emp_10.sal) = TRIM(tranche_balance_rec.sal)
AND TRIM(emp_10.deptno) = TRIM(tranche_balance_rec.deptno)

View 10 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.

CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Load 10 Million Rows In Table From Another Table Based On Multiple Joins

Sep 24, 2010

We have to load 10 million rows in a table from another table based on the multiple joins. How much tablespace size we allocate to the table and for performance point of view how much should be the SGA size.

View 11 Replies View Related

Delete 4 Millions Of Rows From A Table Having 25 Millions Of Rows?

Dec 14, 2007

I joined the forum just today, i need some tips on deleting the millions of rows from a huge table having 25 millions of rows.

View 4 Replies View Related

PL/SQL :: How To Transpose A Table From Rows To Columns Or Columns Into Rows

Aug 22, 2012

what are the collections available in Oracle Plsql, what are concepts of collection.

How to Transpose a Table from rows to columns or columns into rows.

DDL and DML concepts.

What is the concepts of statistics in Oracle Plsql.

View 4 Replies View Related

SQL & PL/SQL :: Rows From Each Table In DB

May 5, 2011

Scenario like this , I have a dB which contain 100 tables, i want to find out each table contain how many rows excluding

system tables ..

how can i write query for this.

View 2 Replies View Related

Purging Table Which Is Having 98 M Rows

Dec 3, 2012

I want to purge a table which is having more then 98M rows...here are the details...

Purge Process I followed
---------------------------------------------
Step 1. Created backup table from Main table with required data only
create table abc_98M_purge as
select * from abc_98M where trunc(tran_date)>='01-jul-2011'
-> table created with 5325411 rows

Step 2. truncate table abc_98M

Step 3. inserted all 5325411 rows back to abc_98M from abc_98M_purge using below procedure

DECLARE
TYPE ARRROWID IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
tbrows ARRROWID;
row PLS_INTEGER;
cursor cur_insert is select rowid from abc_98M_purge order by rowid;
BEGIN
open cur_insert;
loop
[code]....

View 4 Replies View Related

SQL & PL/SQL :: How To Delete Rows From Table

Oct 19, 2011

I am using the below proc to delete some records

1)select client_id,count(*) from TCLIENT_NOTIFICATION_PACK where client_id=1620560178 group by client_id order by 2 desc;

client_id count(*)
----------- ---------
16205601785128

2)select client_id, count(*) from TCLIENT_NOTIFICATION_PACK
where client_id=1620560178
group by client_id
having count(*) > 40
order by 2 desc
client_id count(*)
----------- ---------
16205601785128

3) select client_id,clnt_notification_pack_tid
-- bulk collect into v_client_id,v_notif_tid
from (select clnt_notification_pack_tid,
client_id,
clnt_notification_pack_typ_tid,
crte_dt,

[code]....

4) Iam using the below proc to delete the rows from table, except the 4 rows returned above

declare
v_clnt_notification_pack_tid TCLIENT_NOTIFICATION_PACK.CLNT_NOTIFICATION_PACK_TID%type;
tYPE t_client_id is table of TCLIENT_NOTIFICATION_PACK.client_id%type;
tYPE t_notif_tid is table of TCLIENT_NOTIFICATION_PACK.clnt_notification_pack_tid%type;
v_client_id t_client_id;
v_notif_tid t_notif_tid;

[code]....

5) After running this procedure, i shud see 5124 records, but i see zero records.

View 23 Replies View Related

SQL & PL/SQL :: How To Display All The Rows In A Table

Sep 8, 2010

I am bit confused to display all the rows in a column using stored procedure like

In sqlserver:
create procedure test
as
begin
select * from table;
end
in sqlserver it is working perfectly.

In oracle:
create or replace procedure test
is
begin
select * from table;
end;

But in oracle i am getting an error like select * into something like that.I understand the error. i.e. i need to move the column values into variables.if it a single row then there will be no problem.i want to display all the rows in the table at one time.i can not use cursors.

View 3 Replies View Related

SQL & PL/SQL :: Rows Of A Table Updated

Jul 17, 2012

write a query find which rows of a table is updated on 2 days before?

(OR)

In table there r so many rows write a query which two rows r updated last two day before?

View 4 Replies View Related

SQL & PL/SQL :: Delete Even Rows Of A Table

Oct 25, 2006

How to delete even rows of a table or rather alternate rows of a table.

View 31 Replies View Related

SQL & PL/SQL :: How To Delete 1% Of Rows From A Table

Jun 13, 2011

I have a table, in that i have "n" no of rows from that i want to delete 1% of rows from that table

View 26 Replies View Related

PL/SQL :: Reduce Rows In A Big Table

Dec 19, 2012

I have one very big table , says employee table, I need to keep less rows to do this

1. create table employee_backup as select * from employee

2. truncate table employee

3. insert into employee select * from employee_backup where rownum < 1000

is #3 sript is OK? if I only want 1000 rows ?

View 5 Replies View Related







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