Easytech blog

When developing multi-organization web applications it is of utmost importance that data from one organization is not visible to the other. There are several software aproaches for this. Usually this can be solved by having an organization ID and whenever a query is made a ‘where’ clause is added to filter by organization. Although this approach works fine it is up to developers to always filter by org_id but its certainly error prone.

To solve this problem there are other approaches. One is that each organization gets a copy of the database and a single database user to log to this database. When the user logs in the application uses the URL to determine the organization (say: http://organization.domain.com/login). With the organization name extracted from the URL, the aplication determines which database instance, database user and password to use for the application to use. This obviously solves the problem since, by definition, there is no way data from the two organizations can mix: they live in different databases alltogether.

(more…)

Sometimes I need to use data from a csv (comma separated values) in an oracle procedure. The best way to do that is inserting the data into a db table using sqlloader and then reading it from the PL/SQL.

In this post I will show you how to use sql loader to load a csv file exported from excel into an oracle table.

Suppose that you got a XLS spreadsheet with contact data: first name, last name, security number, phone number.

First, create a table where the file will be loaded:

In my xls (people.xls) I have the following info:

Save the data as a comma delimited file.

File -> Save As
Change File name to people_csv
Select ‘Text CSV ’ in the File type drop down box.

If you open the people_csv.csv file in a text editor (vi in my case), you will see it in a comma separated format. Two commas will denote fields without a value, these will be loaded as nulls into the Oracle table.

CSV file:

Create the following sql loader control file (people_ctrl.txt)

at last  run the sql loader to put the info into the db table.

Now I can use the data from sql with a simple query:

In the Oracle world, a lot of the code of an application is stored in procedures inside the database. This gives the best possible performance since the data never leaves the database avoiding data to be sent to and from the client. Code in stored procedures in Oracle is written using the PL/SQL language.

On the other hand, writing web applications is best done in scripting languages like PHP or Python. In addition, it is always recommended to use some type of framework. In PHP you can use CakePHP, Symfony, Codeigniter or, the one we are currently using: Zend Framework.

Calling PL/SQL code from PHP can be tricky sometimes, specially when the PL/SQL procedure has input and output parameters. In this posting I will show you how to call a procedure from the PHP using Zend Framework. I will assume you have some experience using Zend Framework, specially the Database module (Zend_db).

Lets start by creating in the database a simple PL/SQL procedure. Log into sqlplus or sqldeveloper and create the following package and package body:

This code will create a procedure say_hello which receives two parameters: as input, name and an output variable, greeting. The procedure will write in the out parameter ‘Greeting, XXXXX‘ where XXXXX will be replaced by the name input parameter.

Now lets see how we can call the PL/SQL code from PHP. Take a look at the following code:

To execute the code you need a database connection called $db. When you run this PHP code you will get:

  array(2) {
    ["p1"]=>
      string(5) "Cesar"
    ["p2"]=>
      &string(12) "Hello, Cesar"
  }

It is important to note a couple of things:

1. In Zend Framework you must use named parameters since positional parameters are not supported when using the Oracle adapter.
2. You must reserve space for our output. The best way is to create a string with enough blank spaces for the longest possible answer. You will get the following message if its too short: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
3. When you add output variable you must do so by reference (notice the ‘&’ sign in the $params array).

Hope this helps someone!

Many times you will need to quickly executa a query in PostgreSQL and dump
the results to comma delimited value (CSV) file. In this post I’ll show you
a quick way to do it with the psql command in PostgreSQL.
Just copy the following to a file called dump2csv.sql

To dump the result of the query to a file do:

$ psql -h -U -d -W -f dump2csv.sql

the result of your query will be stored in /tmp/result.csv.

The first time that i had to find the exact Oracle Application Server Version I thought it would be an easy task but, it is not so simple if you don’t know where to look for it. I forgot that it was not straightforward because now I know where to find it. But the other day a friend called me and asked me where to find OAS version. That’s why i’m writing this post.

If you got a fresh install you can look into <ORACLE_HOME>/install/readme.txt and find the version.

But i prefer to take a look into <ORACLE_HOME>/config/ias.properties
For example:

$ grep Version $ORALCE_HOME/config/ias.properties
Version=10.1.2.0.2

In AIX 5L Version 5.3 when you start the iasconsole and then you access to Oracle Application Server console using a web browser, then the application hangs and the following error message is displayed in the web browser:

500 Internal Server Error

java.lang.NoClassDefFoundError: oracle/sysman/eml/app/Console
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java(Compiled Code))
at com.evermind[Oracle Application Server Containers for J2EE 10g ...

…

…

at java.lang.Thread.run(Thread.java:568)


This error occurs because the EMCTL IAS console hangs after logging in. Check the emdctl.trc file to verify the issue.

The next steps describe how to rectify this issue.

(more…)

RMan is Oracles de-facto backup and recovery application. It is used to backup the database files and archive logs. Sometimes an archive log might be mistakenly removed, moved or renamed from disk without following RMan’s backup retention policy. Since it is in the rman catalog it will try to make a backup of the missing archive log. This will cause rman to fail when it tries to make a backup of the archive log with the following error message:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at <date>
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file <archive_log>
ORA-27037: unable to obtain file status

(more…)

This post just describe the shortest way to verify Oracle Applications 11i Version. You can obtain this information typing the following URL in your browser.

http://<host>:<port>/OA_HTML/OAInfo.jsp

host: Where Oracle apps mid tier is installed
port: apps mid apache listener port (default 8000)

The URL show :

• OA Framework Version
• MDS Version
• UIX Version
• BC4J Version

One of our latest projects here in Easytech was done using PHP as our programming language and Oracle as our Database. We had a lot of experience coding in PHP and developing with the Oracle Database but none using both together. The project turned out to be a great one but there isn’t a lot of information on the web since most PHP applications use MySQL or PostgreSQL as their database. Today I was looking at what blogs where available on the Oracle site and came across Christopher Cross’s blog which has a lot of interesting information for PHP developers. In it I found links to two interesting presentations: “Leveraging the Power of Oracle with PHP: Taking Advantage of the Database” and ” PHP and Performance” and a link to a book called “The Underground PHP and Oracle Manual” written by Christopher himself and Alison Holloway which covers PHP and Oracle development. For more references on using PHP and Oracle visit the PHP section in the Oracle Technology Network.