Re: dbms_scheduler and ora-01756

"himzo" <asmir.biscevic_at_gmail.com> wrote in news:1175524011.411316.186620_at_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
>
>

I always assign the SQL statement into a single VARCHAR2 varible before execution & print/output/display the string to allow cut & paste into SQL*PLUS for testing. Received on Mon Apr 02 2007 - 09:39:31 CDT