Home » SQL & PL/SQL » SQL & PL/SQL » execute immediate (11gR2, windows 7)
execute immediate [message #659144] Sat, 07 January 2017 12:17 Go to next message
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 #659146 is a reply to message #659144] Sat, 07 January 2017 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 25848
Registered: January 2009
Location: SoCal
Senior Member
when all else fails Read The Fine Manual

http://docs.oracle.com/database/121/SQLRF/functions167.htm#SQLRF00697

no explicit LOOP exists; just REPLACE function
Re: execute immediate [message #659147 is a reply to message #659144] Sat, 07 January 2017 12:37 Go to previous messageGo to next message
Michel Cadot
Messages: 65377
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 #659149 is a reply to message #659147] Sat, 07 January 2017 13:10 Go to previous messageGo to next message
deep_saha
Messages: 3
Registered: January 2017
Junior Member
Thanks for your quick reply .
Thanks Michel and BlackSwan.
Re: execute immediate [message #659151 is a reply to message #659149] Sat, 07 January 2017 18:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2701
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

Re: execute immediate [message #659156 is a reply to message #659151] Sun, 08 January 2017 01:49 Go to previous messageGo to next message
deep_saha
Messages: 3
Registered: January 2017
Junior Member
Thanks SY. Will refrain from 'when others'.
Re: execute immediate [message #659157 is a reply to message #659156] Sun, 08 January 2017 02:37 Go to previous message
Michel Cadot
Messages: 65377
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Read this wiki page WHEN OTHERS to know why you must not use it and the cases you can and have to do it.

Previous Topic: How to Move fast the data from one database to another database ?
Next Topic: Getting ORA-01422: exact fetch returns more than requested number of rows
Goto Forum:
  


Current Time: Sat Feb 17 14:30:13 CST 2018

Total time taken to generate the page: 0.07645 seconds