Something like this should get you started
SQL> create table t (c clob);
Table created.
SQL>
SQL> declare
2 l_sql clob :=
3 q'{ /* some initial stuff
4 which we dont care about
5 -- and comments in comments
6 */
7 select * -- all cols
8 from user_tables
9 where table_name like 'T%'
10 -- and num_rows > 0
11 /* omitting these ones
12 and nested = 'Y'
13 and user_stats = 'Y'
14 */ order by 1}';
15 begin
16 insert into t values (l_sql );
17 commit;
18 end;
19 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from t;
C
---------------------------------------------------------------------------------------------------
--------
/* some initial stuff
which we dont care about
-- and comments in comments
*/
select * -- all cols
from user_tables
where table_name like 'T%'
-- and num_rows > 0
/* omitting these ones
and nested = 'Y'
and user_stats = 'Y'
*/ order by 1
1 row selected.
SQL>
SQL> set serverout on
SQL> declare
2 l_text clob;
3 l_clean clob;
4 l_in_comment boolean := false;
5 l_in_comment_mode varchar2(10);
6 pos pls_integer;
7 begin
8 for i in ( select * from t )
9 loop
10 l_text := ltrim(i.c);
11 l_clean := null;
12 pos := 1;
13 loop
14 if substr(l_text,pos,2) = '--'
15 then
16 if l_in_comment then
17 null;
18 else
19 l_in_comment := true;
20 l_in_comment_mode := 'dashes';
21 end if;
22 pos := pos + 2;
23 elsif substr(l_text,pos,2) = '/*'
24 then
25 if l_in_comment then
26 null;
27 else
28 l_in_comment := true;
29 l_in_comment_mode := 'slashes';
30 end if;
31 pos := pos + 2;
32 elsif substr(l_text,pos,1) = chr(10)
33 then
34 if l_in_comment and l_in_comment_mode = 'dashes' then
35 l_in_comment := false;
36 else
37 l_clean := l_clean || substr(l_text,pos,1);
38 end if;
39 pos := pos + 1;
40 elsif substr(l_text,pos,2) = '*/'
41 then
42 if l_in_comment and l_in_comment_mode = 'slashes' then
43 l_in_comment := false;
44 else
45 l_clean := l_clean || substr(l_text,pos,2);
46 end if;
47 pos := pos + 2;
48 else
49 if not l_in_comment then
50 l_clean := l_clean || substr(l_text,pos,1);
51 end if;
52 pos := pos + 1;
53 end if;
54 exit when pos > length(l_text);
55 end loop;
56 end loop;
57 dbms_output.put_line(l_clean);
58 end;
59 /
select * from user_tables
where table_name like 'T%'
order by 1
PL/SQL procedure successfully completed.
But I must stress, *anything* that takes SQL text from a source and then might run it needs to be REALLY REALLY REALLY REALLY tightly controlled because the risks are huge in terms of security.
Anyway, once you have a cleansed sql statement, could do something like this to check its validity:
SQL> declare
2 l_sql clob := 'select * from tab';
3 l_cursor number := dbms_sql.open_cursor;
4 begin
5 if substr(ltrim(upper(l_sql),' '||chr(13)||chr(10)),1,7) = 'SELECT ' then
6 dbms_sql.parse( l_cursor,l_sql, dbms_sql.native );
7 else
8 raise_application_error(-20000,'SELECT not the leading keyword');
9 end if;
10 exception
11 when others then
12 dbms_sql.close_cursor( l_cursor );
13 raise;
14 end;
15 /
PL/SQL procedure successfully completed.
If you are expecting lots of SQL's, then you probably dont want to smoke your shared pool, so you might something like:
SQL> declare
2 l_sql clob := 'select * from tab';
3 l_cursor number := dbms_sql.open_cursor;
4 begin
5 if substr(ltrim(upper(l_sql),' '||chr(13)||chr(10)),1,7) = 'SELECT ' then
6 execute immediate 'alter session set cursor_sharing=force';
7 dbms_sql.parse( l_cursor, l_sql, dbms_sql.native );
8 execute immediate 'alter session set cursor_sharing=exact';
9 else
10 raise_application_error(-20000,'SELECT not the leading keyword');
11 end if;
12 exception
13 when others then
14 execute immediate 'alter session set cursor_sharing=exact';
15 dbms_sql.close_cursor( l_cursor );
16 raise;
17 end;
18 /
PL/SQL procedure successfully completed.