Re: Can I do this with the dynamic SQL?

From: Jan <janik_at_pobox.sk>
Date: 10 Jul 2004 05:49:47 -0700
Message-ID: <81511301.0407100449.32bc5310@posting.google.com>

I would code it:

Procedure abc( p_sa IN VARCHAR2) IS

    v_name tbl_name; -- is a table type variable decalred in package spec.     x NUMBER; -- same datatype as const1,2     BEGIN     IF .... THEN
       x:=cons1;
    ELSE IF ...
       x:=const2;
    END IF;         select name BULK COLLECT INTO v_name

        from fl_management f, 
             TABLE(f.mgmts_nt) f2 
                where f2.sa= x;      

  ...
   END abc;

cschang <cschang_at_maxinter.net> wrote in message news:<40EEAE5C.1000502_at_maxinter.net>...
> I found an alternative for my last post, however, there was still a
> problem. I wrote a package with a procedure as
>
> Procedure abc( p_sa IN VARCHAR2) IS
> v_name tbl_name; -- is a table type variable decalred in package spec.
> BEGIN
> sqlStmt := 'select name from fl_management f, TABLE(f.mgmts_nt) f2
> where f2.sa= :x';
> If ... Then
> EXECUTE IMMEDIATE sqlStmt BULK COLLECT INTO v_name USING const1;
> ELSIF .. THEN
> EXECUTE IMMEDIATE sqlStmt BULK COLLECT INTO v_name USING const2;
> END IF;
> ...
> END abc;
> When I compiled this, the error showing BULK can not be used here. Can
> anyone tell me why? I did follow the syntax, did I? My setup is 8.1.7.4
> on NT 4 Thanks.
>
> C Chang
Received on Sat Jul 10 2004 - 07:49:47 CDT