Reserved Word for Table 1

[ekta22]

Hi,

We have A table named 'LOG' in our database. When I use that inside a cursor in my package header, I get an error saying-

Error(17,14): PLS-00225: subprogram or cursor 'LOG' reference is out of scope

I read on this forum that using a double quote around a reserved word should work fine. But that gives me the same error. How to get around this w/o changing the table name.

create or replace
PACKAGE REPORT_P_SUMMARY AS
TYPE SUMMARY_TYPE IS REF CURSOR;

PROCEDURE SUMMARY(
  SUMMARY_CURSOR IN OUT SUMMARY_TYPE,
  pFAC_TYPE IN FACILITIES.F_TYPE%TYPE,
  pFAC_IDENT IN FACILITIES.F_IDENT%TYPE,
  pLOG_ID IN "LOG".LOG_ID%TYPE,
  pACTION_CODE IN "LOG".ACTION_CODE%TYPE    
  
);
END REPORT_P_LOGSUM;

Thanks!

 

[SantaMufasa]

Ekta,

First, if you name an Oracle object (e.g., table, column name, et cetera) using double quotes to surround the name, then all subsequent references (including references in existing scripts and other code), must also contain the double quotes.

If you were willing to go to all of that trouble (i.e., renaming the table with double quotes) which requires modification to every reference in existing code, then I suggest that you just rename the table to something that is not a keyword in Oracle.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

LOG is not a reserved word. It is a keyword. It is possible (but not advised) to create an object/entity/attribute with the name LOG without having to use quotes (as Dave says - using quotes is a bad idea anyway) This implies that your error has nothing to do with the table's name. Can you provide the code itself?
You say

When I use that inside a cursor in my package header,

And yet the code that you post seems to have no relevance to this statement at all.

 

[ChrisHunt]

You can get it to work by specifying the schema name as well as the table:

create or replace
PACKAGE REPORT_P_SUMMARY AS
TYPE SUMMARY_TYPE IS REF CURSOR;

PROCEDURE SUMMARY(
  SUMMARY_CURSOR IN OUT SUMMARY_TYPE,
  pFAC_TYPE IN FACILITIES.F_TYPE%TYPE,
  pFAC_IDENT IN FACILITIES.F_IDENT%TYPE,
  pLOG_ID IN owning_username.LOG.LOG_ID%TYPE,
  pACTION_CODE IN owning_username.LOG.ACTION_CODE%TYPE    
  
);
END;

Obviously you have to put the right schema name in instead of "owning_username".


-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[ekta22]

Thanks Chris! Using the schema name in front of LOG worked just fine.

 

[SantaMufasa]

Ekta,

Chris's resolution is excellent! Such a solution would not have occurred to me.

Since Chris bailed you out of your fix, be sure to award him a

for his ingenuity.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]