DBMS_SQL.PARSE Question

From: <snapondba_at_hotmail.com>
Date: 1998/03/25
Message-ID: <6fbne3$ph2$1@nnrp1.dejanews.com>#1/1

I am having a bit of trouble trying to get the DBMS_SQL.PARSE procedure to work (using a statement longer that 255 chars) and I hope someone can assist. This is being run against a version 7.3.2.3 database. I am trying to use the form of the parse procedure which uses a PL/SQL table of varchar2's as the statement to be parsed. I continue to get the following error returned when I attempt to create the following procedure. I have tried the call to dbms_sql.parse the way it appears in the code below and also like the commented statement directly following it with the same error.

HELP! Line 24: Pos 3: PLS-00306: wrong number or types of arguments in call to 'PARSE' Line 24: Pos 3: PL/SQL: Statement ignored

CREATE OR REPLACE PROCEDURE PREDICTOR.pr_dyn_ddla (stmt in varchar2) is
  cid integer;
  TYPE StmtTabType IS TABLE of VARCHAR2(255) INDEX BY BINARY_INTEGER;   StmtTab StmtTabType;

  i       integer;
  lStmt   integer;
  ubound  integer;
  low     integer;
  high    integer;

BEGIN
  lStmt := LENGTH(Stmt);
  dbms_output.put_line(lStmt||';'||CEIL(lStmt/255));   ubound := CEIL(lStmt/255);
  FOR i in 1..ubound
  LOOP
    low := 1+(i-1)*255;
    high := LEAST(i*255,lStmt);
    StmtTab(i) := SUBSTR(Stmt,low, high);     dbms_output.put_line(i||' ('||low||','||high||') -->'||StmtTab(i));   END LOOP;
  cid := dbms_sql.open_cursor;
  dbms_output.put_line(cid);
  dbms_sql.parse(
    C=>cid,
    STATEMENT=>StmtTab,
    LB=>1,
    UB=>2,
    LFFLG=>FALSE,
    LANGUAGE_FLAG=>dbms_sql.v7);
  --sys.dbms_sql.parse(cid, StmtTab, 1, 2, FALSEl, dbms_sql.v7l);   dbms_sql.close_cursor(cid);
  exception
    when others
    then
      dbms_output.put_line(sqlerrm(sqlcode)); end;

Thanks in Advance,
David C. Greene
Database Administrator
Enterprise Data Management
Snap-on Tools Company

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed Mar 25 1998 - 00:00:00 CST