Great i have to clean my glasses !!
greating
"Dirk Tschentscher" <dirk.tschentscherREM_at_THISvolkswagen.de> a écrit dans le
message de news: arvejr$rnm1_at_doiweb4.volkswagen.de...
> 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 - 04:43:44 CST
