Re: DBMS_SQL.PARSE Question

From: Andrew Barnett <nobody_at_spamandeggs.bp.com>
Date: 1998/03/25
Message-ID: <01bd583b$b4b4bb60$8c0564a1@azmelw1358.mel.az.bp.com>#1/1

here's a version which works - only diff I can see up front is that I take my table type directly from dbms_sql, whereas you do not.

   function exec_ddl

      (p_sql_text in dbms_sql.varchar2s)
      return integer
   is
      l_lb integer := 0;
      l_ub integer := 1;
      l_cursor_id integer;
      l_rowcount integer;
   begin
      l_lb := p_sql_text.first;
      l_ub := p_sql_text.last;
      --
      l_cursor_id := dbms_sql.open_cursor;
      dbms_sql.parse
         (l_cursor_id,
          p_sql_text,
          l_lb,
          l_ub,
          FALSE,
          dbms_sql.native);
      l_rowcount := dbms_sql.execute (l_cursor_id);
      dbms_sql.close_cursor (l_cursor_id);
      return l_rowcount;
   exception
      when OTHERS
      then
         raise;

   end exec_ddl;

-- 
Andrew - Wizzard

barnetaj_at_bp.com

snapondba_at_hotmail.com wrote in article <6fbne3$ph2$1_at_nnrp1.dejanews.com>...


> 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