Db2 LUW 11.1 - GET_LINE procedure - NO_DATA_FOUND exception SQLSTATE

Hi there,

I am testing this stored procedure using Db2 11.1 on Windows 10 64-bit.
Informational tokens are "DB2 v11.1.4050.859", "s1911120100",
"DYN1911120100WIN64", and Fix Pack "5".

For NO_DATA_FOUND exception I am getting SQLSTATE = 'ORANF' but in the example below its checking for '02000'.
Is this correct or am I missing something ?

https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/r0053697.html

Thanks.

Regards,

Gagan

Views: 491

Reply to This

Replies to This Discussion

Hi Gagan,

Not sure you got or not.  If not,

As DB2 UDB supports SQL Pl and Oracle PL/SQL procedures. First set compatibility to "ORACLE"

1. db2set  DB2_COMPATIBILITY_VECTOR=ORA.

2. stop db2 instance db2stop

3. restart db2 instance db2start

4.  Inform db2 instance, that your input file is located T below location using the below procedure.

connect to sample;
CALL SYSIBMADM.UTL_DIR.CREATE_OR_REPLACE_DIRECTORY('empdir', 'C:\temp');

and compile your procedure again with 

DECLARE CONTINUE HANDLER FOR SQLSTATE 'ORANF' SET SQLSTATE1 = SQLSTATE;

--SET SERVEROUTPUT ON@
--#SET TERMINATOR @
--set schema - THIRU'@
CREATE or replace PROCEDURE thiru.proc3()
BEGIN
  DECLARE    v_empfile       UTL_FILE.FILE_TYPE;
  DECLARE    v_dirAlias      VARCHAR(50) DEFAULT 'empdir';
  DECLARE    v_filename      VARCHAR(20) DEFAULT 'empfile.csv';
  DECLARE    v_empline       VARCHAR(200);
  DECLARE    v_count         INTEGER DEFAULT 0;
  DECLARE SQLCODE INTEGER DEFAULT 0;
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
  DECLARE SQLSTATE1 CHAR(5) DEFAULT '00000';
  DECLARE CONTINUE HANDLER FOR SQLSTATE 'ORANF' SET SQLSTATE1 = SQLSTATE;
  SET v_empfile = UTL_FILE.FOPEN(v_dirAlias,v_filename,'r');
  loop1: LOOP
    CALL UTL_FILE.GET_LINE(v_empfile, v_empline);
    IF SQLSTATE1 = 'ORANF' THEN -- NO DATA FOUND
      LEAVE loop1;
    END IF;
    CALL DBMS_OUTPUT.PUT_LINE(v_empline);
    SET v_count = v_count + 1;
  END LOOP;
  CALL DBMS_OUTPUT.PUT_LINE('End of file ' || v_filename || ' - ' || v_count
        || ' records retrieved');
  CALL UTL_FILE.FCLOSE(v_empfile);
END@
--#SET TERMINATOR ;
call  thiru.proc3 ;
After this, hope you will get return code zero.
Return Status = 0

If any questions  let me know.

Rgds,

Thiru Swamy

thiruswamy@gmail.com

Hi Thiru,
Thank you for your reply.

Well, I sorted this one out a while back just by replacing  SQLSTATE '02000' with SQLSTATE 'ORANF' as mentioned in the Version 9.7 example.
I already have DB2_COMPATIBILITY_VECTOR set to 'ORA'.


https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm...

Regards,
Gagan

RSS

Latest Activity

Diego Cardalliaguet liked Diego Cardalliaguet's event EMEA IBM Db2 Analytics Accelerator Users Group meeting 2021
14 hours ago
An event by Diego Cardalliaguet was featured
Thumbnail

EMEA IBM Db2 Analytics Accelerator Users Group meeting 2021 at Video conference

May 18, 2021 from 10am to 4:15pm
14 hours ago
Diego Cardalliaguet posted an event
Thumbnail

EMEA IBM Db2 Analytics Accelerator Users Group meeting 2021 at Video conference

May 18, 2021 from 10am to 4:15pm
14 hours ago
Diego Cardalliaguet liked Surekha Parekh's blog post Register Now Enterprise Data & AI Virtual Symposium - 27th April 2021
15 hours ago
Diego Cardalliaguet liked Surekha Parekh's event 2021 April 27th Enterprise Data & AI Virtual Symposium
15 hours ago
Udo Brede liked Jim Wankowski's blog post Leveraging your Db2 Skills with Big Data
18 hours ago
long lin posted a discussion
19 hours ago
blon sin posted a discussion
yesterday
A blog post by Surekha Parekh was featured
Tuesday
blon sin liked Jim Wankowski's blog post Leveraging your Db2 Skills with Big Data
Monday
Jonathan Sloan liked Surekha Parekh's event 2021 April 27th Enterprise Data & AI Virtual Symposium
Apr 14
A blog post by Surekha Parekh was featured
Apr 14
2 events by Surekha Parekh were featured
Apr 14
Jim Reed liked Umesh J Mehta's event TRIDEX Db2 for z/OS Q22021 Meeting
Apr 13
An event by Umesh J Mehta was featured

TRIDEX Db2 for z/OS Q22021 Meeting at Virtual Event

May 20, 2021 from 9:30am to 1:15pm
Apr 13
Jim Reed liked Manuel Gomez Burriel's event IDUG Db2 North America Virtual Tech Conference
Apr 12

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

Forum

who opened the scoring from the penalty spot

Started by long lin in What's hot ? 19 hours ago. 0 Replies

fotbollströja barn eget tryck Lionel Messi opened the scoring from the penalty spot, but that was as good as it got for Ronald Koeman's side as Mbappe took over to evoke memories of recent defeats to…Continue

Fact.MR redefines the way

Started by blon sin in What's hot ? yesterday. 0 Replies

New York, April 20, 2021 - The primary reason for the failure of startups is not lack of funding or intense competition. The main reason is "misunderstanding of market demand."Therefore, for start-ups, it is important to thoroughly study the…Continue

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service