Home » Applications » Oracle Fusion Apps & E-Business Suite » function call with optional parameters
function call with optional parameters [message #687289] Wed, 15 February 2023 18:57 Go to next message
deahayes
Messages: 6
Registered: January 2023
Junior Member
I have this function call from a concurrent program parameter:
select RPT_PROJNUM_PKG.get_selected_list(:$FLEX$.PW_PROJECT_NUMBERS, :$FLEX$.PW_PROJECTS) from dual

I would like :$FLEX$.PW_PROJECTS to be optional and only used when :$FLEX$.PW_PROJECT_NUMBERS = ALL. The value from each comes from lovs(valuesets)


FUNCTION get_selected_list( p_item VARCHAR2, p_pw_project_num VARCHAR2 default NULL)
RETURN VARCHAR2
IS 
  v_pw_project_num varchar2(30);
BEGIN
  -- set default
  v_pw_project_num := nvl(p_pw_project_num,'P');

  IF p_item = 'Clear' THEN
    v_selected_list := NULL;
  Elsif p_item = 'All' Then
    SELECT LISTAGG(segment1,',') WITHIN GROUP (ORDER BY segment1) into v_selected_list
    FROM PW_PROJNUMS_V
    where class_code = nvl(v_pw_project_num, class_code);
    dbms_output.PUT_LINE (' elsif VSELECT LIST = '||v_selected_list);
   ELSE
    --Concatenate the selected value to the existing list
    SELECT NVL2(v_selected_list,v_selected_list ||',',v_selected_list) || p_item
    INTO v_selected_list
    FROM dual;
    dbms_output.PUT_LINE (' Else VSELECT LIST = '||v_selected_list);
  END IF;
  RETURN v_selected_list;
END get_selected_list;
Right now when else is triggered it displays nothing, because its looking for something in second parameter. If I do something like this
select RPT_PROJNUM_PKG.get_selected_list(:$FLEX$.PW_PROJECT_NUMBERS, NULL) from dual. The else condition triggers and I get a value and if ALL is selected that does not trigger because of the NULL, what should I do here

[moderator: Code tags added by bb. Next time please add them yourself]

[Updated on: Wed, 15 February 2023 23:35] by Moderator

Report message to a moderator

Re: function call with optional parameters [message #687291 is a reply to message #687289] Thu, 16 February 2023 00:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA
Senior Member
Just get rid of the P default, as shown below. If there are any continuing problems, it is more likely due to how you are calling the function or other things outside the function.

SCOTT@orcl_12.1.0.2.0> -- table and sample data for testing
SCOTT@orcl_12.1.0.2.0> CREATE TABLE pw_projnums_v AS
  2  SELECT 1 AS segment1, 'PROJ1' AS class_code FROM DUAL UNION ALL
  3  SELECT 2 AS segment1, 'PROJ2' AS class_code FROM DUAL
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> 
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE FUNCTION get_selected_list( p_item VARCHAR2, p_pw_project_num VARCHAR2 default NULL)
  2  RETURN VARCHAR2
  3  IS
  4    v_pw_project_num varchar2(30);
  5  -- added missing variable below, presumably in package not provided and data for testing
  6    v_selected_list	varchar2(1000) := 'some already existing data';
  7  BEGIN
  8    -- set default
  9  -- removed the P deafult below
 10  -- v_pw_project_num := nvl(p_pw_project_num,'P');
 11  -- and replaced it with the below
 12  	v_pw_project_num := p_pw_project_num;
 13  
 14    IF p_item = 'Clear' THEN
 15  	 v_selected_list := NULL;
 16    Elsif p_item = 'All' Then
 17  	 SELECT LISTAGG(segment1,',') WITHIN GROUP (ORDER BY segment1) into v_selected_list
 18  	 FROM PW_PROJNUMS_V
 19  	 where class_code = nvl(v_pw_project_num, class_code);
 20  	 dbms_output.PUT_LINE (' elsif VSELECT LIST = '||v_selected_list);
 21  	ELSE
 22  	 --Concatenate the selected value to the existing list
 23  	 SELECT NVL2(v_selected_list,v_selected_list ||',',v_selected_list) || p_item
 24  	 INTO v_selected_list
 25  	 FROM dual;
 26  	 dbms_output.PUT_LINE (' Else VSELECT LIST = '||v_selected_list);
 27    END IF;
 28    RETURN v_selected_list;
 29  END get_selected_list;
 30  /

Function created.

SCOTT@orcl_12.1.0.2.0> SHOW ERRORS
No errors.
SCOTT@orcl_12.1.0.2.0> 
SCOTT@orcl_12.1.0.2.0> -- tests with second parameter null:
SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('Clear') FROM DUAL
  2  /

GET_SELECTED_LIST('CLEAR')
--------------------------------------------------------------------------------


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('All') FROM DUAL
  2  /

GET_SELECTED_LIST('ALL')
--------------------------------------------------------------------------------
1,2

1 row selected.

elsif VSELECT LIST = 1,2
SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('Some') FROM DUAL
  2  /

GET_SELECTED_LIST('SOME')
--------------------------------------------------------------------------------
some already existing data,Some

1 row selected.

Else VSELECT LIST = some already existing data,Some
SCOTT@orcl_12.1.0.2.0> -- tests with value in second parameter
SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('Clear', 'PROJ1') FROM DUAL
  2  /

GET_SELECTED_LIST('CLEAR','PROJ1')
--------------------------------------------------------------------------------


1 row selected.

SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('All', 'PROJ1') FROM DUAL
  2  /

GET_SELECTED_LIST('ALL','PROJ1')
--------------------------------------------------------------------------------
1

1 row selected.

elsif VSELECT LIST = 1
SCOTT@orcl_12.1.0.2.0> SELECT get_selected_list ('Some', 'PROJ1') FROM DUAL
  2  /

GET_SELECTED_LIST('SOME','PROJ1')
--------------------------------------------------------------------------------
some already existing data,Some

1 row selected.

Else VSELECT LIST = some already existing data,Some
SCOTT@orcl_12.1.0.2.0> 
Re: function call with optional parameters [message #687293 is a reply to message #687291] Thu, 16 February 2023 08:03 Go to previous messageGo to next message
deahayes
Messages: 6
Registered: January 2023
Junior Member
Yes it works fine in sql developer, does not work from EBS concurrent program Default type: sql statement default value: select RPT_PROJNUM_PKG.get_selected_list(:$FLEX$.PW_PROJECT_NUMBERS, :$FLEX$.PW_PROJECTS) from dual
Re: function call with optional parameters [message #687296 is a reply to message #687293] Thu, 16 February 2023 10:01 Go to previous message
Barbara Boehmer
Messages: 9063
Registered: November 2002
Location: California, USA
Senior Member
I have moved this to the forum for E-Business Suite so that hopefully somebody experienced with that may be able to figure out what the problem is there.
Previous Topic: XDOLoader error for eTEXT template
Next Topic: Call PDF from Concurrent request / Customized Form in R12
Goto Forum:
  


Current Time: Sun Oct 01 20:34:13 CDT 2023