Identifiers in pl/sql, PL-SQL Programming

Identifiers

You use identifiers to name the PL/SQL program items and units that include constants, variables, cursors, exceptions, cursor variables, subprograms, and packages. Some of the examples of identifiers is shown below:

X

 t2

phone#

credit_limit

LastName

oracle$number

An identifier consists of a letter optionally followed by many letters, numerals, underscores, dollar signs, and number signs. Other characters like slashes, hyphens, and spaces are illegal, as the examples shown below:

mine&yours -- illegal ampersand

debit-amount -- illegal hyphen

on/off -- illegal slash

user id -- illegal space

The next examples represents that adjoining and trailing dollar signs, underscores, and number signs are permitted:

money$$tree

SN##

try_again_

You can use lower, upper, or mixed case to write the identifiers. The PL/SQL is not case sensitive except within the string and character literals. Therefore, if the only difference between identifiers is the case of corresponding letters, then PL/SQL considers the identifiers to be similar, as the example shown below:

lastname

LastName-- same as lastname

LASTNAME-- same as lastname and LastName

The length of an identifier may not exceed 30 characters. But, each character, involving underscores, dollar signs, and number signs, is significant. For example, the PL/SQL  considers the following identifiers to be different:

lastname last_name

Identifiers must be descriptive. And hence, avoid obscure names like cpm. Rather, use of meaningful names like cost_per_thousand.

Predefined Identifiers

The Identifiers worldwide declared in package STANDARD, like the exception INVALID_NUMBER, can be re-declared. Though, re-declaring predefined identifiers is error prone as your local declaration overrides the global declaration.

Quoted Identifiers

For flexibility, the PL/SQL encloses identifiers within the double quotes. The Quoted identifiers are seldom required, but rarely can they be useful. They can contain any sequence of printable characters together with spaces but excluding the double quotes. And hence, the following identifiers are valid:

"X+Y"

"last name"

"on/off switch"

"employee(s)"

"*** header info ***"

The highest length of a quoted identifier is 30 characters not counting the double quotes. However allowed, using the PL/SQL reserved words as quoted identifiers is a poor programming practice.

Some of the PL/SQL reserved words are not reserved by the SQL. For example, you can use the PL/SQL reserved word TYPE   in a CREATE TABLE  statement to name a database column. But, if a SQL statement in your program refers to that column, you get a compilation error, as the following example is shown below:

SELECT acct, type, bal INTO ...-- causes compilation error

To prevent the error, enclose the uppercase column name in double quotes, as shown below:

SELECT acct, "TYPE", bal INTO ...

The column name cannot appear in the lower or mixed case (unless it was defined that way in the CREATE TABLE statement). For example, the statement below is invalid:

SELECT acct, "type", bal INTO ...-- causes compilation error

Otherwise, you can create a view that renames the troublesome column, then use the view rather of the base table in SQL statements.

Posted Date: 10/2/2012 2:05:28 AM | Location : United States







Related Discussions:- Identifiers in pl/sql, Assignment Help, Ask Question on Identifiers in pl/sql, Get Answer, Expert's Help, Identifiers in pl/sql Discussions

Write discussion on Identifiers in pl/sql
Your posts are moderated
Related Questions
set serveroutput on declare a number(5); b number(5); c number(5); begin a:=&a; b:=&b; c:=a/b; dbms_output.put_line(c); exception when zero_divide then d

Need Azure CRM Web Application with two-factor authentication We presently have a CRM-like database stored on MS Azure that we presently access over an MS Access application. It

Managing Cursors The PL/SQL uses 2 types of cursors: implicit and explicit. The PL/SQL declares a cursor implicitly for all the SQL data manipulation statements, including th

SSRS Report Writing Project Description: This report is part of a larger project to make a SQL Server Reporting Service (SSRS) based reporting solution. There can be more rep

How Exceptions Are Raised By the run-time system, the internal exceptions are raised implicitly as are user-defined exceptions that you have related with an Oracle error number

PRIMARY KEY: PRIMARY KEY  indicates that the table is subject to a key constraint, in this case declaring that no two rows in the table assigned to ENROLMENT can ever have the

Implicit Rollbacks Before execute the INSERT, UPDATE, or DELETE statement, the Oracle marks an implicit savepoint . When the statement fails, the Oracle rolls back to the save

SQL Is a Database Language: The commands given to a DBMS by an application are written in the database language of the DBMS. The term data sublanguage is sometimes used instea

Write SQL queries to solve the following specifications. Include the query AND THE OUTPUT.  A screen dump of the output is acceptable. Show as many rows as you can. A screen dump i

Varrays versus Nested Tables The Nested tables are differing from varrays in the following ways: 1)  Varrays have a maximum size, while nested tables do not. 2)  Varrays are