Re: procedure with tables

Hi,
there's a space missing I think, so Oracle can't find the keyword "SET", the tablename and the SET are concatenated to one word

old : >     v1_text := 'update '||p_tab_name||'set maj=4 ';
new:  >     v1_text := 'update '||p_tab_name||'  set maj=4 ';

Rgds

    Dirk

"alainc" <alain2208_at_caramail.com> schrieb im Newsbeitrag news:arvd2m$lq1$1_at_s1.read.news.oleane.net...
> 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 - 03:24:16 CST