Re: Form 4.5 and cursor

From: Pedro Nuno Gomes Pimenta <p.pimenta_at_ifb.pt>
Date: 1997/12/18
Message-ID: <3498F351.636D2A22@ifb.pt>#1/1

 Lando is wright!
Well, the problem can be solved with the PL/SQL package DBMS_SQL like this

   procedure test(table_name char, f_1 char,f_2) is     cursorID INTEGER := DBMS_SQL.OPEN_CURSOR;     comando_pl_sql CHAR(2000) := 'SELECT DISTINCT :f1 FROM :tablename WHERE :f2 = ''abc''';

   dummy                   INTEGER;
    v_f_1                    table_1.field_1%TYPE;

   begin
      DBMS_SQL.PARSE(cursorID,comando_pl_sql,2);
      DBMS_SQL.BIND_VARIABLE(cursorID,':f1',f_1);
      DBMS_SQL.BIND_VARIABLE(cursorID,':f2',f_2);
      DBMS_SQL.BIND_VARIABLE(cursorID,':tablename',:table_name);

      DBMS_SQL.DEFINE_COLUMN(cursorID,1,v_f_1,{max size of f_1 if

CHAR/VARCHAR2});       dummy := DBMS_SQL.EXECUTE(cursorID);

      LOOP
        IF DBMS_SQL.FETCH_ROWS(cursorID) = 0 THEN
          exit;
        END IF;

        DBMS_SQL.COLUMN_VALUE(cursorID, 1, v_f_1);

        ..............

      END LOOP;
      DBMS_SQL.OPEN_CURSOR(cursorID);

Lando wrote:

> What you are doing will not work. To do something
> like this you need to use dynamic sql. Check out
> package forms_ddl. Even then you may have problems since
> you will be trying to do dynamic sql in an explicit cursor
> declaration. You may find is easier to use an implicit
> cursor, that is, just do the select (using forms_ddl) without declaring
> a
> cursor. You will also encounter problems when you
> try to do the select...into using forms_ddl. Basically, no value is
> ends up being stored in the variable being selected into even though
> the statement will execute successfully. I have not worked
> this part out yet, so let me know if you come up with a solution.
Received on Thu Dec 18 1997 - 00:00:00 CST