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

[greenmtnsun]

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

[PAVB]

VARCHAR2 datatype max size is 4,000 bytes.

If you really have queries larger than 4000 characters I would suggest to rethink your strategy.

[greenmtnsun]

Do you have a suggestion?

[PAVB]

Yes. Work it out with your DBA.

[gandolf989]

Have you tried creating views in each database where you have a db link with?

While the limit for a VARCHAR2 in PL/SQL is 32767, the limit for a VARCHAR2 in SQL is only 4000. When you store the query in PL/SQL you don't have a problem, but when you run it the SQL engine takes over, and you find the limit for query length is 4000. But creating a view will hopefully help you to make the query much smaller.

SQL> set serveroutput on

declare
v_varchar2 varchar2(32767);
begin
v_varchar2 := RPAD('Itworks', 8000, '!');
dbms_output.put_line(substr(v_varchar2, 1, 254));
end;
/

create table delme ( c1 varchar(4001));

SQL> SQL> 2 3 4 5 6 7 Itworks!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!

PL/SQL procedure successfully completed.

SQL> SQL> create table delme ( c1 varchar(4001))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype