Re: procedure with tables

From: Ken Denny <ken_at_kendenny.com>
Date: Mon, 25 Nov 2002 20:46:53 GMT
Message-ID: <Xns92D1A299C79B6kendenny@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 Mon Nov 25 2002 - 14:46:53 CST