Posts Tagged ‘Oracle’

Oracle: Delete all rows from all tables etc…

Quick method to do multiple actions across multiple tables.

Simply spool a script with your commands in for each table – this way you can avoid dropping your tables or user and still keep all your schema structure.

So for instance to delete all data from all tables you could do the following:

set heading off;
set feedback off;
spool C:my_file_name.sql
select 'delete from ' || tname ||';' from tab where tabtype = 'TABLE';
spool off;

This will produce an SQL file with a series of delete commands for each table and then you can just run this script which will delete all rows in all tables. The || is the shortcut for the concatenation function in Oracle and this basically works the same as a select script using Dual.

Tab like user_tables refers to a system table that describes the users tables in the schema. Tab has only a few columns and I’ve used this because it has the type of table as a column (just in case). You can see this for yourself by doing:

select * from tab;

And if you do:

select * from user_tables;

You’ll see the difference, user_tables contains alot more data about each table.

You can change the SQL command to ‘truncate table’ or whatever else you want – even drop all tables by querying user_tables instead of tab:

set heading off;
set feedback off;
spool C:my_file_name.sql
select 'drop table ' || table_name || 'cascade constraints;' from user_tables;
spool off;

Using this method you can do the same action across multiple tables and save it for later use.

Oracle: Update From equivalent

update statement

Quick note on how to update data in a series of joined tables using a subquery in the UPDATE statement in Oracle, kind of like the UPDATE FROM in SqlServer.

First of all check out the above diagram, I’ve stolen it from this link: which also gives you more information on the UPDATE statement.

So what this says is that we can update on a table alias/subquery , which could contain our data set made from a  series of joins such as the code snippet below:

update (select
                                    schema1.table1 tb1,
                                    schema1.table2 tb2,
                                    schema1.table3 tb3,
                                    schema1.table4 tb4,
            schema2.table1 tb5
                           = AND
                                    tb2.prod_id = tb3.prod_id AND
                                    tb3.sku_id = tb4.sku_id AND
                                    tb4.sku_id NOT IN (SELECT sku_id FROM schema1.table6) AND
            tb5.ref_id = tb4.sku_id AND
                                    tb1.country_iso_code = 'GB') mySubQuery
set mySubQuery.stock_level = 0

[ad#Google Ad in content]
Basically the same layout as doing subqueries with a SELECT statement, however, there are a few things to note. First of all if you use a SELECT DISTINCT in your subquery you’ll get an Oracle error ORA-01732 mentioning something about data manipulation not being legal for your table view. Next you may find that you get another error  ORA-01779 which is something about modifying a column which maps to a non key-preserved table.

While the above syntax is legal and you can get around the latter error, providing your constraints and foreign keys are declared on your database correctly, a better way to achieve updating records in a joined subquery is to use the WHERE IN clause on your UPDATE statement as below:

UPDATE schema2.table1
SET    schema2.table1.stock_level = 0
WHERE  schema2.table1.ref_id IN (select
                                    schema1.table1 tb1,
                                    schema1.table2 tb2,
                                    schema1.table3 tb3,
                                    schema1.table4 tb4,
            schema2.table1 tb5
                           = AND
                                    tb2.prod_id = tb3.prod_id AND
                                    tb3.sku_id = tb4.sku_id AND
                                    tb4.sku_id NOT IN (SELECT sku_id FROM schema1.table6) AND
            tb5.ref_id = tb4.sku_id AND
                                    tb1.country_iso_code = 'GB')

Seems just as quick and works (0.14 seconds for 2000 updates out of a 50000+ across separate schemas). Also handy if you dont have access to fix the database constraints etc… (Or if you don’t know how).

Changing the HTTP port Oracle uses

In a production environment your database server will be completely separate from your application server or at least it should be. So in theory you should never really need to change this setting unless its for your development environment.

In my case I needed to run both JBoss and Oracle on the same PC in order to test my environment. Why would I need to change the HTTP port of Oracle? Well Oracle has an HTML set of admin screens it uses as an interface to let users do DBA stuff rather than doing it from a prompt screen, on Oracle XE this is called Apex and for Oracle Enterprise, I think it’s just called Enterprise Manager or some such shit. Two different GUI’s but both  use and reserve the HTTP port on your computer. Since Oracle starts before JBoss, Apache then can’t use this port to talk to JBoss.

The easiest way around this is to set the HTTP port that Oracle uses and it’s really really simple. You’ll need to have the SYSDBA priviledges for this to work, so I’ll assume as much.

Start SQLPlus and login to your server connecting as SYSDBA. Normally by default the connection will be something like: connect SYS as SYSDBA@XE etc… where XE is your service identifier – for Enterprise it’s what ever you called it on install.

Once you’ve done this then just run this command: exec dbms_xdb.sethttpport(9090);

This sets my Oracle HTTP port to 9090, something JBoss shouldn’t be using. Now I can have everything running without conflict. Apache can now see JBoss and I can still get to Oracles admin screens.

Setting Database Connections in JBoss/ ATG


ATG communicates to a database via JBoss via a dynamo server setup or in your home/localconfig directory if you’re not using specific dynamo servers. In this setup you specify the JNDI connection name which will then refer to an XML file which makes up part of your JBoss server. So to summarise ATG connects via its own JNDI config file which maps to an XML file on the JBoss server. JBoss then handles the connection out to the data source via a JDBC call – this can be any type of database or repository potentially.

Here’s how to set the database connections for something like Oracle. For MySQL etc… the procedure is pretty much the same and you can find example XML configs for these in your JBoss install the only real difference is the driver used for the connection.

First start with JBoss and setup the XML. Go to your servers deploy directory: \jboss\server\MY_SERVER_NAME\deploy. If you haven’t setup a server then this will be called default – if you’ve installed ATG then there will be a server called ATG. In your deploy directory there will be XML files normally with the mention of ‘ds’ in the file name, to let us know its for a data Source. If you go to:  \jboss\docs\examples\jca you should find example XML connection files here.

Alternatively using the below code we can create one for Oracle called something like ATG-Oracle-DS.xml.

In this XML file we basically set the JNDI reference name, the JDBC connection URL which is the database server IP/name along with the port and the SID (Service Identifier). We then set the schema that we want to connect to and it’s password. It’s the same for any database connection, although you’ll also see that we set the driver to use for the connection (more on this after the below XML)…







OK so the XML is pretty simple – you can have as many connections in one XML file as you need but sometimes it’s easier to keep them in separate files to identify connections. You can also MIX connections, so I can have a schema on Oracle, another on MsSQL and a third on MySQL and ATG can read and use data from all of these as part of its data anywhere architecture.

Here’s the gotcha – you MUST make sure the driver/ libary jar file is installed for your server to connect to your database. So go to your server’s lib directory e.g. \jboss\server\MY_SERVER_NAME\lib and for Oracle you will need a jar called ojdbc14.jar which contains the necessary classes for JBoss to connect to your database – generally JBoss does come with most database library jars but you may need to hunt this one down on Oracles website.

So We’ve set an XML file that specifies a connection for JBoss to use, we’ve added the necessary libary files to our JBoss server so it can make the connection. Finally we need ATG to be configured to use this connection, this is the really easy part!

In ATG the sources are referenced via the Dynamo servers localconfig, so for example in: \ATG\ATG2007.1\home\servers\MY_SERVER_NAME\localconfig\atg\dynamo\service\jdbc or if you’re not using Dynamo servers then just look in home\localconfig\atg\dynamo\service\jdbc.

If you’re building an external EAR file for your deployment then include this  in your Dynamo servers that you export with your EAR file. Anyway in this config directory you need to make a .properties file to set the JNDI connection to use. Just add the below 2 lines – the JNDIName variable should reference the JNDI name in your XML file.


And thats it! You should be able to map any database for use with ATG replacing the Solid database that comes with ATG.

Now a word of warning – it’s fine using additional databases for ATG but if you want to completely replace the Solid database, and in a production environment this is a must, you will need to load in the tables to your database for ATG to use, but I’ll write this up in a separate post – It’s pretty easy as ATG ships with various SQL setup scripts for you to use to achieve this.

Joining columns/ concatenate strings in Oracle PLSQL/ MySQL

Joining columns and results together is really easy in Oracle or MySQL, in both there is the same function – CONCAT().

However you should be aware that there are differences between the databases on how this function works. In MySQL the CONCAT() function works by joining any number of items specified in the function – I don’t think that there is a limit? But with Oracle it seems that you can only join two strings at a time using this function.

However Oracle offers an alternative for concatenation which also allows us to specify additional characters between the strings that we wish to join. To do this we use ||”||, so to add a comma we do column1||’,’||column2||’,’||column3.

I believe that in MsSQL the concatenation function just uses a plus sign: column1 + column2 + string1 etc…

Generally we use these functions as part of our SELECT statement so for instance if I have a table that stores First Name and Last Name as two separate columns and then I need an export which needs them combined into one field, I can do:

SELECT CONCAT(first_name, last_name) As "Name" FROM [TABLE] WHERE ...

To do multiple CONCAT() in Oracle you could probably do something retarded like:

SELECT CONCAT(CONCAT(first_name, ','), last_name) As "Name" FROM [TABLE] WHERE ...

But it’s just easier to do:

SELECT 'Mr'||first_name||','||last_name As "Name" FROM [TABLE] WHERE ...

In MySQL we use the same CONCAT() function which is just the same, except I’m not limited by the number of columns/ strings I can join:

SELECT CONCAT(first_name, last_name) As "Name" FROM [TABLE] WHERE ...

And if we want to add in strings or commas in MySQL we then do:

SELECT CONCAT('Mr', first_name, ',', last_name, 'some text') As "Name" FROM [TABLE] WHERE ...

Typically I find the concatenation function is really useful in Oracle when I want to export a CSV file with out quotes around each field, I just concatenate all columns together – it also means that any quotes in the field records aren’t lost either like in my example here:

Spool a CSV file from Oracle/ SQL*Plus without quotes

Writing basic case switch statements in Oracle PL/SQL

Oracle CASE

This is the equivalent code for writing a switch statement in Oracle. In Oracle to do the switch we use a function called case. Which looks for a value in the field specified, when it returns true it returns a value else returns a default. This has the same logic as writing an if statement pretty much, e.g. IF (value == “bob”) { do stuff } ELSE….

This is very very handy when having to do some reports and for instance the categories in your database don’t match up to what the report needs or you need combine a few columns together based on their values.

When writing a CASE typically it should be a part of your SELECT columns statement as below. We start it by declaring a CASE, and then a WHEN clause, so when value = xxx THEN do this…

  WHEN table.column_name = 'me' THEN 'yes'
  WHEN table.column_name = 'you' THEN 'no'
  ELSE 'neither'
END AS "Me or You"

So when the value in our column equals ‘me’ then the value output in the results will be ‘yes’. We use the ELSE line to return a default if no WHEN clause is matched. We then end our CASE with END and then we can assign a name to this column using the AS command. Otherwise your column will be named as the entire CASE function which isn’t really that handy.

You can add multiple clauses just like a basic WHERE query on your WHEN clause for example:

  WHEN tableA.column_name = 'me' AND tableB.column_name = 'myself' THEN 'yes'

This is pretty handy when you want to base a case on multiple tables or columns and of course you can use OR, IS NOT etc… in the WHEN clause. For the THEN part you can also return another column instead of an arbitary value. So:

  WHEN tableA.column_name = 'me' AND tableB.column_name = 'myself' THEN tableC.column_some_name

And finally instead of just referring to a pre-defined column name from our tables we can write a SQL SELECT statement for the WHEN clause which is very handy when we want to switch on a record count for instance:

  WHEN (SELECT COUNT(tableD.column_name) FROM tableD WHERE column_name = 'me') > 0 THEN 'Yes'
  ELSE 'No'
END AS "me"

Spool a CSV file from Oracle/ SQL*Plus without quotes

oracle spool csv

Quite useful, I had the case where I needed to provide a CSV file to an external party from our Oracle 10g database. No problem, there are loads of CSV export tools out there. But it then transpired that the CSV’s that I was generating were using quote (“) marks to identify fields e.g. “field1″,”field2” and so on…

When I ran the script in something like SQL Developer it would spool the file and also spool the query as well into the file no matter what I specified. Along with this it also wouldn’t recognise usage of variables in SQL Developer. Basically what I’m saying here is write the query in something like SQL Developer by all means but for writting and debugging a CSV spooler etc… just save the hassle and go straight to SQL*Plus.

First of all lets start with how to spool the CSV file without leading line breaks, no quote marks etc… the below is the SQL script to run, we’ll need to save this in order to run it later.

[ad#Google Ad in content]

set echo off
set verify off
set termout on
set heading off
set pages 50000
set feedback off
set newpage none
set linesize 160
spool /path/save/file/myFile.csv
SELECT 'Field1Name,Field2Name,Field3Name,Field4Name,Field5Name' from dual;
spool off;

We first set any settings we need at the start for the script these are as follows:

set echo off [Won’t display any SQL commands that are run]
set verify off [If turned on, it prints out each defined variable twice]
set termout on [Supresses the output of SQL commands but not the commands themselves]
set heading off [Sets whether column headings are outputted or not]
set pages 50000 [50000 lines without a page break, basically sets the height of the page]
set feedback off [If on it will feedback to user e.g. table created, row deleted etc..]
set newpage none [Removes any leading/ blank lines at the start of a page]
set linesize 200 [Sets the width of a line]

[ad#Google Ad in content]

We then start to build our file by calling the spool function with the directory of where to save the file and the file name. To get our headings or column names we then do a simple select statement on dual where each item will be what we want the column to be named – basically to print out the headings to the spool file. We could of course just set heading to ‘on’ and get the column names, but then column names are not always that useful.

We then run our query as normal, the only thing to note is the use of ||’,’|| which instead of double quotes it will still get used as the field separator, you can use ; or tabs for instance instead. The || || is actually used in Oracle for concatenation of fields to allow 2 strings to be used with an additional element imbetween – it also saves us writing CONCAT() for every 2 strings/ columns. If you do not specify this parameter after each column you select then the default double quotes will be used around your fields. We then turn spool off and then exit so when our .sql file is run it will stop and break the connection.

That’s our SQL script, we want to run this script in command prompt because we can launch SQL*Plus with various parameters and switches, the one we need here is silent,  -s, which tells SQL*Plus not to show the dialogue and thus not to spool it.

The command to run your script will then be something like:

sqlplus -s {schema_name}/{schema_pwd}@{service_name} @test.sql

That lauches SQL*Plus in silent mode, connects to your schema and then runs the .sql script spooling your .csv file and finally exiting SQL*Plus.

Quick note, if you want to append your filename with todays date for instance then you can do something like the below where we assign the date to a variable, &mydate and set it with the value from your databases sysdate.

set linesize 160
column dcol new_value mydate noprint
select to_char(sysdate,'YYYYMMDD') dcol from dual;
spool /path/to/save/file/&mydate.myFile.csv
SELECT 'Field1Name,Field2Name,Field3Name,Field4Name,Field5Name' from dual;

LIMIT equivalent for Oracle

So you use MySQL etc… and you’re used to adding a LIMIT clause to your query for example,

SELECT * FROM table WHERE name = ‘bob’ LIMIT 1

And now you’re using Oracle and want to do the same thing? But it throws some dread ORA error – why?

Well because Oracle doesn’t have LIMIT, thats why!

So instead we have ROWNUM and using this instead we can do pretty much the same thing by adding an AND to the where clause as thus:

SELECT * FROM table WHERE name = ‘bob’ AND rownum = 1

This also means we can do < 10, > 10 etc…

And thats what I learnt in Oracle land today.