Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: dbms_scheduler and ora-01756

Re: dbms_scheduler and ora-01756

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Mon, 02 Apr 2007 20:26:44 +0200
Message-ID: <eurhp0$o0j$1@news5.zwoll1.ov.home.nl>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US