dbms_scheduler and ora-01756

From: himzo <asmir.biscevic_at_gmail.com>
Date: 2 Apr 2007 07:26:51 -0700
Message-ID: <1175524011.411316.186620@d57g2000hsg.googlegroups.com>

This is my code:

call dbms_scheduler.create_program(

   program_name=>'RESET_MEMBERNUMBER',
   program_action=>

   	'BEGIN
		EXECUTE IMMEDIATE ''drop sequence membernumber_seq'';
		EXECUTE IMMEDIATE ''CREATE SEQUENCE MEMBERNUMBER_SEQ INCREMENT BY 1
START WITH 10000 MAXVALUE 9999999 MINVALUE 10000 NOCYCLE NOCACHE'';
	END;',

   program_type=>'PLSQL_BLOCK',
   number_of_arguments=>0,
   enabled=>TRUE,
   comments=>'');

Line: EXECUTE IMMEDIATE ''drop sequence membernumber_seq''; produces an ora-01756 error message - quoted string not properly terminated. I'm using two single quotes after each other so that the second one is being escaped by the first one.

Note: After I paste my code into a development tool, this works excellent, but as soon I put this into a script so that sqlplus can execute it, it fails.

Help Received on Mon Apr 02 2007 - 09:26:51 CDT