How to handel preserved words (as a columnname) into a stored procedure??

[GKramer]

Hi out there,

Due to some history development (not mine) we had to convert and Interbase database into an Oracle environment. Some tables-columns where named "LABEL". This gave no problem into Interbase and when using a SQL editor this columns can be qeuried in Orcale. But when I want to define a cursor into a stored procedure it returns an error that it will not regognize the right datatype.

Here's the code I use;

SELECT ITEM.ITEM_CODE,
ITEM.LABEL||'_'||LPAD(ITEM.ITEM_CODE,4,0) AS TABCOL
FROM ITEM
WHERE ITEM.MODULE_CODE = xxxx
ORDER BY ITEM.ITEM_CODE;

Where to use it...
this one works

L_COLUMNS1 := L_COLUMNS1||', ANSW'||R_C_ITEM.ITEM_CODE||' NUMBER' ;

But this one is NOT

L_COLUMNS1 := L_COLUMNS1||','||R_C_ITEM.TABCOL||' NUMBER' ;

I found out that the last one is not working because the LABEL column is a preserved word in Oracle. ( replace it by one of the other columns and it will work )!!

PLEASE HELP ME OUT ON THIS

Regards
GKramer

[SANJAY_G]

Have you tried putting it in double quotes?

[GKramer]

Sanjay_G,

This makes no difference !!

these are the errors popping up

.ORA-00902: Unkown datatype.
ORA-06512: in "Q1000_VES.SPSS_CREATE_REP_TABLES", line 87
ORA-06512: in line 1


GKramer

[GKramer]

Sanjay_G,

Yhis makes no difference !!

these are the errors popping up

.ORA-00902: Unkown datatype.
ORA-06512: in "Q1000_VES.SPSS_CREATE_REP_TABLES", line 87
ORA-06512: in line 1

???????????

GKramer

[SANJAY_G]

I don't think the error is not because of reserved word, Something else is causing that. You may post few lines of your actual code and people here can help.

[GKramer]

I'm building a string ( for a create table ) and declared it as a varchar2(32767). I've been told that the max of varchar2 can be 2000.
If so this will be the problem but I'm not sure about this.

After buiding the string I give an "execute Immediate" on it . can this statement handle such a long string??

Please let me know

Regards GKramer.

[SANJAY_G]

VARCHAR2 max is 4000

[slimdave]

32767 is the max inPL/SQL, 4000 is the max in a table (use CLOB for longer strings)