===========================================
=============================
What are the different types of pragma and where can we use them?
Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler. The syntax for pragmas are as follows
PRAMA
The instruction is a statement that provides some instructions to the compiler. Pragmas are defined in the declarative section in PL/SQL.
The following pragmas are available: AUTONOMOUS_TRANSACTION:
Prior to Oracle 8.1, each Oracle session in PL/SQL could have at most one active transaction at a given time. In other words, changes were all or nothing. Oracle8i PL/SQL addresses that short comings with the
AUTONOMOUS_TRANSACTION pragma. This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction. For instance, if rollback or commit needs to take place within the block without effective the transaction outside the block, this type of pragma can be used.
EXCEPTION_INIT:
The most commonly used pragma, this is used to bind a user defined exception to a particular error number. For example: Declare I_GIVE_UP EXCEPTION; PRAGMA EXCEPTION_INIT(I_give_up, -20000); BEGIN ..
EXCEPTION WHEN I_GIVE_UP do something..
END;
RESTRICT_REFERENCES:
Defines the purity level of a packaged program. This is not required starting with Oracle8i.
Prior to Oracle8i if you were to invoke a function within a package specification from a SQL statement, you would have to provide a RESTRICT_REFERENCE directive to the PL/SQL engine for that function.
Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT
To handle error conditions (typicallyORA-messages) that have no predefined name, you must use
theOTHERShandler or the pragmaEXCEPTION_INIT. Apragma is a compiler directive that is processed at
compile time, not at run time.
In PL/SQL, the pragmaEXCEPTION_INITtells the compiler to associate an exception name with an Oracle
error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.
You code the pragmaEXCEPTION_INITin the declarative part of a PL/SQL block, subprogram, or package
PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);
whereexception_nameis the name of a previously declared exception and the number is a negative value
corresponding to anORA-error number. The pragma must appear somewhere after the exception declaration
in the same declarative section, as shown inExample 10-4.
Example 10-4 Using PRAGMA EXCEPTION_INIT DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60); BEGIN
NULL; -- Some operation that causes an ORA-00060 error EXCEPTION
WHEN deadlock_detected THEN NULL; -- handle the error END;
/
Defining Your Own Error Messages: Procedure RAISE_APPLICATION_ERROR
The procedureRAISE_APPLICATION_ERRORlets you issue user-definedORA-error messages from stored
subprograms. That way, you can report errors to your application and avoid returning unhandled exceptions.
To callRAISE_APPLICATION_ERROR, use the syntax
raise_application_error(
error_number, message[, {TRUE | FALSE}]);
whereerror_numberis a negative integer in the range -20000 .. -20999 andmessageis a character string up
to 2048 bytes long. If the optional third parameter isTRUE, the error is placed on the stack of previous errors.
If the parameter isFALSE(the default), the error replaces all previous errors.RAISE_APPLICATION_ERRORis
part of packageDBMS_STANDARD, and as with packageSTANDARD, you do not need to qualify references to
it.
An application can callraise_application_erroronly from an executing stored subprogram (or method). When
called,raise_application_errorends the subprogram and returns a user-defined error number and message to
the application. The error number and message can be trapped like any Oracle error.
InExample 10-5, you callraise_application_errorif an error condition of your choosing happens (in this case,
if the current schema owns less than 1000 tables):
Example 10-5 Raising an Application Error With raise_application_error DECLARE
num_tables NUMBER; BEGIN
SELECT COUNT(*) INTO num_tables FROM USER_TABLES; IF num_tables < 1000 THEN
/* Issue your own error code (ORA-20101) with your own error message. Note that you do not need to qualify raise_application_error with DBMS_STANDARD */
raise_application_error(-20101, 'Expecting at least 1000 tables'); ELSE
NULL; -- Do the rest of the processing (for the non-error case). END IF;
END; /
The calling application gets a PL/SQL exception, which it can process using the error-reporting
map specific error numbers returned byraise_application_errorto exceptions of its own, as the following Pro*C example shows:
EXEC SQL EXECUTE
/* Execute embedded PL/SQL block using host variables v_emp_id and v_amount, which were assigned values in the host environment. */ DECLARE
null_salary EXCEPTION;
/* Map error number returned by raise_application_error to user-defined exception. */
PRAGMA EXCEPTION_INIT(null_salary, -20101); BEGIN
raise_salary(:v_emp_id, :v_amount); EXCEPTION
WHEN null_salary THEN
INSERT INTO emp_audit VALUES (:v_emp_id, ...); END;
END-EXEC;
This technique allows the calling application to handle error conditions in specific exception handlers.
Redeclaring Predefined Exceptions
Remember, PL/SQL declares predefined exceptions globally in packageSTANDARD, so you need not declare
them yourself. Redeclaring predefined exceptions is error prone because your local declaration overrides the
global declaration. For example, if you declare an exception namedinvalid_numberand then PL/SQL raises
the predefined exceptionINVALID_NUMBERinternally, a handler written forINVALID_NUMBERwill not
catch the internal exception. In such cases, you must use dot notation to specify the predefined exception, as follows:
EXCEPTION
WHEN invalid_number OR STANDARD.INVALID_NUMBER THEN -- handle the error
END;
===========================================
=============================
Posted 19th April 2011 by Prafull Dangore 0 Add a comment
78.
APR13
A Comparison of Oracle's DATE and TIMESTAMP Datatypes
==============================================================
A Comparison of Oracle's DATE and TIMESTAMP Datatypes
Oracle date and time data types, calculations around these data types, and just plain who to use them often plauge Oracle users more than they should. Here is an article I wrote a while back but still holds some good insight (I think) to using these data types. Hope you agree. If you want to store date and time information in Oracle, you really only have two different options for the column's datatype. Lets take a quick look at these two datatypes and what they offer.
DATE datatype
This is the datatype that we are all too familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved later in this article when we discuss the TIMESTAMP datatype. In order to represent the date stored in a more readable format, the TO_CHAR function
has traditionally been wrapped around the date as inListing A.
LISTING A: Formatting a date
SQL> SELECT TO_CHAR(date1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table; Date
--- 06/20/2003 16:55:14 06/26/2003 11:16:36
About the only trouble I have seen people get into when using the DATE datatype is doing arithmetic on the column in order to figure out the number of years, weeks, days, hours, and seconds between two dates. What needs to be realized when doing the calculation is that when you do subtraction between dates, you get a number that represents the number of days. You should then multiply that number by the number of seconds in a day (86400) before you continue with calculations to determine the interval with which you are
concerned. Check outListing Bfor my solution on how to extract the individual time intervals for a
subtraction of two dates. I am aware that the fractions could be reduced but I wanted to show all the numbers to emphasize the calculation.
LISTING B:
Determine the interval breakdown between two dates for a DATE datatype 1 SELECT TO_CHAR(date1,'MMDDYYYY:HH24:MI:SS') date1, 2 TO_CHAR(date2,'MMDDYYYY:HH24:MI:SS') date2, 3 trunc(86400*(date2-date1))- 4 60*(trunc((86400*(date2-date1))/60)) seconds, 5 trunc((86400*(date2-date1))/60)- 6 60*(trunc(((86400*(date2-date1))/60)/60)) minutes, 7 trunc(((86400*(date2-date1))/60)/60)- 8 24*(trunc((((86400*(date2-date1))/60)/60)/24)) hours, 9 trunc((((86400*(date2-date1))/60)/60)/24) days, 10 trunc(((((86400*(date2-date1))/60)/60)/24)/7) weeks 11* FROM date_table
DATE1 DATE2 SECONDS MINUTES HOURS DAYS WEEKS --- --- --- --- --- --- ---
06202003:16:55:14 07082003:11:22:57 43 27 18 17 2 06262003:11:16:36 07082003:11:22:57 21 6 0 12 1
TIMESTAMP datatype
One of the main problems with the DATE datatype was its' inability to be granular enough to determine which event might have happened first in relation to another event. Oracle has expanded on the DATE datatype and has given us the TIMESTAMP datatype which stores all the information that the DATE datatype stores, but also includes fractional seconds. If you want to convert a DATE datatype to a TIMESTAMP datatype
format, just use the CAST function as I doin Listing C. As you can see, there is a fractional seconds part of
'.000000' on the end of this conversion. This is only because when converting from the DATE datatype that does not have the fractional seconds it defaults to zeros and the display is defaulted to the default timestamp format (NLS_TIMESTAMP_FORMAT). If you are moving a DATE datatype column from one table to a
TIMESTAMP datatype column of another table, all you need to do is a straight INSERTSELECT FROM and
Oracle will do the conversion for you. Look atListing Dfor a formatting of the new TIMESTAMP datatype
where everything is the same as formatting the DATE datatype as we did inListing A. Beware while the
TO_CHAR function works with both datatypes, the TRUNC function will not work with a datatype of
TIMESTAMP. This is a clear indication that the use of TIMESTAMP datatype should explicitly be used for date and times where a difference in time is of utmost importance, such that Oracle won't even let you compare
like values. If you wanted to show the fractional seconds within a TIMESTAMP datatype, look atListing E. In
Listing E, we are only showing 3 place holders for the fractional seconds. LISTING C:
Convert DATE datatype to TIMESTAMP datatype
SQL> SELECT CAST(date1 AS TIMESTAMP) "Date" FROM t; Date
--- 20-JUN-03 04.55.14.000000 PM
26-JUN-03 11.16.36.000000 AM LISTING D:
Formatting of the TIMESTAMP datatype
1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS') "Date" FROM date_table Date
--- 06/20/2003 16:55:14 06/26/2003 11:16:36 LISTING E:
Formatting of the TIMESTAMP datatype with fractional seconds
1 SELECT TO_CHAR(time1,'MM/DD/YYYY HH24:MI:SS:FF3') "Date" FROM date_table Date
---
06/20/2003 16:55:14:000 06/26/2003 11:16:36:000
Calculating the time difference between two TIMESTAMP datatypesdatatype. Look at what happens when you
just do straight subtraction of the columns inListing F. As you can see, the results are much easier to
recognize, 17days, 18hours, 27minutes, and 43seconds for the first row of output. This means no more worries about how many seconds in a day and all those cumbersome calculations. And therefore the calculations for getting the weeks, days, hours, minutes, and seconds becomes a matter of picking out the
LISTING F:
Straight subtraction of two TIMESTAMP datatypes 1 SELECT time1, time2, (time2-time1)
2* FROM date_table
TIME1 TIME2 (TIME2-TIME1)
--- --- ---
06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 +000000017 18:27:43.000000 06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 +000000012 00:06:21.000000 LISTING G:
Determine the interval breakdown between two dates for a TIMESTAMP datatype 1 SELECT time1, 2 time2, 3 substr((time2-time1),instr((time2-time1),' ')+7,2) seconds, 4 substr((time2-time1),instr((time2-time1),' ')+4,2) minutes, 5 substr((time2-time1),instr((time2-time1),' ')+1,2) hours, 6 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))) days, 7 trunc(to_number(substr((time2-time1),1,instr(time2-time1,' ')))/7) weeks 8* FROM date_table
TIME1 TIME2 SECONDS MINUTES HOURS DAYS WEEKS --- --- --- --- --- ---- ---
06/20/2003:16:55:14:000000 07/08/2003:11:22:57:000000 43 27 18 17 2 06/26/2003:11:16:36:000000 07/08/2003:11:22:57:000000 21 06 00 12 1
System Date and Time
In order to get the system date and time returned in a DATE datatype, you can use the SYSDATE function such as :
SQL> SELECT SYSDATE FROM DUAL;
In order to get the system date and time returned in a TIMESTAMP datatype, you can use the SYSTIMESTAMP function such as:
SQL> SELECT SYSTIMESTAMP FROM DUAL;
You can set the initialization parameter FIXED_DATE to return a constant value for what is returned from the SYSDATE function. This is a great tool for testing date and time sensitive code. Just beware that this
parameter has no effect on the SYSTIMESTAMP function. This can be seen inListing H.
LISTING H:
Setting FIXED_DATE and effects on SYSDATE and SYSTIMESTAMP SQL> ALTER SYSTEM SET fixed_date = '2003-01-01-10:00:00'; System altered.
SQL> select sysdate from dual; SYSDATE
--- 01-JAN-03
SQL> select systimestamp from dual; SYSTIMESTAMP
--- 09-JUL-03 11.05.02.519000 AM -06:00
When working with date and time, the options are clear. You have at your disposal the DATE and TIMESTAMP datatypes. Just be aware, while there are similarities, there are also differences that could create havoc if you try to convert to the more powerful TIMESTAMP datatype. Each of the two has strengths in simplicity and granularity. Choose wisely.
===========================================
=======================
Posted 13th April 2011 by Prafull Dangore 0 Add a comment