Re: PL/SQL question

Manjula Krishnan wrote:

> Hi:
> I have a question for you ORACLE gurus out there. I want to
> write a
> procedure with a dynamic SELECT statement. For eg:
>
> select a, b, c from table
> where key1 = inp1
> join key1 = inp2;
>
> The join can be either and/or. I am passing inp1, join, inp2 to the
> procedure from a webform. Ideally I would like to have inp1, inp2, ...
>
> inpn with join1....join(n-1).
>
> How can I do this? Please HELP
>
> Thanks,
>
> Manjula

Hello -- I enclose a snippet of example PL/SQL for you, but please read it carefully... Also, it would
not hurt to review dynamic SQL from the Oracle Application Developers Guide.. I have also ignored error checks, etc...

Ken Klatt

    BEGIN       /* Open new cursor and return cursor ID. Using defaults for this.. */

      /* The intent is to get the same table from a number of different schemas */

      cid := DBMS_SQL.OPEN_CURSOR;

      IF sname IS NULL
        THEN mytable := 'emp';
      ELSE
        mytable := sname || '.' || 'emp';
      END IF;

      myquery := 'SELECT sal  FROM '  || mytable ||
                 'WHERE  '            || mytable ||
                 '.empno = increase_sal.empno';

      /* Parse and immediately execute dynamic SQL statement built by
         concatenating table name to SELECT Table command. The name
         of the table is the schema owner.tablename -- The call is of
         the form source cursor id, string that is the query, and a
         parameter to indicate a V7 SQL statement. */

      DBMS_SQL.PARSE(cid,myquery,dbms_sql.v7);

      /* Now that I defined the query, the results are defined by using
         the define column call. */

      DBMS_SQL.DEFINE_COLUMN(cid,1,curr_sal);

      /* Query is defined, as is the result expected .. Time to Fly! */

      ret := DBMS_SQL.EXECUTE(cid);

      IF DBMS_SQL.FETCH_ROWS(cid)>0 THEN
        DBMS_SQL.COLUMN_VALUE(cid,1,curr_sal);
      END IF;

      DBMS_SQL.CLOSE_CURSOR(cid);

      IF curr_sal IS NULL
       THEN RAISE no_sal;
      ELSE
       UPDATE emp
       SET    sal = sal + sal_incr
       WHERE  empno = empno;
      END IF;

    END; Received on Wed Jul 30 1997 - 00:00:00 CDT