Re: procedure with tables

Sorry but..
now i want to add features to my proc so i done that CREATE OR REPLACE
  PROCEDURE supp (p_tab_name in varchar2) as     v_text varchar2(4000);
    v1_text varchar2(4000);
    v2_text varchar2(4000);
    c_dyn BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;     c1_dyn BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;      c2_dyn BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;     dummy integer;

  BEGIN
    v_text := 'delete from '||p_tab_name;     DBMS_SQL.PARSE(c_dyn, v_text,DBMS_SQL.NATIVE);     dummy:=DBMS_SQL.EXECUTE(c_dyn);
    DBMS_SQL.CLOSE_CURSOR(c_dyn);

--all maj flags set to 3 with the previous delete done with triggers (buisness rules)

    v1_text := 'update '||p_tab_name||'set maj=4 ';     DBMS_SQL.PARSE(c1_dyn, v1_text,DBMS_SQL.NATIVE);     dummy:=DBMS_SQL.EXECUTE(c1_dyn);
    DBMS_SQL.CLOSE_CURSOR(c1_dyn);

--delete is now allowed by the maj flag to 4 (buisness rules)

    v2_text := 'delete from '||p_tab_name||'where maj=4 ';    DBMS_SQL.PARSE(c2_dyn, v2_text,DBMS_SQL.NATIVE);     dummy:=DBMS_SQL.EXECUTE(c2_dyn);
    DBMS_SQL.CLOSE_CURSOR(c2_dyn);
    commit;

  END;
Bu the second parse is not allowed receive a ora 971 'word set is missing' strange?

thanks

"alainc" <alain2208_at_caramail.com> a écrit dans le message de news: arvbnm$kmo$1_at_s1.read.news.oleane.net...
> Thanks Ken,
> just a little correction as the dbms_sql.execute is a function it should
be
> dummy:=dbms_execute.sql(.....)
>
> Bye from France
>
>
> "Ken Denny" <ken_at_kendenny.com> a écrit dans le message de news:
> Xns92D1A299C79B6kendenny_at_65.82.44.9...
> > I guess EXECUTE IMMEDIATE isn't available on your level of Oracle. I
> > believe it was introduced in 8.1.? or maybe 8i. You'll need to use
> DBMS_SQL
> > package. It's a little more complicated. The procedure would be:
> >
> > CREATE OR REPLACE
> > PROCEDURE xyz (p_tab_name in varchar2,p_where_bed in varchar2) as
> > v_text varchar2(4000);
> > c_dyn BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
> > BEGIN
> > v_text := 'delete from '||p_tab_name;
> > IF p_where_bed IS NOT NULL
> > THEN
> > v_text := v_text||' where '||p_where_bed;
> > END IF;
> > DBMS_SQL.PARSE(c_dyn, v_text,DBMS_SQL.NATIVE);
> > DBMS_SQL.EXECUTE(c_dyn);
> > DBMS_SQL.CLOSE_CURSOR(c_dyn);
> > END;
> >
> > "alainc" <alain2208_at_caramail.com> wrote in
> > news:artm5m$3p0$1_at_s1.read.news.oleane.net:
> >
> > > hi,
> > >
> > > i try both but the problem is that it told me that immediate should be
> > > declare... does it wotk in all oracle version i'm in 8.0.x NT thanks
> > >
> > > Alain
> > > "Ken Denny" <ken_at_kendenny.com> a écrit dans le message de news:
> > > Xns92D16830D9C30kendenny_at_65.82.44.10...
> > >> This is good but it requires a "WHERE" condition. You could make the
> > >> WHERE condition optional by changing it to:
> > >>
Received on Tue Nov 26 2002 - 02:59:12 CST