I am trying to spool the data to a file , and my query has 115 columns and out of which 20 columns have varchar2(2000). And its throwing result string concatenation too long.
I tried using to_clob function , but spool file does not show the complete result set.
I am receiving a ORA-01489: result of string concatenation is too long error on the following code. The size of the MNO_NOTE fields is: MNO_NOTES_1 X(2000). I'd rather not modify the DB table column size, but rather that capacity of the "notes", or whatever structure the concatenated string is stored in. Could I use the substring method?
SELECT TO_DATE(TO_CHAR(mno_date_recorded,'yyyymmdd')||
TO_CHAR(mno_time_recorded,'0009'),'yyyymmddhh24mi')
AS create_date,
stf_id AS create_user,
RTRIM(MNO_NOTES_1)||
RTRIM(MNO_NOTES_2)||
RTRIM(MNO_NOTES_3)||
RTRIM(MNO_NOTES_4)||
RTRIM(MNO_NOTES_5)||
[code]...
I have a sql query which has around 115 columns and out of which 25 columns are of varchar2(2000) and when I run the query I get the ORA-01489: result of string concatenation is too long error.
I tried to use to_clob function for the columns having varchar2(2000) and if I run the sql from toad , it works fine, but when I tried to run the same query from sqlplus and spool to a file, the result doesn't come in a single line. I have tried to import the spool file to my local and open it , but still it doesn't come in a single line, the data is trucated This is how my data looks in the spool file.
1-L31OGM|Red|1|Due|Qualified|02/08/2012||02/08/2012| you are missing a message.
These are the below set options used in the query . I even tried set long 100000000 and also set longchucksize option also, I have tried with WRAP OFF and WRAP ON also ,but still it doesn't work.
SET HEADING OFF
SET WRAP OFF
SET LINESIZE 32000
SET FEEDBACK OFF
SET PAGESIZE 0
SET LONG 32000
SET TRIMSPOOL ON
SET ECHO OFF
SET TERMOUT OFF
get the data in a single line and using utl_file package is not an option in our project due to security reason.
my rdbms, os version
SYS@prod> select PLATFORM_ID, PLATFORM_NAME from v$database;
PLATFORM_ID
-----------
PLATFORM_NAME
-------------------------------------------------------------------------
12
Microsoft Windows x86 64-bit
[code]...
as I googled the solution does not seems to apply to my case.it very puzzling that such a short query can produce
ORA-01489: result of string concatenation is too long.
when i run the interrogation bellow :
SELECT 'Existing Tables: ' || LISTAGG(table_name, ',') WITHIN GROUP (ORDER BY table_name) tablenames FROM user_tables;
i receive the error :
ORA-01489: result of string concatenation is too long
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.
I am getting below error when i try to update in my table
ERROR at line 1:
ORA-01489: result of string concatenation is too long
how to resolve this. Note :This is Update query not select query
I am trying
IF LENGTH(v_final_string) < 3800 THEN
SELECT nvl2(v_final_string,v_final_string
||',' ,v_final_string)
|| temp.temp_string
INTO v_final_string
FROM DUAL;
DBMS_OUTPUT.put_line ('v_final_string=' || v_final_string );
ELSE
EXIT;
END IF;inside a loop.
But it's not concatenating. I am alwas getting empty v_final_string
i try to insert Concatenation string to my table,i need that all traps that has 12 length will be insert the new trapnum like this:
for example: 26001005CC45 = 260001005CC0045 ....... 08060027RF05 = 080600027RF0005 ......... and so....
update trap set TrapNum = (
select trim(both from to_char(substr(TrapNum,1,4),'0000'))||
trim(both from to_char(substr(TrapNum,5,1),'00'))||
trim(both from to_char(substr(TrapNum,6,3),'000'))||
substr(TrapNum,9,2)||
trim(both from to_char(substr(TrapNum,11,2),'0000')) from Trap)
where length(Trapnum)=12
but i got error ORA-01427
Im trying to generate this link on my result [URL].....
materialId and materialFolderId are the results of my query.. Im not sure how to combine my results and string on my query..
I have problem with query ( i'll only use DUAL table for example):
SELECT T.DUMMY P1,
T.DUMMY P2,
T.DUMMY C1,
T.DUMMY C2,
T.DUMMY C3,
T.DUMMY C4,
T.DUMMY C5
FROM DUAL T;
I need to concatenate only columns with name starting with C and add separator between them. Is there any function do concatenate columns using separator like:
SELECT T.DUMMY P1,
T.DUMMY P2,
DBMS_SOMETHING.SOME_FUNCTION(',', C1,C2,C3,C4,C5)
FROM DUAL T;
Or I must do like this:
SELECT T.DUMMY P1,
T.DUMMY P2,
C1 || ',' || C2 || ',' || C3 || ',' || C4 || ',' || C5
FROM DUAL T;
problem is that I have 3.000+ columns in 23 different selects :/
We are using Oracle 11g (11.1.0).I'm not all that prolific when it comes to writing queriesI have a table...
Table1
-----------------------------------------
oid narr parent
-----------------------------------------
1 some narrative null
2 more narrative 1
3 a bit of test narrative 2
Simply put, I need a query that will recurse up through each rows parent and return the concatination of all parents and itself narrative.
Expected output
------------------------------------
some narrative
more narrative some narrative
a bit of test narrative more narrative some narrative
The requirement is that this is one single query as it will be called from a third party application we are using. We need this recursion and concatenation to be done on the database, as while we have control over the database queries that get executed we have no control over the internal source of this third party applications.I have been digging around for a bit, I have tried using a combination of JOINS and UNIONS but keep hitting a brick wall.
The best I could come up with is...
SELECT concat(n1.narr, n2.narr) FROM table1 n1
JOIN table1 n2 ON n1.oid = n2.parent
But this only returns two lines, for oid's 2 and 3, and only concatinates with the immediate parent.
I have the following fields:
Addressln1
Addressln2
Suburb
Town
I know how to concatenate them
trim(Addressln1) || ',' || trim(Addressln2) || ',' || trim(Suburb) || ',' || trim(Town) as Address
1. I would like to know if any of the fields are empty I would like to eliminate the comma character from the string.
2. Can I replace the comma with a new line character and what character to be used in the syntax.
Every prospect person in the prospect table are approached with a proposal. This proposal record is stored in the proposal table. Proposal is managed by many primary and proposal managers over the period of proposal. They are assigned the proposal in the assignment table. Primary managers have an assignment type 'PM' and proposal manager have a assignment type of 'PS'
i Need to make below1 and 2 changes to the script.
1-when proposal is current (proposal.active_ind = 'Y' ) then extract only currently assigned primary and proposal managers (assignment active_ind = 'Y') and these managers should be assigned after the proposal has started (assignment.start_date >= proposal.start_date )
2-when proposal is not current (proposal.active_ind = 'N' ) then extract even not curRent assigned primary and proposal managers (not use this in criteria assignment active_ind = 'Y') and these managers should be assigned only after the proposal has started (assignment.start_date >= proposal.start_date )
Primary/Proposal Manager column : Format to print as below:
All Primary managers seperated with space "/" between and all proposal managers after that and "*" at the end
For Example : Mary Steve Roger / Chris Danny Veronica * also, current script only extracts 5 primary managers and 4 proposal managers but thats ok. i can put 10 max(decode statements in the output cause i think they cannot have more than 10 managers.
/*script*/
SELECT DISTINCT p.prospect_id "PROSPECT ID"
,p.prospect_name "Prospect Name"
,pro.proposal_id
,pro.proposal_title "Proposal Title/Purpose"
, MAX(decode(pm_seq
,1
,primary_manager_name
,NULL)) || ' ' ||
[code]...
How to generate a PAN number by concatenation of data from different tables
View 3 Replies View RelatedI am currently doing column values concatenation from multiple rows and then removing duplicates as in the following example:
SQL> select pid
2 , regexp_replace(ltrim(sentence), '([A-Za-z0-9]+,)1+', '1')
3 from ( select pid
4 , seq
5 , sentence
6 from b
7 model
8 partition by (pid)
9 dimension by (seq)
[code]....
but for some reason regexp_replace does not seem to work with clob and I get:
ORA-00932: inconsistent datatypes: expected - got CLOB
00932. 00000 - "inconsistent datatypes: expected %s got %s"
Is it possible to eliminate duplicates in the model before the concatenation?
I have this error (and solution):
ORA-02085: database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?
I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.
For ex:
The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.
writing that piece of code to append the value fetched which doesn't exactly match with the existing string
show an ex to use string buffer for select statemnt
View 1 Replies View RelatedI got a two tables, table the_table_1 consist of customers and the other one the_table_2 consist of channels.
And I need to get list of channels by contract which are an assigned to customers.
The select below give me a only assigned channels but I need to get a contract_key too from the_table_1.
select o.channel_name from the_table_2 o
minus
select o.channel_name from the_table_1 a, the_table_2 o
where a.contract_key=237092201
and a.offer_channel_key=o.OFFER_CHANNEL_KEY
And the select under (after minus) gives a list of non assigned channels by contract_key:
select o.channel_name from the_table_1 a, the_table_2 o
where a.contract_key=237092201
and a.offer_channel_key=o.OFFER_CHANNEL_KEY
how to rewrite a select to get required result?
CREATE TABLE FLIGHTS(
FLIGHT_NUM NUMBER PRIMARY KEY
)
[Code]...
how to get the name of all passengers who have bookings in all flight_num?
o/p is 555
I am using Oracle 8i.
Below is my query...
In the below 2 tables the records are like...
Table_1
IDNameCode
1AII
1AMNET
1AAXIS
1AUAT
2BMNET
2BUAT
2BUTC
3CMNET
3CII
3CUTC
Table_2
IDName
1AAA
2BBB
3CCC
4DDD
When i execute below query with using (NOT IN & IN) i am getting same result..
select * from Table_2 where id in
(select * from Table_1
where SRVC_PROV_CDE not in ('MNET', 'II', 'AXIS', 'UAT'))
Result:
IDName
1A
2B
select * from Table_2 where id in
(select * from Table_1
where SRVC_PROV_CDE in ('MNET', 'II', 'AXIS', 'UAT'))
Result:
IDName
1A
2B
When we execute the 1 query above it should not show 1 and 2 records...as i am using NOT IN condition.
I want to run below query to get the result set that I am after. But It takes long time even with the indexes...Here in IM_Mapping table is having 1.7 mio records and T_Extract table about 35000 records. All the other tables having below 1000 records
SELECT DISTINCT T_Extract.SerGrp, T_Extract.SerCat, T_Extract.Component, T_Extract.Mod_Item ID,
" ##" AS IM, IM_Mapping.TrfCode, IM_Mapping.CatCode, IM_Mapping.CatDescr, IM_Mapping.SubCatCode, IM_Mapping.SubCatDescr,
LKP_Item.Group, LKP_Item.Modality Source
FROM ((LKP_Product
[code]....
T_Extract.Component
T_Extract.Mod_Item ID
T_Extract.SerCat
T_Extract.SerGrp
Just want to know if there is any possibility of running the query with all the Groups in one go
Even if I run group wise it took 30 min query still running without any results...Is there anyway of restructuring the query for better performance.
How to do the partition on sql result result using query.
sample
BPREF_NO BILL_MONTt AVG_IND partition
Q12345 1/31/2009 2 part1
Q12345 2/28/2009 2 part1
Q12345 3/31/2009 2 part1
Q12345 4/30/2009 2 part1
Q12345 5/31/2009 2 part1
Q12345 6/30/2009 1 part1
Q12345 7/31/2009 2 part1
Q12345 9/30/2009 1 part2
Q12345 10/31/2009 2 part2
Q12345 11/30/2009 2 part2
Q12345 1/31/2010 1 part3
Q12345 2/28/2010 2 part3
Q12345 3/31/2010 2 part3
Q12345 11/30/2011 2 part4
Q12345 2/29/2012 2 part5
Q12345 3/31/2012 2 part5
Q12345 4/30/2012 2 part5
Q12345 5/31/2012 2 part5
Q12345 7/31/2012 2 part6
i want to create the partition column using query based on below logic
if bill_month is sequence then we have to create a partition and if any breaks comes we have to introduce the new partition.
just for example....
from 31-jan-2009 to 31-jul-2009 is called part1
from 30-aug-2009 to 30-nov-2009 is called part2
like wise...
is it possible to make the partition by query itself.
BANNER
--------------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
[code]....
Middle EastThe table contains more than a million records, pivoting using a group by clause will have performance issues. Would it be possible to do this with Xquery or any other method?
I need to return the stored result in a record by ref cursor. In the below code I am displaying the result through DBMS_OUTPUT.PUT_LINE but i need to do it through ref cursor to display the result.
declare
type r_cursor is REF CURSOR;
c_emp r_cursor;
type rec_emp is record
(
name varchar2(20),
sal number(6)
[code].....
My scenario is something like this:
MyTable
Rownum Colval
1 23
2 34
3 45
I need to write a query which will get me output: 233445, i.e. all the three rows concatenated. How can it be done? I want to do it through sql only and not to use PL/SQL. Is this possible?
1.Requirement....
deptno ename
++++++++++++++++++++++++++++++
10 a,d,e,g
20 b,c,j
30 k,l,o
2.How to swap the column values ? ex. col2 into col1 and col1 into col2
3.What is the major Diff. b/w Right outer join & Left outer join ?
I have the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:
create table zTEST
( IDx number,
STARTx number,
DURATION number,
RESULT number );
insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );
[code]...
The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.
What is the sql code for the RESULT-column?
How can i join two quires together to get result.
My requirement is:
First i want to select Table as we do
Select * from tab;
Then i want to describe the table as we do
Desc WO;
How can we join these two queries to have result.
i have following table
CREATE TABLE THREAD_SHADES
(
ITM_COD NUMBER NOT NULL,
ITM_DES VARCHAR2(250 BYTE) NOT NULL,
)
[Code]...
Result is
ITM_COD ITM_DES
80064186THREAD TEX-105 SHADE# 7921 (1500 MTRS)
80064187THREAD TEX-40 SHADE#7921 (3000 MTRS)
80114482THREAD TEX 40 SHADE C-8762 1500MTR LOCAL
80130541THREAD TEX-60 SHADE C8676 J&P COAST ASTRA 1000 MTRS
I want a query which should return only Sahde # from above data result must be as
7921
C-8762
C-8762
C8676