Dynamic SQL and the ORA-01489: result of string concatenation is too long
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):
CREATE OR REPLACE PROCEDURE IMSV7.Template_LinkedServer2
(ORACLE_DB_ENVIRONMENT IN varchar2
,p_recordset1 OUT SYS_REFCURSOR)
hold_query := 'select acctkey,acctno from imsv7.account@'
|| oracle_db_environment|| '.world';
OPEN p_recordset1 FOR hold_query;
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;
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
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,