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


ADVERTISEMENT

Backup & Recovery :: Exact Fetch Returns More Than Requested Number Of Row

Aug 24, 2011

I have perform a failover and encounters the following problem.

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

initially primary db: chicago
physical standby db: boston

after failover

primary db:boston
physical standby: NA

after recreating physical standby

primary db:boston
physical standby: chicago

over at chicago (new standby)

log_archive_dest_3=E:chicago_arc
over at boston (new primary)
log_archive_dest_3=E:oston_arc

over at boston when I connect to both boston as target db and recovery catalog

RMAN> list archivelog like 'E:CHICAGO_ARC\%';

List of Archived Log Copies

Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
252690 1 10 A 22-AUG-11 E:CHICAGO_ARC10_759842424_1.ARC

over at chicago when I connect to both chicago as target db and recovery catalog

RMAN> list archivelog like 'E:CHICAGO_ARC\%';

List of Archived Log Copies

Key Thrd Seq S Low Time Name
------- ---- ------- - --------- ----
252690 1 10 A 22-AUG-11 E:CHICAGO_ARC10_759842424_1.ARC

however at E:chicago_arc when I do a os check

C:Documents and SettingsAdministrator>dir /w E:chicago_arc

Volume in drive E is New Volume
Volume Serial Number is C874-DD6D

Directory of E:chicago_arc

[.] [..] 100_759410562_1.ARC
101_759410562_1.ARC 102_759410562_1.ARC 103_759410562_1.ARC

[Code]....

26 File(s) 9,077,989 bytes
2 Dir(s) 2,650,337,280 bytes free

there's more files than indicated by rman

also at both chicago and boston

I have the following error:

RMAN> debug on;

RMAN-03036: Debugging set to level=9, types=ALL

RMAN> report obsolete;

RMAN-06524: RMAN retention policy will be applied to the command
RMAN-06511: RMAN retention policy is set to redundancy 1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of report command at 08/24/2011 23:12:15
RMAN-06004: ORACLE error from recovery catalog database: ORA-01422: exact fetch returns more than requested number of rows

RMAN> debug off;

Debugging turned off

here's the trace files I logged

DBGRCVMAN: EXITING listBackup with exception: ORA-01422: exact fetch returns more than requested number of rows
DBGMISC: krmicomp: error 6004 signalled during compilation [23:12:15.358]
DBGMISC: ENTERED krmkmrsr [23:12:15.358]

[Code]...

How should I resolve this issue?

View 5 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

Forms :: ROWNUM Returns NULL Rows When Value Greater Than 1

Dec 9, 2011

I am trying to do a simple query where I need to return the rows from a table and treat each rown according to some rules.The query works fine, and returns all the rows, usually I have 2 rows returned. WHen I add to the query where ROWNUM = 1, I get the first row returned, but when I use when ROWNUM =2 OR ROWNUM >1, I always get null rows retured, even if I have rows in the database. Here is my query:

SELECT on_time
INTO on_time2
FROM work.work_unit
WHERE work_code = 1
AND emp_no = :entry_blk.p_emp_no
AND work_date = :entry_blk.p_work_date
WHERE ROWNUM = 2;
--RETURN NULL

I changed it to the following format, but still I get the same results, only I get data when I say when rownum = 1, i get back the first record in the query

SELECT on_time
INTO on_time2
FROM (SELECT on_time
FROM work.work_unit
WHERE work_code = 1
AND emp_no = :entry_blk.p_emp_no
AND work_date = :entry_blk.p_work_date)
WHERE ROWNUM = 2;

I can't move forward in my form until I figure out why this is not returning records

View 4 Replies View Related

Forms :: WHEN-VALIDATE-ITEM Trigger Raise Unhandled Exception ORA-01422

Sep 15, 2011

I have a form (StaffDetail) with three different blcoks. One of them is an Application Assignment block which consists of nothing but Assignment Type, Staff (Name), Last Updated By and Last Updated Date.. The Assignment Type and Staff has LOVs assigned to them individually.. Assignment type lets me pick all different types except for Project Manager. As soon as I select this option, i get the following error :

FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception...

This does not even let me select any other Assignment type, then i have to go close all sessions and come back into the same screen.

Also, this form was first created and tested on the development environment and later moved to QA environment. All selections work in the development environment.. I checked for the roles in QA for individual Users..

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

SQL & PL/SQL :: Join Running Forever - Returns No Rows

May 23, 2012

select rl.org_rollup_skey from (select fc.org_skey as "FC_ORG_SKEY" from IA.HIST_FCT_FCST_SLS fc
inner join IA.DIM_ORG do
on fc.org_skey = do.org_skey
where do.org_nam IN ('101', '485','486')) p
INNER JOIN IA.DIM_ORG_HIER h
ON p.fc_org_skey = h.desc_org_skey
inner join IA.FCT_FCST_SLS_ORG_ROLLUP rl
on h.GPRNT_ORG_SKEY = rl.org_rollup_skey

Above join is taking is running forever even as subquery

(select fc.org_skey as "FC_ORG_SKEY" from IA.HIST_FCT_FCST_SLS fc
inner join IA.DIM_ORG do
on fc.org_skey = do.org_skey
where do.org_nam IN ('101', '485','486'))

returns no rows and this subquery give result in 10 seconds according to me Full query should not take more tha 20 secs.

View 1 Replies View Related

SQL & PL/SQL :: Query Returns Multiple Rows / How To Get Distinct Row

Jul 18, 2010

in 10g report builder i have written this query

----------------------------------------------------
SELECT DISTINCT(A.TRANS_NUM)
, A.POST_CD
, A.TRANS_DT
, A.EFF_DT
, A.TRANS_TYPE
, ( SELECT DES FROM SMT_CODE_LIST WHERE CD = A.TRANS_TYPE) DTRANS_TYPE
, A.TIME_STAMP

[code]....

this query returns multiple row. how to get distinct row.

View 5 Replies View Related

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 View Related

Forms :: Handle Huge Number Of Rows In Data Block

Jun 14, 2011

I need to read a huge number of rows, say in lakhs and then need to populate it in data block. Since it is having huge data am never able to run the form. it hangs after some time. when i test with few rows it is working. so no problem in coding.

View 4 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

Performance Tuning :: Servers Will Be Running SELECT Which Returns Zero Rows All Time

Feb 11, 2011

Our application servers will be running a SELECT which returns zero rows all the time.This SELECT is put into a package and this package will be called by application servers very frequently which is causing unnecessary CPU.

Original query and plan

SQL> SELECT SEGMENT_JOB_ID, SEGMENT_SET_JOB_ID, SEGMENT_ID, TARGET_VERSION
FROM AIMUSER.SEGMENT_JOBS
WHERE SEGMENT_JOB_ID NOT IN
(SELECT SEGMENT_JOB_ID
FROM AIMUSER.SEGMENT_JOBS) 2 3 4 5 ;
[code]....

Which option will be better or do we have other options?They need to pass the column's with zero rows to a ref cursor.

View 6 Replies View Related

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 :: 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

SQL & PL/SQL :: ORA-01422 Multiple Records In SP

Jul 30, 2010

I'm looking to create a STORED PROCEDURE to do the following per v1...but I seem to misunderstanding some ORACLE concept(s) as when called in a PLSQL window I'm getting an ORA-01422 message when using the V2 syntax.

Ideally I want this code called from VB, but for the moment I'm trying to get it to work when called from the PLSQL Developer toolkit. Am I trying the wrong concept? per previous posts my background is MSSQL, but I've used Oracle SQL from approx 2 years (at a SQL level) only.

v1
create procedure myabc
as
begin
SELECT custNAME
FROM MYCUSTOMER

[code]....

View 7 Replies View Related

Forms :: Oracle 6i For Windows 7 64 Bit / Can't Find Exact Location From Google

Sep 20, 2012

i want to download form 6i and report 6i for window 7 64bit. from where i can download it. i can't find exact location from google.

View 6 Replies View Related

SQL & PL/SQL :: Query Using DBLink Returns More Rows Than Direct Query?

Apr 6, 2010

I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.

For example:

select
x,y,z
from
mytable@dblink

returns 788,324 rows

while
select
x,y,z
from
mytable

returns 712,102 rows

It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.

View 10 Replies View Related

PL/SQL :: Function That Returns The Check Digit Of 9-digit Number

Jan 17, 2013

I would like a pl/sql function that returns the check digit of a 9-digit number?

Look at

[UR]......

For example number 109409714 (9 digits) should do check digit. I want validation of check digit, & want to return the check digit.

View 8 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

Selecting 100 Number Rows?

Jan 15, 2011

how can I select whole table in parts of 100 rows?

If I have primary key I can:

CODEstart=0;
end=100;
select * from table where ID>=start_point and ID<end;
start=end;
end=end+100;
and repeat:
CODEselect * from table where ID>=start_point and ID<end;

How can I do it without primary key? Is there another posibility to getting 100 number of rows? Maybe using rowid?

View 1 Replies View Related

Display Different Number Of Rows?

Jan 23, 2009

i have a table displaying location, cost, inner_num and rownum ordered by the descending order of the cost.

the question is: display locations of the highest 10 costs. if the cost of 11th row is the same as 11th, then display 11 rows.

View 3 Replies View Related

Select Number Of Rows From Query?

Jun 13, 2007

In sql plus How do I get the number of distinct rows of a certain value?

for example

select group_number from records group by group_number

How would I query for the total number of group_numbers in this query?

View 2 Replies View Related

SQL & PL/SQL :: How To Find Number Of Rows Of View

Oct 22, 2010

is there a way to find out how many rows a View has? Something similar to NUM_ROWS with regular tables perhaps? Or do I have to use Count(*)?

View 4 Replies View Related

SQL & PL/SQL :: Select Only Rows Which Has Number And Not Alphanumeric Value?

Oct 16, 2012

I have a column COL1 in table TAB1 which is varchar2. I want select only rows which has number and not alphanumeric value? I don't want to use regexp for this since

View 11 Replies View Related

SQL & PL/SQL :: Commit After Inserting N Number Of Rows?

Feb 27, 2012

I need to load 2 trillion data from an external table to Oracle Heap table. I am using Direct Path insert for that. how to commit after inserting n number of rows.

View 8 Replies View Related

SQL & PL/SQL :: Selected Number Of Rows From A Table

Aug 3, 2011

Can we select table's rows of 5 to 15, like

select * from emp
where rowid <=5;

It is through an error when i used the below method.

select * from emp
where rowid >=5 and rowid <=15;

View 4 Replies View Related

SQL & PL/SQL :: Rows In Table With Sequence Number

May 17, 2012

I have a table ABC with two columns NAME and SEQUENCE. The valid values of sequence are from 1..50. The table may not have all the Sequence number. For example following is the data in my table..

Quote:NAME SEQUENCE
------------------------
Jonh| 5
Amy| 1
Suresh| 3

I need a SQL to get the data like

Quote:NAME SEQUENCE
------------------------
Amy| 1
| 2
Suresh| 3
| 4
Jonh | 5
|6
|7
..
..
..
|50

Basically I want to have rows for the sequence numbers which are not present i my table. I thought of using the following query to generate a sequence and then join, but does not work.

select rownum from dual connect by rownum <=50

I tried something like

with temp as (select name, sequence from abc where supp_ref_order is not null order by sequence )
select rownum as num, temp.name from dual, temp connect by rownum <=50 where temp.sequence =num

View 12 Replies View Related

SQL & PL/SQL :: Count Number Of Rows With Certain Condition

Jun 21, 2010

To count the number of rows to the below table entries with certain conditions

my table

col1(varchar2(4000)) col2(varchar2(10))
3-1808116101 3-Standard
3-1808312141 3-Standard
3-1808312061 3-Standard
3-1807714441 2-Significant
3-1808284201 2-Significant
3-1808278361 2-Significant
3-1808284111 3-Standard
3-1807714261 3-Standard
3-1807128371 3-Standard
3-1807128351 3-Standard
3-1808301721 2-Significant
3-1808301701 3-Standard
3-1808322501 2-Significant

where conditions:

1)need to take the count of rows(col1).where col2 value is 3 or 2 .
2)value 3 or 2 is extracted and conditioned in the query

View 13 Replies View Related

SQL & PL/SQL :: Select Specific Number Of Rows By Each Value

Oct 17, 2012

This is what I've been trying to do.

I have the following table:

FOOTBALL PLAYERS < ID, NAME, ATTRIBUTE>
100-JIM-TALL
101-BOB-STRONG
102-MARK-SMART
...etc

I want to form a query that regardless of the total returned records, I will be able to specify how many of each "kind" of players I want returned. There are several good reasons that it has to be one query and not many.

View 4 Replies View Related







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