PL/SQL example

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.

Stored Procedure


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;
/

PL/SQL playground