Seeking Oracle out of the matrix

DBMS_JOB Cheat sheet

noreply@blogger.com (Admin) — Mon, 10 Oct 2011 04:39:00 +0000

I recently wanted to schedule jobs on my Oracle 9i (9.2.0.8) database so i refreshed my memory on how to do it based on the Oracle's job engine.

I will note down a quick reference post for you to check out:

View all running jobs:

select * from user_jobs

Submit a job (this example analyzes a table every 24 hrs:

VARIABLE jobno NUMBER
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',
''HR'', ''EMPLOYEES'',
''ESTIMATE'', NULL, 50);',
SYSDATE, 'SYSDATE + 1');
COMMIT;
END;
/

jobno is returned by the system.
Assuming 14144 for this example.

Remove a job from the job queue:

BEGIN
DBMS_JOB.REMOVE(14144);
END;
/

Change a job:

BEGIN
DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3');
END;
/

Alter the definition of a job:

BEGIN
DBMS_JOB.WHAT(14144,
'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',
''HR'', ''DEPARTMENTS'',
''ESTIMATE'', NULL, 50);');
END;
/

Alter the next execution day of a job:

BEGIN
DBMS_JOB.NEXT_DATE(14144, SYSDATE + 4);
END;
/

Alter execution interval:

BEGIN
DBMS_JOB.INTERVAL(14144, 'NULL');
END;
/

Brake a scheduled job:

BEGIN
DBMS_JOB.BROKEN(14144, TRUE);
END;
/

Continue to run a previously broken job:

BEGIN
DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));
END;
/

Forcing a job to execute:

BEGIN
DBMS_JOB.RUN(14144);
END;
/

Working with intervals:
'SYSDATE + 7' Exactly seven days from the last execution

'SYSDATE + 1/48' Every half hour

'NEXT_DAY(TRUNC(SYSDATE), ''MONDAY'') + 15/24' Every Monday at 3PM

'NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ''Q''), 3), ''THURSDAY'')' First Thursday of each quarter

Reference:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/jobq.htm

Java Server Faces - Export any IteratorBinding to a CSV format file

noreply@blogger.com (Admin) — Sun, 06 Jan 2008 10:45:00 +0000

Due to a large amount of requests regarding data extraction from JSF, i have compiled a small java class which you can embed in jDeveloper, that allows you to extract any IteratorBinding to a CSV format file.

The file is named according to the current date/time where the request was made.

This class can be used for the Greek character set as well.

Below follows the complete source code. Enjoy :)

import java.io.IOException;
import java.io.PrintWriter;

import java.text.SimpleDateFormat;

import java.util.Calendar;
import java.util.Date;
import java.util.Locale;

import javax.faces.context.ExternalContext;
import javax.faces.context.FacesContext;

import javax.servlet.http.HttpServletResponse;

import oracle.adf.model.binding.DCIteratorBinding;

import oracle.jbo.AttributeDef;
import oracle.jbo.AttributeHints;
import oracle.jbo.LocaleContext;
import oracle.jbo.Row;
import oracle.jbo.RowSetIterator;

import oracle.jbo.common.DefLocaleContext;

public class ExportDataClass {
public ExportDataClass() {
}

public void exportToCSV(DCIteratorBinding tableContent) throws IOException {

ExternalContext ectx = FacesContext.getCurrentInstance().getExternalContext();
HttpServletResponse response = (HttpServletResponse)ectx.getResponse();
Date now = Calendar.getInstance().getTime();
SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd-hhmmss");
String filename = formatter.format(now.getTime()) + "file.csv";
String label = "";
String strBuffer = "";

//define the encoding of the returned data. Critical for proper Greek Language exported data.
String contentType = "text/csv; charset=windows-1253";

try {
response.setContentType(contentType);
response.setHeader("Content-disposition", "attachment; filename=" + filename);

RowSetIterator rsi = tableContent.getRowSetIterator();
String[] attNames = rsi.getRowAtRangeIndex(0).getAttributeNames();
AttributeDef[] attr = tableContent.getAttributeDefs(attNames);
for(int i=0; i < attr.length; i++){
AttributeHints hints = attr[i].getUIHelper();
label = hints.getLabel(this.getLocaleContext());
strBuffer = strBuffer + label + ";";

}
strBuffer = strBuffer + "\n";

rsi.first();

for (int i = 0; i < rsi.getFetchedRowCount() ; i++)
{
Row currentRow = rsi.getRowAtRangeIndex(i);
Object[] attValues = currentRow.getAttributeValues();
for (int j = 0; j < attValues.length; j++)
{

strBuffer = strBuffer + attValues[j] + ";";
}
strBuffer = strBuffer + "\n";
}

Row[] ArrayRows = rsi.getNextRangeSet() ;

while (ArrayRows.length != 0){

for (int i = 0; i < ArrayRows.length; i++)
{
Row currentRow = ArrayRows[i];
Object[] attVals = currentRow.getAttributeValues( );
for (int j = 0; j < attVals.length; j++){
strBuffer = strBuffer + attVals[j] + ";";
}
strBuffer = strBuffer + "\n";
}

ArrayRows = rsi.getNextRangeSet() ;
}

PrintWriter out = response.getWriter();
response.setContentLength(strBuffer.length() + 1);
out.write(strBuffer);
out.flush();
out.close();
if (response.isCommitted() == false){
response.reset();
}
}
catch(IOException ex){
ex.printStackTrace();
throw ex;
}
finally {
FacesContext.getCurrentInstance().responseComplete();
}
}

private LocaleContext getLocaleContext(){
Locale locale = FacesContext.getCurrentInstance().getViewRoot().getLocale();
LocaleContext myLocale = new DefLocaleContext(locale);
return myLocale;
}

}

A typo can knock you out

noreply@blogger.com (Admin) — Tue, 11 Dec 2007 08:03:00 +0000

While trying to implement Note:270160.1 Single Sign-On Accessibility Through a Firewall
Oracle Application Server 10g (9.0.4), i realized that a simple typo can make your mood for the day.

To be more specific, in the part where the author of the Note writes :

Listen 8001
NameVirtualHost *:8001 # You may prefer IP Address instead of *
<VirtualHost *:8001> # You may prefer IP Address instead of *
ServerName portal.<yourname>.com
# Network entry-point/webcache:
Port 80
# IMPORTANT - Must inherit any Portal rewrites
RewriteEngine on
RewriteOptions inherit
# Other rewrites optional, but may improve functionality
# To send direct requests on this virtual host to Portal
RewriteCond %{HTTP_HOST} !^portal\.<yourname>\.com [NC]
RewriteRule ^/$ http://portal.<yourname>/pls/portal/$1 [L,R]
LogLevel error
ErrorLog "|/path/to/oracle/904mid/Apache/Apache/bin/rotatelogs /path/to/oracle/904mid/Apache/Apache/logs/portal_error_log 43200"
</VirtualHost>

Did you see it? Me either

RewriteRule ^/$ http://portal.<yourname>/pls/portal/$1 [L,R]

The [R] is after the [L] meaning that the redirection will not occur!

The line should be

RewriteRule ^/$ http://portal.<yourname>/pls/portal/$1 [R,L]

I simply lost a server with a k.o.

Resolution to the ORA-02262 error

noreply@blogger.com (Admin) — Thu, 10 May 2007 20:39:00 +0000

If you have ever faced this error message

ORA-02262: ORA-%05d occurs while type-checking column default value expression

while trying to alter the column definition of a table to NVARCHAR2 with Default Value just remember :

A 'string' literal is of type VARCHAR2
A N'string' literal is of type NVARCHAR2 !!

So now, you can correct your syntax and alter your table properly like this :

ALTER TABLE mytbl MODIFY mycolumn NVARCHAR2(3) DEFAULT N'A';

Oracle Application Server 10.1.3 Bug when submitting Greek characters the result returns question marks

noreply@blogger.com (Admin) — Thu, 10 May 2007 19:19:00 +0000

I remember back in the good old days of Oracle Application Server 10g R2 when i faced the same problem while developing in struts with jDeveloper 10g R2. It was indeed very difficult to finaly realize that a simple addition to orion-web.xml of the default-charset="windows-1253" would solve the problem so easily.

Now with the latest version of Oracle Application Server 10g R3 (10.1.3) i came across the same issue and i thought that i could easily solve the problem by simply doing the same thing.

I was wrong :-)

People, unfortunately i must warn you that this workaround doesn't apply to the R3 family. What you have to do now is to include

request.setCharacterEncoding("Windows-1253");

in all your JSP pages.

For those of you still developing in struts, i will show you a smarter way to automatically include this in all your pages without modifying any of these pages at all.

Have you ever "played" with the RequestProcessor ? The RequestProcessor has the ability to execute code before it goes to the Action Servlet. What you need to do here is extend the RequestProcessor so that it includes

request.setCharacterEncoding("Windows-1253");

in every call.

In jDeveloper 10g R3, create a new java class and write the following code :

package testgreek.view; //don't forget to change the name of the package to the
//one that you are using

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.RequestProcessor;

public class CustomRequestProcessor extends RequestProcessor {
protected boolean processPreprocess (HttpServletRequest request, HttpServletResponse response) {

try{
request.setCharacterEncoding("Windows-1253");
}
catch(Exception ex){
//.....
}

return true;
}

protected void processContent(HttpServletRequest request,
HttpServletResponse response) {
try{
request.setCharacterEncoding("Windows-1253");
}
catch(Exception ex){
//........
}
super.processContent(request, response);
}
}

Then, go to your struts-config.xml and add the following property to your controller :

That's it. Now every time you submit a page to the servlet, it includes the character encoding that you have selected.
That means no more ????

TIP: How to move a datafile from one hard disk to another (quick way)

noreply@blogger.com (Admin) — Sat, 24 Feb 2007 07:32:00 +0000

If you want a newer, fresher and quicker method to move a datafile from one disk to another here is the way to do it : Make sure the tablespaces are read-only and then

alter tablespace users offline;

copy c:\mydb\users01.dbf e:\mydb\users01.dbf

alter database rename file 'c:\mydb\users01.dbf' to 'e:\mydb\users01.dbf';

alter tablespace users online;

then make your tablespaces read write again,
and you're done :-)

More information : Note:115424.1 on Metalink

TIP: How to move a datafile from one hard disk to another (safe way)

noreply@blogger.com (Admin) — Thu, 22 Feb 2007 11:28:00 +0000

I had a test server with 60Gb Hard disk drive. Database was updated thoroughly and space run out quickly. What i did was to install a second hard disk drive and create there another folder to store my moved datafile.
So in order to move c:\mydb\users.dbf to e:\mydb\users.dbf here are the steps to do it safely :

open sql worksheet and connect to the database as sysdba with the sys account,

make sure that all users are out of the database and that no pending transactions are taking place, then run
shutdown immediate;

execute
start mount;

copy-paste the datafile you wish to move, to the new location,

execute
ALTER DATABASE RENAME FILE 'C:\MYDB\USERS.DBF' TO 'E:\MYDB\USERS.DBF';

then execute
ALTER DATABASE OPEN;

...and you're done.

Easy, isn't it? :)

Get the SSO user id in Oracle Forms and JSP - JSF

noreply@blogger.com (Admin) — Tue, 06 Feb 2007 09:50:00 +0000

If you use SSO authentication for your applications and you need to grab the SSO User from your applications here is how you do it:

Oracle Forms

GET_APPLICATION_PROPERTY(SSO_USERID);

jDeveloper project that uses JSP and java beans

request.getHeader("OSSO_SUBSCRIBER")

or

request.getRemoteUser();

jDeveloper that uses Java Server Faces (JSF)

In your AppModuleImpl.java you can get the SSO user by calling

this.getUserPrincipalName();

Can and Could be accessed

noreply@blogger.com (Admin) — Wed, 31 Jan 2007 17:15:00 +0000

Just another funny little spot of Oracle's help file.

This one was located on Oracle Application Server 10.1.3 help file while digging for some information on how to connect Oracle SSO 10g R2 with a 10.1.3 middle-tier installation.

Honey, you missed a spot

noreply@blogger.com (Admin) — Sun, 24 Dec 2006 03:10:00 +0000

Just a humourous mood here... something that catched my attention ;-)

The above, was retrieved from the help file of jDeveloper 10g R2. This error was corrected on jDeveloper 10.1.3 as you can see below:

A funny little break; now get back to work.

TIP: Use exp.exe to export specific rows from a table, with command-line SQL

noreply@blogger.com (Admin) — Sat, 09 Dec 2006 19:04:00 +0000

If you ever need to export specific records from your table to a .dmp file using exp.exe here is an example :

Suppose we have a table Orders and we want to export that table to a dmp file - not the whole table but today's orders.

from the prompt you write :

exp.exe 'sys/mypass@mydb as sysdba' file=c:\orders.dmp log=c:\orders.log tables=(ORDERS) QUERY=\" WHERE ORDER_DATE = '11/08/2006'\"

Remember :
query=\" \" or else you get an error !

INFO : Aggregator for Oracle jDeveloper

noreply@blogger.com (Admin) — Sat, 09 Dec 2006 14:14:00 +0000

There is an an aggregator for all Oracle JDeveloper related blogs. Visit http://thepeninsulasedge.com/adfblog/ . This blog has every recent post found on blogs about Oracle JDeveloper issues.
You can find a related link in the [Oracle Blogs of Interest] in this blog as well :-)

TIP: Upload a file using struts (includes jdeveloper 10g complete example)

noreply@blogger.com (Admin) — Wed, 06 Dec 2006 20:42:00 +0000

Struts simplify the file uploading process really easy.
You dont need third party stuff; just a few lines of code and a couple of tricks
will do the job :)

I have included a sample project for you to download and experiment with.

Key parts are :

1. Your form that will include the <html:file> tag, must be configured to handle multipart/form-data and to POST the data to the server, eg :

<html:form enctype="multipart/form-data" method="POST" action="upload.do">

2. The form bean that will handle the uploading, must have a property for the file been uploaded set like this :

<form-property name="filename" type="org.apache.struts.upload.FormFile">

After that, coding is really simple. I have included a complete project for uploads at the end of this post.
Here follows the java source part for the upload file process:


package fileuploader.view;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

import java.text.DecimalFormat;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.Action;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.DynaActionForm;
import org.apache.struts.upload.FormFile;

public class UploadAction extends Action
{
/**
* This is the main action called from the Struts framework.
* @param mapping The ActionMapping used to select this instance.
* @param form The optional ActionForm bean for this request.
* @param request The HTTP Request we are processing.
* @param response The HTTP Response we are processing.
* @throws javax.servlet.ServletException
* @throws java.io.IOException
* @return
*/

public ActionForward execute(ActionMapping mapping, ActionForm form, HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException, Exception
{
String toPage = "";
DynaActionForm df = null;
FormFile webFile = null;
FileOutputStream ServerFileStream = null;
File SaveToFile = null;
int FILE_SIZE_LIMIT = 5000000;
double filesize = 0 ;

try
{
//convert to a DynaActionForm
df = (DynaActionForm) form;
//class cast to a struts type FormFile filename
webFile = (FormFile) df.get("filename");

//get the size of the file that user wants to upload
filesize = webFile.getFileSize();

//i have included a file size limitation in this version
if (filesize > FILE_SIZE_LIMIT )
{
//format the size of the file to return two decimals
String pattern = "####.##";
DecimalFormat dblFormat = new DecimalFormat(pattern);
//file size returned to megabytes
String strFileSize = dblFormat.format((filesize / 1024) / 1024);

//redirect to the page that informs the user that there is a
//file size limitation
toPage = "filesizepage";
request.setAttribute("fsize", strFileSize);
return mapping.findForward(toPage);
}
//get the real path of the project
String strFilePath = getServlet().getServletContext().getRealPath("/");
//we want to upload to UploadedFiles directory
strFilePath = strFilePath + "UploadedFiles";

//initialize a file to write to
SaveToFile = new File(strFilePath, webFile.getFileName());


//in this version, if the file to upload already exists on the server
//i delete the server-side one and reupload it
if(SaveToFile.exists())
{
//delete the file
SaveToFile.delete();
}
//save the file to server
ServerFileStream = new FileOutputStream(SaveToFile);
ServerFileStream.write(webFile.getFileData());
ServerFileStream.flush();
ServerFileStream.close();
ServerFileStream = null;

toPage="success";
}
catch(Exception e)
{
//just print the trace
e.printStackTrace();
//and redirect to an error page
toPage = "error";
}
finally
{
//release resources
df = null;
webFile = null;
ServerFileStream = null;
SaveToFile = null;
}

return mapping.findForward(toPage);

}
}

Download the full jdeveloper sample project
(right-click and select "save target as")

just remember to rename it to .rar

TIP: Grant select on all user tables to another user

noreply@blogger.com (Admin) — Mon, 04 Dec 2006 16:47:00 +0000

This is one of the number one issues for a DBA. Well, the good news is that it has become really easy to do this.

My methodology is to create public synonyms for the user tables and grant access to the user tables underlying those synonyms.

Example:
User : Tom
Table : Products, PriceList
Purpose : Grant access to user Bob

Here is how to do it:

Create public synonyms for tables Products, PriceList
Normally User Tom sees them as Products and PriceList. Even if you grant access to those tables to user Bob, he still gonna access them as Tom.Products and Tom.PriceList
To allow user Bob to access those tables as Products and PriceList, you must create public synonyms. Public synonyms can be created under SYS so log in to your SQL Worksheet as SYS and type the following:

SET SERVEROUTPUT ON
begin
dbms_output.enable(1000000);
for x in ( select table_name from DBA_ALL_TABLES where owner='TOM' )
loop
dbms_output.put_line('CREATE OR REPLACE PUBLIC SYNONYM ' x.table_name ' FOR Tom.' x.table_name);
EXECUTE IMMEDIATE ('CREATE OR REPLACE PUBLIC SYNONYM ' x.table_name ' FOR Tom.' x.table_name);
end loop;
end;

You will find this piece of source code really useful when you will apply it in a production system with hundreds or thousands of tables.
What it does is apply for every table belonging to user Tom a public synonym which is the name of the table (but ommiting the Tom. part).

Now what you must do is grant access to user Bob on those tables

In the following piece of code i will grant select on a role. It's better to work with roles because they can be easily implemented to a user and then you can customize user rights independently

Here is the source :

SET SERVEROUTPUT ON
begin
dbms_output.enable(1000000);
for x in ( select table_name from DBA_ALL_TABLES where owner='Tom' )
loop
dbms_output.put_line('grant select on ' x.table_name ' to myrole');
execute immediate 'grant select on ' x.table_name ' to myrole';
end loop;
end;

After that, you will assign the role to your user(s) and that's it.

You have granted access to those tables to your second user with a few lines of code.

Info: Oracle DBA Toolbar (wishlist)

noreply@blogger.com (Admin) — Fri, 24 Nov 2006 08:02:00 +0000

Oracle released a tool for the DBA. It's a cool freebie where you can download here.
It features :
- Pop-up Blocker
- Integrated RSS Manager
- OTN Search
- One-click access to SQL*Plus install, Downloads, MetaLink, and more
- Online Support FAQ

It's available for Microsoft Windows Platforms and Internet Explorer.

The Oracle DBA Toolbar Wishlist:
I wish it had a few more buttons like :
- Oracle Enterprise Manager
- Performance Manager
- Top Sessions

Tip : recovering from problems after performing remote deployment from jDeveloper to Oracle Application Server

noreply@blogger.com (Admin) — Mon, 20 Nov 2006 18:55:00 +0000

Those of you who develop on Oracle jDeveloper 10.x.x and deploy on Oracle Application Server 10g might face the following situation :

During remote deployment, a power failure occurs or something else goes wrong and the deployment interrupts leaving you with an incomplete process and a bunch of files - other deployed and other not deployed.

If, God forbids, you come across to such a situation, you cannot even perform a backup/restore of your Oracle Application Server (using it's internal backup/restore facility) leaving you with an error message that says that you cannot proceed with backup while another process is performing file operations.

You cannot deploy your project again either. Oracle AS thinks that another deployment is actually taking place and cannot perform another task.

To overcome this situation perform the following steps:

1. go to your oracle directory where the bi (business intelligence) is located (eg : c:\oracle\bi)
2. find the folder dcm\bin (eg: c:\oracle\bi\dcm\bin )
3. execute the following command : dcmctl resetFileTransaction

The file operations will reset and you will be able to perform deployments or any other operation under your Oracle Application Server.

TIP : request.getRemoteAddr() and request.getRemoteHost() return the Server names, not the client's host name and ip (on Oracle Application Server)

noreply@blogger.com (Admin) — Sun, 12 Nov 2006 21:17:00 +0000

Well, this is a known issue since Oracle Application Server 9i. I have found a solution in Oracle Application Server 10g release 2. I know it applies to the 10g product family but i didn't test it on 9i. If anyone knows if this also applies on 9i, don't hesitate to leave a comment here :-)

Goto your Oracle Business Intelligence platform (not infrastructure) and click on Http Server-> Administration-> Advanced Server Properties-> httpd.conf

and uncomment the line :

UseWebCacheIp On

When webcache is forwarding requests to OHS, the clientIP becomes the IP of the originating webcache server and the real client IP is stored in the ClientIP header.
Instead of modifying the header with more java source code, just uncomment the UseWebCacheIp and you will have the Client's IP recorded in the log files and also your source code : request.getRemoteAddr() request.getRemoteHost() will work properly.

TIP : Collection of tips and tricks from Oracle Magazine (2003 - 2006)

noreply@blogger.com (Admin) — Tue, 08 Aug 2006 09:47:00 +0000

There is a huge amount of tips and tricks from many Oracle DBAs out there which can help DBA's everyday burden more easier to beary.
These were selected and posted by Oracle magazine for all of us to use.
You can view and save this valuable information at these links.

Year 2003
Year 2004
Year 2005
Year 2006

INFO : Oracle TimesTen 6.04

noreply@blogger.com (Admin) — Mon, 07 Aug 2006 06:39:00 +0000

Having an Indiana Jones mood for the weekend, i downloaded Oracle TimesTen 6.04 to take a look at what it has to offer to a developer and a DBA. Is this another database system? What it means by going in-memory?
Well the results might be surprising:
Oracle TimesTen can be used as a primary DBMS for storing data by applications that want to do it real time.
It can be used for storing data from multiple data sources playing a role of the central repository, combining multiple data sources together.
It can accelerate stuff by providing a memory-persistant data model.
Collaboration between in-memory tasks with disk-based RDBMS.
Downloaded it here.
Download the documentation here.

INFO : Oracle Application Server Security

noreply@blogger.com (Admin) — Fri, 04 Aug 2006 08:09:00 +0000

Security, Security, Security! This word is in everyone's mouth either a developer or a dba or a user; this word dominates the internet world.

Servers hosting internet applications need to be very well maintained and tested for intrusions and hacking attempts.

Oracle Application server 10g is a good solution for a corporate web server. It combines a powerful user interface through Oracle Enterprise Manager that allows admins to control the entire farm remotely with minimum effort.
If you already have such an installation in your servers, i recommend spending a little more time on security.

If you seek guidance on that matter, take a look at SPI Dynamic's : Securing Oracle Application Server
and NGSSoftware:Hackproofing Oracle Application Server where you can also find a very usefull list with all default Oracle UserIDs and Passwords!!

INFO : Oracle Database XE

noreply@blogger.com (Admin) — Wed, 02 Aug 2006 20:45:00 +0000

Those of you that build retail applications and seek for a database storage solution that is free to distribute, might want to take a look at Oracle Database 10g Release 2 (10.2.0.1)Express Edition for Microsoft Windows or Linux.

Now, hold your horses there because Oracle database XE comes with some limitations :

it can utilize only 1GB of RAM

it can utilize only one CPU

it can utilize only 4GB disk space

no more than one installations may exist in a computer

It is ment to be for the DBA who wishes to create some testcase for the production database, and for the developer who builts small scale applications that do not demand a tera of disk capacity to store data.

Its free to use and distribute.

Version available for
Linux fans
and
Windows fans.

TIP: ORA-01843 Not a valid month error

noreply@blogger.com (Admin) — Wed, 02 Aug 2006 20:45:00 +0000

Those of you developing applications that utilize Oracle Databases, might have face the ORA-01843 Not a valid month error. This is a major problem because most of us develop applications that use dates as part of their WHERE clause.

Solution : Before executing any SQL statement though code, you must execute the following SQL statement :

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY'

Note that date format may vary depending on your location. This directive for example is used in Greece.

TIP: Beware of the HOME

noreply@blogger.com (Admin) — Wed, 02 Aug 2006 20:45:00 +0000

I recently faced an issue regarding a Visual Basic application trying to connect to an Oracle Database 9i through an ODBC DSN. While everything seemed to work fine, applications could not connect to the Oracle Database and the error referred to some dlls that were missing from the installation.

After a little reseach i did, i found out that the default home (which was of Oracle Client 9i) has changed because of a recent installation of Oracle Discoverer Desktop 9.0.4

The default home changed and due to the incompatibility of the libraries the application couldn't connect to the database.

Solution : Go to Start->Programs-> Oracle Installation products-> Home Selector and switch back to your Oracle Client home. This will solve the problem and Oracle Discoverer will continue to function.

Oracle Critical Patch Update July 2006

noreply@blogger.com (Admin) — Wed, 02 Aug 2006 20:45:00 +0000

Oracle has sent an email regarding a new update for some of its products like Oracle Database 9i release 1, Oracle Application Server 9i and 10g, Oracle Database 10g etc.

Those of you who didn't receive this email should take action a.s.a.p. as this patch includes security updates and fixes as well.

Next date of Patch update would propably be October 17, 2006

More information here.

~~ Let there be blog

noreply@blogger.com (Admin) — Wed, 02 Aug 2006 18:45:00 +0000

Hello all! I welcome each an everyone of you in this small effort of sharing thoughts and experience about Oracle tools.

In the company i work for, i am a DBA in an Oracle 9i database.
I also do web development using Oracle jDeveloper 10.1.2 and i use Oracle Application Server 10g R2.

Feel free to post your thoughts, any tips or solutions to problems that may have arise during work. Share your experience for the benefit of all of us.