Data is not displayed for some ID from the procedure in oracle

Question

I have a stored procedure which brings all the data created by a particular users. So if a user with name A has created 100 records and when I run this procedure it should bring 100 records.

Currently it is not bringing all those 100 records, some of them are missing and I am unable to find out why it's like that.

Below is my stored procedure.

create or replace PROCEDURE CHANGEREQUESTS_CREATED_GETLIST
(
  P_USERNAME in CHANGEREQUESTS.CREATEDBY%type
, P_JOBID IN CLOB--VARCHAR2
, P_PAGENO IN NUMBER
, P_PAGESIZE IN NUMBER
, TOTALCOUNT OUT NUMBER
, OUTPUTTABLE OUT SYS_REFCURSOR
) AS 

  START_RECORD_NO NUMBER := 1;
  END_RECORD_NO   NUMBER := 10;
BEGIN

  IF P_PAGENO IS NOT NULL AND P_PAGESIZE IS NOT NULL THEN
    START_RECORD_NO := ((P_PAGENO -1) * P_PAGESIZE) +1;
    END_RECORD_NO := P_PAGESIZE * P_PAGENO;
  END IF;
    
  --open TOTALCOUNT for
  SELECT COUNT(*) INTO TOTALCOUNT 
  FROM  
      CHANGEREQUESTS CR
  where
   EXISTS ( SELECT * FROM THE ( SELECT CAST( STR2TBL( P_JOBID ) AS MYTABLETYPE ) FROM DUAL ) TT WHERE TT.COLUMN_VALUE = CR.JOBID )
      --CR.JOBID in (select REGEXP_SUBSTR(P_JOBID,'[^,]+', 1, level) from DUAL connect by REGEXP_SUBSTR(P_JOBID, '[^,]+', 1, level) is not null)
      and CREATEDBY = P_USERNAME
      and JOBID > 0;
          
  OPEN OUTPUTTABLE FOR 
  select 
     CHANGEREQUESTID
    ,DESCRIPTION 
    ,CHANGEREQUESTNUMBER
    ,STATENAME
    ,CITYNAME
    ,CATEGORY
    ,CHANGETYPE
    ,STATUSID
    ,CREATEDBY
    ,OPENCLOSED
    ,JOBID
    ,CREATEDON
    ,LASTMODIFIEDON
  from
  (   select 
       ROW_NUMBER() over ( order by CR.CHANGEREQUESTID DESC) ROWNO
      ,CR.CHANGEREQUESTID
      ,CR.DESCRIPTION 
      ,CR.CHANGEREQUESTNUMBER
      ,CR.STATENAME
      ,CR.CITYNAME
      ,CC.CATEGORY
      ,CT.CHANGETYPE
      ,CR.STATUSID
      ,CR.CREATEDBY
      ,'' as OPENCLOSED
      ,CR.JOBID
      ,CR.CREATEDON
      ,CR.LASTMODIFIEDON
      FROM  
        CHANGEREQUESTS CR
          inner join
        CHANGETYPES CT
          on CR.CHANGETYPEID = CT.CHANGETYPEID
          inner join 
        CHANGECATEGORIES CC
          ON CC.CHANGECATEGORYID = CT.CHANGECATEGORYID
      where
       EXISTS ( SELECT * FROM THE ( SELECT CAST( STR2TBL( P_JOBID ) AS MYTABLETYPE ) FROM DUAL ) TT WHERE TT.COLUMN_VALUE = CR.JOBID )
       --CR.JOBID in (select REGEXP_SUBSTR(P_JOBID,'[^,]+', 1, level) from DUAL connect by REGEXP_SUBSTR(P_JOBID, '[^,]+', 1, level) is not null)
      and CREATEDBY = P_USERNAME
        and JOBID > 0
        --ORDER BY CR.CHANGEREQUESTID DESC
      )
  WHERE ROWNO >= START_RECORD_NO AND ROWNO <= END_RECORD_NO;
  
END CHANGEREQUESTS_CREATED_GETLIST;

Also the table definition and required details are below

`CHANGEREQUESTS`

Name                        Null     Type           
--------------------------- -------- -------------- 
CHANGEREQUESTID             NOT NULL NUMBER(10)     
CHANGEREQUESTNUMBER         NOT NULL VARCHAR2(50)   
CHANGETYPEID                NOT NULL NUMBER(10)     
NETWORKTYPE                          VARCHAR2(50)   
STATENAME                            VARCHAR2(50)   
CITYNAME                             VARCHAR2(50)   
DESCRIPTION                          VARCHAR2(100)  
REASON                               VARCHAR2(250)  
REMARK                               VARCHAR2(250)  
CREATEDBY                            NVARCHAR2(100) 
CREATEDON                            DATE           
LASTMODIFIEDBY                       NVARCHAR2(100) 
LASTMODIFIEDON                       DATE           
JOBID                                NUMBER(10)     
GROUPID                              NUMBER(10)     
STATUSID                             NUMBER         
ISCANCELLED                 NOT NULL NUMBER(1)      
APPLICATIONID                        NUMBER(10)     
APPCHANGETYPE                        VARCHAR2(50)   
CHANGEDETAILID                       NUMBER(10)     
REQSUBMITTEDON                       DATE           
PRIORITY                             VARCHAR2(50)   
MODELING_CHANGE_TYPE_IDS             VARCHAR2(200)  
MODELING_CHANGE_DETAILS_IDS          VARCHAR2(200)  
DB_CHANGE_DETAIL_ID                  NUMBER(10)     
BAND                                 VARCHAR2(2000) 



`CHANGETYPES`

Name             Null     Type          
---------------- -------- ------------- 
CHANGETYPEID     NOT NULL NUMBER(10)    
CHANGETYPE       NOT NULL VARCHAR2(250) 
CHANGECATEGORYID          NUMBER(10)    
ISACTIVE         NOT NULL NUMBER(1) 


`CHANGECATEGORIES`

Name             Null     Type          
---------------- -------- ------------- 
CHANGECATEGORYID NOT NULL NUMBER(10)   
CATEGORY         NOT NULL VARCHAR2(50) 
ISACTIVE         NOT NULL NUMBER(1)

UPDATE

So basically my Oracle version is:- 18.3

Also,

The above procedure consists of 3 tables in which main columns are CHANGETYPEID, CHANGECATEGORYID They have joined to bring the data.

So my main table is CHANGEREQUESTS where records are been fetched from joining the ID's of other two tables.

For ex:-

CREATE TABLE CHANGEREQUESTS ( CHANGEREQUESTID, CHANGETYPEID, JOBID ) AS 
SELECT 7835,  4, 42234 FROM DUAL UNION ALL
SELECT 7834, 22, 42233 FROM DUAL UNION ALL
SELECT 7833,  8, 42242 FROM DUAL;

CREATE TABLE CHANGETYPES (CHANGETYPEID, CHANGETYPE, CHANGECATEGORYID, ISACTIVE) AS
SELECT  8, 'Change in media type (OFC/MW)', 1, 1 FROM DUAL UNION ALL
SELECT 22, 'RF site deletion',              1, 1 FROM DUAL;

CREATE TABLE CHANGECATEGORIES ( CHANGECATEGORYID, CATEGORY, ISACTIVE ) AS
SELECT 3, 'OSP Engineering', 1 FROM DUAL UNION ALL
SELECT 2, 'Wireline',        1 FROM DUAL UNION ALL
SELECT 1, 'Wireless',        1 FROM DUAL;

user_1509264 · Answer

You can simplify your procedure a lot by:

testing if the jobid is a substring of the p_jobid parameter; and
using OFFSET m ROWS FETCH NEXT n ROWS ONLY syntax.

create or replace PROCEDURE CHANGEREQUESTS_CREATED_GETLIST
(
  P_USERNAME IN CHANGEREQUESTS.CREATEDBY%type
, P_JOBID    IN CLOB--VARCHAR2
, P_PAGENO   IN NUMBER
, P_PAGESIZE IN NUMBER
, TOTALCOUNT OUT NUMBER
, OUTPUTTABLE OUT SYS_REFCURSOR
)
AS
  v_size  NUMBER := COALESCE( p_pagesize, 10 );
  v_start NUMBER := COALESCE( p_pageno - 1, 0 ) * v_size;
BEGIN
  SELECT COUNT(*) INTO TOTALCOUNT 
  FROM   CHANGEREQUESTS CR
  WHERE  ',' || p_jobid || ',' LIKE '%,' || jobid || ',%'
  AND    CREATEDBY = P_USERNAME
  AND    JOBID > 0;

  OPEN OUTPUTTABLE FOR 
  SELECT CR.CHANGEREQUESTID
        ,CT.CHANGETYPE
        ,CR.JOBID
  FROM   CHANGEREQUESTS CR
         INNER JOIN CHANGETYPES CT
           ON ( CR.CHANGETYPEID = CT.CHANGETYPEID )
         INNER JOIN CHANGECATEGORIES CC
           ON ( CC.CHANGECATEGORYID = CT.CHANGECATEGORYID )
  WHERE  ',' || p_jobid || ',' LIKE '%,' || jobid || ',%'
  AND    CREATEDBY = P_USERNAME
  AND    JOBID > 0
  ORDER BY CR.CHANGEREQUESTID DESC
  OFFSET v_start ROWS
  FETCH NEXT v_size ROWS ONLY;
END CHANGEREQUESTS_CREATED_GETLIST;
/

Which, for the sample data:

CREATE TABLE CHANGEREQUESTS ( CHANGEREQUESTID, CHANGETYPEID, JOBID, CREATEDBY ) AS 
SELECT 7835,  4, 42234, 'user1' FROM DUAL UNION ALL
SELECT 7834, 22, 42233, 'user1' FROM DUAL UNION ALL
SELECT 7833,  8, 42242, 'user1' FROM DUAL UNION ALL
SELECT LEVEL,
       DECODE( MOD( LEVEL, 3), 0, 4, 1, 22, 8 ),
       99999,
       'user1'
FROM   DUAL
CONNECT BY LEVEL <= 40;

CREATE TABLE CHANGETYPES (CHANGETYPEID, CHANGETYPE, CHANGECATEGORYID, ISACTIVE) AS
SELECT  4, 'Random Type',                   1, 1 FROM DUAL UNION ALL
SELECT  8, 'Change in media type (OFC/MW)', 1, 1 FROM DUAL UNION ALL
SELECT 22, 'RF site deletion',              1, 1 FROM DUAL;

CREATE TABLE CHANGECATEGORIES ( CHANGECATEGORYID, CATEGORY, ISACTIVE ) AS
SELECT 3, 'OSP Engineering', 1 FROM DUAL UNION ALL
SELECT 2, 'Wireline',        1 FROM DUAL UNION ALL
SELECT 1, 'Wireless',        1 FROM DUAL;

Then:

DECLARE
  v_pgsz NUMBER := 20;
BEGIN
  FOR v_pgno IN 1 .. 3 LOOP
    DECLARE
      v_cur  SYS_REFCURSOR;
      v_size NUMBER;
      v_crid CHANGEREQUESTS.CHANGEREQUESTID%TYPE;
      v_ctyp CHANGETYPES.CHANGETYPE%TYPE;
      v_jid  CHANGEREQUESTS.JOBID%TYPE;
    BEGIN
      CHANGEREQUESTS_CREATED_GETLIST( 'user1', '42234,99999', v_pgno, v_pgsz, v_size, v_cur );
      DBMS_OUTPUT.PUT_LINE( 'Page:' || v_pgno || ' (Total: ' || v_size || ' rows)' );
      LOOP
        FETCH v_cur INTO v_crid, v_ctyp, v_jid;
        EXIT WHEN v_cur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(
          LPAD( v_crid,  4, ' ' ) || ' | ' ||
          RPAD( v_ctyp, 30, ' ' ) || ' | ' ||
          LPAD( v_jid,   4, ' ' )
        );
      END LOOP;
      CLOSE v_cur;
    END;
  END LOOP;
END;
/

Outputs:

Page:1 (Total: 41 rows)
7835 | Random Type                    | 4223
  40 | RF site deletion               | 9999
  39 | Random Type                    | 9999
  38 | Change in media type (OFC/MW)  | 9999
  37 | RF site deletion               | 9999
  36 | Random Type                    | 9999
  35 | Change in media type (OFC/MW)  | 9999
  34 | RF site deletion               | 9999
  33 | Random Type                    | 9999
  32 | Change in media type (OFC/MW)  | 9999
  31 | RF site deletion               | 9999
  30 | Random Type                    | 9999
  29 | Change in media type (OFC/MW)  | 9999
  28 | RF site deletion               | 9999
  27 | Random Type                    | 9999
  26 | Change in media type (OFC/MW)  | 9999
  25 | RF site deletion               | 9999
  24 | Random Type                    | 9999
  23 | Change in media type (OFC/MW)  | 9999
  22 | RF site deletion               | 9999
Page:2 (Total: 41 rows)
  21 | Random Type                    | 9999
  20 | Change in media type (OFC/MW)  | 9999
  19 | RF site deletion               | 9999
  18 | Random Type                    | 9999
  17 | Change in media type (OFC/MW)  | 9999
  16 | RF site deletion               | 9999
  15 | Random Type                    | 9999
  14 | Change in media type (OFC/MW)  | 9999
  13 | RF site deletion               | 9999
  12 | Random Type                    | 9999
  11 | Change in media type (OFC/MW)  | 9999
  10 | RF site deletion               | 9999
   9 | Random Type                    | 9999
   8 | Change in media type (OFC/MW)  | 9999
   7 | RF site deletion               | 9999
   6 | Random Type                    | 9999
   5 | Change in media type (OFC/MW)  | 9999
   4 | RF site deletion               | 9999
   3 | Random Type                    | 9999
   2 | Change in media type (OFC/MW)  | 9999
Page:3 (Total: 41 rows)
   1 | RF site deletion               | 9999

db<>fiddle here

Data is not displayed for some ID from the procedure in oracle

1 Answers