Oracle string concatenation
I have 3 strings I want to concatenate, they exceed 4000 together:
decode(trim(C.TEST_DESC) || trim(C.KEY_ITMS) || trim(C.TEST_EXCPTN), null, 'No',
the first is 1951, second is 586, third is 2017 for a total of 4554
this of course works, which seriously, is all I need:
decode(substr(substr(C.TES
Because all I'm checking is if the first sets are all null or not...
But I want to understand this... So please help me...
This breaks:
decode(substr(substr(C.TES
So does this:
decode(substr(trim(C.TEST_
Shouldn't the outside substr be enough? Taking the final string and making sure it is 4000? But it doesn't. Even with an addtional subtr wrap on all the columns, it still breaks.
What would one do with this if one reallly wanted to return 4000 characters of all of them concatenated?
Because I do have existing SP's that use this type of concatenation everywhere so I want to make certain I can fix any further issues.
Thanks!
decode(trim(C.TEST_DESC) || trim(C.KEY_ITMS) || trim(C.TEST_EXCPTN), null, 'No',
the first is 1951, second is 586, third is 2017 for a total of 4554
this of course works, which seriously, is all I need:
decode(substr(substr(C.TES
Because all I'm checking is if the first sets are all null or not...
But I want to understand this... So please help me...
This breaks:
decode(substr(substr(C.TES
So does this:
decode(substr(trim(C.TEST_
Shouldn't the outside substr be enough? Taking the final string and making sure it is 4000? But it doesn't. Even with an addtional subtr wrap on all the columns, it still breaks.
What would one do with this if one reallly wanted to return 4000 characters of all of them concatenated?
Because I do have existing SP's that use this type of concatenation everywhere so I want to make certain I can fix any further issues.
Thanks!
>>Because I do have existing SP's that use this type of concatenation
Yes, PL/SQL can do a 32k varchar. That is why.
Yes, PL/SQL can do a 32k varchar. That is why.
ASKER
this *is* in a stored procedure.
It's part of a select query in a stored procedure.
How would I "fix" this using 4000?
It's part of a select query in a stored procedure.
How would I "fix" this using 4000?
ASKER
Your response doesn't add up to me. You say it will work in a stored procedure. yet this is a stored procedure and it doesn't work.
Does it have to do with it being inside the Decode?
Does it have to do with it being inside the Decode?
Move the concatenation from the select.
(= select the different columns in the select and do the concation of the columns in de pl/sql-code of the procedure)
Within the select you use the sql-engine and not the pl/sql so you can't fixed the 4000 limit.
(= select the different columns in the select and do the concation of the columns in de pl/sql-code of the procedure)
Within the select you use the sql-engine and not the pl/sql so you can't fixed the 4000 limit.
>>Does it have to do with it being inside the Decode?
No. It has to do with being SQL. You cannot select more than a 4k in any one varchar in a SQL statement, even inside PL/SQL. You can select 4k each into s1, s2 and s3 and then concat inside the procedure, but you would not be able to return more than 4k to a caller. I will provide a working sample, give me a minute.
No. It has to do with being SQL. You cannot select more than a 4k in any one varchar in a SQL statement, even inside PL/SQL. You can select 4k each into s1, s2 and s3 and then concat inside the procedure, but you would not be able to return more than 4k to a caller. I will provide a working sample, give me a minute.
ASKER
the select is building a result_cursor, so I have to concatenate within the select...
OPEN rslt_cursor FOR
OPEN rslt_cursor FOR
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
flow01 and I are heading in the same direction. :)
You can use many parameters in the PROCEDURE
CREATE PROCEDURE (l OUT integer, p1 OUT varchar2, p2 OUT varchar2,
p3 OUT varchar2, p4 OUT varchar2) IS
.......
In the procedure use a variable that puts the whole string.
Make a block that using SUBSTR divides it in chunks and stores the chunks (4000 bytes) in p1,p2,p3,p4 Put the total length in l.
This is one possible solution.
CREATE PROCEDURE (l OUT integer, p1 OUT varchar2, p2 OUT varchar2,
p3 OUT varchar2, p4 OUT varchar2) IS
.......
In the procedure use a variable that puts the whole string.
Make a block that using SUBSTR divides it in chunks and stores the chunks (4000 bytes) in p1,p2,p3,p4 Put the total length in l.
This is one possible solution.
I fear not. Not in SQLPLUS.
You may need to either write a stored procedure in PL/SQL (which can go to 32k for varchar) or change to use a CLOB