Monday
May032010

Modifying an execution plan inside a View using a Query Block Hint (QB_NAME hint)

Updated on Wednesday, May 5, 2010 at 11:54AM by Registered CommenterKarl Reitschuster

Optimizer Hints like Index, Cardinality, Ordered ... are local settings to a SQL statement. Local means the context given is the context of the SQL statement, the scope is local. If you query a View you cannot use for example an Index hint because the table names and aliases are hidden behind the view. So normal hinting would not work without  changing the view to add a hint.

Click to read more ...

Wednesday
Feb172010

Include-Directive for SQL-Scripts with Powershell (II)

In the last recent article I presented the possibilities of the Powershell Import-Schema function; Now we do not want to use the function interactive via Powershell Console but also call it from a SQL-Script; Your SQL-Script will not process Powershell commands but a Powershell script could process the SQL file and execute all Powershell related commands and let SQL commands untouched. The stream then could be redirected to a pure SQL file.

Click to read more ...

Monday
Feb152010

Include-Directive for SQL-Scripts with Powershell (I)

Updated on Tuesday, February 16, 2010 at 5:19PM by Registered CommenterKarl Reitschuster

One thing is to write SQL -, PL/SQL- Code for Packages, Trigger, Views ... and to bring every database object as DDL-File under source control. The other thing is to build a deployment package of the modified database objects(*) involved and to ship them to production. At my current customer the production database deployment package is a flat file without folder hierarchy as usually in development. So you have to merge all affected DDL-Scripts into flat files.

Click to read more ...

Tuesday
Feb092010

Extract Database Object Header Comment with Powershell

Some database objects in my current project are created by ORM or a PL/SQL-framework and hence need not to be put under source control; All manual created objects are strictly stored in source control.

Every database object definition under source control is stored as DDL file. And every file starts with a comment about the database object properties. Once an object is created in database and it's definition is added to source control it's time to automate some repeating stuff like creating a comment header. For the current project I use Windows Powershell for this. All stuff which needs to be generated is done via PL/SQL. The return value of these functions is very simple : a collection of strings.

Click to read more ...

Monday
Jan182010

Access Oracle Database with Windows Powershell 2.0

Updated on Tuesday, February 2, 2010 at 9:31AM by Registered CommenterKarl Reitschuster

This year I plan to implement database build automation. There are lot of manual tasks for a developer DBA to create and deploy a release package.
I decided to use the Windows Powershell as build tool. it integrates best in the Windows environment to access ...

Click to read more ...