How to handel preserved words (as a columnname) into a stored procedure??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

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

  1. #1
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73

    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

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  3. #3
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73
    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

  4. #4
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73
    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

  5. #5
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    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"

  6. #6
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73
    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.

  7. #7
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    VARCHAR2 max is 4000
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    32767 is the max inPL/SQL, 4000 is the max in a table (use CLOB for longer strings)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width