ListAgg results [message #662829] |
Fri, 12 May 2017 15:27 |
|
lott42_gmail
Messages: 146 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
With (A) using 2 as input
I should not see this message
With (B) using 1 as input
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] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: ListAgg results [message #662924 is a reply to message #662923] |
Mon, 15 May 2017 11:41 |
|
lott42_gmail
Messages: 146 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
|
|
|