Reserved word NUMBER used as column name causing cursor declaration error

Question

... at least I think this is a problem.

I am writing a function that contains a cursor declaration that accesses a table where one of the columns is the reserved word NUMBER (yes, I know ..). This function got into a problem during compilation:

Error (16.10): PL / SQL: ORA-06552: PL / SQL: compilation module analysis completed ORA-06553: PLS-488: invalid variable declaration: object "NUMBER" must be a type or subtype

MY code looks something like this:

CURSOR my_cur IS SELECT "NUMBER", col2, col3 FROM tb1_x; 

To make sure this is a problem, I changed the code to

 CURSOR my_cur IS SELECT 'NUMBER', 'col2', 'col3' FROM dual; 

and it compiled in order, but obviously this is not what I want.

Unfortunately, I have no way to change the column name (sigh), but for the record

  SELECT "NUMBER", col2, col3 FROM tb1_x; 

works fine with normal SQL execution.

Anyway, can I get around this problem? Any help is much appreciated!

Answer 1

We can also create a record, as well as use the column in cursor.fetching data from cursor i, surprising to me since I used this before.

 Create table temp2("number" integer,id integer,name varchar2(200)); insert into temp2 values(1,1,'Gaurav Soni'); insert into temp2 values(2,2,'Niharika Saraf'); Commit; DECLARE type abc is record( "number" number, id NUMBER, name varchar2(200)); v_rec abc; TYPE v_cur IS REF CURSOR; cur v_cur; v_temp INTEGER; BEGIN OPEN cur FOR SELECT "number", id, name FROM temp2; LOOP FETCH cur INTO v_rec; EXIT when cur%notfound; DBMS_OUTPUT.put_line('number is ' || v_rec."number"); DBMS_OUTPUT.put_line('id is ' || v_rec.id); DBMS_OUTPUT.put_line('name is ' || v_rec.name); end loop; CLOSE cur; end; 

Exit

 number is 1 id is 1 name is Gaurav Soni number is 2 id is 2 name is Niharika Saraf 

Answer 2

 Create table temp2("number" integer); insert into temp2 values(1); insert into temp2 values(2); insert into temp2 values(3); commit; DECLARE TYPE v_cur IS REF CURSOR; cur v_cur; v_temp INTEGER; BEGIN OPEN cur FOR SELECT "number" FROM temp2; FETCH cur INTO v_temp; DBMS_OUTPUT.put_line ('number is ' || v_temp); CLOSE cur; 

END;

Exit

number is 1

I cannot repeat this problem, but for me it works great

Answer 3

Hm. The structure of your Cursor ad is slightly different from mine. Can you try this:

 drop table temp2; Create table temp2("NUMBER" integer ); insert into temp2 values(1); Commit; CREATE OR REPLACE FUNCTION func1 RETURN VARCHAR2 IS l_dummy VARCHAR2(10) := ''; CURSOR cur1 IS SELECT * FROM temp2; BEGIN FOR a_rec IN cur1 LOOP l_dummy := 'dummy'; END LOOP; RETURN l_dummy; END func1; / SHOW ERRORS; 

Compilation error above. Just change the second line to get rid of the reserved word that I managed to collect. BTW I am using an Oracle SQL Developer client connecting to Oracle 10.2 db.

 Create table temp2("NUMBERxxx" integer );