Home » SQL & PL/SQL » SQL & PL/SQL » SP2-0552: Bind variable "LIMIT" not declared. (Oracle database 11.2.04 on Linux)
SP2-0552: Bind variable "LIMIT" not declared. [message #662729] Wed, 10 May 2017 18:39 Go to next message
youngb912
Messages: 53
Registered: October 2007
Location: New York
Member
I am getting error while trying to run a select against the oracle sys.dba_profiles view. Please see below:

SQL> desc sys.dba_profiles
Name Null? Type
----------------------------------------- -------- ----------------------------
PROFILE NOT NULL VARCHAR2(30)
RESOURCE_NAME NOT NULL VARCHAR2(32)
RESOURCE_TYPE VARCHAR2(Cool
LIMIT VARCHAR2(40)

SQL> SELECT substr(profile,1,length(profile))||':'||
substr(resource_name,1,length(resource_name))||':'||
substr(resource_type,1,length(resource_type)) ||':'||
substr(limit,1,length(limit))||':'
profile:name:type:limit
FROM sys.dba_profiles;

SP2-0552: Bind variable "LIMIT" not declared.
Re: SP2-0552: Bind variable "LIMIT" not declared. [message #662730 is a reply to message #662729] Wed, 10 May 2017 19:15 Go to previous messageGo to next message
mikek
Messages: 29
Registered: January 2017
Junior Member
Column Header Name for query needed to be enclosed in double quotes ". Oracle was
misinterpreting it as a bind variable that starts with a colon : .
(i.e profile:name:type:limit should be "profile:name:type:limit")

SELECT   SUBSTR(profile,1,length(profile))
      ||':'
      || SUBSTR(resource_name,1,length(resource_name))
      ||':'
      || SUBSTR(resource_type,1,length(resource_type))
      ||':'
      || SUBSTR(limit,1,length(limit))
      ||':'                               "profile:name:type:limit" 
FROM    sys.dba_profiles; 

Re: SP2-0552: Bind variable "LIMIT" not declared. [message #662731 is a reply to message #662730] Wed, 10 May 2017 19:24 Go to previous message
youngb912
Messages: 53
Registered: October 2007
Location: New York
Member
It works with your recommendation. Thanks very much for your assitance.
Previous Topic: Using CHECK constraint to validate two types of telephone formatted insert data
Next Topic: Loop SP out cursor inside a stored procedure
Goto Forum:
  


Current Time: Tue Oct 23 23:41:39 CDT 2018