The create or replace function SP_STRSORTFUNC (STR in VARCHAR2, split_param VARCHAR2) in return VARCHAR2 as
T_temp varchar2 (2048); Item - access to an array of
T_str varchar2 (2048); - returns the contents of the
Split_param_count number; - the number of parameters
T_begin number:=0; , the start of the parameters of loop variable
T_end number:=1; - the end of the starting position parameters of the intercept length
T_length number:=0; - the length of the string
T_paramLength number:=0; - the length of the incoming string interception parameters
I_temp number:=0; - identifies a
V_strsql VARCHAR (300); - perform statement
The begin
- create a temporary table
Select count (1) INTO i_temp from all_tables where the lower (table_name)='table_temp' and owner='TEST';
IF (i_temp & gt;=1) THEN
V_strsql:='drop table table_temp';
The execute immediate v_strsql;
I_temp:=0;
END the IF;
V_strsql:='CREATE TABLE table_temp (item varchar2 (2048))';
The execute immediate v_strsql;
/* -- judge split_param appeared many times in the STR
Select LENGTH (REGEXP_REPLACE (REPLACE (STR, '+', '@'), '[^ @] +', ')) into split_param_count from dual; - to get the number of parameters */
T_length:=length (STR); - to get into character length
T_paramLength:=length (split_param); - the length of the intercept parameter
WHILE t_begin & lt; T_length loop

- starting position starting from 1 interception match split_param string and returns where he
T_begin:=instr (STR, split_param t_end);
IF t_begin=0 THEN
T_begin:=t_length;
T_temp:=SUBSTR (STR, t_end t_begin);
='insert into table_temp v_strsql: select' | | t_temp | | 'from dual';
The execute immediate v_strsql;
Commit;
IF t_end & gt;=t_length THEN
The EXIT;
END the IF;
The ELSE
IF t_end & gt;=t_length THEN
The EXIT;
END the IF;
T_temp:=SUBSTR (STR, t_end t_begin - t_end); Item - get temporary
T_end:=t_begin + t_paramLength;
- item will be temporarily inserted into a temporary table
='insert into table_temp v_strsql: select' | | t_temp | | 'from dual';
The execute immediate v_strsql;
Commit;
END the IF;
END LOOP;
-- -- -- -- -- -- -- -- -- -- -- -- -- -- the string within the temporary table for Mosaic
The select wmsys. Wm_concat (item) into t_str from table_temp order by item;
Return (t_str);
End SP_STRSORTFUNC;

An error is as follows:
Error: PLS - 00103: Encountered the symbol "?" When expecting one of the following:

. (* @ % & amp; - +/ats loop mod remainder rem
The symbol "?" The was ignored.

25:Text: WHILE t_begin & lt; T_length loop

Error: PLS - 00103: Encountered the symbol "?" When expecting one of the following:

(the begin case declare exit for goto the if loop mod null pragma
Raise the return to select the update while with & lt; The an identifier>
<
The continue close current delete the fetch lock insert open rollback
The savepoint set the execute SQL commit forall the merge pipe purge
Line: 28
Text: IF t_begin=0 THEN

CodePudding user response:

Dynamically create table, must use dynamic SQL reference,

The select wmsys. Wm_concat (item) into t_str from table_temp order by item;


PS: function, it is best to use global temporary tables