Unix : Exporting a schema / stored procedure packages

Often you need to update the stored procedure packages for a schema.

So you laboriously export them via DDL and pretty them up and then run them on the target schema and then commit them.

Sometimes it's just easier to export the schema as an Oracle dump (which includes all the related stored procedures) and then import them on the target schema.

An example of the export command is:

exp login/password@schema file=target file directory/export name.dmp

Enjoy!

SQL : Comparing VSS stored procedures with the DB

This is a right royal pain. You have to extract every .pkg and .pkb manually e.g. if using SQL Navigator you right click, Extract DDL and save.

But how do you ensure that VSS is up-to-date?

An easy way is to right click on the heading e.g. right click on Package Bodies and then Extract DDL from here. What you are doing is creating one DDL file that contains ALL the package stored procedures.

In VSS, however, each package is stored individually. To combine them, DOS comes to the rescue! Use the copy construct "+".

e.g. copy a + b + c d.

This concatenates a, b and c and puts them in a file called d.

Now you can compare the combined DDL extract with the concatenated file from VSS.

But wait, there's more!

Part of the DDL extract puts the date on the extract and the tnsname that you use to access the DB. Different developers may have different tns names. So you need to remove these lines. This can be done very simply by writing a program to parse the file and create a new one without these lines.

e.g. in C#, something like:

(where the command line is something like 'Program 'input file' 'filtered file'')

static void Main(string[] args)
{

string readBuffer = "";
int gIndex = 0;
int fIndex = 0;

if (args.Length != 2)
{

Console.WriteLine ("\nFormat is Prog 'input' 'output'");
System.Environment.Exit (0);

}

try
{

FileStream readStream = new FileStream (args[0], FileMode.Open);
StreamReader fileRead = new StreamReader (readStream);
FileStream writeStream = new FileStream (args[1], FileMode.Create);
StreamWriter fileWrite = new StreamWriter(writeStream);

while (fileRead.Peek () >= 0)
{

readBuffer = fileRead.ReadLine();

gIndex = readBuffer.IndexOf ("-- Generated");

if (gIndex == -1)
{

string tempReadBuffer = readBuffer.ToLower();
fIndex = tempReadBuffer.IndexOf ("-- from 'a specific DB string'");

if (fIndex == -1)
fileWrite.WriteLine (readBuffer);

}

}

fileRead.Close ();
fileWrite.Flush ();
fileWrite.Close ();

}

catch (Exception e)
{

Console.WriteLine("Error : {0}", e.ToString());

}

}

So the batch file would be:

copy concatenate statement
run filter on concatenated file
run filter on saved DDL file
compare the two filtered files

You could use something like Examdiff which is free to do the compare in which case the batch file line would be:

start ExamDiff.exe File1 File2

Enjoy!

SQL : Date format and style

I'm always getting confused between the UK and US formats:

For UK format i.e. dd/mm/yyyy, the style parameter is 103

CONVERT (DATETIME, '18/12/2005 00:00:00', 103)

For US format i.e. mm/dd/yyyy, the style parameter is 101

CONVERT (DATETIME, '18/12/2005 00:00:00', 101)

And I really wish that people would give examples like '18/12/2005' where it's quite obvious which is the date and which is the month instead of the normal examples like '04/06/2005' where it could be either.

Enjoy!

SQL : Comparing dates with SQL server

The date is actually stored as "date & time".

So a search:

SELECT * FROM Table
WHERE Table_Date > GETDATE()

will not find any articles with a Table_Date of today - because the GETDATE() time now is > the time the record was inserted into the table.

To do the compare properly, only the date portions must be compared i.e.

SELECT * FROM Table
WHERE (CONVERT(varchar(10), Table_Date, 112) >= CONVERT(varchar(10), GETDATE(), 112))

112 is the ISO standard style to ensure the correct compare results.

Enjoy!