Why does the SYS_REFCURSOR results in a null value?-CodePudding

Table CKSFLTHD

FILTERC	DESCR	ACTIVATE
ABC	Apple Bees Corner	1
EFG	Elephant Forest Grove	1

Table CKSFLTLN

FILTERC	SITE	LNUM
ABC	Apple Bees Corner	ABC123
EFG	Elephant Forest Grove	EFG456

This is what the output should look like, but a null table instead. I'm going to use the DISPLAY to populate a dropdownlist.

DISPLAY	DATAVAL
Apple Bees Corner	ABC

OPEN  cv_2 FOR 
  SELECT descr DispVal, filterc DataVal
  FROM cksflthd
  WHERE activate = 1
  AND ( v_showAll = 1
  OR filterc IN ( SELECT *
                  FROM ( SELECT DISTINCT filterc
                         FROM cksfltln
                         WHERE  SITE IN ( SELECT SITE
                                          FROM cksfltln
                                          WHERE  filterc = v_userfiltercode )

                         AND ( lnum <> ' '
                               OR filterc = v_userfiltercode ) ) useflt
                        
                  WHERE  filterc IN ( SELECT DISTINCT filterc  
                                      FROM cksfltln
                                      WHERE  SITE IN ( SELECT SITE
                                                       FROM cksfltln
                                                       WHERE  filterc = v_filterCode )

                                      AND ( lnum <> ' '
                                            OR filterc = v_filterCode ) )
                       )
                  );

CodePudding user response：

Check your code...

Here's what I did, trying to observe what you shared.

create table CKSFLTLN
 (FILTERC varchar2(10),
  SITE varchar2(25),
  LNUM varchar2(10));

create table CKSFLTHD
 (FILTERC varchar2(10),
  DESCR varchar2(25),
  ACTIVATE varchar2(25));

insert into cksflthd values ('ABC', 'Applebees Corner', 1);
insert into cksflthd values ('EFG', 'Elephant Forest Grove', 1);

insert into CKSFLTLN values ('ABC', 'Applebees Corner', 'ABC123');
insert into CKSFLTLN values ('EFG', 'Elephant Forest Grove', 'EFG456');





CREATE OR REPLACE FUNCTION refcursor_function 
  RETURN SYS_REFCURSOR
AS
  c SYS_REFCURSOR;
  v_userfiltercode varchar2(25) :='ABC';
  v_filterCode varchar2(25) := 'ABC';
  v_showall number(9,0) :=1 ;
BEGIN
  OPEN  c FOR 
  SELECT descr DispVal, filterc DataVal
  FROM cksflthd
  WHERE activate = 1
  AND ( v_showAll = 1
  OR filterc IN ( SELECT *
                  FROM ( SELECT DISTINCT filterc
                         FROM cksfltln
                         WHERE  SITE IN ( SELECT SITE
                                          FROM cksfltln
                                          WHERE  filterc = v_userfiltercode )

                         AND ( lnum <> ' '
                               OR filterc = v_userfiltercode ) ) useflt
                        
                  WHERE  filterc IN ( SELECT DISTINCT filterc  
                                      FROM cksfltln
                                      WHERE  SITE IN ( SELECT SITE
                                                       FROM cksfltln
                                                       WHERE  filterc = v_filterCode )

                                      AND ( lnum <> ' '
                                            OR filterc = v_filterCode ) )
                       )
                  );
  RETURN c;
END;
/

You only shared the cursor definition, not the PL/SQL program you are using, so I mocked one up, and hardcoded your user values...

Then I executed the function with an anonymous block, or really I asked SQL Developer to execute the function for me. And back came the refcursor...not null.