The code below removes comments from executable code, ignoring comments that need to be stored in database (between quotes).
It shows some features of the PL/SQL syntax. Notable, not shown, is that "%" is not the modulo operator; an inbuilt function "mod(number,divisor)" is used instead.
CREATE OR REPLACE FUNCTION scan_comment (string in varchar2) RETURN varchar2 IS
--returns string without --comment and /*comment*/
quotopen boolean:=FALSE;
dubquotopen boolean:=FALSE;
l_string varchar2(32767):=string||chr(10);
r_string varchar2(32767);
lastpoint integer:=1;
terminator integer;
skip integer:=0;
BEGIN
for i in 1..nvl(length(l_string),0) loop
if i>=skip then
if substr(l_string,i,1)=chr(39) and not quotopen and not dubquotopen then
quotopen:=TRUE;
elsif substr(l_string,i,1)=chr(39) and quotopen and not dubquotopen then
quotopen:=FALSE;
elsif substr(l_string,i,1)='"' and not quotopen and not dubquotopen then
dubquotopen:=TRUE;
elsif substr(l_string,i,1)='"' and not quotopen and dubquotopen then
dubquotopen:=FALSE;
end if;
if not dubquotopen and not quotopen then --###clean it up
if substr(l_string,i,2)='--' then --###look for comment-terminator chr(10)
terminator:=instr(l_string,chr(10),i+2,1);
r_string:=r_string||substr(l_string,lastpoint,i-lastpoint);
skip:=terminator;
lastpoint:=terminator; --### save chr(10)
elsif substr(l_string,i,2)='/*' then --###look for comment-terminator */
terminator:=instr(l_string,'*/',i+2,1)+2;
r_string:=r_string||substr(l_string,lastpoint,i-lastpoint);
skip:=terminator;
lastpoint:=terminator; --### loose /
end if;
end if; --cleaning
end if; --skip-condition
end loop;
r_string:=r_string||substr(l_string,lastpoint);
return rtrim(r_string,chr(10));
END;
/
DECLARE
message varchar2(1000):= 'Hello, "--ignore quoted" World!--line comment'||chr(10)||'In case we ''/*the people*/'' /*,despite Trump,*/ survive...';
BEGIN
dbms_output.put_line(message);
dbms_output.put_line('======');
dbms_output.put_line(scan_comment(message));
END;
/