Re: procedure with tables

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:
> >>
> >> CREATE OR REPLACE
> >> PROCEDURE xyz (p_tab_name in varchar2,p_where_bed in varchar2) as
> >> v_text varchar2(4000);
> >> 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;
> >> execute immediate v_text;
> >> END;
> >>
> >> "Dirk Tschentscher" <dirk.tschentscherREM_THIS_at_volkswagen.de> wrote in
> >> news:art3r0$5uu2_at_doiweb4.volkswagen.de:
> >>
> >> > Hi,
> >> >
> >> > CREATE OR REPLACE
> >> > PROCEDURE xyz (p_tab_name in varchar2,p_where_bed in varchar2) as
> >> > v_text varchar2(4000);
> >> > BEGIN
> >> > v_text := 'delete from '||p_tab_name||' where '||p_where_bed;
> >> > execute immediate v_text;
> >> > END;
> >> >
> >> >
> >> >
> >> >
> >> > "alainc" <alain2208_at_caramail.com> schrieb im Newsbeitrag
> >> > news:arsvuf$jf8$1_at_s1.read.news.oleane.net...
> >> >> Hi,
> >> >> I need to make a procedure to do the same action( ie delete or
> >> >> update all elements) but on different tables. so i want to use a
> >> >> proc and give as parameters the table name.
> >> >> how to do it
> >> >> thanks
>
> --
> Ken Denny
> http://www.kendenny.com/
>
Received on Tue Nov 26 2002 - 02:36:16 CST