ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML

Cause:
=====
DML operations like create, drop etc. and rollback/commit can not be performed in a SQL statement

Example:
=======
The most general case where this error occurs is calling a function as
SELECT myFunc(.. .. ..) FROM dual..
Something like this or the like, Where myFunc contains DML operations, rollback or commit statements.

Solution:
==========
You have to ensure that DML operations and rollback/commit operations must be autonomous instead of inside a query.

For the case described above you can execute the function myFunc as
var:=myFunc(.. .. ..);

Or redefine the function as
CREATE OR REPLACE function myFunc(.. .. ..) RETURN ..
AS
....
....
PRAGMA AUTONOMOUS_TRANSACTION
begin
.....
.....
.....
end;

ORACLE: Getting maximum sub string

We can easily find the maximum sub-string from a table of oracle. Let's see the following example..

CREATE table test(
name varchar2(100),
code varchar2(50)
)
INSERT INTO test(name,code) VALUES('Bangladesh','880');
INSERT INTO test(name,code) VALUES('BGD-Mobile','8801');
INSERT INTO test(name,code) VALUES('BGD-GP','88017');
INSERT INTO test(name,code) VALUES('BGD-AKTEL','88018');
INSERT INTO test(name,code) VALUES('BGD-Bangla Link','88019');
INSERT INTO test(name,code) VALUES('BGD-CITYCELL','88011');
INSERT INTO test(name,code) VALUES('BGD-WARID','88016');
INSERT INTO test(name,code) VALUES('BGD-TELETALK','88015');

SELECT * FROM test

SELECT max(code) FROM test WHERE instr('8801234',code)>0;

This will give: 8801


SELECT max(code) FROM test WHERE instr('880234',code)>0;

This will give: 880


SELECT max(code) FROM test WHERE instr('8801123',code)>0;

This will give: 88011

ORA-01704: String literal too long

The Mail cause of this error is stating by the message itself 'String literal too long'. If you try to INSERT more than 4000 characters to a varchar2 field this error will occur. Another case where this may occur is if you try to update a CLOB field. CLOB field doesn't support update.

In Java if you try to execute PreparedStatement.executeUpdate() to INSERT or UPDATE CLOB field of ORACLE with more than 4000 characters, you'll get this error.

To avoid this use PreparedStatement.execute() methode to to INSERT CLOB field.

This may occur in other language also. And you can use same technique to avoid errors.

Happy programming.

Comment my blog/ email me for any problems related to this.

Oracle : Hierarchical Data/ Tree in Oracle

Oracle provides strong way to query tree like data or hierarchical data.

The syntax for query hierarchical data is-

 

SELECT columns

FROM table_name

WHERE where_condition

CONNECT BY PRIOR condition_for_next_level

START WITH start_condition

ORDER SIBLINGS BY columns

 

Let's try it with a practical example.    

 

CREATE
TABLE location(

    id NUMBER,

    name
VARCHAR2(100),

    parent NUMBER

);

 

INSERT
INTO location(id,name,parent) VALUES(1,'Earth',0);

INSERT
INTO location(id,name,parent) VALUES(2,'Asia',1);

INSERT
INTO location(id,name,parent) VALUES(3,'Europe',1);

INSERT
INTO location(id,name,parent) VALUES(4,'America',1);

INSERT
INTO location(id,name,parent) VALUES(5,'Africa',1);

INSERT
INTO location(id,name,parent) VALUES(6,'Oceania',1);

 

INSERT
INTO location(id,name,parent) VALUES(7,'Bangladesh',2);

INSERT
INTO location(id,name,parent) VALUES(8,'India',2);

INSERT
INTO location(id,name,parent) VALUES(9,'Pakistan',2);

INSERT
INTO location(id,name,parent) VALUES(10,'Singapore',2);

INSERT
INTO location(id,name,parent) VALUES(11,'Malaysia',2);

 

INSERT
INTO location(id,name,parent) VALUES(12,'England',3);

INSERT
INTO location(id,name,parent) VALUES(13,'France',3);

INSERT
INTO location(id,name,parent) VALUES(14,'Turkey',3);

INSERT
INTO location(id,name,parent) VALUES(15,'Scotland',3);

 

INSERT
INTO location(id,name,parent) VALUES(16,'Mexico',4);

INSERT
INTO location(id,name,parent) VALUES(17,'Canada',4);

INSERT
INTO location(id,name,parent) VALUES(18,'USA',4);

 

INSERT
INTO location(id,name,parent) VALUES(19,'Dhaka',7);

INSERT
INTO location(id,name,parent) VALUES(20,'Rajshahi',7);

INSERT
INTO location(id,name,parent) VALUES(21,'Chittagong',7);

 

INSERT
INTO location(id,name,parent) VALUES(22,'Las Vegas',18);

INSERT
INTO location(id,name,parent) VALUES(23,'New Orlince',18);

INSERT
INTO location(id,name,parent) VALUES(24,'New York',18);

Now

SELECT * FROM location;

Will give..

ID        NAME            PARENT

-----------------------------------------------

1        Earth            0

2        Asia            1

3        Europe         1

4        America         1

5        Africa            1

6        Oceania        1

7        Bangladesh        2

8        India            2

9        Pakistan        2

10        Singapore        2

11        Malaysia        2

12        England        3

13        France            3

14        Turkey            3

15        Scotland        3

16        Mexico            4

17        Canada            4

18        USA            4

19        Dhaka            7

20        Rajshahi        7

21        Chittagong        7

22        Las Vegas        18

23        New Orlince        18

24        New York        18

 

Now

SELECT
lpad(' ',8*(level-1))||NAME AS name
FROM LOCATION

CONNECT
BY
PRIOR id = parent

START
WITH parent = 0

Will give the following result

NAME

------------

Earth

Asia

Bangladesh

Dhaka

Rajshahi

Chittagong

India

Pakistan

Singapore

Malaysia

Europe

England

France

Turkey

Scotland

America

Mexico

Canada

USA

Las Vegas

New Orlince

New York

Africa

Oceania

 

You can also use ORDER SIBLINGS BY to sort siblings

Oracle DATE to TIMESTAMP

The is a function in oracle name to_timestamp that can transform from CHAR to TIMESTAMP. We can not transform a DATE to TIMESTAMP properly.
Lets consider a table mytab as follows-
mytab
name(CHAR(100)) bdate (DATE)
------------------------------------------------
Mr. Abc 10-10-2009 10:22:22 AM

If we use
SELECT TO_TIMESTAMP(bdate,'DD-MM-RRRR HH24:MI:SS') FROM mytab
we get the value of TO_TIMESTAMP(bdate,'DD-MM-RRRR HH24:MI:SS') as 10-10-2009 00:00:00 AM instead of 10-10-2009 10:22:22 AM. To get proper value of bdate in TIMESTAMP we can first change the DATE to CHAR then CHAR to TIMESTAMP as follows
TO_TIMESTAMP(TO_CHAR(bdate,'DD-MM-RRRR HH24:MI:SS'),'DD-MM-RRRR HH24:MI:SS') FROM mytab

So now
SELECT TO_TIMESTAMP(TO_CHAR(bdate,'DD-MM-RRRR HH24:MI:SS'),'DD-MM-RRRR HH24:MI:SS') FROM mytab
Will give us the value of bdate as TIMESTAMP as 10-10-2009 10:22:22 AM.


I expect your comment on this topic and other questions relating to this topic...