Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: DBMS_SQL.PARSE Question
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;
-- Andrew - Wizzard barnetaj_at_bp.com snapondba_at_hotmail.com wrote in article <6fbne3$ph2$1_at_nnrp1.dejanews.com>...Received on Wed Mar 25 1998 - 00:00:00 CST
> 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
>