it is just a programming technique that is rather language independent.
Don't read anything special into it, just look at the code:
8 replace( q'|, sum(decode(fish_type,'$X$',fish_weight)) $X$|',
9 '$X$',
10 dbms_assert.simple_sql_name(x.fish_type) );
Now, what does replace do - it takes the first argument (a string) as input and uses the second argument as a pattern to match and when it finds that pattern in the first argument it replaces it with the third argument.
I simply put $X$ as a string, a pattern, in the first string and I'm just asking for $X$ to be replaced in that first string with x.fish_type (assuming x.fish_type is "safe" to be replaced in there - that is what dbms_assert is doing for me)
It is just a programming technique.
in my project are not a simple
sql names.they are dangerous to place in SQL then. You can use dbms_assert.Enquote_Literal for the first occurrence (in the decode) but should use simple_sql_name after quoting for the second one.
Suppose COD was <>1 in the above example. Then:
ops$tkyte%ORA11GR2> create or replace procedure go_fishing( p_cursor in out sys_refcursor )
2 as
3 l_query long := 'select fish_id';
4 begin
5 for x in (select distinct fish_type from fish order by 1 )
6 loop
7 l_query := l_query ||
8 replace(
9 replace( q'|, sum(decode(fish_type,$1$,fish_weight)) $2$|',
10 '$1$',
11 dbms_assert.enquote_literal(x.fish_type) ),
12 '$2$',
13 dbms_assert.simple_sql_name( '"' || x.fish_type || '"' ) );
14 end loop;
15
16 l_query := l_query || ' from fish group by fish_id order by fish_id';
17
18 dbms_output.put_line( l_query );
19 open p_cursor for l_query;
20 end;
21 /
Procedure created.
ops$tkyte%ORA11GR2> variable x refcursor
ops$tkyte%ORA11GR2> exec go_fishing( :x )
select fish_id, sum(decode(fish_type,'<>1',fish_weight)) "<>1",
sum(decode(fish_type,'CTY',fish_weight)) "CTY",
sum(decode(fish_type,'HAD',fish_weight)) "HAD",
sum(decode(fish_type,'HKE',fish_weight)) "HKE",
sum(decode(fish_type,'LIN',fish_weight)) "LIN" from fish group by fish_id order
by fish_id
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> print x
FISH_ID <>1 CTY HAD HKE LIN
---------- ---------- ---------- ---------- ---------- ----------
1 20 30
2 45 10 55
3 52 90 60