ORA-01002: fetch out of sequence

ORA-01002: fetch out of sequence

ORA-06512: at line 33

01002. 00000 -  "fetch out of sequence"

*Cause:    This error means that a fetch has been attempted from a cursor

           which is no longer valid.  Note that a PL/SQL cursor loop

           implicitly does fetches, and thus may also cause this error.

           There are a number of possible causes for this error, including:

           1) Fetching from a cursor after the last row has been retrieved

           and the ORA-1403 error returned.

           2) If the cursor has been opened with the FOR UPDATE clause,

           fetching after a COMMIT has been issued will return the error.

           3) Rebinding any placeholders in the SQL statement, then issuing

           a fetch before reexecuting the statement.

*Action:   1) Do not issue a fetch statement after the last row has been

           retrieved - there are no more rows to fetch.

           2) Do not issue a COMMIT inside a fetch loop for a cursor

           that has been opened FOR UPDATE.

           3) Reexecute the statement after rebinding, then attempt to

           fetch again.

There are various reasons why the program will fail because of out of sequence. Will explain one of the reason below.

Below code will error because after insert to employee table the transaction was not explicitly ended (committed or rollback).

Hence when rollback the transaction inside the loop the loop becomes out of sequence. 

create table employee (emplid number, employee_name varchar2(40));

Sample code:

declare
    cursor c1 is select invoice_num from ap_invoices_all where rownum <110;
begin

    insert into employee values (1,'ADAM');
    insert into employee values (2,'SAM');
    -- do not commit/rollback
  
    for i in c1 loop 
    
    BEGIN
      -- write bad code to make the update statement fail
      update employee set emplid = '1B';
      exception when others then
      rollback;
    END;
     dbms_output.put_line(i.invoice_num);
    end loop;
end; 

working code
declare
    cursor c1 is select invoice_num from ap_invoices_all where rownum <110;
begin

    insert into employee values (1,'ADAM');
    insert into employee values (2,'SAM');
    COMMIT;
  
    for i in c1 loop 
    
    BEGIN
      -- write bad code to make the update statement fail
      update employee set emplid = '1B';
      exception when others then
      rollback;
    END;
     dbms_output.put_line(i.invoice_num);
    end loop;
end; 

How to make file name parameter in Sqlload in Oracle concurrent program

Date: 27-Feb-2019

Define concurrent program with parameter.
Parameter name can be any name.
Create control file with infile as '$FILE_NAME'

How to load selected rows using Sqlloader

Use "load" option to load selected no of rows.

In below example first row will be loaded into employee table.

Standard hooks in Oracle Purchasing and Oracle payable module

Date: 27-Jul-2017
Instance Version: 12.1.3
Database: 11.2.0.4

Purpose:

Today we will see what is the purpose of standard hooks and how to use it and available hooks in purchasing and payable module.

What is hook: it use to enable the custom functionality or custom validation in the standard process without modifying the seeded objects.

Advantage: When you apply patch, hooks are not impacted so it is safe to apply patch. When you modify seeded objects, oracle will not support and seeded objects can be easy impacted due to patch.

With no delay we will see what are the hooks available.

PO Module:

Submit Purchase Order: 

PO_CUSTOM_SUBMISSION_CHECK_PVT


AP Module:

Invoice Validation

AP_CUSTOM_INV_VALIDATION_PKG

Internet Expense Report:

AP_WEB_EXPENSE_CUST_WF

This package is called in workflow (APEXP).


Example:

I will show you how to add custom logic while submitting purchase Order.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

PACKAGE PO_CUSTOM_SUBMISSION_CHECK_PVT ...... PROCEDURE do_pre_submission_check( p_api_version IN NUMBER, p_document_id IN NUMBER, p_action_requested IN VARCHAR2, p_document_type IN VARCHAR2, p_document_subtype IN VARCHAR2, p_document_level IN VARCHAR2, p_document_level_id IN NUMBER, p_requested_changes IN PO_CHANGES_REC_TYPE, p_check_asl IN BOOLEAN, p_req_chg_initiator IN VARCHAR2, p_origin_doc_id IN NUMBER, p_online_report_id IN NUMBER, p_user_id IN NUMBER, p_login_id IN NUMBER, p_sequence IN OUT NOCOPY NUMBER, x_return_status OUT NOCOPY VARCHAR2 ) IS l_api_name CONSTANT varchar2(50) := 'do_pre_submission_check'; l_api_version CONSTANT NUMBER := 1.0; d_mod CONSTANT VARCHAR2(100) := d_do_pre_submission_check; d_position NUMBER := 0; lv_return_status VARCHAR2(4000); BEGIN x_return_status := FND_API.G_RET_STS_SUCCESS; d_position := 100; -- Call custom validation BEGIN xx_po_process.validate_po(p_document_id,p_user_id,lv_return_status); IF (lv_return_status = 'S' OR lv_return_status IS NULL) THEN x_return_status := FND_API.G_RET_STS_SUCCESS; ELSE x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; FND_MESSAGE.Set_Name('PO', lv_return_status); FND_MSG_PUB.Add; END IF; EXCEPTION WHEN OTHERS THEN NULL; END; EXCEPTION WHEN OTHERS THEN x_return_status := FND_API.G_RET_STS_UNEXP_ERROR; IF po_log.d_exc THEN PO_LOG.exc(d_mod, d_position, SQLCODE || ': ' || SQLERRM); PO_LOG.proc_end(d_mod, 'd_position', d_position); PO_LOG.proc_end(d_mod, 'x_return_status', x_return_status); PO_LOG.proc_end(d_mod); END IF; END do_pre_submission_check;

Thanks for visiting my blog! Your comments are much appreciated!

PL/SQL Record type/ Table type IN OUT example

In pl/sql if you are technical analyst working with record type/table type would be little difficult.

So in this blog we will see how to define table type with IN and OUT put as most of the Oracle standard API are using table type with IN OUT parameter.

Create package with type xx_test_rec and make table type xx_test_tbl from xx_test_rec.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

CREATE OR REPLACE PACKAGE xx_type_test AS TYPE xx_test_rec IS RECORD ( p_id NUMBER, req_num VARCHAR2 (100), x_return_status VARCHAR2 (2000), x_error_code VARCHAR2 (2000), x_msg_data VARCHAR2 (2000) ); TYPE xx_test_tbl IS TABLE OF xx_test_rec INDEX BY BINARY_INTEGER; PROCEDURE xx_proc (p_req_rec IN OUT NOCOPY xx_test_tbl); END xx_type_test;

Create package body with input/output parameter. Loop input parameter and extract data from the input parameter, process the value and can assign value to output parameter.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

/* Formatted on 4/5/2016 8:26:13 PM (QP5 v5.287) */ CREATE OR REPLACE PACKAGE BODY xx_type_test AS PROCEDURE xx_proc (p_req_rec IN OUT NOCOPY xx_test_tbl) AS l_req_tab xx_test_tbl; l_req_rec xx_test_rec; I NUMBER; BEGIN l_req_tab := p_req_rec; I := l_req_tab.FIRST; WHILE I IS NOT NULL LOOP l_req_rec := l_req_tab (I); DBMS_OUTPUT.put_line ('output New 2.1 ' || l_req_rec.p_id); l_req_rec.x_return_status := 'S'; l_req_rec.p_id := l_req_rec.p_id - 1; DBMS_OUTPUT.put_line ('output New 2.2 ' || l_req_rec.p_id); p_req_rec (I) := l_req_rec; I := l_req_tab.NEXT (I); END LOOP; END xx_proc; END xx_type_test;

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

DECLARE l_req_tab_in xx_type_test.xx_test_tbl; l_req_tab_out xx_type_test.xx_test_tbl; I NUMBER; BEGIN FOR i IN 1 .. 2 LOOP l_req_tab_in (i).p_id := 3 + i; END LOOP; xx_type_test.xx_proc (l_req_tab_in); l_req_tab_out := l_req_tab_in; I := l_req_tab_out.FIRST; WHILE I IS NOT NULL LOOP DBMS_OUTPUT.put_line ('tax ' || l_req_tab_out (i).x_return_status); DBMS_OUTPUT.put_line ('id ' || l_req_tab_out (i).p_id); I := l_req_tab_out.NEXT (I); END LOOP; END;

Approval Management Engine (AME) - Query to find Rule/condition/approval type/approval group

Agenda:
This article will help to extract AME data from database.



-- Rule
select * from AME_RULES_TL where rule_id=10023;
--Condition
select * from AME_CONDITION_USAGES where rule_id=10023;
select * from AME_CONDITIONS where condition_id in (12013,12023);
-- Condition value
select * from ame_string_values where condition_id in (12013,12023);
-- Attribute
select * from AME_ATTRIBUTES where attribute_id in (15265,15264,15264);
--Action
select * from AME_ACTION_USAGES where rule_id=10023;
select * from AME_ACTIONS where action_id=16322;
--approval group
select * From hr.ame_approval_groups f where approval_group_id='15019';
--appgroval goroup type
select * from AME_ACTION_TYPES where action_type_id in (10013,10007,10006);
--approval group member
select * from ame_approval_group_members where approval_group_id=;



select distinct a.description rule, e.name attribute,c.PARAMETER_ONE condition_value,c.PARAMETER_two condition_value, c.PARAMETER_THREE condition_value, d.string_value condition_value,k.name approval_type,f.name approval_group
from AME_RULES_TL a, AME_CONDITION_USAGES b,  AME_CONDITIONS c, ame_string_values d ,AME_ATTRIBUTES e, hr.ame_approval_groups f, AME_ACTIONS g, AME_ACTION_USAGES h,AME_ACTION_TYPES k--, ame_approval_group_members i, hr.per_all_people_f j
where 1=1
--and a.description='Req Source - EDC Exception'
and f.creation_date > '01-Jan-2010'
and a.rule_id=b.rule_id
and b.condition_id=c.condition_id
and b.condition_id=d.condition_id(+)
and c.attribute_id=e.attribute_id
and g.PARAMETER = to_char(f.APPROVAL_GROUP_ID)
and g.action_id=h.action_id
and h.RULE_ID=a.rule_id
and h.rule_id=b.rule_id
and k.action_type_id=g.action_type_id
order by 1,2;

--and f.approval_group_id = i.approval_group_id
--  and i.orig_system = 'PER'
--  and j.person_id = i.orig_system_id
--  AND trunc(sysdate) BETWEEN j.effective_start_date AND j.effective_end_date;