Regular Expressions in Oracle SQL

Posted on September 26, 2014 by Frank Kim

For a project I needed to change the picture URLâ€™s of all users. Fortunately Oracle SQL supports regular expressions making this problem much easier to solve.

SQL to select pictures to change:

SELECT picture FROM users
WHERE REGEXP_LIKE(picture, '^graph\.facebook\.com.*?type=square');

SQL to update pictures from type=square to type=large:

UPDATE users SET picture =
REGEXP_REPLACE(picture,
               '^(graph\.facebook\.com.*?type=)square$', '\1large')
WHERE REGEXP_LIKE(picture, '^graph\.facebook\.com.*?type=square$');

SQL to select updated pictures:

SELECT user_id, picture FROM users
WHERE REGEXP_LIKE(picture, '^graph\.facebook\.com.*?type=large$');

For more information please read Using Regular Expressions in Oracle Database.

ATG Ordered One-to-Many Relationships

Posted on April 8, 2013 by Frank Kim

Former NY Knicks guard Jeremy Lin wins debut with Houston Rockets as James Harden explodes for 37 points

Last week I blogged about ATG unordered one-to-many relationships. Ordered one-to-many is quite similar but a little more complex.

Here again is the same example of players belonging to a team except now the players are a list property of the team.

[code language=”xml”]<item -descriptor name="team">
<table name="team" type="primary" id-column-names="id">
<property name="name" required="true"></property>
</table>
<table name="player" type="multi" multi-column-name=â€idxâ€ id-column-names="team_id">
<property name="players" column-name="id" data-type="list" component-item-type="player"></property>
</table>
</item>

<item -descriptor name="player">
<table name="player" type="primary" id-column-names="id">
<property name="name" required="true"></property>
<property name="team" column-name="team_id" item-type="team"></property>
</table>
</item>[/code]

And here is the SQL.

[code language=”sql”]CREATE TABLE team (
id VARCHAR2(40) NOT NULL,
name VARCHAR2(3) NOT NULL,
CONSTRAINT team_p PRIMARY KEY (id)
);

CREATE TABLE player (
id VARCHAR2(40) NOT NULL,
idx NUMBER(2,0),<p> name VARCHAR2(40),
team_id VARCHAR2(40),
CONSTRAINT player_p PRIMARY KEY (id)
);[/code]

Now comes the tricky part. You can never just set the team in the player repository item.

[code language=”java”]player.setPropertyValue("team", team);[/code]

This is because when you do this the idx column in the player table is not set. We could create a repository property and try to set it separately but that is bad practice. You would have to know how many players are already on the team and then set the idx to that number.

What you should do is not allow the team property to be written.

[code language=”xml”]<property name="team" column-name="team_id" item-type="team" writable="false" />;[/code]

And instead add the player to the team repository item.

[code language=”java”]Set<RepositoryItem> players = (Set<RepositoryItem>) team.getPropertyValue("players");
players.add(player);[/code]

Because of this requirement you cannot make the team property required for the player as discussed before.

Again if you have any questions please leave them in the comments.

ATG Unordered One-to-Many Relationships

Posted on April 4, 2013 by Frank Kim

Photo by james_in_to

Previously I blogged about specifying one-to-many relationships in ATG. Turns out I could have done it much more simply.

Using the same example of players belonging to a team here are the simplified SQL repository definitions.
[code language=”xml”]<item-descriptor name="team">
<table name="team" type="primary" id-column-names="id">
<property name="name" required="true" />
</table>
<table name="player" type="multi" id-column-names="team_id">
<property name="players" column-name="id" data-type="set" component-item-type="player" />
</table>
</item-descriptor>

<item-descriptor name="player">
<table name="player" type="primary" id-column-names="id">
<property name="name" required="true" />
<property name="team" column-name="team_id" item-type="team" />
</table>
</item-descriptor>[/code]
And here is the SQL.
[code language=”sql”]CREATE TABLE team (
id VARCHAR2(40) NOT NULL,
name VARCHAR2(3) NOT NULL,
CONSTRAINT team_p PRIMARY KEY (id)
);

CREATE TABLE player (
id VARCHAR2(40) NOT NULL,
name VARCHAR2(40) NOT NULL,
team_id VARCHAR2(40),
CONSTRAINT player_p PRIMARY KEY (id)
);[/code]
You can set the team in the player repository item.
[code language=”java”]player.setPropertyValue("team", team);[/code]
Or add the player to the team repository item.
[code language=”java”]Set<RepositoryItem> players = (Set<RepositoryItem>) team.getPropertyValue("players");
players.add(player);[/code]
What gets tricky is if you require a player to be on a team.
[code language=”xml”]<property name="team" column-name="team_id" item-type="team" required="true" />[/code]
[code language=”sql”]team_id VARCHAR2(40) NOT NULL,[/code]
In this case you cannot remove a player from a team by doing this:
[code language=”java”]Set<RepositoryItem> players = (Set<RepositoryItem>) team.getPropertyValue("players");
players.remove(player);[/code]
This is because when you remove the player from the team the player will no longer have a team. This violates both the repository definition and will result in a SQL error because team_id cannot be null.

The only way you can change a player’s team is to do this.
[code language=”java”]player.setPropertyValue("team", newTeam);[/code]
To help enforce this you can make the team’s players property unwritable.
[code language=”xml”]<property name="players" column-name="id" data-type="set" component-item-type ="player" writable="false" />[/code]
I hope this makes sense. If you have any questions please leave them in the comments.

Fixing Existing Repository ID Errors

Posted on June 11, 2012 by Frank Kim

I was feeling part of the scenery. I walked right out of the machinery. by Neal.

The other day I was trying to create a page repository item using the BCC when I got an error saying it could not create the item. Looking in the logs I saw this exception.

16:13:59,909 ERROR [RepositoryAssetService]
atg.repository.RepositoryException: createItem() was called with an existing ID : '3100007' for type 'page'. Use getItemForUpdate() instead.
        at atg.adapter.version.VersionRepository.createItem(VersionRepository.java:1903)
        at atg.adapter.version.VersionRepository.createItem(VersionRepository.java:1125)
        at atg.remote.assetmanager.editor.service.RepositoryAssetServiceImpl.doCreateAsset(RepositoryAssetServiceImpl.java:476)
        at atg.remote.assetmanager.editor.service.AssetServiceImpl.createAsset(AssetServiceImpl.java:331)
        at atg.remote.assetmanager.editor.service.AssetEditorService.createAssetAndLink(AssetEditorService.java:555)
        at atg.remote.assetmanager.editor.service.AssetEditorService.createAsset(AssetEditorService.java:508)

The problem was the ID generator was producing IDâ€™s that already existed in the database. This sometimes happens when things are imported into the BCC.

To fix this problem do the following.

Look for the row in the das_id_generator table which has the item descriptor that is causing the error.
Change the seed value (typically upwards) or put a prefix or suffix on it.
Restart the server.

In this case I ended up running this SQL to fix the existing ID error:

UPDATE DAS_ID_GENERATOR SET PREFIX = 'pg_' WHERE id_space_name = â€˜pageâ€™;

SQL Delete in One Table Based on Values in Another Table

Posted on March 14, 2011 by Frank Kim

Growing by Simon Peckham

Delete From One Table Whose Values Donâ€™t Appear in Another Table

Sometimes you will find that you have items in a table whose values reference items in another table that no longer exist. For example in ATG you may have orders that reference profiles that no longer exist. This could happen if an order is for an anonymous profile that was deleted.

Here is an example of how to delete items in a table whose values reference items in another table that no longer exist, in this case ATG orders whose profiles no longer exist.

DELETE FROM dcspp_order
WHERE profile_id
NOT IN
(
  SELECT id
  FROM dps_user
);

This example does not actually work because of the dependencies on the dcspp_order table so please donâ€™t try it. Smile

Delete From One Table Based on Values in Another Table

Sometimes you want to delete items in one tables based on values in another table. You can do this similarly to the previous case.

DELETE FROM dcspp_order
WHERE profile_id
IN
(
  SELECT id
  FROM dps_user
  WHERE id LIKE '6%'
);

This example also does not actually work because of the dependencies on the dcspp_order table so please donâ€™t try it. Smile

For further reading please see How to delete records from a SQL Server database and SQL Delete Rows Based on Another Table.

Specifying One-to-Many Relationship in ATG Repositories

Posted on January 21, 2010 by Frank Kim

(Photo: Monta driving by Yogma)

Specifying one-to-many relationships is ridiculously easy in Ruby on Rails. Unfortunately itâ€™s not so straight-forward in ATG repositories.

First you specify the â€œbelongs toâ€ relationship. In this example the player belongs to a team.

[xml]<item-descriptor name="player">
<table name="team_players" type="auxiliary" id-column-names="team_id" shared-table-sequence="1">
<property name="team" column-name="team_id" item-type="team" />
</table>
</item-descriptor>[/xml]

Then you specify the â€œhas manyâ€ relationship. In this example the team has many players.

[xml]<item-descriptor name="team">
<table name="team_players" type="multi" id-column-names="player_id" shared-table-sequence="2">
<property name="players" column-name="player_id" data-type="set" component-item-type="player" />
</table>
</item-descriptor>[/xml]

Note the trick is specifying the â€œshared-table-sequence.â€

Here is the SQL for the table that specifies this relationship in our example.

[sql]CREATE TABLE team_players
(
team_id VARCHAR2(40) NOT NULL,
player_id VARCHAR2(40) NOT NULL,
CONSTRAINT team_players_pk PRIMARY KEY (team_id, player_id),
CONSTRAINT team_players_players_fk foreign key (player_id) references players (id),
CONSTRAINT team_players_team_fk foreign key (team_id) references teams (id)
);[/sql]

SQL Insert in One Table Based on Values in Another Table

Posted on January 18, 2010 by Frank Kim

(Photo: Love’s Old Sweet Song by linda yvonne)

The syntax for doing this is similar to doing an update in one table based on values in another table yet simpler.

INSERT INTO suppliers (name)
SELECT customers.name
  FROM customers
  WHERE customers.id = suppliers.id);

If you want to add constant values into the insert you can do something like this.

INSERT INTO suppliers (name, city)
SELECT customers.name, 'Toronto'
  FROM customers
  WHERE customers.id = suppliers.id);

For further reading please see SQL INSERT INTO.

How to Alter Table

Posted on October 6, 2009 by Frank Kim

(Photo: Rain on a window in Sunnyvale by basictheory)

There are various ways to alter a table and I usually forget what they are so I am writing this post to remind me. 🙂

Columns

ALTER TABLE foo DROP COLUMN nickname;
ALTER TABLE foo RENAME COLUMN name TO nickname;
ALTER TABLE foo ADD name VARCHAR2(254) DEFAULT 'Frank' NOT NULL;
ALTER TABLE foo ADD age INTEGER DEFAULT 0 NOT NULL;
ALTER TABLE foo MODIFY name VARCHAR2(500);

Constraints

ALTER TABLE foo DROP CONSTRAINT foo_a_f;
ALTER TABLE foo ADD CONSTRAINT foo_b_f FOREIGN KEY (bar_id) REFERENCES bar (id);

If you forget the name of a constraint and you can try to find it using this handy piece of SQL.

SELECT constraint_name FROM user_constraints WHERE constraint_name LIKE 'foo_%_f%';

SQL*Plus Commit on Exit

Posted on August 25, 2009 by Frank Kim

I was always doing a commit before exiting SQL*Plus when it occurred to me today that maybe I didnâ€™t need to do that. Doing a Google search quickly answered that for me.

If you issue a graceful exit (via the â€œexitâ€ or â€œquitâ€ command), sqlplus will always issue a commit. However, if you were to be ungracefully disconnected, for example by closing your terminal window, then PMON will issue a rollback like it does with any other disconnected session.

sqlplus commit-on-exit?

Therefore there is no need to do a commit before you exit.

You can also set autocommit on, it is off by default, but I would not recommend doing this.

During interactive usage with sqlplus, Oracle also supports an AUTOCOMMIT option. With this option set to ON each individual SQL statement is treated as a transaction an will be automatically commited right after it is executed. A user can change the AUTOCOMMIT option by typing
 SET AUTOCOMMIT ON
or
 SET AUTOCOMMIT OFF
whereas by typing
 SHOW ALL
a user can see the current setting for the option (including other ones).

Oracle SQL Transactions

Describing Tables and Constraints in Oracle

Posted on November 21, 2006 by Frank Kim

Stone Table by WorldWanderlust

To get the table definition.

describe foo;

Or simply

desc foo

To get the constraints of a table.

select * from user_constraints where table_name='foo';

Similarly to find the table for a constraint.

select table_name from user_constraints where constraint_name='bar';

To find all the tables that have foreign key constraints for a given table you can use this query. It first find the name of the unique and/or primary key constraints in the given table. Then it finds all the constraints in other tables that reference this constraint. Note that the table names are usually uppercase.

select owner,constraint_name,constraint_type,table_name,r_owner,
r_constraint_name
from all_constraints
where constraint_type='R'
and r_constraint_name
in (select constraint_name
from all_constraints
where constraint_type in ('P','U')
and table_name='BAZ');

A simpler way which also constrains the owner would be like this.

select table_name,constraint_name
from all_constraints
where constraint_type='R'
and owner='FRANK'
and r_constraint_name
in (select constraint_name
from all_constraints
where constraint_type in ('P','U')
and table_name='BAZ');

For further reading please see Finding Foreign Key Constraints in Oracle and Oracle Constraints.

betweenGo

Tag Archives: sql