execute immediate [message #659144] |
Sat, 07 January 2017 12:17 |
|
deep_saha
Messages: 3 Registered: January 2017
|
Junior Member |
|
|
Hi, I am new to sql/plsql .
I came across a function which is used to convert ascii value to chr.
SQL> create or replace
2 FUNCTION fn_convertascii(iv_ascii_string VARCHAR2)
3 RETURN VARCHAR2
4 AS
5 v_string VARCHAR2(32767);
6 v_delimiter CONSTANT VARCHAR2(1):=',';
7 BEGIN
8 execute immediate REPLACE('select chr('||iv_ascii_string||') from dual',v_delimiter,')||chr(') INTO v_string;
9 RETURN(v_string);
10 EXCEPTION
11 WHEN OTHERS THEN
12 --DBMS_OUTPUT.PUT_LINE(lv_string);
13 RETURN(iv_ascii_string);
14 END;
15
16 /
Function created
SQL> show errors;
No errors for FUNCTION SCOTT.FN_CONVERTASCII
SQL> select fn_convertascii('65,66,67,68,69,70') from dual;
FN_CONVERTASCII('65,66,67,68,6
--------------------------------------------------------------------------------
ABCDEF
SQL> select fn_convertascii('69,70') from dual;
FN_CONVERTASCII('69,70')
--------------------------------------------------------------------------------
EF
SQL>
I am not able to understand how the looping is done in execute immediate replacing comma with null.
|
|
|
|
Re: execute immediate [message #659147 is a reply to message #659144] |
Sat, 07 January 2017 12:37 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
1/ The first thing to understand EXECUTE IMMEDIATE is to display what is executed replace EXECUTE IMMEDIATE by a DBMS_OUTPUT call:
SQL> create or replace
2 FUNCTION fn_convertascii(iv_ascii_string VARCHAR2)
3 RETURN VARCHAR2
4 AS
5 v_string VARCHAR2(32767);
6 v_delimiter CONSTANT VARCHAR2(1):=',';
7 BEGIN
8 -- execute immediate REPLACE('select chr('||iv_ascii_string||') from dual',v_delimiter,')||chr(') INTO v_string;
9 dbms_output.put_line(REPLACE('select chr('||iv_ascii_string||') from dual',v_delimiter,')||chr('));
10 RETURN(v_string);
11 /*
12 EXCEPTION
13 WHEN OTHERS THEN
14 --DBMS_OUTPUT.PUT_LINE(lv_string);
15 RETURN(iv_ascii_string);
16 */
17 END;
18 /
Function created.
SQL> set serveroutput on
SQL> select fn_convertascii('65,66,67,68,69,70') from dual;
FN_CONVERTASCII('65,66,67,68,69,70')
------------------------------------------------------------------------------------------------------------------------
1 row selected.
select chr(65)||chr(66)||chr(67)||chr(68)||chr(69)||chr(70) from dual
Now you can see from the query it generates (last line) that it does not loop anywhere it just replaces all commas by ")||chr'(" inserting "select chr(" at the beginning and appending ") from dual" to build the query.
2/ Don't use WHEN OTHERS in this king of code, read WHEN OTHERS.
|
|
|
|
Re: execute immediate [message #659151 is a reply to message #659149] |
Sat, 07 January 2017 18:21 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Also keep in mind - function is written inefficiently. First of all, never use when others unless it is justified. But most importantly function builds a different dynamic SQL statement each time a new string is passed. Therefore it results in hard parse each time a new string is passed. There is no need for dynamic SQL:
create or replace
function fn_convertascii(
iv_ascii_string varchar2
)
return varchar2
as
v_string varchar2(32767);
v_delimiter constant varchar2(1):=',';
v_next_pos number;
v_pos number := 1;
begin
loop
v_next_pos := instr(iv_ascii_string,v_delimiter,v_pos);
exit when v_next_pos = 0;
v_string := v_string || chr(substr(iv_ascii_string,v_pos,v_next_pos - v_pos));
v_pos := v_next_pos + 1;
end loop;
v_string := v_string || chr(substr(iv_ascii_string,v_pos));
return v_string;
end;
/
SY.
[Updated on: Sat, 07 January 2017 18:23] Report message to a moderator
|
|
|
|
|