| | |
Description | Compatibility | Level/
Author | Views/
Date
Submitted | User Rating |
| |
| |
 |
 add a foreign key constraint on the fly, referencing an in-use parent table | Oracle | Advanced /
Andy Archer | 4834 since
12/2/2008 4:08:06 PM | Unrated
|
| |
This SQL*Plus script executes a PL/SQL block to add a foreign key constraint, and provides for referencing a table on which DML is occurring. An opening is sought amidst ORA-00054 exceptions while looping EXECUTE IMMEDIATE to ALTER TABLE. |
| |
| |
| |
 |
Add Hours Or Days Or Weeks Or Months Or Years | Oracle | Intermediate /
Andy Archer | 11443 since
6/14/2012 5:18:22 PM | Unrated
|
| |
Using simple Oracle date functionality, this PL/SQL stored function adds the specified number of hours, days, weeks, months, or years to the IN date and time, returning the new date and time. With SQL*Plus test script. |
| |
| |
| |
|
append the date and time to a SQL*Plus output filename | Oracle | Intermediate /
Andy Archer | 6333 since
1/7/2009 9:03:35 AM | Unrated
|
| |
If you've got an output file that you SPOOL to regularly from SQL*Plus, but you don't want to overwrite the file, you can uniquely identify it by appending the date and time to the filename. Here's an example. |
| |
Please support the site by visiting our
sponsor:
|
| |
| |
|
build control and real-time statistics into your PL/SQL loops | Oracle | Beginner /
Andy Archer | 8923 since
5/2/2006 2:26:25 AM |
By 4 Users
|
| |
Code examples, with narrative, that illustrate how to build control and real-time statistic
gathering into PL/SQL loops. |
| |
| |
| |
|
Calendar | Oracle | Advanced /
Andy Archer | 38499 since
12/21/2008 11:10:23 AM |
By 2 Users
|
| |
Calendar is a PL/SQL stored procedure that displays a 12-month calendar for the input year upon SQL*Plus execution. (This is a modified version of Sergio Rueda's 7/25/2002 "calproc.sql" Planet Source Code submission). |
| |
| |
| |
|
Can Row Be Locked | Oracle | Intermediate /
Andy Archer | 1369 since
1/11/2012 2:29:01 PM | Unrated
|
| |
This little PL/SQL stored function tells you whether or not a particular row in a particular table can be locked. Handy code-saver when you need to know. Just pass it schema name (optional), table name, and ROWID. |
| |
| |
| |
|
check the free space in your database | Oracle | Advanced /
Andy Archer | 1551 since
12/25/2011 5:50:39 PM | Unrated
|
| |
This SQL*Plus script prompts for a PERCENT USED value, runs SQL to get data on all tablespaces with that percentage (or more) of used space, spools the results to a file, and opens the file in Notepad. |
| |
| |
| |
|
Compute Age | Oracle | Advanced /
Andy Archer | 12784 since
11/5/2009 9:08:28 AM |
By 6 Users
5 Excellent Ratings
|
| |
This PL/SQL stored procedure returns a person's age in whole years. It returns the number of whole years between two dates, and will also validate a date. Includes three stored function variants and SQL*Plus test scripts. |
| |
| |
| |
|
Count Character Or String | Oracle | Advanced /
Andy Archer | 10170 since
4/15/2008 1:08:50 PM |
By 1 Users
|
| |
Without looping, this little PL/SQL stored function returns the number of times a character or string of characters appears in the IN string. With SQL*Plus test script. |
| |
| |
| |
|
Count Characters & Count Digits | Oracle | Advanced /
Andy Archer | 11365 since
1/4/2010 8:55:44 AM | Unrated
|
| |
Without looping, the tiny count_characters PL/SQL stored function returns the count of IN set characters that are in the IN string. Similarly, count_digits returns the count of digits (0123456789). With SQL*Plus test scripts. |
| |
| |
| |
|
Count Distinct Characters | Oracle | Intermediate /
Andy Archer | 6503 since
11/25/2008 2:27:25 PM | Unrated
|
| |
This little PL/SQL stored function returns the count of distinct characters in the IN string. With SQL*Plus test script. |
| |
| |
| |
|
Count Integer Places & Count Fractional Places | Oracle | Advanced /
Andy Archer | 12660 since
11/8/2012 4:09:27 PM |
By 1 Users
|
| |
Without looping, these tiny PL/SQL stored functions return the number of places to the left or right of the input number's decimal point, respectively. With both-in-one version and SQL*Plus test scripts. |
| |
| |
| |
|
Count Words | Oracle | Advanced /
Andy Archer | 6656 since
10/21/2010 9:38:08 PM | Unrated
|
| |
Without looping, this tiny PL/SQL stored function returns the number of space-delimited characters and strings in in_string. With SQL*Plus test script. |
| |
| |
| |
|
DBMS_JOB job scheduler - INTERVAL examples | Oracle | Advanced /
Andy Archer | 15119 since
1/17/2012 5:09:30 PM |
By 3 Users
3 Excellent Ratings
|
| |
Here are nineteen examples of scheduling jobs to run at different intervals using the DBMS_JOB package. (Intermediate to Advanced). |
| |
| |
| |
|
delete duplicates in a table | Oracle | Intermediate /
Andy Archer | 11389 since
5/12/2013 6:46:38 PM |
By 1 Users
|
| |
Here's an example PL/SQL stored procedure that uses a cursor with an ORDER BY clause (carrying the primary key) to delete duplicate rows in a table. |
| |
| |
| |
|
delete duplicates in a table, using FOR UPDATE NOWAIT | Oracle | Intermediate /
Andy Archer | 12844 since
5/12/2013 6:46:33 PM |
By 1 Users
|
| |
Here's an example PL/SQL block that uses a cursor with an ORDER BY (primary key) clause and a FOR UPDATE NOWAIT clause, along with the pseudocolumn ROWID, to delete duplicate rows in a table. |
| |
| |
| |
|
delete duplicates in a table, with one SQL statement | Oracle | Intermediate /
Andy Archer | 18709 since
5/12/2013 6:46:48 PM |
By 4 Users
3 Excellent Ratings
|
| |
This kind of SQL statement deletes duplicates in a table, leaving the first row inserted of each duplicate set. It deletes rows from a set of duplicates where the primary key values are greater than that of the original row. |
| |
| |
| |
|
describe all tables and views | Oracle | Intermediate /
Andy Archer | 7075 since
2/5/2008 1:32:37 PM |
By 1 Users
|
| |
This little SQL*Plus script builds and runs DESC (describe) strings for all user tables and views, spools the results to a file, and opens the file in Notepad. A quick and handy look at all your tables and views. |
| |
| |
| |
|
display a chart of the printable and space ASCII characters and their codes on your PC's screen | Oracle | Intermediate /
Andy Archer | 1707 since
2/10/2012 12:05:12 PM | Unrated
|
| |
This very compact SQL*Plus script displays a nice chart of the printable and space ASCII characters and their codes on your screen. (This is really just a PL/SQL block with a SET SERVEROUTPUT command stuck on the top). (ScreenShot) |
| |
| |
| |
|
display your pertinent SQL*Plus session info on your PC's screen with this little script | Oracle | Advanced /
Andy Archer | 2311 since
9/24/2012 4:13:03 PM | Unrated
|
| |
See your pertinent SQL*Plus session info (and some other stuff) on your PC's screen when you run this little script at the prompt. Nice and compact. (Intermediate to Advanced).(ScreenShot) |
| |
| |
| |
|
do DML on many tables, from many other similar tables, with minimal coding | Oracle | Advanced /
Andy Archer | 3635 since
6/22/2010 8:37:19 AM | Unrated
|
| |
This write-up with code shows how to do inserts, updates, and deletes on many tables, from many other similar tables, by using a mapping table, a REF cursor, and SQL built around PL/SQL variables. The mapping table makes for tight PL/SQL coding. |
| |
| |
| |
|
Does Row Exist | Oracle | Intermediate /
Andy Archer | 3111 since
9/21/2009 8:55:48 AM | Unrated
|
| |
Oracle developers commonly use "SELECT COUNT(*) INTO <variable>" in their PL/SQL as an easy way to see whether or not rows exist in a table. But this can be slow. Here's a PL/SQL stored function that takes a different, faster approach. |
| |
| |
| |
|
Dollars And Cents To English | Oracle | Advanced /
Andy Archer | 22854 since
3/16/2007 2:40:59 PM | Unrated
|
| |
This PL/SQL stored function returns the English language translation of the dollars and cents input. (Range: -999999999999999.99 through 999999999999999.99). |
| |
| |
| |
|
eight Oracle developer pitfalls | Oracle | Intermediate /
Andy Archer | 9447 since
11/15/2010 8:28:05 PM |
By 2 Users
|
| |
Write-up and examples: (1) NULL, (2) NOT IN, (3) inadvertent index suppression, (4) unexpected COMMIT, (5) TO_CHAR conversions, (6) SQLCODE and SQLERRM, (7) EXECUTE IMMEDIATE of SELECT INTO, and (8) initializing in DECLARE. |
| |
| |
| |
|
eight SQL*Plus tips | Oracle | Beginner /
Andy Archer | 9411 since
9/4/2012 3:10:51 PM |
By 3 Users
2 Excellent Ratings
|
| |
A short write-up: suppress "Input truncated" and SET echoing; see full text of DBMS_OUTPUT and LONG Selects; execute scripts with shorthand; edit session profile; save and reapply settings; see leading spaces on DBMS_OUTPUT. |
| |
(ScreenShot)
(ScreenShot)
