-
How to handel preserved words (as a columnname) into a stored procedure??
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
-
Have you tried putting it in double quotes?
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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
-
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
-
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.
Last edited by SANJAY_G; 09-19-2003 at 04:47 AM.
Sanjay G.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
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.
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
-
32767 is the max inPL/SQL, 4000 is the max in a table (use CLOB for longer strings)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|