Lacroe_utils.Create_Load_Script( Table_Name, Schema_Owner, Date_Format, Action_Name, Script_Format, In_File_Dir, Bad_File_Dir, Dis_File_Dir, Direct_Load, Load_Unrecoverable, Field_Seperator) : Creating load scripts has always been a nightmare for a lot of people, not any more. Now there is an automated solution to that problem. Using Lacroe_Utils you can now create loader scripts for SQL*Loader (for both position based and delimited source files). The only known limitation of this script is dealing with clobs and blob and bfiles (haven't had to time to update this script). You can define the Table, Owner, Date format of your choice, Mode : Append/Replace etc.., Data Type 1=CSV, 2=Fixed Length.

Lacroe_utils.Elapsed_time (Start_Date_Time, Stop_Date_Time) : This function returns a string with the elapsed time in HH:MI:SS format, given 2 parameters start_date_time and stop_date_time. Unlike most scripts that I have seen with this script your date can span any number of days. This script can be very useful in cases where you wanted to calculate response times from the time a problem was reported to the time it was resolved or wish to find out how long was a person inside a restricted area (provided you have the date and time).

Lacroe Utils.List_Columns (Table_Name, Owner, #of spaces to lpad) : When you are coding a script and wish to list the columns explicitly and if you are like me where you wish to indent your code properly, you might want to use this script. 

Lacroe_Utils.List_Index ( Table_Name, Owner) : This script will give you a detailed list of all the indexes associated with a table, by table name, index_name and columns associated  with each index.

Lacroe_Utils.List_Lock_Info : Monitoring Locks has been a constant headache when your system goes live for the first time and your users are unaware of the lock implications. Here is a script that will help you keep up with such situation, on Unix systems you can also get the Unix PID for the process that is locking (just in case you decide to use the kill command).

If you forget the syntax for any of the functions within lacroe_utils, you can have execute the lacroe_utils.show_usage and it will list all the functions with samples.

I don't know who would use this but I had someone come to me the other day and ask me for a function to verify if a certain position in a string was a number, so I called the function IS_NUMBER, it takes 3 parameters, input string that you wish to evaluate, the start position that you wish to check and the number of positions from the start location to be evaluated for a number, returns A 'Y' if the the values in the position is a number else returns an 'N'.

Download Lacroe_Utils

Other Utilities :

Re-compile invalid objects: Script to recompile either all invalid objects in the system or just the ones in the user's schema. 

NEW : Re-compile invalid objects dynamic SQL Oracle Version 8.0.5Oracle Version 8.1.6.

List Session Locks : This Script will list sessions that are holding locks which is preventing other sessions from continuing.

Tablespace Summary: This script will give you a the list of tablespaces in your system, their allocated size, free space, used space, percent free and the size of the biggest extent.

Tablespace Space Estimate : This script provides all same information as Tablespace Summary but in addition to that it can also predict space requirements based on next extent size.

Oracle Process(Session)Monitor This script enables you to list all the current oracle sessions, their Session Id, Unix process id, os user name etc...

List invalid objects: Script to list either all invalid objects in the system or just the ones in the user's schema.

Find the center of the screen for an LOV object in Oracle Forms. This script will take out the guess work when designing Oracle forms. 

Script to rebuild all indexes for a given table .

Script to rebuild all indexes for a given schema owner .

Script to rebuild all indexes on a given tablespace .

Stand alone version ofIS_NUMBER function (see lacroe_utils for details).

Script to generate script(s) to drop a named tablespace. It is assumed that you are running any variation of unix (Solaris/HPUX/AIX/Dynix etc...) because one part of the script use the rm command. The script by itself will not destroy anything on the database but if you execute its output you are in danger of putting your job on the line.

Script to help monitor the health of your database daily.

Stored procedure to list long running jobs in the concurrent manager. This procedure uses the utl_file functions to write the request ID and the elapsed time. Please note that this script is useful only if you are using Oracle Financials.

Script to demonstrate how to load clob column with data from the filesystem Clob Sample.

Shell script(s) that will help you create a new database

Handy unix commands

Some handy Oracle commands

Dynamic sql function that wil allow you to select any column from any table.

Row Level Security This is a working example of how to implement row level security. In ths example the objective is to restrict what is displayed to the user without having to write complex security inside every application. This oracle feature allows you to enable security policy at the object level and can be enforced even when the object is accessed with a third party tool/ODBC/JDBC etc..

Reset password to Welcome If you forgot your sysadmin (fin apps) password you can reset it to "welcome" here is how...

Partitioning : Sample script that demonstrates the how to create partition tables and also use the Exchange partition feature of oracle where you can replace a partition with a table.