dynamic sql, error ora-06512

From: <cy552_at_my-deja.com>
Date: 2000/07/13
Message-ID: <8klenl$5f1$1@nnrp1.deja.com>#1/1

I am attempting to clean the data that is in a holding table. I am doing this column by column. I am also using dynamic sql to create the update statement. The parse procedure keeps giving me the following error:

ERROR at line 1:
ORA-01747: invalid user.table.column, table.column, or column specification

ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "EXPRESS.DYNAMIC_BATCH_DATA", line 34
ORA-06512: at line 1

The whole procedure is as follows:

CREATE OR REPLACE
PROCEDURE dynamic_batch_data(p_table_name IN VARCHAR2) IS

        CURSOR column_cursor IS
        SELECT column_name
        FROM user_tab_columns
        WHERE table_name = UPPER(p_table_name);
          CURSOR column_count IS

          SELECT COUNT(column_name)
          FROM user_tab_columns
          WHERE table_name = UPPER(p_table_name);

          v_update_clause VARCHAR2(200);
          v_loop_control BOOLEAN := TRUE;
        v_cursor_pointer INTEGER;
        v_column_name VARCHAR2(30);
        v_rows_updated INTEGER;
        array DBMS_SQL.VARCHAR2_TABLE;
        v_count INTEGER;
        v_num_columns NUMBER;
BEGIN
        OPEN column_cursor;
          OPEN column_count;
        FETCH column_count INTO v_num_columns;

      WHILE v_loop_control LOOP

                IF v_num_columns - column_cursor%ROWCOUNT < 20
THEN                         v_count := v_num_columns - column_cursor%
ROWCOUNT;
                ELSE
                        v_count := 20;
                END IF;

                FOR i IN 1..v_count LOOP
                        FETCH column_cursor INTO array(i);
                END LOOP;
                    v_update_clause := 'UPDATE '||p_table_name||' SET
:column_array = UPPER(RTRIM(LTRIM(:column_array)))';
                v_cursor_pointer := DBMS_SQL.OPEN_CURSOR;
                    DBMS_SQL.PARSE(v_cursor_pointer, v_update_clause,
DBMS_SQL.NATIVE);
                DBMS_SQL.BIND_ARRAY(v_cursor_pointer, ':column_array',
array);
                v_rows_updated := DBMS_SQL.EXECUTE


(v_cursor_pointer);                 DBMS_SQL.CLOSE_CURSOR

(v_cursor_pointer);

                IF column_cursor%NOTFOUND THEN
                        v_loop_control := FALSE;
                END IF;

        END LOOP;
        CLOSE column_cursor;

END; Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jul 13 2000 - 00:00:00 CDT