Category: Oracle

How to commit a single DML statement in Oracle (PL/SQL)

Posted: 2007-05-24   |   Categories: Oracle, Programming   |   Tags: DML, Statement

Sometimes you might need to commit a single INSERT, UPDATE or DELETE statement and not the whole main transaction. In such a case you can use an AUTONOMOUS TRANSACTION which is a feature available since Oracle 8i. The trick is to encapsulate the statement in a procedure and use the pragma AUTONOMOUS_TRANSACTION. Here is an example, suppose you have a table, your_table, which has just one column of type NUMBER and you want to insert the value 1, irrespective of the state of the main transaction:

How to put a set of rows into a single row in Oracle

Posted: 2007-04-27   |   Categories: Oracle, Programming

In Oracle you can execute hierarchical queries using some cool operators–CONNECT BY PRIOR, START WITH and the pseudocolumn SYS_CONNECT_BY_PATH, namely. However, using your fantasy you can exploit these operators to put in a single row–with each item separated by the next through a separator, such as a comma–what you have in multiple rows. For example if you have more than one telephone number in different rows and you want to put them in a single comma-separated row you could use a query similar to the following:

How to tell Oracle to use more than one byte per character

Posted: 2007-04-26   |   Categories: Oracle, Programming

When you create a table, by default, the number of bytes used to represent a character in a VARCHAR2 type is 1, irrespective of the characterset of the DB. This may be fine if the characterset is such that it uses just one byte per character. However, if the characterset of the DB is such that there might take more than one byte to represent a character (for example for characterset = AL32UTF8) you might run into troubles, such as the error: ORA-01401: inserted value too large for column. However you can specify, at creation time, that the size of characters is expressed in CHARs instead of bytes. For example:

Using dynamic SQL statements from PL/SQL

Posted: 2006-09-06   |   Categories: Oracle, Programming   |   Tags: Dynamic statement

Sometimes you need to execute dynamic SQL statements. Starting from Oracle8i you can accomplish this task using the EXECUTE IMMEDIATE statement. Here are three examples of how you can take advantage of this great statement.

    
sql_select := 'SELECT * FROM your_table WHERE field1 = :1';
EXECUTE IMMEDIATE sql_select
INTO your_cursor
USING your_parameter_for_field1;

In this first example I showed how you can use EXECUTE IMMEDIATE to execute the query and put the result into a cursor. The USING your_parameter_for_field1 part replaces the :1 bind variable with the value contained in the your_parameter_for_field1 parameter.