Adding or subtracting time from Oracle dates is not always obvious and not always readable. Adding whole days is straightforward – 1 = one day – or with the use of the add_months function, adding months is straightforward, but if, for example you want to add 3 hours to sysdate you would have to do something like the following:
Of course that could be written differently – using constants perhaps – but for the purposes of this example i’ve kept it simple.
Luckily interval can be used to produce much more readable code. The example above could be rewritten like so:
sysdate + INTERVAL '3' HOUR
Note the use of singular “hour”.
Here are a few other uses for interval, including adding seconds and minutes to a date:
SELECT sysdate + INTERVAL '3' SECOND,
sysdate + INTERVAL '3' MINUTE,
sysdate + INTERVAL '3' HOUR,
sysdate + INTERVAL '3' DAY,
sysdate + INTERVAL '3' MONTH,
sysdate + INTERVAL '3' YEAR FROM dual
The following example will create a PLSQL variable for a record type that consists of two attributes – id and description. The attributes aren’t restricted to standard Oracle types; you could also include user created types like the record_type in the example.
type record_type is record(id number, description varchar2(1000));
Creating a variable of this type is as simple as referring a variable as something like a varchar2
The following example assigns values to the record.
variable1.id := 1;
variable1.description := 'this is a test description;
When attempting to display a full month in Oracle, it adds spaces by default, up to the length of the maximum month.
This occurs when using
You could wrap that with a trim function call, or you could use
To empty a PL/SQL array, simply call delete.
This example shows how to delete an APEX collection called TEST_COLLECTION.
APEX_COLLECTION.DELETE_COLLECTION ( p_collection_name => 'TEST_COLLECTION');
Wikipedia describes MD5 as
The MD5 Message-Digest Algorithm is a widely used cryptographic hash function that produces a 128-bit (16-byte) hash value. Specified in RFC 1321, MD5 has been utilized in a wide variety of security applications, and is also commonly used to check data integrity. An MD5 hash is typically expressed as a hexadecimal number, 32 digits long.
One example could be to encrypt a password to store in a users table. When a user attempts to login, you can MD5 the entered password and compare it with the stored value.
Here is a function that accepts in a varchar2 as input and then generates an md5 checksum, before finally returning it.
CREATE OR REPLACE FUNCTION generate_md5 (input VARCHAR2)
DBMS_OBFUSCATION_TOOLKIT.md5 (input_string => input)
Here is an example on how to use DBMS_SCHEDULER to create a directory. The database server must have access to the location where you wish to create the directory.
DBMS_SCHEDULER.CREATE_JOB(job_name => 'create_dir_example',
job_type => 'executable',
job_action => 'c:\windows\system32\cmd.exe /c mkdir \\PATH_TO_NEW_DIR',
enabled => TRUE,
auto_drop => TRUE);
Although this example creates a directory, you could alter it to run any batch script you want.
If you have ever programmed before, you will know what a for loop is. This is Oracle’s for loop, which is specifically designed to loop through the results of a select statement.
There are a number of ways you can implement a cursor for loop. I will include a couple and perhaps add more when I get time.
In the examples, I am simply output the contents of column a from within the query.
FOR r IN (SELECT 234 a FROM dual UNION SELECT 5673 a FROM dual) LOOP
This has the exact same output, but it declares the cursor query in the declaration section. This makes more sense if you are right a larger block of code.
CURSOR c IS
SELECT 234 a FROM dual
SELECT 5673 a FROM dual;
FOR r IN c LOOP
This is obviously a very basic example, and unexciting, but hopefully it shows how a PL/SQL array can be used. In Oracle an array is classed as a collection.
I select from a table, in this case it is dummy data from dual, and then populate the array using a cursor for loop. I then loop through the array and output the contents. I told you it wasn’t exciting.
TYPE v_type IS TABLE OF NUMBER;
v_array v_type := year_type();
CURSOR c IS
SELECT 1 a
SELECT 81 a
FOR r IN c LOOP
v_array(v_array.LAST) := r.a;
FOR i IN v_array.FIRST .. v_array.LAST LOOP
DBMS_OUTPUT.put_line('Array value: ' || v_array(i));