"Column ambiguously defined" - Difference in Oracle 10g and 11g

Our product is always tested against Oracle 10g and one of our client insisted to move to Oracle 11g database and shockingly the database creation crashed.On studying the logs we came to the conclusions that best coding practices if followed always helps you to make your product stable, scalable.

What we found is that Oracle 11g does not compile queries that have  columns from different table without their corresponding table aliases.

e.g.

This query will work till Oracle 10g only and will fail in 11g and higher versions.This will give "Column ambiguously defined" error message in Oracle 11g and higher versions.

Select Column1,Column2 from Table1 inner join Table2 On Table1.Column1 = Table2.Column2;


However the query below will Work in Oracle 10g ,11g and all coming higher versions as this uses best coding practices

Select Table1.Column1 ,Table2.Column2 from Table1 inner join Table2 On Table1.Column1 = Table2.Column2;


This clearly shows that best practices if followed give slight pain while implementing but are always fruitful in longer run.

Problem with Office Automation (MS-Word) using Asp.Net 3.5 on 64 bit Windows 2008/Vista

Implementing MS-Word Automation using VSTO 3.0 works well with Windows 2008 / XP /2003 all with 32 bit versions. But these office automation Application start to fails once deployed on 64 bit machines.I am still searching for the exact reasons.However this can be termed as bug from Microsoft or not willing to update 64 bit Windows to support Office Applications. However now OpenXML is the new term which they strongly recommended for implementing office automation.

After searching on various forums I came to know that it is still possible to run office automations application on 64 bit machines(I tested on windows 2008 64 bit version). This is possible by

On Windows 2008 Server x64
Please make this folde
C:\Windows\SysWOW64\config\systemprofile\Desktop

Windows 2008 Server x86
Please make this folder
C:\Windows\System32\config\systemprofile\Desktop

There is one more solution available which talks about doing some hack in registry which I am not very comfortable at so i did'n tried that.However I am writing the it for the reference if somebody need that.

First, use regedit (from the SysWOW64 folder) to search the registry for the CLSID(s) related to the command "WINWORD.EXE /Automation" , you might find more than one of them.
(in my case this was: {000209FE-0000-0000-C000-000000000046} and {000209FF-0000-0000-C000-000000000046})
Under those keys in HKEY_CLASSES_ROOT\CLSID\, add a string value AppID = {same value as the IDs}
Then under HKEY_CLASSES_ROOT\AppID\ create a new key (folder) for each of these IDs, and inside each of them add a string value: RunAs = Interactive User
Next go to Dcomcnfg (from the SysWOW64 folder) and search for those IDs. (there might be a third ID related to Winword.exe), in my case this was {00020906-0000-0000-C000-000000000046}.


Note all those IDs, then right click on each of them, Properties, Security, and edit both the launch & access permissions to add and give Network Service and Interactive full permissions.

Task Scheduling in Oracle 10g

I have a working experience in Sql Server 2005 and creating Jobs and scheduling them is just the mater of few clicks in MS SQL Server.

However in Oracle this can be achieved with lot more control

Jobs are creared in Oracle using "DBMS_SCHEDULER" which is internal Oracle package (since Version 10g).I will try to describe about three basic aspects of DBMS_SCHEDULAR which are bare minimum required to create a Job in Oracle.

• Programs
• Schedule
• Jobs

Here Programs and Schedules are individual entities , then Jobs are used to associate a Program with a Schedule.

PROGRAMS

Programs contains the actual task that a scheduler will be required to perform.Oracle supports these tasks implementation in various ways.

DBMS_SCHEDULER.create_program (

program_name => 'plsql_block',

program_type => 'PLSQL_BLOCK',
program_action => 'Any valid pl-sql block.....',
enabled => TRUE,
comments => 'This program will run the above pl-sql block');


Here "program_type" parameter decides how the tasks are written in program. This can be either of these

1. PLSQL_BLOCK
2. EXECUTABLE
3. STORED_PROCEDURE

Here EXECUTABLES can be used to call OS utilities and programs, not just PL/SQL program units. This ability makes it the most comprehensive job management tool for managing Oracle Database and related jobs.

SCHEDULES

Here is the sample sql to create a simple schedular.

DBMS_SCHEDULER.create_schedule (
schedule_name => 'Audit_Purge_schedule',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily;byhour=0;byminute=0;bysecond=0',
end_date => NULL,
comments => 'This will run daily');

The most important parameter of Schedular is repeat_interval , some of the mostly used options are
freq
This specifies the type of recurrence. It must be specified. The possible values are YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.
bymonth
This specifies which month or months you want the job to execute in. You can use numbers such as 1 for January and 3 for March, as well as three-letter abbreviations such as FEB for February and JUL for July.
byyearday
This specifies the day of the year as a number. Valid values are 1 to 366. An example is 69, which is March 10 (31 for January, 28 for February, and 10 for March). 69 evaluates to March 10 for non-leap years and March 9 in leap years. -2 will always evaluate to December 30th independent of whether it is a leap year.
bymonthday
This specifies the day of the month as a number. Valid values are 1 to 31. An example is 10, which means the 10th day of the selected month. You can use the minus sign (-) to count backward from the last day, so, for example, BYMONTHDAY=-1 means the last day of the month and BYMONTHDAY=-2 means the next to last day of the month.
byday
This specifies the day of the week from Monday to Sunday in the form MON, TUE, and so on. Using numbers, you can specify the 26th Friday of the year, if using a YEARLY frequency, or the 4th THU of the month, using a MONTHLY frequency. Using the minus sign, you can say the second to last Friday of the month. For example, -1 FRI is the last Friday of the month.
byhour
This specifies the hour on which the job is to run. Valid values are 0 to 23. As an example, 10 means 10AM.
byminute
This specifies the minute on which the job is to run. Valid values are 0 to 59. As an example, 45 means 45 minutes past the chosen hour.
bysecond
This specifies the second on which the job is to run. Valid values are 0 to 59. As an example, 30 means 30 seconds past the chosen minute.

JOBS

Jobs are basically the actuall implementation where you combine your task (program) with a time schedule. They can either be made up of predefined parts (programs and schedules) or completely self contained depending on which overload of the CREATE_JOB procedure is used to create them:

The first overload will create job entirely of its own, no programs and Schedules

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'JobswithNoProgramNoSchedule',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.gather_schema_stats(''SCOTT''); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0',
end_date => NULL,
enabled => TRUE,
comments => 'Job defined entirely by the CREATE JOB procedure.');
END


The second overload will display a Job defined by an existing program and schedule.

This is the best modular approach to create Job.With this approac you can clearly isolate task with schedule and edit both of them separately.

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test_prog_sched_job_definition',
program_name => 'test_plsql_block_prog',
schedule_name => 'test_hourly_schedule',
enabled => TRUE,
comments => 'Job defined by an existing program and schedule.');
END


Grant Permissions

If you are planning to use scheduler within a stored procedure, then you must have Create Job privilege to the schema.
Grant Create Job to mySchema

Here myschema is the name schema where schedular is created.
Create Job is the privilege required to execute DBMS_Schedular

Monitoring

Show all schedules
select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules;

Show all jobs and their attributes:
select * from dba_scheduler_jobs

Show all program-objects and their attributes
select * from dba_scheduler_programs;

Show all program-arguments:
select * from dba_scheduler_program_args;