Re: DBMS_SQL.PARSE Question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/03/25
Message-ID: <351c7936.10873895@192.86.155.100>#1/1

A copy of this was sent to snapondba_at_hotmail.com (if that email address didn't require changing) On Wed, 25 Mar 1998 13:57:22 -0600, you wrote:

>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!
>

The problem is you are not using the TYPE that dbms_sql.parse is defined as taking. DBMS_SQL.PARSE is defined as taking a type of dbms_sql.varchar2s -- not that type you defined.

In PL/SQL, a strict type model is in place. For example, even tho the following two types are identical, as far as pl/sql is concerned, they are different:

SQL> declare

  2          type  Array1 is table of number index by binary_integer;
  3          type  Array2 is table of number index by binary_integer;
  4  
  4          a1              array1;
  5          a1_x    array1;
  6          a2              array2;
  7          a2_x    array2;
  8  begin
  9          a1 := a1_x;
 10          a2 := a2_x;
 11          a1 := a2;

 12 end;
 13 /
declare
*
ERROR at line 1:

ORA-06550: line 11, column 8:
PLS-00382: expression is of wrong type
ORA-06550: line 11, column 2:

PL/SQL: Statement ignored

SQL> declare

  2          type  Array1 is table of number index by binary_integer;
  3          type  Array2 is table of number index by binary_integer;
  4  
  4          a1              array1;
  5          a1_x    array1;
  6          a2              array2;
  7          a2_x    array2;
  8  begin
  9          a1 := a1_x;
 10          a2 := a2_x;

 11 end;
 12 /

PL/SQL procedure successfully completed.

So only completely type compatible tables may be assigned to each other. In your snippet below, I've corrected the declare of StmtTab.

>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;

   StmtTab dbms_sql.varchar2s;

> i integer;
> lStmt integer;
> ubound integer;
> low integer;
> high integer;
>BEGIN
[snip]

>
>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
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Mar 25 1998 - 00:00:00 CST