Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: dbms_scheduler and ora-01756
himzo schreef:
> 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
>
You need to 'escape' the quotes within you exec immediate:
EXECUTE IMMEDIATE '''drop sequence membernumber_seq''';
That is three quotes in a row (in order to produce one)
-- Regards, Frank van Bortel Top-posting is one way to shut me up...Received on Mon Apr 02 2007 - 13:26:44 CDT