Oracle & Oracle

ORACLE - String Functions : rtrim

2008-12-19T18:35:00.000-08:00

the rtrim function removes all specified characters from the right-hand side of a string.

The syntax is:

rtrim( string1, [ trim_string ] )

string1 is the string to trim the characters from the right-hand side.
trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the rtrim function will remove all trailing spaces from string1.

For example:

rtrim('hola '); would return 'hola'
rtrim('hola ', ' '); would return 'hola'
rtrim('paris000', '0'); would return 'paris'
rtrim('332312paris1233213', '123'); would return '332312paris' (removes the individual occurrences of '1', '2', and '3')

ORACLE - String Functions : length

2008-12-19T18:27:00.000-08:00

The length function returns the length of the specified string.

The syntax is:

length( string )

string is the string to return the length for.
If string1 is NULL, then the function returns NULL.

For example:

length('Hola Mundo') would return 10.
length('') would return NULL.

ORACLE - String Functions : Instr

2008-09-22T19:58:00.001-07:00

The instr function returns the location of a substring in a string.
The syntax is:
instr( stringA, stringB [, start_position [, nth_appearance ] ] )
stringA is the string to search.
stringB is the substring to search for in stringA .
start_position is the position in stringA where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of stringA and then searches towards the beginning of stringA .
nth_appearance is the nth appearance of stringB. This is optional. If omitted, it defaults to 1.

For example:
instr('hello word hello', 'e') would return 2; the first occurrence of 'e'
instr('hello word hello', 'e', 1, 1) would return 2; the first occurrence of 'e'
instr('hello word hello', 'e', 1, 2) would return 13; the second occurrence of 'e'
instr('hello word hello', 'e', -3, 2) would return 2.
instr('hello word hello', 'X', 1, 1) would return 0.

ORACLE - Conversion Functions : To_char

2008-09-22T19:58:00.000-07:00

the to_char function converts a number or date to a string.

The syntax :
to_char( value, [ format_mask ], [ nls_language ] )
value can either be a number or date that will be converted to a string.
format_mask is optional. This is the format that will be used to convert value to a string.
nls_language is optional. This is the nls language used to convert value to a string.

Examples - Numbers
The following are number examples for the to_char function.
to_char(1113214.23, '9999.999.9') would return '1113,214.7'
to_char(3214.23, '9,999.99') would return '3,214.23'
to_char(3214.23, '$9,999.00') would return '$3,214.23'
to_char(78, '000099') would return '000078'

Examples - Dates
The following are date examples for the to_char function.
to_char(sysdate, 'yyyy/mm/dd'); would return '2008/07/19'
to_char(sysdate, 'Month DD, YYYY'); would return 'July 19, 2003'

ORACLE - Conversion Functions : To_Number

2008-09-22T19:29:00.000-07:00

The to_number function converts a string to a number.

The syntax is:
to_number( string1, [ format_mask ], [ nls_language ] )
string1 is the string that will be converted to a number.
format_mask is optional. This is the format that will be used to convert string1 to a number.
nls_language is optional. This is the nls language used to convert string1 to a number.

For example:
to_number('981.53', 9999.99') would return the number 981.53
to_number('225', '999') would return the number 225
to_number('23', '99') would return the number 23

Since the format_mask and nls_language parameters are optional, you can simply convert a text string to a numeric value as follows:
to_number('1210.73'') would return the number 1210.73

ORACLE - Date Functions : Months_Between

2008-09-01T13:51:00.001-07:00

The months_between function returns the number of months between date1 and date2.

The syntax is:
months_between( dateA, dateB )
dateA and dateB are the dates used to calculate the number of months.
If a fractional month is calculated, the months_between function calculates the fraction based on a 31-day month.

Example :
months_between (to_date ('2008/01/01', 'yyyy/mm/dd'), to_date ('2008/03/14', 'yyyy/mm/dd') )
would return -2.41935483870968
months_between (to_date ('2008/10/18', 'yyyy/mm/dd'), to_date ('2008/8/18', 'yyyy/mm/dd') )
would return 2

Applies To:
*Oracle 10g
*Oracle 11g

ORACLE - Date Functions : Add_Months

2008-08-29T11:26:00.001-07:00

The add_months function returns a date plus number months.
The syntax is:
add_months( date, number )

date : is the starting date (before the n months have been added).
number : is the number of months to add to date.

Example:
add_months('10-Feb-08', 1) would return '10-Mar-08'
add_months('01-Aug-03', 3) would return '01-Nov-03'
add_months('01-Sep-08', -3) would return '01-Jun-08'

Applies To:
* Oracle 8i
* Oracle 9i
* Oracle 10g
* Oracle 11g

Oracle - Tuning

2008-04-16T17:11:00.000-07:00

Tuning Goals

Response time
Database availability
Database hit percentages
Memory utilization

ORACLE : List of the Oracle system tables

2008-04-10T13:53:00.001-07:00

Below is an listing of the Oracle system tables that are commonly used.

System Table	Description
ALL_CATALOG	All tables, views, synonyms, sequences accessible to the user
ALL_COL_COMMENTS	Comments on columns of accessible tables and views
ALL_CONSTRAINTS	Constraint definitions on accessible tables
ALL_CONS_COLUMNS	Information about accessible columns in constraint definitions
ALL_DB_LINKS	Database links accessible to the user
ALL_ARGUMENTS	Arguments in object accessible to the user
ALL_ERRORS	Current errors on stored objects that user is allowed to create
ALL_LOBS	Description of LOBs contained in tables accessible to the user
ALL_OBJECTS	Objects accessible to the user
ALL_OBJECT_TABLES	Description of all object tables accessible to the user
ALL_SEQUENCES	Description of SEQUENCEs accessible to the user
ALL_SNAPSHOTS	Snapshots the user can access
ALL_SOURCE	Current source on stored objects that user is allowed to create
ALL_SYNONYMS	All synonyms accessible to the user
ALL_TABLES	Description of relational tables accessible to the user
ALL_TAB_COLUMNS	Columns of user's tables, views and clusters
ALL_TAB_COL_STATISTICS	Columns of user's tables, views and clusters
ALL_TAB_COMMENTS	Comments on tables and views accessible to the user
ALL_TRIGGERS	Triggers accessible to the current user
ALL_TRIGGER_COLS	Column usage in user's triggers or in triggers on user's tables
ALL_INDEXES	Descriptions of indexes on tables accessible to the user
ALL_IND_COLUMNS	COLUMNs comprising INDEXes on accessible TABLES
ALL_TYPES	Description of types accessible to the user
ALL_UPDATABLE_COLUMNS	Description of all updatable columns
ALL_USERS	Information about all users of the database
ALL_VIEWS	Description of views accessible to the user
DATABASE_COMPATIBLE _LEVEL	Database compatible parameter set via init.ora
DBA_DB_LINKS	All database links in the database
DBA_ERRORS	Current errors on all stored objects in the database
DBA_OBJECTS	All objects in the database
DBA_ROLES	All Roles which exist in the database
DBA_ROLE_PRIVS	Roles granted to users and roles
DBA_SOURCE	Source of all stored objects in the database
DBA_TABLESPACES	Description of all tablespaces
DBA_TAB_PRIVS	All grants on objects in the database
DBA_TRIGGERS	All triggers in the database
DBA_TS_QUOTAS	Tablespace quotas for all users
DBA_USERS	Information about all users of the database
DBA_VIEWS	Description of all views in the database
DICTIONARY	Description of data dictionary tables and views
DICT_COLUMNS	Description of columns in data dictionary tables and views
GLOBAL_NAME	global database name
NLS_DATABASE_PARAMETERS	Permanent NLS parameters of the database
NLS_INSTANCE_PARAMETERS	NLS parameters of the instance
NLS_SESSION_PARAMETERS	NLS parameters of the user session
PRODUCT_COMPONENT _VERSION	version and status information for component products
ROLE_TAB_PRIVS	Table privileges granted to roles
SESSION_PRIVS	Privileges which the user currently has set
SESSION_ROLES	Roles which the user currently has enabled.
SYSTEM_PRIVILEGE_MAP	Description table for privilege type codes. Maps privilege type numbers to type names
TABLE_PRIVILEGES	Grants on objects for which the user is the grantor, grantee, owner, or an enabled role or PUBLIC is the grantee
TABLE_PRIVILEGE_MAP	Description table for privilege (auditing option) type codes. Maps privilege (auditing option) type numbers to type names

More Oracle
Mas Oracle

ORACLE - NLS_DATE_LANGUAGE

2008-02-26T08:56:00.000-08:00

The NLS_DATE_LANGUAGE parameter explicitly changes the language for abbreviations and names of days and months, and for spelled-out values of other date format elements.

NLS_DATE_LANGUAGE = language

Example :
select to_char(to_date('01-' 'JAN' '/' '2005','dd-MONTH/yyyy',
'NLS_DATE_LANGUAGE = ''ENGLISH'' ') ,
'MONTH DD, YYYY')
from dual

More Oracle
Mas Oracle

ORACLE - NLS_NUMERIC_CHARACTERS, Decimal character and Group Separator

2008-02-26T08:38:00.000-08:00

The NLS_NUMERIC_CHARACTERS parameter explicitly specifies a new decimal character and group separator.

The text value must have this form: 'dg'
- d is the new decimal character.
- g is the new group separator.

The decimal character and the group separator must be two different single-byte characters, and cannot be a numeric value or any of the following characters: plus sign ("+"), less-than sign ("<"), minus sign or hyphen ("-" ), or greater-than sign (">").
If the decimal character is not a period (.), you must use single quotation marks to enclose all number values that appear in expressions in your SQL statements.
When not using a period for the decimal point, use the TO_NUMBER function to ensure that a valid number is retrieved.

EXAMPLE 1 :
to_number( products.price, '999G999G999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')

EXAMPLE 2 :
to_number(products.price, '999G999G999D999999', 'NLS_NUMERIC_CHARACTERS = ''.,''')

EXAMPLE 3 :
SELECT to_number('10.123456', '999G999G999D999999',
'NLS_NUMERIC_CHARACTERS = ''.,''') FROM DUAL

More Oracle
Mas Oracle

ORACLE - The Right Place for PL/SQL

2007-06-19T06:40:00.001-07:00

By Steven Feuerstein

Best practices for PL/SQL location, location, location

I write packages and procedures in both Oracle Database and Oracle Developer applications (Oracle Forms). How should I decide where to put my code?

Another way to frame this question is to ask what the scope of your program should be. That is, from which programs in your application can it be called? Within just a single form? All forms? From within a single program on the server? From any schema that connects to your instance?

I make my decision on these issues by following this principle: Implement the program as closely as possible to where it is used (called).

Location and Relocation Demonstration

For this answer, I use the following business requirement to demonstrate the variations possible and appropriate ways to define my code:

My team is building a call support application. Purchasers of my company's product call us when they have a problem, and we put their calls into a queue if they cannot be handled immediately. I must now write a program that distributes unhandled calls to members of the call support team. The package that will hold all of this logic is call_manager. The procedure for distributing unhandled calls is distribute_calls. Listing 1 shows the header and executable section of this program.

Code Listing 1: distribute_calls procedure

PROCEDURE distribute_calls (    department_id_in IN departments.department_id%TYPE) IS BEGIN    WHILE ( calls_are_unhandled ( ) )    LOOP        FOR emp_rec IN emps_in_dept_cur (department_id_in)        LOOP           IF current_caseload (emp_rec.employee_id) <                   avg_caseload_for_dept (department_id_in)           THEN               assign_next_open_call (emp_rec.employee_id);           END IF;        END LOOP;    END LOOP; END distribute_calls;

As you can see, this executable section is quite readable: While there is still at least one unhandled call, then for each employee in the specified department, if the current caseload is less than the average for that department, assign the next open call to that employee. Then continue only if there are still unhandled calls.

The executable section calls many subprograms to get the work done:

calls_are_unhandled function: takes no arguments; returns TRUE if there is still at least one unhandled call, FALSE otherwise
current_caseload function: returns the number of calls (caseload) assigned to that employee
avg_caseload_for_dept function: returns the average number of calls assigned to employees in that department
assign_next_open_call procedure: assigns the employee to the call, making it handled (as opposed to unhandled)

One other thing to note: I haven't yet actually implemented any of these programs. I am using top-down design, also known as stepwise refinement, to stay focused on the overall, high-level logic of the program. That way I avoid getting consumed by all the little details.

I can now go down to the next level of detail and figure out where to put the implementation for these subprograms.

As I said, my rule is: Define the subprogram as closely as possible to its usage. Following that rule without any further analysis, I would define each of the programs as local subprograms within distribute_calls itself, as shown in Listing 2 (the ellipses [...] indicate the implementation of the subprograms).

Code Listing 2: Four local programs in distribute_calls

PROCEDURE distribute_calls (      department_id_in IN departments.department_id%TYPE) IS     FUNCTION calls_are_handled RETURN BOOLEAN            IS BEGIN ... END calls_are_handled;         FUNCTION current_caseload (              employee_id_in IN employees.employee_id%TYPE)         RETURN PLS_INTEGER           IS BEGIN ... END current_caseload;      FUNCTION avg_caseload_for_dept (              employee_id_in IN employees.employee_id%TYPE)         RETURN PLS_INTEGER           IS BEGIN ... END current_caseload;         PROCEDURE assign_next_open_call (              employee_id_in IN employees.employee_id%TYPE)                IS BEGIN ... END assign_next_open_call;    BEGIN

Procedures and functions directly defined within the declaration section of any PL/SQL block are called local or nested subprograms. In this example, they can be called only within the distribute_calls procedure, and that certainly defines them as closely as possible to their usage.

Yet, as I do this, I find myself thinking about the other programs I have already written in this package and how I might want to use some of this new code in programs I'll write in the future.

I realize, for example, that last week I wrote another function that is very similar to current_caseload. It is now "buried" inside a procedure named show_caseload. Rather than implement the same logic twice (and thereby need to debug and maintain it in both places), it makes more sense for me to move the current_caseload function out of both distribute_calls and show_caseload.

So with a little reshuffling of code, I end up with the package body shown in Listing 3.

Code Listing 3: Relocating the current_caseload function

CREATE OR REPLACE PACKAGE BODY call_manager IS    FUNCTION current_caseload (          employee_id_in IN employees.employee_id%TYPE)        RETURN PLS_INTEGER    IS BEGIN ... END current_caseload;     PROCEDURE show_caseload (          department_id_in IN departments.department_id%TYPE)    IS BEGIN ... END show_caseload;     PROCEDURE distribute_calls (          department_id_in IN departments.department_id%TYPE    )    IS BEGIN ... END distribute_calls; END; /

Now I have moved the current_caseload function farther away from distribute_calls, but that is because it is used by two subprograms in the package. So it is now as close as possible to both of its usages. Yet I don't expect or see any need for current_caseload to be used outside of the distribute_calls package, so I do not place the header of current_caseload in the package specification.

Now my attention turns to avg_caseload_for_dept. Something about this program seems so familiar. What is it, what is it? Oh, yeah! My coworker Sandra sent out an e-mail last week letting us all know that she had put together a package named call_util that contained several handy utility programs, including a function that returned the average caseload for an employee.

I slap my forehead, dig out the e-mail, and find that the function is named dept_avg_caseload. I check for the existence of call_util in my PL/SQL editing environment, andlo and beholdthe call_util.dept_avg_caseload function in all its already implemented beauty is there, waiting to be used.

I now go back to my distribute_calls procedure, delete the avg_caseload_for_dept function, and change my executable section as shown in Listing 4.

Code Listing 4: Revised distribute_calls executable section

BEGIN     WHILE ( calls_are_unhandled ( ) )     LOOP        FOR emp_rec IN emps_in_dept_cur (department_id_in)        LOOP            IF current_caseload (emp_rec.employee_id) <                      call_util.dept_avg_caseload (department_id_in)            THEN                 assign_next_open_call (emp_rec.employee_id);            END IF;        END LOOP;     END LOOP; END distribute_calls;

Now one of the subprograms I am using in my procedure is declared so far away that I don't even have control over its implementation and may never even see that implementation. Is that a problem? No. I have more than enough to do and worry about!

The call_util.dept_avg_caseload function is implemented far from my usage, but it is as close as possible to all usages, which span various packages, and so must be declared in the package specification of call_utils.

Whew. I think I have now finished optimizing the location of the definitions of my subprograms. I am left with two local subprograms (calls_are_unhandled and assign_next_open_call), one program (current_caseload) defined privately at the package level (not appearing in the package specification), and another function (call_util.dept_avg_caseload) that someone else wrote and that is available to any schema with execute authority on the call_util package.

I hope the steps I went through to build distribute_calls will help you make your own decisions on where best to place the implementations of your own complex, multilayered programs.

Oracle Developer Code Location

This column has focused on where and how to define code in Oracle Database, but the same rules and logic apply to the Oracle Developer environment. I suggest these guidelines:

If your program could be useful on both the server side and the client side, move it to the server, because it can be invoked from both sides there.
If the program is used only in and relevant to client-side modules (it may, for example, manipulate the contents of a field in a form) and you think or know that it will be useful in more than one module, put it into a shared library.
If your client program is very specific to a current form or report, define it within that module.

Steven Feuerstein is Quest Software's PL/SQL evangelist. He has published 10 books on Oracle's programming language, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media), and has created the free Quest Code Tester for Oracle.
More Oracle
Mas Oracle

ORACLE - Using PL/SQL Associative Arrays

2007-06-19T06:33:00.001-07:00

By Mark A. Williams

Bring the power of PL/SQL stored procedures to ODP.NET.

Suppose you've just received an e-mail detailing several IT initiatives, two of which will affect your future application development techniques. First, all applications should be designed to minimize network utilization. And second, applications that use Oracle databases must use PL/SQL for access to the database structures.

Knowing that you need to use PL/SQL in the database as the application-programming interface (API) and that you need to reduce network usage, where do you begin? Fortunately, ODP.NET's support for PL/SQL associative arrays can help you meet both IT mandates.

ODP.NET developers can use PL/SQL as an API to the data in the database and use associative array binding to reduce network round-trips. The result is a reduced workload for both the network and Oracle Database, which means faster performance and better scalability for your applications.

The Sample Application

Suppose that one of the reasons for the PL/SQL and performance mandates is that your company is growing rapidly: The IT department alone has three new job classificationsdatabase administrator, manager, and vice president. This column demonstrates how to use .NET, PL/SQL, and associative arrays to add these job categories to an Oracle databasewith minimal network utilization.

The sample developed in this column uses the HR schema provided with Oracle Database. This schema includes a JOBS table that contains a row for each job.

The PL/SQL Code

PL/SQL code will provide the interface between the application and the database. The associative_array package specification and body code in Listing 1 are the interface, and it runs in the database's HR schema. (Note that a Microsoft Visual Studio developer might use Oracle Developer Tools for Visual Studio .NET or a tool such as Oracle SQL Developer to create and edit the PL/SQL code.)

Code Listing 1: The PL/SQL code

create or replace package associative_array as   -- define an associative array type for each column in the jobs table    type t_job_id is table of jobs.job_id%type index by pls_integer;   type t_job_title is table of jobs.job_title%type index by pls_integer;   type t_min_salary is table of jobs.min_salary%type index by pls_integer;   type t_max_salary is table of jobs.max_salary%type index by pls_integer;    -- define the procedure that will perform the array insert    procedure array_insert (p_job_id in t_job_id,                                    p_job_title in t_job_title,                                    p_min_salary in t_min_salary,                                    p_max_salary in t_max_salary); end associative_array; /  create or replace package body associative_array as   -- implement the procedure that will perform the array insert    procedure array_insert (p_job_id in t_job_id,                                    p_job_title in t_job_title,                                    p_min_salary in t_min_salary,                                    p_max_salary in t_max_salary) is   begin     forall i in p_job_id.first..p_job_id.last     insert into jobs (job_id,                            job_title,                            min_salary,                            max_salary)               values (p_job_id(i),                          p_job_title(i),                          p_min_salary(i),                          p_max_salary(i));   end array_insert; end associative_array; /

The associative_array package contains code for both the package specification and the package body. The package specification declares the single procedure that will be implemented in the package body as well as four datatypes that define the parameter types to the procedure. Each type represents a column in the JOBS table, which has the following structure:

SQL> desc jobs   Name            Null?     Type  ----------      -------   --------------  JOB_ID        NOT NULL  VARCHAR2(10)  JOB_TITLE     NOT NULL  VARCHAR2(35)  MIN_SALARY                NUMBER(6)  MAX_SALARY                NUMBER(6)

Because each datatype in the associative_array package is defined to be a table of each database column type, the package code effectively creates four arrays that match the types of their respective columns in the JOBS table.

For example, the t_job_id type is declared to be a single-column table (a PL/SQL table, not a database table) whose type matches that of the job_id column in the JOBS table.

Each single-column PL/SQL table is essentially an array. The array_insert procedure in the associative_array package body takes four parameters (one for each column in the table); each parameter is an array of values supplied by the .NET client application.

The statement for inserting the rows into the JOBS table resembles a traditional INSERT statement, except that it uses the FORALL keyword and that the inserted values are identified by a lowercase i.

The FORALL keyword allows PL/SQL to process all of the elements in the associative array as a group rather than looping over the array, as with a typical FOR LOOP statement.

The lower and upper bounds of the array are indicated by the first and last methods. The lowercase i in the values clause identifies the correct element in the array for PL/SQL retrieval.

Creating the C# Code

With the PL/SQL interface code available in the Oracle database instance, it's time to deploy the .NET client application code, shown in Listing 2.

Code Listing 2: The .NET C# code

 using System; using System.Data; using Oracle.DataAccess.Client; using Oracle.DataAccess.Types;  namespace JanFeb2007 {   class Program {     static void Main(string[] args) {       // connection string - make sure to adjust for your environment       string constr = "user id=hr; password=hr; data source=oramag; enlist=false; pooling=false";        // create and open connection object       OracleConnection con = new OracleConnection(constr);       con.Open();        // insert the new jobs into the jobs table       // create command object and set attributes       OracleCommand cmd = con.CreateCommand();       cmd.CommandText = "associative_array.array_insert";       cmd.CommandType = CommandType.StoredProcedure;        // create parameter objects for each parameter       OracleParameter p_job_id = new OracleParameter();       OracleParameter p_job_title = new OracleParameter();       OracleParameter p_min_salary = new OracleParameter();       OracleParameter p_max_salary = new OracleParameter();        // set parameter type for each parameter       p_job_id.OracleDbType = OracleDbType.Varchar2;       p_job_title.OracleDbType = OracleDbType.Varchar2;       p_min_salary.OracleDbType = OracleDbType.Decimal;       p_max_salary.OracleDbType = OracleDbType.Decimal;        // set the collection type for each parameter       p_job_id.CollectionType = OracleCollectionType.PLSQLAssociativeArray;       p_job_title.CollectionType = OracleCollectionType.PLSQLAssociativeArray;       p_min_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;       p_max_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;        // set the parameter values       p_job_id.Value = new string[3] { "IT_DBA", "IT_MAN", "IT_VP" };       p_job_title.Value = new string[3] { "Database Administrator", "IT Manager", "IT Vice President" };       p_min_salary.Value = new decimal[3] { 8000, 12000, 18000 };       p_max_salary.Value = new decimal[3] { 16000, 24000, 36000 };        // set the size for each array       p_job_id.Size = 3;       p_job_title.Size = 3;       p_min_salary.Size = 3;       p_max_salary.Size = 3;        // add parameters to command object collection       cmd.Parameters.Add(p_job_id);       cmd.Parameters.Add(p_job_title);       cmd.Parameters.Add(p_min_salary);       cmd.Parameters.Add(p_max_salary);        // execute the insert       cmd.ExecuteNonQuery();        // display the new jobs       cmd.CommandText = "select job_id, job_title from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP') order by job_id";       cmd.CommandType = CommandType.Text;        OracleDataReader dr = cmd.ExecuteReader();        Console.WriteLine("New jobs have been added to the JOBS table:\n");        while (dr.Read()) {         Console.WriteLine("{0,6}: {1}", dr.GetString(0), dr.GetString(1));       }        Console.WriteLine();        // delete the new jobs       cmd.CommandText = "delete from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP')";       cmd.ExecuteNonQuery();        Console.WriteLine("New jobs have been removed from the JOBS table.");        // clean up objects       p_max_salary.Dispose();       p_min_salary.Dispose();       p_job_title.Dispose();       p_job_id.Dispose();       cmd.Dispose();       con.Dispose();        // simple prompt to keep output window from closing when executing from IDE       Console.WriteLine("Press ENTER to continue...");       Console.ReadLine();     }   } }

Because the data processing logic is in the database, the client code is concerned largely with creating parameters, setting values, and invoking the code in the database. Note that the code for inserting data contains no SQL statements, because the PL/SQL interface has been colocated with the data in the database.

To test the new PL/SQL interface, run the C# console application in Visual Studio.

First, change the connection string to the appropriate values for your Oracle database instance so ODP.NET can pass associative arrays, then compile the code in Visual Studio, and then select Debug -> Step Into from the Visual Studio menu to see how it works.

As you step through the code, note that the application creates an OracleCommand object with the CommandText property value set to the PL/SQL package name and procedure (associative_array.array_insert).

Next, the application creates Oracle-Parameter objects for each parameter to the PL/SQL stored procedure. The application then sets each parameter's type to the OracleDbType to match that of the targeted column for each parameter.

One key point to note as you step through the code is that the application must correctly set the collection type for each parameter to OracleCollectionType.PLSQLAssociativeArray for associative arrays to work correctly. This is what enables the ODP.NET application to bind an OracleParameter object, as a PL/SQL associative array, to the PL/SQL stored procedure

After setting the collection type, the application assigns the values (for the three new jobs) that will be inserted into the JOBS table. Next, the application sets the size of each array, adds the parameters to the OracleCommand object's parameter collection, and invokes the stored procedure to insert the data.

Finally, the application queries the table to show that the three new jobs (IT_DBA, IT_MAN, and IT_VP) were successfully added to the JOBS table.

Going Further

PL/SQL associative arrays are not limited to INSERT operations. You can also use them to perform SELECT, UPDATE, and DELETE operations. I encourage you to experiment further with PL/SQL associative arrays to discover how your applications may benefit from centralized data access via a PL/SQL API as well as from a mechanism for bulk data sharing between Oracle databases and .NET middle tiers.

Mark A. Williams is an Oracle ACE, an Oracle Certified Professional DBA, the author of Pro.NET Oracle Programming (Apress, 2004), and a contributor to the Oracle Data Provider for .NET forum on Oracle Technology Network.

ORACLE - Create Sequence

2007-06-06T08:30:00.001-07:00

CREATE SEQUENCE

Purpose

To create a sequence. A sequence is a database object from which multiple users may generate unique integers. You can use sequences to automatically generate primary key values.

Prerequisites

To create a sequence in your own schema, you must have CREATE SEQUENCE privilege.

To create a sequence in another user's schema, you must have CREATE ANY SEQUENCE privilege.

Syntax

Using Sequences

You can use sequence numbers to automatically generate unique primary key values for your data, and you can also coordinate the keys across multiple rows or tables.

Values for a given sequence are automatically generated by special Oracle routines and, consequently, sequences avoid the performance bottleneck that results from implementation of sequences at the application level. For example, one common application-level implementation is to force each transaction to lock a sequence number table, increment the sequence, and then release the table. Under this implementation, only one sequence number can be generated at a time. In contrast, Oracle sequences permit the simultaneous generation of multiple sequence numbers while guaranteeing that every sequence number is unique.

When a sequence number is generated, the sequence is incremented, independent of the transaction committing or rolling back. If two users concurrently increment the same sequence, the sequence numbers each user acquires may have gaps because sequence numbers are being generated by the other user. One user can never acquire the sequence number generated by another user. Once a sequence value is generated by one user, that user can continue to access that value regardless of whether the sequence is incremented by another user.

Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables. It is possible that individual sequence numbers will appear to be skipped, because they were generated and used in a transaction that ultimately rolled back. Additionally, a single user may not realize that other users are drawing from the same sequence.

Accessing Sequence Values

Once a sequence is created, you can access its values in SQL statements with the following pseudocolumns:

CURRVAL	returns the current value of the sequence.
NEXTVAL	increments the sequence and returns the new value.

Example

The following statement creates the sequence ESEQ:

CREATE SEQUENCE eseq

INCREMENT BY 10

The first reference to ESEQ.NEXTVAL returns 1. The second returns 11. Each subsequent reference will return a value 10 greater than the one previous.

CURRVAL and NEXTVAL

A sequence is a schema object that generates sequential numbers. When you create a sequence, you can specify its initial value and an increment.

CURRVAL returns the current value in a specified sequence. Before you can reference CURRVAL in a session, you must use NEXTVAL to generate a number. A reference to NEXTVAL stores the current sequence number in CURRVAL. NEXTVAL increments the sequence and returns the next value. To obtain the current or next value in a sequence, you must use dot notation, as follows:

sequence_name.CURRVAL

sequence_name.NEXTVAL

After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. However, you can use CURRVAL and NEXTVAL only in a select list, the VALUES clause, and the SET clause. In the following example, you use a sequence to insert the same employee number into two tables:

INSERT INTO emp VALUES (empno_seq.NEXTVAL, my_ename, ...);

INSERT INTO sals VALUES (empno_seq.CURRVAL, my_sal, ...);

If a transaction generates a sequence number, the sequence is incremented immediately whether you commit or roll back the transaction.

ORACLE - Create Records

2007-06-04T06:11:00.001-07:00

Create Records

Records are items of type RECORD. Records have uniquely named fields that can store data values of different types.

Syntax

Parameter Description and Keyword

NOT NULL

This constraint prevents the assigning of nulls to a field. At run time, trying to assign a null to a field defined as NOT NULL raises the predefined exception VALUE_ERROR. The constraint NOT NULL must be followed by an initialization clause.

datatype

This is a type specifier. For the syntax of datatype.

record_type_name

This identifies a user-defined type specifier, which is used in subsequent declarations of records.

:= DEFAULT

This operator or keyword allows you to initialize fields to default values.

expression

This is an arbitrarily complex combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. For the syntax of expression. When the declaration is elaborated, the value of expression is assigned to the field. The value and the field must have compatible datatypes.

Usage Notes

You can define RECORD types and declare user-defined records in the declarative part of any block, subprogram, or package. Also, a record can be initialized in its declaration, as the following example shows:

DECLARE

TYPE TimeTyp IS RECORD(

second SMALLINT := 0,

minute SMALLINT := 0,

hour SMALLINT := 0);

The next example shows that you can use the %TYPE attribute to specify a field datatype. It also shows that you can add the NOT NULL constraint to any field declaration and so prevent the assigning of nulls to that field.

DECLARE

TYPE DeptRecTyp IS RECORD(

deptno NUMBER(2) NOT NULL,

name dept.name%TYPE,

local dept.local%TYPE);

dept_rec DeptRecTyp;

To reference individual fields in a record, you use dot notation. For example, you might assign a value to the dname field in the dept_rec record as follows:

dept_rec.dname := 'PURCHASING';

Instead of assigning values separately to each field in a record, you can assign values to all fields at once. This can be done in two ways. First, you can assign one user-defined record to another if they have the same datatype. (Having fields that match exactly is not enough.) You can assign a %ROWTYPE record to a user-defined record if their fields match in number and order, and corresponding fields have compatible datatypes.

Second, you can use the SELECT or FETCH statement to fetch column values into a record. The columns in the select-list must appear in the same order as the fields in your record.

You can declare and reference nested records. That is, a record can be the component of another record, as the following example shows:

DECLARE

TYPE TimeTyp IS RECORD(

minute SMALLINT,

hour SMALLINT);

TYPE MeetingTyp IS RECORD(

day DATE,

time TimeTyp, -- nested record

place CHAR(20),

purpose CHAR(50));

TYPE PartyTyp IS RECORD(

day DATE,

time TimeTyp, -- nested record

loc CHAR(15));

meeting MeetingTyp;

seminar MeetingTyp;

party PartyTyp;

The next example shows that you can assign one nested record to another if they have the same datatype:

seminar.time := meeting.time;

Such assignments are allowed even if the containing records have different datatypes.

User-defined records follow the usual scoping and instantiation rules. In a package, they are instantiated when you first reference the package and cease to exist when you exit the application or end the database session. In a block or subprogram, they are instantiated when you enter the block or subprogram and cease to exist when you exit the block or subprogram.

Like scalar variables, user-defined records can be declared as the formal parameters of procedures and functions. The restrictions that apply to scalar parameters also apply to user-defined records.

You can specify a RECORD type in the RETURN clause of a function specification. That allows the function to return a user-defined record of the same type. When calling a function that returns a user-defined record, you use the following syntax to reference fields in the record:

function_name(parameters).field_name

To reference nested fields in a record returned by a function, you use the following syntax:

function_name(parameters).field_name.nested_field_name

Currently, you cannot use the syntax above to call a parameterless function because PL/SQL does not allow empty parameter lists. That is, the following syntax is illegal:

function_name().field_name -- illegal; empty parameter list

You cannot just drop the empty parameter list because the following syntax is also illegal:

function_name.field_name -- illegal; no parameter list

Instead, declare a local user-defined record to which you can assign the function result, then reference its fields directly.

Example

In the following example, you define a RECORD type named DeptRecTyp, declare a record named dept_rec, then select a row of values into the record:

DECLARE

TYPE DeptRecTyp IS RECORD(

deptno NUMBER(2),

name CHAR(14),

local CHAR(13));

dept_rec DeptRecTyp;

...

BEGIN

SELECT deptno, name, local INTO dept_rec FROM dept

WHERE deptno = 20;

ORACLE - Number Conversions - Number Format

2007-05-29T06:07:00.001-07:00

Number Conversions

Element	Example	Description
9	9999	Return value with the specified number of digits with a leading space if positive. Return value with the specified number of digits with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
0	0999 9990	Return leading zeros. Return trailing zeros.
$	$9999	Return value with a leading dollar sign.
B	B9999	Return blanks for the integer part of a fixed-point number when the integer part is zero (regardless of "0's in the format model).
MI	9999MI	Return negative value with a trailing minus sign "-". Return positive value with a trailing blank.
S	S9999 9999S	Return negative value with a leading minus sign "-". Return positive value with a leading plus sign "+". Return negative value with a trailing minus sign "-". Return positive value with a trailing plus sign "+".
D	99D99	Return a decimal character (that is, a period ".") in the specified position.
G	9G999	Return a group separator in the position specified.
PR	9999PR	Return negative value in <angle brackets>. Return positive value with a leading and trailing blank.
L	L999	Return the local currency symbol in the specified position.
, (comma)	9,999	Return a comma in the specified position.
. (period)	99.99	Return a decimal point (that is, a period ".") in the specified position.
RN rn	RN	Return a value as Roman numerals in uppercase. Return a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999.
FM	FM90.9	Return a value with no leading or trailing blanks.
V	999V99	Return a value multiplied by 10ⁿ(and if necessary, round it up), where n is the number of 9's after the "V".
EEEE	9.9EEEE	Return a value using in scientific notation.

Examples:

SELECT TO_CHAR(number, 'fmt')

FROM DUAL

EJEMPLO :

SELECT TO_CHAR(12021, '999999,99S') FROM DUAL -à RESULT '120,21+'

Results of Example Number Conversions

number	'fmt'	Result
-1234567890	9999999999S	'1234567890-'
0	99.99	' .00'
+0.1	99.99	' 0.10'
-0.2	99.99	' -.20'
0	90.99	' 0.00'
+0.1	90.99	' 0.10'
+1E+123	9.9EEEE	' 1.0E+123'
+123.456	FM9.9EEEE	'1.23E+02'
+123.45	FM999.009	'123.45'
-0.2	90.99	' -0.20'
0	9999	' 0'
1	9999	' 1'
0	B9999	' '
1	B9999	' 1'
0	B90.99	' '
+123.456	999.999	' 123.456'
+123.45	L999.99	' $123.45'
+123.45	FML99.99	'$123.45'
-123.456	999.999	'-123.456'
+123.456	FM999.009	'123.456'
+123.456	9.9EEEE	' 1.2E+02'
+123.0	FM999.009	'123.00'
+1234567890	9999999999S	'1234567890+'

More Oracle
Mas Oracle

ORACLE - Exceptions

2007-05-22T11:02:00.001-07:00

Exceptions

An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers.

Syntax

Keyword and Parameter Description

WHEN

This keyword introduces an exception handler. You can have multiple exceptions execute the same sequence of statements by following the keyword WHEN with a list of the exceptions, separating them by the keyword OR. If any exception in the list is raised, the associated statements are executed.

exception_name

This identifies a predefined exception such as ZERO_DIVIDE, or a user-defined exception previously declared within the current scope.

OTHERS

This keyword stands for all the exceptions not explicitly named in the exception-handling part of the block. The use of OTHERS is optional and is allowed only as the last exception handler. You cannot include OTHERS in a list of exceptions following the keyword WHEN.

exception_name

This identifies a predefined exception such as ZERO_DIVIDE, or a user-defined exception previously declared within the current scope.

statement

This is an executable statement.

Usage Notes

An exception declaration can appear only in the declarative part of a block, subprogram, or package. The scope rules for exceptions and variables are the same. But, unlike variables, exceptions cannot be passed as parameters to subprograms.

Redeclaring predefined exceptions is error prone because your local declaration overrides the global declaration. In such cases, you must use dot notation to specify the predefined exception, as follows:

EXCEPTION

WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN ...

The exception-handling part of a PL/SQL block is optional. Exception handlers must come at the end of the block. They are introduced by the keyword EXCEPTION. The exception-handling part of the block is terminated by the same keyword END that terminates the entire block.

An exception should be raised only when an error occurs that makes it impossible or undesirable to continue processing. If there is no exception handler in the current block for a raised exception, the exception propagates according to the following rules:

· If there is an enclosing block for the current block, the exception is passed on to that block. The enclosing block then becomes the current block. If a handler for the raised exception is not found, the process repeats.

· If there is no enclosing block for the current block, an unhandled exception error is passed back to the host environment.

However, exceptions cannot propagate across remote procedure calls (RPCs). Therefore, a PL/SQL block cannot catch an exception raised by a remote subprogram.

Only one exception at a time can be active in the exception-handling part of a block. Therefore, if an exception is raised inside a handler, the block that encloses the current block is the first block searched to find a handler for the newly raised exception. From there on, the exception propagates normally.

An exception handler can reference only those variables that the current block can reference.

Example

The following PL/SQL block has two exception handlers:

DELARE

bad_emp_id EXCEPTION;

bad_acct_no EXCEPTION;

...

BEGIN

...

EXCEPTION

WHEN bad_emp_id OR bad_acct_no THEN -- user-defined

ROLLBACK;

WHEN ZERO_DIVIDE THEN -- predefined

INSERT INTO inventory VALUES (part_number, quantity);

COMMIT;

END;

How Exceptions Propagate

When an exception is raised, if PL/SQL cannot find a handler for it in the current block or subprogram, the exception propagates. That is, the exception reproduces itself in successive enclosing blocks until a handler is found or there are no more blocks to search. In the latter case, PL/SQL returns an unhandled exception error to the host environment.

However, exceptions cannot propagate across remote procedure calls (RPCs). Therefore, a PL/SQL block cannot catch an exception raised by a remote subprogram. For a workaround,

An exception can propagate beyond its scope, that is, beyond the block in which it was declared. Consider the following example:

BEGIN

...

DECLARE ---------- sub-block begins

past_due EXCEPTION;

BEGIN

...

IF ... THEN

RAISE past_due;

END IF;

END; ------------- sub-block ends

EXCEPTION

...

WHEN OTHERS THEN

ROLLBACK;

END;

Because the block in which it was declared has no handler for the exception named past_due, it propagates to the enclosing block. But, according to the scope rules, enclosing blocks cannot reference exceptions declared in a sub-block. So, only an OTHERS handler can catch the exception.

ORACLE - Date Format

2007-05-22T10:44:00.003-07:00

Default Date Format

The default date format is specified either explicitly with the initialization parameter NLS_DATE_FORMAT or implicitly with the initialization parameter NLS_TERRITORY.

You can change the default date format for your session with the ALTER SESSION command.

Maximum Length

The total length of a date format model cannot exceed 22 characters.

Date Format Elements

A date format model is composed of one or more date format elements . For input format models, format items cannot appear twice, and format items that represent similar information cannot be combined. For example, you cannot use 'SYYYY' and 'BC' in the same format string. Only some of the date format elements can be used in the TO_DATE function

Table 3-15 Date Format Elements

Element	Specify in TO_DATE?	Meaning
- / , . ; : 'text'	Yes	Punctuation and quoted text is reproduced in the result.
AD A.D.	Yes	AD indicator with or without periods.
AM A.M.	Yes	Meridian indicator with or without periods.
BC B.C.	Yes	BC indicator with or without periods.
CC SCC	No	One greater than the first two digits of a four-digit year; "S" prefixes BC dates with "-". For example, '20' from '1900'.
D	Yes	Day of week (1-7).
DAY	Yes	Name of day, padded with blanks to length of 9 characters.
DD	Yes	Day of month (1-31).
DDD	Yes	Day of year (1-366).
DY	Yes	Abbreviated name of day.
E	No	Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE	No	Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
HH	Yes	Hour of day (1-12).
HH12	No	Hour of day (1-12).
HH24	Yes	Hour of day (0-23).
IW	No	Week of year (1-52 or 1-53) based on the ISO standard.
IYY IY I	No	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	No	4-digit year based on the ISO standard.
J	Yes	Julian day; the number of days since January 1, 4712 BC. Number specified with 'J' must be integers.
MI	Yes	Minute (0-59).
MM	Yes	Month (01-12; JAN = 01)
MON	Yes	Abbreviated name of month.
MONTH	Yes	Name of month, padded with blanks to length of 9 characters.
PM P.M.	No	Meridian indicator with or without periods.
Q	No	Quarter of year (1, 2, 3, 4; JAN-MAR = 1)
RM	Yes	Roman numeral month (I-XII; JAN = I).
RR	Yes	Given a year with 2 digits, returns a year in the next century if the year is <50 and the last 2 digits of the current year are >=50; returns a year in the preceding century if the year is >=50 and the last 2 digits of the current year are <50.
RRRR	Yes	Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you don't want this functionality, simply enter the 4-digit year.
SS	Yes	Second (0-59).
SSSSS	Yes	Seconds past midnight (0-86399).
WW	No	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	No	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
Y,YYY	Yes	Year with comma in this position.
YEAR SYEAR	No	Year, spelled out; "S" prefixes BC dates with "-".
YYYY SYYYY	Yes	4-digit year; "S" prefixes BC dates with "-".
YYY YY Y	Yes	Last 3, 2, or 1 digit(s) of year.

Table 3-16 The RR Date Element Format

		If the specified two-digit year is
		0 - 49	50 - 99
If the last two digits of the current year are:	0-49	The return date is in the current century.	The return date is in the preceding century.
	50-99	The return date is in the next century.	The return date is in the current century.

The following example demonstrates the behavior of the RR date format element.

Example 1

Assume these queries are issued between 1950 and 1999:

SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') "Year"

FROM DUAL;

Year

----

1995

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year";

FROM DUAL;

Year

----

2017

Example 2

Assume these queries are issued between 2000 and 2049:

SELECT TO_CHAR(TO_DATE('27-OCT-95', 'DD-MON-RR') ,'YYYY') "Year";

FROM DUAL;

Year

----

1995

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year";

FROM DUAL;

Year

----

2017

Note that the queries return the same values regardless of whether they are issued before or after the year 2000. The RR date format element allows you to write SQL statements that will return the same values after the turn of the century.

Table 3-17 Date Format Element Suffixes

Suffix	Meaning	Example Element	Example Value
TH	Ordinal Number	DDTH	4TH
SP	Spelled Number	DDSP	FOUR
SPTH or THSP	Spelled, ordinal number	DDSPTH	FOURTH

Format Model Modifiers

You can use the FM and FX modifiers in format models for the TO_CHAR function to control blank padding and exact format checking.

"Fill mode". This modifier suppresses blank padding in the return value of the TO_CHAR function:

· In a date format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading and trailing zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, because there is no blank padding, the length of the return value may vary

· In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number, so that the result is left-justified in the output buffer. Without FM, the result is always right-justified in the buffer, resulting in blank-padding to the left of the number.

Example 1

The following statement uses a date format model to return a character expression:

SELECT TO_CHAR(SYSDATE, 'fmDDTH')' of 'TO_CHAR

(SYSDATE, 'Month')', 'TO_CHAR(SYSDATE, 'YYYY') "Ides"

FROM DUAL;

Ides

------------------

3RD of April, 1995

Note that the statement above also uses the FM modifier. If FM is omitted, the month is blank-padded to nine characters:

SELECT TO_CHAR(SYSDATE, 'DDTH')' of '

TO_CHAR(Month, YYYY') "Ides"

FROM DUAL;

Ides

-----------------------

03RD of April , 1995