Identifiers in pl/sql, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Identifiers in pl/sql

Mutual recursion, Mutual Recursion The Subprograms are mutually recursi...

Mutual Recursion The Subprograms are mutually recursive if they directly or indirectly call each other. In the illustration below, the Boolean functions odd & even, that dete

Creating a table, Creating a Table Syantax: CREATE TABLE ENROL...

Creating a Table Syantax: CREATE TABLE ENROLMENT (StudentId SID, Name   VARCHAR (30) NOT NULL, CourseId CID, PRIMARY KEY (StudentId, CourseId)) ; Explan

Effects of null operator, Effects of NULL Operator As a general rule-b...

Effects of NULL Operator As a general rule-but not a universal one-if NULL is an argument to an invocation of a system-defined read-only operator, then NULL is the result of t

Interesting properties of cross join - sql, Interesting properties of CROSS...

Interesting properties of CROSS JOIN - SQL Compare these with the "interesting properties of JOIN", CROSS JOIN is associative but not commutative. Unlike JOIN and NATURAL JOI

Using exists - collection methods, Using EXISTS The EXISTS(n) returns ...

Using EXISTS The EXISTS(n) returns TRUE if the nth element in a collection exist. Or else, EXISTS(n) returns FALSE. Primarily, you use EXISTS with DELETE to maintain the spars

Iterative control:exit statements, EXIT The EXIT statement forces a loop...

EXIT The EXIT statement forces a loop to done unconditionally. Whenever an EXIT statement is encountered, the loop is done immediately and controls the passes to the next statem

Perform exception handling with user-defined errors, On occasion, some of B...

On occasion, some of Brewbean's customers mistakenly leave an item out of a basket already checked out, so they create a new basket containing the missing items. However, they requ

Effects of null in table literal, Effects of NULL in Table Literal Whe...

Effects of NULL in Table Literal When a VALUES expression appears as the source value for an SQL INSERT statement, the key word NULL can appear as a field value, such that for

Using inner join, Using INNER JOIN INNER JOIN is used to retrieve the ...

Using INNER JOIN INNER JOIN is used to retrieve the data from all tables listed based on a condition of equality listed after keyword ON. If the condition is not meet, rows ar

Parameter and keyword description - sql cursor, Parameter and Keyword Descr...

Parameter and Keyword Description: SQL: This SQL is the name of the implicit SQL cursor. %FOUND: This attribute results TRUE if an INSERT, DELETE, or UPDATE state

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd