How To Oracle

Easy Oracle PL/SQL Programming

2007-08-10T06:15:00.009-07:00

Written by a distinguished graduate of West Point, Garmany leverages his 20+ years of experience into an indispensable guide for any Oracle professional who must quickly implement Oracle reporting.

A noted instructor, author and lecturer, John Garmany leverages his ability to explain complex issues in Plain English into a one-of-a-kind book. John Garmany targets his decades of SQL*Plus experience into this must-have book. Intended for anyone who need to extract Oracle data and format reports, John reveals the secrets of quickly and easily producing stunning reports from Oracle.

Easy Oracle PL/SQL provides you with the tools you need to create, debug and execute PL/SQL code. The example driven methodology will have you coding basic PL/SQL blocks by the end of the first chapter. Each chapter will build on previous concepts, introducing you to increasingly more complex PL/SQL coding, while always focusing on readability and manageability.

Each concept is introduced using multiple examples and each chapter has exercises to test you knowledge. With Easy Oracle PL/SQL you will be able to put the incredible power of the Oracle Database to use with your application.

Easy HTML-DB Oracle Application Express

2007-08-10T06:15:00.007-07:00

Oracle Application Express (Apex, formerly called HTML DB) is one of the most exciting web application development tools on the market and this is the first and best HTML-DB Application express book. HTML-DB Application Express is a true Rapid Application Development environment that can take an idea from concept to a working production level application in a very short period of time and this book can help.

This unique book provides easy step-by-step examples to guide you through the various features and removes the guesswork from learning Oracle HTML DB Application Express. This book describes the techniques necessary to build Easy HTML DB Apex applications, and shows working examples of HTML DB database access from the simple web page population to the complex database transaction enforcing multi-user concurrency.

This book is unique because it describes all HTML DB Application Express concepts in an easy to understand language and contains step-by-step working examples of various HTML DB features. The HTML-DB Application Express book shows the when, where, why, and how of using each feature to build and deploy your web applications. It is designed for any Oracle professional including DBA's, application developers, Network Engineers and IT managers.

Oracle Tuning (The Definitive Reference)

2007-08-10T06:15:00.005-07:00

Oracle 10g has become the most flexible and robust database ever created and Oracle tuning has become increasingly complex as the result of this massive power. This book provides a complete step-by-step approach for holistic Oracle tuning and it is the accumulated knowledge from tuning thousands of Oracle databases.

Incorporating the principles of artificial intelligence, Oracle10g has developed a sophisticated mechanism for capturing and tracking database performance over time periods. This new complexity has introduced dozens of new v$ and DBA views, plus dozens of Automatic Workload Repository (AWR) tables.

The AWR and its interaction with the Automatic Database Diagnostic Monitor (ADDM) is a revolution in database tuning. By understanding the internal workings of the AWR tables, the senior DBA can develop time-series tuning models to predict upcoming outages and dynamically change the instance to accommodate the impending resource changes.

This is not a book for beginners. Targeted at the senior Oracle DBA, this book dives deep into the internals of the v$ views, the AWR table structures and the new DBA history views. Packed with ready-to-run scripts, you can quickly monitor and identify the most challenging performance issues.

Oracle Job Scheduling

2007-08-10T06:15:00.003-07:00

This unique book focuses on the use of internal utilities to schedule complex Oracle database tasks. The book compares external job scheduling with UNIX cron and Windows AT command, and shows the differences between the dbms_job and dbms_scheduler utilities.

This indispensable reference shows working examples of how to schedule complex time-based jobs, creating streams of multiple jobs, scheduling job streams with intermediate error checking, scheduling of event-based jobs, and much, much more.

Oracle Privacy Security Auditing

2007-08-10T06:15:00.001-07:00

Written by one the world's most widely-read developers and author of best-selling Oracle books, Don Burleson and Arup Nanda target their substantial knowledge of Oracle Internals to this important book. With decades of experience installing Oracle auditing, Arup Nanda shares secrets for the effective creation of auditing mechanisms for HIPAA compliant Oracle systems.

The Health/Insurance Portability and Accountability Act of 1996 (HIPAA) was created to ensure privacy for medical patient data. HIPAA requires complete auditing to show everyone who has viewed confidential medical patient information. This permeates from Hospitals, insurance companies, and dozens of healthcare related industries. HIPAA is a framework that provides a complete security access and auditing for Oracle database information.

This book provides complete details for using Oracle auditing features, including auditing from Oracle redo logs, using system-level triggers, and using Oracle9i fine-grained auditing (FGA) for auditing of the retrieval on sensitive information.

Best of all, Burleson & Nanda share dozens of working samples in his online code depot. Examples from all areas of auditing are covered with working scripts and code snippets. Your time savings from a single script is worth the price of this great book.

Oracle PL/SQL Programming, 4th Edition [ILLUSTRATED] (Paperback)

2007-08-10T06:10:00.005-07:00

Topics covered: How to use Oracle PL/SQL in all its manifestations through Oracle9i. Fundamentals of program structure (loops, cases, exceptions, etc.) and execution get attention, as do data types, transaction management, triggers, and the object-oriented aspects of the language. There's also coverage of calls to external Java and C programs. --This text refers to an out of print or unavailable edition of this title.

Book Description
For the past ten years, O'Reilly's Oracle PL/SQL Programming has been the bestselling book on PL/SQL, Oracle's powerful procedural language. Packed with examples and helpful recommendations, the book has helped everyone--from novices to experienced developers, and from Oracle Forms developers to database administrators--make the most of PL/SQL.

The fourth edition is a comprehensive update, adding significant new content and extending coverage to include the very latest Oracle version, Oracle Database 10g Release 2. It describes such new features as the PL/SQL optimizing compiler, conditional compilation, compile-time warnings, regular expressions, set operators for nested tables, nonsequential collections in FORALL, the programmer-defined quoting mechanism, the ability to backtrace an exception to a line number, a variety of new built-in packages, and support for IEEE 754 compliant floating-point numbers.

The new edition adds brand-new chapters on security (including encryption, row-level security, fine-grained auditing, and application contexts), file, email, and web I/O (including the built-in packages DBMS_OUTPUT, UTL_FILE, UTL_MAIL, UTL_SMTP, and UTL_HTTP) and globalization and localization.

Co-authored by the world's foremost PL/SQL authority, Steven Feuerstein, this classic reference provides language syntax, best practices, and extensive code, ranging from simple examples to complete applications--making it a must-have on your road to PL/SQL mastery. A companion web site contains many more examples and additional technical content for enhanced learning.

Oracle Performance Tuning (Nutshell Handbooks) (Paperback)

2007-08-10T06:10:00.003-07:00

The books in O'Reilly's Oracle series are authoritative -- they tell the whole story about complex topics, ranging from performance tuning to the use of packages in PL/SQL to new technologies like Power Objects. And they're independent; they're alternatives for readers who need to know how products and features really work. They're packed with real-world advice and techniques from practitioners in the field, and they come with disks containing code you can use immediately in your own applications. O'Reilly is the alternative for Oracle people who need to solve problems -- and solve them now. Performance tuning is crucial in any modern relational database management system. Too many organizations respond to Oracle performance problems by throwing money at these problems -- by buying larger and more expensive computers or by hiring expert consultants. But there's a lot you can do on your own to increase dramatically the performance of your existing system. Whatever version of Oracle you're running -- from Version 6 to Oracle8, proper tuning can save your organization a huge amount of money in additional equipment, extra memory, and hardware upgrades. The first edition of Oracle Performance Tuning became a classic for programmers, managers, database administrators, system administrators, and anyone who cares about improving the performance of an Oracle system. This second edition contains 400 pages of updated material updating on Oracle features, incorporating advice about disk striping and mirroring, RAID, client-server, distributed databases, MPPS, SMPs, and other architectures. It also includes chapters on parallel server, parallel query, backup and recovery, the Oracle Performance Pack, and more.

Oracle9i DBA Handbook (Paperback)

2007-08-10T06:10:00.001-07:00

Book Description
This authorized guide from Oracle Press explains how implement all the new features of Oracle9i and manage a robust, high-performance Oracle database. You'll get new and revised material on installation, database creation, using STATSPACK, Recovery Manager, Oracle9i Application Server, external tables, flashback queries, and much more.

From the Back Cover

Implement Expert Administrative Solutions

Keep your mission-critical Internet and e-business systems flexible, secure, and available by using the exclusive information contained in this Oracle-authorized volume. Oracle9i DBA Handbook explains how to set up and maintain a high-performance database and maximize the all-new, powerful features of Oracle9i. Oracle insiders Kevin Loney and Marlene Theriault cover all the bases and provide real-world examples and techniques. A must-have resource for Oracle DBAs.

Create and manage databases, using the Oracle9i Database Configuration Assistant
Monitor and tune file usage, transactions, queries, and memory
Implement rollback segments and system-managed undo
Handle application migration and online table evolution
Diagnose and optimize system performance with STATSPACK
Implement state-of-the-art auditing and security procedures
Automate backup and recovery operations, using RMAN
Implement partitions to manage VLDBs
Distribute computing power and share data across servers, using Oracle Net
Use Oracle9iAS services to improve flexibility, scalability, and availability

Oracle9i: A Beginner's Guide (Paperback)

2007-08-10T06:09:00.001-07:00

Book Description
Your introductory resource to all the powerful new features of Oracle9i--only from the authorized Oracle Press. This beginner's guide starts with an introduction to Oracle Corporation and where it is going with its product strategy, and then moves on to an overview of the RDBMS and the roll of a DBA, and progresses to slightly more advanced topics.

Book Info
Beginner's guide starts with an introduction to Oracle Corporation and where it is going with its product strategy, and then moves on to an overview of the RDBMS and the role of a DBA, and progresses to slightly more advanced topics. Softcover.

Oracle Database Administration: The Essential Reference [ILLUSTRATED] (Paperback)

2007-08-10T06:09:00.000-07:00

Oracle database administration requires a vast amount of information and an ability to perform a myriad of tasks--from installation to tuning to network troubleshooting to overall daily administration. Oracle provides many tools for performing these tasks; the trick is knowing what tool is right for the job, what commands you need to issue (and when), and what parameters and privileges you need to set. And, as every DBA knows, you need to know how do all this under pressure, while you face crisis after crisis. This book provides a concise reference to the enormous store of information an Oracle DBA needs every day (as well as what's needed only when disaster strikes). It's crammed full of quick-reference tables, task lists, and other summary material that both novice and expert DBAs will use time and time again. It covers the commands and operations new to Oracle8, but also provides Oracle7 information for sites still running earlier versions. Oracle Database Administration provides two types of material:

DBA tasks--chapters summarizing how to perform critical DBA functions: installation, performance tuning, preventing data loss, networking, security and monitoring, auditing, query optimization, and the use of various Oracle tools and utilities
DBA reference--chapters providing a quick reference to the Oracle instance and database, the initialization (INIT.ORA) parameters, the SQL statements commonly used by DBAs, the data dictionary tables, the system privileges and roles, and the SQL*Plus, Export, Import, and SQL*Loader syntax

The book also includes a resource summary with references to additional books, Web sites, and other online and offline resources of special use to Oracle DBAs. Oracle Database Administration is the single essential reference you'll turn to again and again. If you must choose only one book to use at the office, keep at home, or carry to a site you're troubleshooting, this will be that book.

How To Access an Oracle Database by Using the OLE DB .NET Data Provider and Visual C# .NET

2007-07-03T11:33:00.001-07:00

SUMMARY

This article demonstrates how to use the ADO.NET OLE DB managed provider to access an Oracle database.

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

	Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
	Oracle Client tools (installed on the computer)
	Microsoft Visual Studio .NET

This article assumes that you are familiar with the following topics:

	Visual Studio .NET
	ADO.NET fundamentals and syntax
	Oracle connectivity

Steps to Access an Oracle Database

1.	In Oracle, create a table named TestTable as follows:`Create Table TestTable (c1 char(5));`
2.	Insert data into TestTable as follows:`Insert into TestTable c1 values('Test1'); Insert into TestTable c1 values('Test2'); Insert into TestTable c1 values('Test3');`
3.	Start Visual Studio .NET.
4.	Create a new Windows Application project in Visual C# .NET.
5.	Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
6.	Drag a Button control to Form1, and change its Name property to btnTest.
7.	Use the using statement on the System, System.Data, and System.Data.OleDb namespaces so that you are not required to qualify declarations in those namespaces later in your code.`using System; using System.Data; using System.Data.OleDb;`
8.	Switch to Form view, and double-click btnTest to add the click event handler. Add the following code to the handler:String sConnectionString = "Provider=MSDAORA.1;User ID=myUID;password=myPWD; Data Source=myOracleServer;Persist Security Info=False"; String mySelectQuery = "SELECT * FROM TestTable where c1 LIKE ?"; OleDbConnection myConnection = new OleDbConnection(sConnectionString); OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection); myCommand.Parameters.Add("@p1", OleDbType.Char, 5).Value = "Test%"; myConnection.Open(); OleDbDataReader myReader = myCommand.ExecuteReader(); int RecordCount=0; try { while (myReader.Read()) { RecordCount = RecordCount + 1; MessageBox.Show(myReader.GetString(0).ToString()); } if (RecordCount == 0) { MessageBox.Show("No data returned"); } else { MessageBox.Show("Number of records returned: " + RecordCount); } } catch (Exception ex) { MessageBox.Show(ex.ToString()); } finally { myReader.Close(); myConnection.Close(); }
9.	Save your project.
10.	On the Debug menu, click Start to run your project.
11.	Click the button to display the data.

How To Call a Parameterized Stored Procedure in Oracle from a Recordset DTC

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

To Add a Data Connection

1.	From the Project menu, choose Add Data Connection.
2.	In the Data Link Properties dialog box, select Microsoft OLE DB Provider for ODBC drivers, and then click Next >>.
3.	Choose Use data source name and provide the system DSN that was created to connect to the Oracle database. (For information on how to create ODBC DSNs to connect to Oracle databases, see the "References" section).
4.	Type a User name and Password. Click the Test Connection button to make sure that you can connect to your Oracle database.

Once you have established a successful data connection in the Visual InterDev Web project, you can start to create ASP pages to call nonparameterized and parameterized stored procedures from your Oracle database server.

To Call a Nonparameterized Stored Procedure in Oracle

Add an ASP page to the Web project: right-click the project in the Project Explorer, click Add, and then select Active Server Page.

Add a Recordset DTC to the ASP page. To do so:

a.	From the View menu, select Toolbox, and then select Design-Time Controls.
b.	Drag a recordset DTC to a position following the <BODY> tag on your ASP page. A dialog box appears and asks if the Visual InterDev Scripting Object Model should be implemented.
c.	Click yes.

Right-click the recordset DTC, and then select Properties. Select SQL Statement.

In the SQL Statement text box, type the following:

{call packperson.allperson({resultset 9, ssn, lname, fname})}

Add a Grid DTC to the ASP page. To do so:

a.	From the Design-Time Controls Toolbox, select Grid DTC.
b.	Drag the GRID DTC onto the ASP page to a position following the recordset DTC.
c.	Bind it to the recordset DTC. To do so, right-click the Grid DTC, and then select Properties.
d.	Click the Data tab, and then select your recordset DTC (for example, Recordset1) as the Recordset.

Save the ASP page and view it in the browser.

This brings up all records from the Oracle stored procedure and only the specific columns ssn, lname, and fname.

Call a Parameterized Stored Procedure in Oracle

NOTE: See Microsoft Knowledge Base article Q240338 in the "References" section of this article if you want to call a parameterized stored procedure in Oracle.

1.	Add an ASP page to the Web project.
2.	Add a recordset DTC to the ASP page. A dialog box appears and asks if you want the Visual InterDev Scripting Object Model to be implemented. Click yes.
3.	Right-click the recordset DTC, and then select Properties.
4.	On the General tab, select SQL Statement.
5.	In the SQL Statement text box, type the following:

{call packperson.oneperson(?, {resultset 2, ssn, lname, fname})}

6.	Select the Parameters tab of the Recordset DTC dialog box. In the Value column of the Values for parameters frame, type the following (include the quotation marks):

'555662222'

7.	Add a Grid DTC to the ASP page and bind it to the recordset DTC.
8.	Save the ASP page and view it in your browser.

Only the one record with the "SSN = '555662222'" is returned from the Oracle stored procedure (and only the columns ssn, lname, and fname appear).

How To Retrieve Resultsets from Oracle Stored Procedures

2007-06-19T11:10:00.001-07:00

SUMMARY

This article demonstrates how to use Visual Basic Enterprise Edition, Remote Data Object (RDO), the Microsoft ODBC Driver for Oracle version 2.0 and higher, and an Oracle PL/SQL package to retrieve resultsets from an Oracle stored procedure.

MORE INFORMATION

With the release of the Microsoft ODBC Driver for Oracle version 2.0 and higher, you can now retrieve resultsets from Oracle stored procedures. By creating Oracle stored procedures that return parameters of type TABLE, you can return row and column data that can then be manipulated and displayed as a resultset. This article uses the example in the Help file for the Microsoft ODBC Driver for Oracle v2.0 and shows how to use that example in Visual Basic.

NOTE: The resultsets created by the Microsoft ODBC Driver for Oracle version 2.0 and 2.5 using Oracle stored procedures are READ ONLY and STATIC. To retrieve a resultset requires that an Oracle Package be created.

Before you can start working with the Visual Basic application, you must create an Oracle package called SimplePackage. SimplePackage is taken from the Help File for Microsoft ODBC Driver for Oracle: Advanced Topics: "Returning Array Parameters from Stored Procedures."

How to connect to an Oracle database by using ASP and ADO

2007-06-13T05:24:00.000-07:00

INTRODUCTION

This article discusses how to connect to an Oracle database by using a Microsoft Active Server Pages (ASP) page and Microsoft ActiveX Data Objects (ADO).

MORE INFORMATION

To connect to an Oracle database, you can create an ASP page that contains the following code.

Note

Make sure that the connect string has a valid user ID and password and that the SQL statement references a valid table.

<%@ Language=VBScript %>
   <html>
   <head>
   <title>Oracle Test</title>
   </head>
   <body>
   <center>
   <%
     Set objConn = Server.CreateObject("ADODB.Connection")
     objConn.Open "Provider=MSDAORA;Data Source=<Your_TNSNames_Alias>;User Id=<userid>;Password=<password>;"

     Set objRs = objConn.Execute("SELECT * FROM DEMO.EMPLOYEE")

     Response.Write "<table border=1 cellpadding=4>"
     Response.Write "<tr>"

     For I = 0 To objRS.Fields.Count - 1
       Response.Write "<td><b>" & objRS(I).Name & "</b></td>"
     Next

     Response.Write "</tr>"

     Do While Not objRS.EOF
       Response.Write "<tr>"

       For I = 0 To objRS.Fields.Count - 1
         Response.Write "<td>" & objRS(I) & "</td>"
       Next

       Response.Write "</tr>"

       objRS.MoveNext
     Loop

     Response.Write "</table>"

     objRs.Close
     objConn.Close
   %>
   </center>
   </body>
   </html>

APPLIES TO

-	Microsoft Active Server Pages 4.0
-	Microsoft Internet Information Services 6.0
-	Microsoft Data Access Components 2.8

ORACLE - Date Format

2007-05-16T14:01:00.001-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. For information on these parameters, see Oracle8 Reference .

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 as listed in Table 3-15. 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 as noted in Table 3-15.

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.

FM

"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

Make Your ASP Work With An Oracle Database

2007-05-07T16:31:00.001-07:00

Oracle is one of the most popular databases in the world, also Active Server Pages (ASP) is a powerful server-side scripting language widely used to build dynamic Web pages. There are many ASP developers who wonder if they can use the ASP technology with Oracle database to build a web application, E-commerce and E-business web sites or internet management systems. The answer is YES! You can access Oracle using VB to create Oracle Applications as well. Here, I will discuss with you how to use ASP dealing with Oracle data.

Before we start, there are a few things you need to know. The Oracle Objects for OLE (OO4O) method is one of them. The OO4O is an Oracle middleware that allows native access to Oracle from client applications using the Microsoft Object Linking and Embedding (OLE) standard. Some of you may think that the ODBC can be used. Surely, you can use the standard database access method developed by Microsoft Corporation to access Oracle, but in my opinion the OO4O is better than ODBC because the OO4O is thread safe and provides full support for PL/SQL. PL/SQL stands for Procedural Language/SQL. It is an Oracle extension of the SQL statement set which allows the developer to impose flow control and logic design onto unstructured SQL command blocks. If you have fully installed Oracle8i, I am sure the OO4O is there for use already. If not, you can download it from Oracle web site.

The other thing is, we need know two objects and one interface that Oracle developed for Visual Basic Development, OraSession and OraDynaset objects, and OraDatabase interface. The OraSession object manages collections of OraDatabase, OraConnection, and OraDynaset used within an application. It is the object created by the CreateObject ASP and not by an OO4O method. The OraDatabase interface represents a user session to an Oracle database and provides methods for SQL and PL/SQL execution. Each of them has some of properties, and methods. For instance, the OraDynaset has some of properties, such as BOF, EOF, Bookmark, Connection, and so on, and ten methods, such as AddNew, Update, Delete, Edit, Refresh, Clone, and so on.

Now, let's start working on Oracle data using ASP technology.

Source: ASP 101

How To Troubleshoot an ASP-to-Oracle Connectivity Problem

2007-05-07T16:20:00.000-07:00

This article outlines some of the common steps to take when you troubleshoot a problem with a connection to an Oracle Database from an Active Server Pages (ASP) application. Some of the more common error messages are:

Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Oracle][ODBC][Ora]ORA-12154: TNS:Could not resolve service name /vdir/filename.asp, line xxx.

-and-

The Oracle(tm) client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3 (or greater) client software installation.

You will be unable to use this driver until these components have been installed.

How to connect to Oracle

2007-05-07T16:16:00.000-07:00

- ODBC,
- OLE DB, OleDbConnection (.NET)
- OracleConnection, Oracle Data Provider, ODP.NET

How to connect to an Oracle database by using ASP and ADO

2007-05-07T15:51:00.000-07:00

This article discusses how to connect to an Oracle database by using a Microsoft Active Server Pages (ASP) page and Microsoft ActiveX Data Objects (ADO).

Source: Microsoft

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.

		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.

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