Home » SQL & PL/SQL » SQL & PL/SQL » ListAgg results (11.2)
ListAgg results [message #662829] Fri, 12 May 2017 15:27 Go to next message
lott42
Messages: 117
Registered: June 2010
Senior Member
I created this function below that uses the ListAgg function (A). When I use an input that should return "no_data_found", the exception handler skips over it to the next statement in sequence. If I don't use ListAgg (B) with an input that should return "no_data_found", the exception handler does catch it correctly. Is there a reason the "no_data_found" exception handler for ListAgg doesn't catch it??

CREATE OR REPLACE FUNCTION listagg_test(p_val IN VARCHAR2) RETURN VARCHAR2 IS
  v_sql         VARCHAR2(32000); 
  v_temp_string VARCHAR2(32000);

BEGIN
  --(A)
  --v_sql := q'[SELECT LISTAGG(lower(id),',') WITHIN GROUP (ORDER BY val) FROM (select 'a' id, 'b' val from dual where 1=:1)]';
  --(B)  
  v_sql := q'[select 'x' from dual where 1=:1]';

  BEGIN
    EXECUTE IMMEDIATE v_sql
      INTO v_temp_string
      USING p_val;

  EXCEPTION
    WHEN no_data_found THEN
      v_temp_string := 'Result is "no_data_found"';
    WHEN OTHERS THEN
      v_temp_string := 'Err: ' || SQLERRM;
  END;

  v_temp_string := nvl(v_temp_string, 'I should not see this message');
  dbms_output.put_line(v_temp_string);
  RETURN(v_temp_string);

END listagg_test;

With (A) using 1 as input
a

With (A) using 2 as input
I should not see this message

With (B) using 1 as input
x

With (B) using 2 as input
Result is "no_data_found"

*I won't have access to test over the weekend but please offer your suggestions and I can try again on Monday.

Thanks

[Updated on: Fri, 12 May 2017 15:30]

Report message to a moderator

Re: ListAgg results [message #662835 is a reply to message #662829] Sat, 13 May 2017 00:20 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SQL Language Reference
Section Aggregate Functions

Quote:
For all the remaining aggregate functions [other than COUNT and REGEXP_COUNT], if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.
Re: ListAgg results [message #662915 is a reply to message #662835] Mon, 15 May 2017 10:22 Go to previous messageGo to next message
lott42
Messages: 117
Registered: June 2010
Senior Member
Michel, thanks for letting me know about the SQL Language Reference guide.

I was playing around with the statement and added a "group by 1" clause to the end of the sql

CREATE OR REPLACE FUNCTION listagg_test(p_val IN VARCHAR2) RETURN VARCHAR2 IS
  v_sql         VARCHAR2(32000); 
  v_temp_string VARCHAR2(32000);

BEGIN
  --(A)
  --v_sql := q'[SELECT LISTAGG(lower(id),',') WITHIN GROUP (ORDER BY val) FROM (select 'a' id, 'b' val from dual where 1=:1) GROUP BY 1]';

  BEGIN
    EXECUTE IMMEDIATE v_sql
      INTO v_temp_string
      USING p_val;

  EXCEPTION
    WHEN no_data_found THEN
      v_temp_string := 'Result is "no_data_found"';
    WHEN OTHERS THEN
      v_temp_string := 'Err: ' || SQLERRM;
  END;

  v_temp_string := nvl(v_temp_string, 'I should not see this message');
  dbms_output.put_line(v_temp_string);
  RETURN(v_temp_string);

END listagg_test;

Did I just get "lucky" that the code reached the "no_data_found" code path but this is a NOT correct way to formulate the "aggregate" function??

By entering a "2", I get

Result is "no_data_found"
Re: ListAgg results [message #662916 is a reply to message #662915] Mon, 15 May 2017 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't understand what you executed.
Show us, use SQL*Plus and copy and paste your session.

Re: ListAgg results [message #662918 is a reply to message #662916] Mon, 15 May 2017 10:33 Go to previous messageGo to next message
lott42
Messages: 117
Registered: June 2010
Senior Member
I'm not sure how to execute sql*plus. I've only used pl/sql developer. I went to a command window and got to sql*plus but I don't know how to execute the function above. From Developer, I just entered a "p_val " parm of "2" and executed the function. My result came back with

Result is "no_data_found"

which is what I wanted by adding the clause "GROUP BY 1" in the sql statement.
Re: ListAgg results [message #662920 is a reply to message #662918] Mon, 15 May 2017 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can execute it at SQL*Plus level using:
-- show the actual function code
set linesize 120 trimout on autoprint on
col source format a120
Select to_char(line,'99990')||' - '||text source
from user_source
where name = upper('listagg_test')
  and type = 'FUNCTION'
order by line
/
var res varchar2(100)
exec :res := listagg_test('<your value>')
exec :res := listagg_test('<another value>')
...

Re: ListAgg results [message #662921 is a reply to message #662918] Mon, 15 May 2017 10:40 Go to previous messageGo to next message
cookiemonster
Messages: 13011
Registered: September 2008
Location: Rainy Manchester
Senior Member
In the command window (which is a sqlplus emulator) you can do this:
select listagg_test(2) from dual;
Re: ListAgg results [message #662922 is a reply to message #662921] Mon, 15 May 2017 11:11 Go to previous messageGo to next message
lott42
Messages: 117
Registered: June 2010
Senior Member
cookiemonster wrote on Mon, 15 May 2017 08:40
In the command window (which is a sqlplus emulator) you can do this:
select listagg_test(2) from dual;
Nice. Thanks.

Here's my result from the command window using developer

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 
Connected as phone_dev@dev10.UCR.EDU

SQL> select listagg_test(2) from dual;
SQL> 
LISTAGG_TEST(2)
--------------------------------------------------------------------------------
Result is "no_data_found"

SQL> 
Re: ListAgg results [message #662923 is a reply to message #662922] Mon, 15 May 2017 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

yes but we don't know which code has been executed, so post what I asked.

Re: ListAgg results [message #662924 is a reply to message #662923] Mon, 15 May 2017 11:41 Go to previous message
lott42
Messages: 117
Registered: June 2010
Senior Member
Here's my entire session with all my errors trying to type in sql*plus.

SQL> SQL*Plus: Release 11.1.0.7.0 - Production on Mon May 15 09:28:30 2017
SQL> Copyright (c) 1982, 2008, Oracle.  All rights reserved.
SQL> Connected to:
  2  Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  3  With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> set linesize 120 trimout on autoprint on
  2  SQL> col source format a120
  3  SQL> select to_char(line,'99990')||' - '||text_source
  4    2  from user_source
  5    3  where name = upper(listagg_test') and type = 'FUNCTION' order by line
  6    4  /
  7  ERROR:
  8  ORA-01756: quoted string not properly terminated
  9  
 10  
 11  SQL> where name = upper('listagg_test') and type = 'FUNCTION' order by line
 12  SP2-0734: unknown command beginning "where name..." - rest of line ignored.
 13  SQL> col source format a120
 14  SQL> set linesize 120 trimout on autoprint on
 15  SQL> select to_char(line,'99990')||' - '||text_source
 16    2  from user_source
 17    3  where name = upper('listagg_test') and type = 'FUNCTION' order by line
 18    4  /
 19  select to_char(line,'99990')||' - '||text_source
 20                                       *
 21  ERROR at line 1:
 22  ORA-00904: "TEXT_SOURCE": invalid identifier
 23  
 24  
 25  SQL> set linesize 120 trimout on autoprint on
 26  SQL> select to_char(line,'99990')||' - '||text source
 27    2  from user_source
 28    3  where name = upper('listagg_test') and type = 'FUNCTION' order by line
 29    4  /
 30  
 31  SOURCE
 32  --------------------------------------------------------------------------------
 33  ----------------------------------------
 34       1 - FUNCTION listagg_test(p_val IN VARCHAR2) RETURN VARCHAR2 IS
 35       2 -   v_sql         VARCHAR2(32000);
 36       3 -   v_temp_string VARCHAR2(32000);
 37       4 -
 38       5 - BEGIN
 39       6 -   --(A)
 40       7 -   v_sql := q'[SELECT LISTAGG(lower(id),',') WITHIN GROUP (ORDER BY val)
 41   FROM (select 'a' id, 'b' val from dual
 42  where 1=:1) GROUP BY 1]';
 43  
 44       8 -   --(B)
 45       9 -   --v_sql := q'[select 'x' from dual where 1=:1]';
 46  
 47  SOURCE
 48  --------------------------------------------------------------------------------
 49  ----------------------------------------
 50      10 -
 51      11 -   BEGIN
 52      12 -     EXECUTE IMMEDIATE v_sql
 53      13 -       INTO v_temp_string
 54      14 -       USING p_val;
 55      15 -
 56      16 -   EXCEPTION
 57      17 -     WHEN no_data_found THEN
 58      18 -       v_temp_string := 'Result is "no_data_found"';
 59      19 -     WHEN OTHERS THEN
 60      20 -       v_temp_string := 'Err: ' || SQLERRM;
 61  
 62  SOURCE
 63  --------------------------------------------------------------------------------
 64  ----------------------------------------
 65      21 -   END;
 66      22 -
 67      23 -   v_temp_string := nvl(v_temp_string, 'I should not see this message');
 68  
 69      24 -   dbms_output.put_line(v_temp_string);
 70      25 -   RETURN(v_temp_string);
 71      26 -
 72      27 - END listagg_test;
 73  
 74  27 rows selected.
 75  
 76  SQL>
 77  
Previous Topic: Loading Data into multiple tables
Next Topic: snapshot too old
Goto Forum:
  


Current Time: Sat Feb 17 17:10:15 CST 2018

Total time taken to generate the page: 0.01406 seconds