Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic SQL and the ORA-01489: result of string concatenation is too long
icon5.gif   Dynamic SQL and the ORA-01489: result of string concatenation is too long [message #260657] Mon, 20 August 2007 11:17 Go to next message
Messages: 1
Registered: August 2007
Location: Portland, OR
Junior Member
I have a database instance that is nothing but a linked server place.
In it, I can do something like this...

select acctkey,acctno from imsv7.account@HVDEV.world
select acctkey,acctno from imsv7.account@HVPRD.world

Each time I substitute the Database environment I want to query out of.

The problem I have occurs when the SQL that I build is too great in length.

Here is my little template Stored Procedure (NOTE: I would be adding more fields to pass into the call for the where clause):

,p_recordset1 OUT SYS_REFCURSOR)
hold_query varchar2(2000);
hold_query := 'select acctkey,acctno from imsv7.account@'
|| oracle_db_environment|| '.world';
OPEN p_recordset1 FOR hold_query;
END Template_LinkedServer2;

When I try to increase this varchar2 past 4000, I get the:
ORA-01489: result of string concatenation is too long
this comes with the ever so helpful instruction to make my string shorter.

I can't! I have a query that is 7000 characters long. So I tried blobs and clobs, and nothing seems to make peace with Oracle.

I tried putting my SQL in a function in each database environment that returned a cursor so that I could do something like this in each environment..

OPEN l_cursor FOR select cast(ACCTKEY as INT) as ACCTKEY, cast(trunc(STOPDTTM) as DATE) as STOPDTTM, cast(trunc(STARTDTTM) as DATE) as STARTDTTM
from IMSV7.acctsrv;
RETURN l_cursor;

Then from the linked environment, just call the function from a select statement. That was a 6 hour trap that ended up fruitless as my DBA said it was impossible. Something about my call was calling the function in the target database but that the target database function ended up calling a table in the linked database, which of course wasn't there.

Is there a way for me to do this and get huge query strings to run against all db environments that I link to?

Thanks for your help,

Re: Dynamic SQL and the ORA-01489: result of string concatenation is too long [message #260777 is a reply to message #260657] Mon, 20 August 2007 22:19 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You could upgrade to 11g. I read that it had support for longer dynamic SQL strings.

If you have a limited number of environments, you can do it without dynamic SQL. There is a concept called Partitioned Views, which died with 7.3, although it remained in the 8.0 (and maybe even the 8i) documentation for this exact problem.

SELECT 'HVDEV' env, a.*
FROM   imsv7.account@HVDEV.world a
SELECT 'HVXYZ' env, a.*
FROM   imsv7.account@HVXYZ.world a

You SELECT from such a VIEW like so:
FROM   account
WHERE  env = :oracle_db_environment

The WHERE clause will perform a type of Partition Pruning on the underlying view, and the SQL will not even be run against all of the unwanted databases.

Ross Leishman
Re: Dynamic SQL and the ORA-01489: result of string concatenation is too long [message #260781 is a reply to message #260657] Mon, 20 August 2007 22:34 Go to previous messageGo to next message
Messages: 25151
Registered: January 2009
Location: SoCal
Senior Member
One application I wrote a few years ago I had what seems to be a similar requirement.

What I did was to write SQL similar to the following:

The "secret" was that I created the desired private database link such that REMOTE would "point" to the desired database.
This resulted in no need for EXECUTE IMMEDIATE on the SELECT.
Re: Dynamic SQL and the ORA-01489: result of string concatenation is too long [message #260871 is a reply to message #260781] Tue, 21 August 2007 03:01 Go to previous message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I had a similar problem to this - we had to run the same code against several different databases, and be able to switch between them in the middle of processing.

The solution I used was to create several seperate schemas, one per remote Db, each containing a db link to one remote db plus synonyms to all of the remote tables we needed to look at on that db, plus synonyms for all of the local tables we needed.

We then used 'ALTER SESSION SET current_schema = <username>' to set which of the other schema's we'd run things as. This would casue everything from that point on to run as though run by one of the remote db schemas, and all the table references would point accross that specific db link. Your code needs to use Invoker rather than definer rights for thsi to work, but it worked like a dream for us.
Previous Topic: Early Binding/Late Binding concept
Next Topic: update query
Goto Forum:

Current Time: Mon Jan 23 21:08:21 CST 2017

Total time taken to generate the page: 0.07117 seconds