Dynamic SQL and the ORA-01489: result of string concatenation is too l

[kxramse]

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
or
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):

CREATE OR REPLACE PROCEDURE IMSV7.Template_LinkedServer2
(ORACLE_DB_ENVIRONMENT IN varchar2
,p_recordset1 OUT SYS_REFCURSOR)
AS
hold_query varchar2(2000);
BEGIN
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..

CREATE OR REPLACE FUNCTION IMSV7.fctn_RETURN_CURSOR RETURN SYS_REFCURSOR
AS l_cursor SYS_REFCURSOR;
BEGIN
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;
END;
/

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,

Keith

 

[Dagon]

I just tried it with the following query on Oracle 9i and got no problems at all:

CREATE OR REPLACE PROCEDURE test_proc
(ORACLE_DB_ENVIRONMENT IN varchar2
,p_recordset1 OUT SYS_REFCURSOR)
AS
hold_query varchar2(32000);
BEGIN
hold_query := 'select dummy from dual@'
|| oracle_db_environment|| '.world where 1=1';
for i in 1..2000 loop
   hold_query := hold_query||' and 1=1';
end loop;
OPEN p_recordset1 FOR hold_query;
END TEST_PROC;

So unless 10g has gone backwards and allows fewer characters than 9i, you shouldn't have a problem. I presume the database where you are running the procedure is 9i or greater ?