My questions are:
1) why ? If it's a working block of code, then it should just work anyway. You can use the extended quote syntax and not have to change anything
SQL> declare
2 l_text varchar2(1000) :=
3 q'{
4 declare
5 lv_tab varchar2(10):='EMP';
6 lv_var varchar2(100);
7 begin
8 --table_name
9 /*the table has total
10 14 columns */
11 execute immediate 'select ename from '||lv_tab||' where rownum=1' into lv_var;
12 dbms_output.put_line(lv_var);
13 end;
14 }';
15
16 begin
17 null;
18 end;
19 /
PL/SQL procedure successfully completed.
2) this scares me in that I'm guessing you want to pass in a block of code, and then run it. Man....does that open up risks. Google "SQL injection".
If you *really* want to do what you asked in the question, here's something to get your started
mcdonac@np12
SQL> set serverout on
mcdonac@np12
SQL> declare
2 l_text varchar2(500) :=
3 q'{
4 declare
5 lv_tab varchar2(10):='EMP';
6 lv_var varchar2(100);
7 begin
8 --table_name
9 /*the table has total
10 14 columns */
11 execute immediate 'select ename from '||lv_tab||' where rownum=1' into lv_var;
12 dbms_output.put_line(lv_var);
13 end;
14 }';
15
16 l_str varchar2(500);
17 c1 int;
18 c2 int;
19 c3 int;
20 idx int;
21 begin
22 loop
23 c1 := instr(l_text,'--');
24 c2 := instr(l_text,'/*');
25 exit when c1 = 0 and c2 = 0;
26 if c1 = 0 and c2 != 0 then idx := c2; end if;
27 if c1 != 0 and c2 = 0 then idx := c1; end if;
28 if c1 != 0 and c2 != 0 then idx := least(c1,c2); end if;
29
30 l_str :=l_str ||substr(l_text,1,idx-1);
31 l_text := substr(l_text,idx);
32 if c1 != 0 then
33 l_text := substr(l_text,instr(l_text,chr(10))+1);
34 else
35 l_text := substr(l_text,instr(l_text,'*/')+2);
36 end if;
37
38 end loop;
39 dbms_output.put_line(l_str || l_text);
40 end;
41 /
declare
lv_tab varchar2(10):='EMP';
lv_var varchar2(100);
begin
execute immediate 'select ename from '||lv_tab||' where
rownum=1' into lv_var;
dbms_output.put_line(lv_var);
end;
PL/SQL procedure successfully completed.