Tip: Reset Oracle Sequence

Yesterday I was working on an ODI interface, a pretty easy one, which was supposed to insert new rows  into a table using a <SEQUENCE>.NEXTVAL for populating the primary key.
The primary key was set NUMBER(4). The last value inserted had 17 as primary key. I was expecting new items to start from 18, but I was constantly getting: 

SQL Error: ORA-12899: value too large for column

After spending  a considerable amount of time for finding out which column was making my ODI interface fail, I have executed the following: 

SELECT MY_SEQ.NEXTVAL

FROM DUAL; 

which returned me: 124586!!!

Something has obviously gone wrong with that sequence and I needed to restore it to 17. So basically I have executed the following: 

ALTER SEQUENCE MY_SEQ INCREMENT BY -(124586-17)

and then execute: 

SELECT MY_SEQ.NEXTVAL

FROM DUAL; 

to restore MY_SEQ.CURRVAL to 17. 

Then you reset the correct increment by executing: 

  

 ALTER SEQUENCE MY_SEQ INCREMENT BY 1

My first post on the Avanttic Blog

Recently I have started working for a new Oracle partner and yesterday I was invited to write a post for the company's blog.
The post is a comparison between OWB and ODI. Unfortunately, the blog is in Spanish. I hope to have some time for translating it in the future.

http://blog.avanttic.com/2013/02/15/owb-vs-odi/#more-8210

Tip: How to get directoy listed in SQL*Plus

Today I needed to launch several .sql scripts from the same directory. I needed to get the script names in a quick way, and possibly being able to copy and paste them.
The solution was using:

SQL> host dir
 El volumen de la unidad C es OS
 El número de serie del volumen es: 6A31-3504

 Directorio de C:\Users\cristina\Documents\

24/01/2013  14:02    <DIR>          .
24/01/2013  14:02    <DIR>          ..
23/01/2013  11:44           291.729 acciones.sql
23/01/2013  18:17            17.826 canales_comunicacion.sql
23/01/2013  18:17           292.138 etapas.sql
24/01/2013  12:14       126.673.515 exp1980.sql
24/01/2013  12:23        91.241.885 exp1985.sql
24/01/2013  12:29        38.205.132 exp1989.sql

SQL*Plus HOST command allows you to execute OS commands such as dir, cd, etc.

Tip: Oracle SQL Developer Data Modeler Create Sequence for an auto incrementing ID

Recently I have started using this tool from Oracle and I must admit it is brilliant for designing Star Schemas and generating DDL code.
For my project I need to define an auto incrementing ID field for several tables of my model. I obviously needed a sequence for each and I was looking for a way to define it in the Data Modeler.
Oracle Data Modeler allows you to mark any column field as auto-incrementing. Select the table in your model and double click on the column. In the example below, I want the primary key field to be auto-incrementing.

Select the option Auto-Incrementing in the Column Property panel.

If you check the DDL you will see the code for the sequence and the trigger that is fired whenever a new line is inserted into the table.

CREATE SEQUENCE CNE_CNEIDE_SEQ
    NOCACHE
    ORDER ;

CREATE OR REPLACE TRIGGER CNE_CNEIDE_TRG
BEFORE INSERT ON T_CANAL_ENTRADA
FOR EACH ROW
WHEN (NEW.CNEIDE IS NULL)
BEGIN
    SELECT CNE_CNEIDE_SEQ.NEXTVAL INTO :NEW.CNEIDE FROM DUAL;
END;
/

If you don't need the trigger, just untick the option in the Auto Increment panel.

Tip: APEX Virtual Environment

I am trying to learn APEX and I was looking for a way to install it on my laptop.
No need to do that. Oracle allows you to create your own APEX workspace.
At the following URL, you can request your own workspace and start getting familiar with APEX fast and easily.
http://apex.oracle.com/i/index.html
Just provide your email and you will be given a login, a test database schema and... enjoy developing! ;)
 

Tip: How to get Today Date using Sunopsis Memory Engine

Today I needed to retrieve today's date in 'YYYYMMDD' using SUNOPSIS Memory Engine.
That would have been much easier to do the following:

SELECT TO_CHAR(sysdate,'YYYYMMDD')
FROM dual

but I wanted to be able to get a date in a string format without relying on any Oracle database schema. To get today's date using In-Memory Engine is quite tricky.
First, you have to create a procedure and make sure you add the following steps:

0 - Drop_Dual_Table
Simply drops a table named dual (which does not exist by default in the SUNOPSIS database)



10 - Create DUAL table



20 - Insert Values



Then you can assign today's date to a variable (and you can format it as you wish) and use it in your interfaces/packages.



P.D. Originally I wanted to get yesterday's date directly in SUNOPSIS MEMORY ENGINE, but I could not find a way to assign -1 offset to CURDATE(). Any ideas?