DYNAMIC SQL PROBLEM - help

From: <kal121_at_yahoo.com>
Date: Mon, 12 Apr 1999 17:16:57 GMT
Message-ID: <7et9q0$tp$1@nnrp1.dejanews.com>

Hello,

Does anybody know what the problem is here? It compiles all right, but blows up at runtime. Thanks

SQL> CREATE OR REPLACE PROCEDURE arl (table_name_in IN VARCHAR2)   2 IS
  3

  4  avg_row_len    NUMBER := 0;
  5  avg_row_len_hold  NUMBER := 0;
  6  col_name    VARCHAR2(30);

  7
  8 cursor1 INTEGER;
  9 ret_val INTEGER;
 10
 11 CURSOR get_cols IS
 12 SELECT column_name
 13 FROM cols
 14 WHERE table_name = UPPER(table_name_in);  15
 16 BEGIN
 17
 18 cursor1 := DBMS_SQL.OPEN_CURSOR;
 19
 20 OPEN get_cols;
 21 LOOP
 22 FETCH get_cols INTO col_name;
 23 EXIT WHEN get_cols%NOTFOUND;
 24
 25 dbms_output.put_line(col_name);
 26
 27 DBMS_SQL.PARSE( cursor1,

 28        'SELECT AVG(VSIZE(col_name)) INTO avg_row_len FROM :x1',
 29        DBMS_SQL.V7);

 30
 31 DBMS_SQL.BIND_VARIABLE(cursor1, ':x1', UPPER(table_name_in));  32
 33 ret_val := DBMS_SQL.EXECUTE(cursor1);  34
 35 avg_row_len_hold := avg_row_len + avg_row_len_hold;  36
 37 END LOOP;
 38
 39 DBMS_OUTPUT.PUT_LINE ('Average Row Length of '||table_name_in||' is '||ROUND(avg_row_len, 2));

 40
 41 END;
 42 /

Procedure created.

SQL> exec arl('company')
ID
begin arl('company'); end;

*
ERROR at line 1:

ORA-00905: missing keyword
ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "KLIST.ARL", line 27
ORA-06512: at line 1

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own    

Received on Mon Apr 12 1999 - 12:16:57 CDT