ORA-01489: result of string concatenation is too long

 

One of  our customers reported us this error during reading stored procedures from Oracle database. What have caused this error? USER_SOURCE view retrieve name, type and line by line of function/stored procedure definition. Each line in a new row. We used hierarchical queries and sys_connect_by_path function to concatenate all of those lines in one which keeps the full function/stored procedure definition.

The problem is, that SQL in Oracle is able to return just 4000 bytes in one column. So, if any string is longer than 4000 bytes in concatenation you will get this error. And there is no possibility to fix it. You must change logic, use chunks or something similar (CLOBs).

As a small notice, T-SQL variables can be used for concatenation very big strings which exceeds 4000 characters. Again, you will not be able to return it back with SQL.

As a sample, the following code produces the error:

select rpad(' ',4000)||'1' as 4001StringLength
from TestTable

but the following is fine:

declare
testVar   varchar2(32767);
begin
testVar := rpad(' ',4000)||'1'
end;

MySQL 5.4

New Features

MySQL has released a new version of database server – MySQL in version 5.4. Latest release was 5.1 with a really great features. This one brings not so much but also very important features:

• InnoDB storage engine can address more than 4 CPU’s/cores. Thjs helps that MySQL scale much better under huge application workloads.
• Till now, subqueries were well known as performance problematical. In version 5.4 subquery optimization has been improved a lot in a number of various use cases. As MySQL mentioned in the example below, execution time dropped from 12 minutes (9 000 000 reads) on MySQL 5.1 to just 1.8 seconds (153 000 reads) on MySQL 5.4

SELECT COUNT(l_orderkey) FROM lineitem
WHERE l_linenumber=1 AND
      l_orderkey IN
      (SELECT o_orderkey FROM orders
         WHERE o_totalprice > 1000 AND
               o_custkey IN
               (SELECT c_custkey FROM customer
                  WHERE c_address LIKE 'Le%'));

• MySQL 5.4 offers a new much better join algorithm which speed up execution time of multi-way joins like in the following example

SELECT COUNT(*) FROM part, lineitem
       WHERE l_partkey=p_partkey AND p_retailprice>2050
             AND l_discount>0.04;

• Improved error handling – through the implementation of standard SQL (SQL 2003) SIGNAL and RESIGNAL operations in stored procedures, functions and triggers developers are able to signal rule violations during execution.

Must mention here that Oracle and SQL Server not implement this functionality but have their own mechanisms: Oracle through RAISE and SQL Server through RAISEERROR statements.

• INFORMATION_SCHEMA got a new objects PARAMETERS. We wait a lot on this.


• In prior version of MySQL out parameter were not possible in prepared statements. That has been changed now.

This is all from programmers perspective. Have a fun.

Compare SQL Server, Oracle and MySQL database with DBTYP.NET Studio

Table 'mysql.proc' doesn't exist.

Sometimes our support team receive a helpdesk request concerning this error message shown in Schema Comparison of DBTYP.NET 2009 Studio.

The usual problem with such a customers is that almost all of them make recent upgrade of MySQL server. While some releases of MySQL introduce changes in the structure of the system tables in mysql database to add new privileges and support new features, it is necessary to update the system tables as well.

This can be achieved by running mysql_fix_privilege_tables script in MySQL prior to version 5.1.7 or mysql_upgrade in version 5.1.7 or above.

For more details check following links:

• http://dev.mysql.com/doc/refman/5.1/en/mysql-fix-privilege-tables.html
• http://dev.mysql.com/doc/refman/5.1/en/mysql-upgrade.html

See all database differences and dependencies with DBTYP.NET Studio