Path: news.f.de.plusline.net!news-fra1.dfn.de!newsfeed.hanau.net!news.tiscali.de!newsfeed.freenet.de!feeder.news-service.com!feeder4.cambrium.nl!feed.tweaknews.nl!63.218.45.10.MISMATCH!nx01.iad01.newshosting.com!newshosting.com!69.28.186.77.MISMATCH!hwmnpeer03.lga!hwmnpeer01.lga!news.highwinds-media.com!hw-filter.phx!newsfe10.phx.POSTED!53ab2750!not-for-mail
Newsgroups: comp.databases.oracle.server
Subject: Re: dbms_scheduler and ora-01756
From: "Ana C. Dent" <anacedent@hotmail.com>
References: <1175524011.411316.186620@d57g2000hsg.googlegroups.com>
Organization: Your Company
User-Agent: Xnews/5.04.25
Lines: 36
Message-ID: <DA8Qh.118887$115.47101@newsfe10.phx>
Date: Mon, 02 Apr 2007 14:39:31 GMT
NNTP-Posting-Host: 70.181.210.12
X-Complaints-To: admin@cox.net
X-Trace: newsfe10.phx 1175524771 70.181.210.12 (Mon, 02 Apr 2007 07:39:31 MST)
NNTP-Posting-Date: Mon, 02 Apr 2007 07:39:31 MST
Xref: news.f.de.plusline.net comp.databases.oracle.server:195616

"himzo" <asmir.biscevic@gmail.com> wrote in
news: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
> 
> 

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.