DBMS_SQL.COLUMN_VALUE - ORA 6562

From: afriKan <bvk_at_fdp.za.co>
Date: Mon, 08 Nov 1999 15:59:23 +0200
Message-ID: <3826D73B.68C94F41@fdp.za.co>

Hi,

Even on the most basic of examples, I continuously get a type mismatch on the DBMS_SQL.COLUMN_VALUE procedure. I will be the first to admit that my documentation is not up to scratch, and may well have a stupid syntax error in one of the DBMS_SQL function or procedure calls.

Any suggestions, here is a "dumbed down" procedure that causes the error.

procedure tst is

  l_cursorid integer;
  l_fetch integer;
  l_dummy integer;

  l_user varchar2(30);
  l_select_clause varchar2(100);

begin

      l_cursorid := DBMS_SQL.OPEN_CURSOR;
      l_select_clause := 'select user from dual';

      DBMS_SQL.PARSE(l_cursorid, l_select_clause, 2);

      DBMS_SQL.DEFINE_COLUMN(l_cursorid, 1, l_user, 30);

      l_dummy := DBMS_SQL.EXECUTE(l_cursorid);

      loop

        l_fetch := DBMS_SQL.FETCH_ROWS(l_cursorid);

        if l_fetch = 0 then
           exit;
        end if;

        DBMS_SQL.COLUMN_VALUE(l_cursorid, 1, l_user);

        text_io.put_line(l_user);

      end loop;

      DBMS_SQL.CLOSE_CURSOR(l_cursorid);
    

exception

when others then text_io.put_line('1'||to_char(sqlcode));

end;

---

Thanks all

--
Bruce

---

Email: Swap co and za Received on Mon Nov 08 1999 - 07:59:23 CST