Блог

У меня есть столбец Oracle с типом данных «Long», так как столбец имеет ограничение в 4000 символов, мы склонны вставлять данные в две строки или более, теперь мы хотим отобразить 2 строки данных в одну, я пытался, но есть некоторые другие ошибки, такие как char to long и так далее, не могли бы вы помочь.

 Id, Series, Serialized

1       1        abc
1       1        def
3       2        gdf

Id, Series

1       1        
1       1        
3       2        

Output

Id, Series, Serialized
1       1        abcdef
3       2        gdf

Here's the table scripts

 CREATE TABLE "LOGMESSAGE" 
   (    "ID" VARCHAR2(36 BYTE) NOT NULL ENABLE,
    "SERIALIZEDMESSAGE" LONG, 
    "SERIES" NUMBER(10,0)
   ) 
   
    CREATE TABLE "LOGENTRY" 
   (    "ID" VARCHAR2(36 BYTE) NOT NULL ENABLE
   ) 
   
    insert into "LOGMESSAGE" values(
   'd8dcd593-af52-425a-8bf2-d93f78a601c6','{"name":"miogetadapter001.xml","type":"miogetadapterresponse","id":"56e125af-4202-44cb-bf90-58df03776793","time":"2021-10-05T06:27:04.8595987Z","source":"miochannelsource","adapter":"miotestadapter","channel":"miotestchannel","original":"<RESTTestResponse TestTimestamp="2021/10/05 11:57:05">rn  <Name>miotestadapter</Name>rn  <IsActive>true</IsActive>rn  <Description>Added during MIO install for confirmation test.  Version=3.0.0.0</Description>rn  <type>ChannelAdapterConfiguration</type>rn  <AdapterConfigurationTypeName>FileAdapterConfiguration</AdapterConfigurationTypeName>rn  <AdapterType>FileAdapter</AdapterType>rn  <AdapterVersion>1</AdapterVersion>rn  <AdapterKind>default</AdapterKind>rn  <ContentFormat>Xml</ContentFormat>rn  <ChannelSourceName>miochannelsource</ChannelSourceName>rn  <MessageChannelName>miotestchannel</MessageChannelName>rn  <MomConnectionName></MomConnectionName>rn  <OutboundNameFormat>{messagename}</OutboundNameFormat>rn  <InboundNameFormat>{messagename}</InboundNameFormat>rn  <DoNotSend>false</DoNotSend>rn  <DoNotSendOutbound>false</DoNotSendOutbound>rn  <ShutdownMaxTime>15</ShutdownMaxTime>rn  <IsXml>true</IsXml>rn  <WakeUpInterval>-1</WakeUpInterval>rn  <UserName></UserName>rn  <Password></Password>rn  <DomainName></DomainName>rn  <VerifyUnique>true</VerifyUnique>rn  <UniqueIncludesTimestamp>true</UniqueIncludesTimestamp>rn  <UniqueCacheExpiration>0</UniqueCacheExpiration>rn  <ClearOriginalContents>true</ClearOriginalContents>rn  <BufferSettings>rn    <Description></Description>rn    <Kind>Persistent</Kind>rn    <ConnectionName></ConnectionName>rn    <ConnectionString>folder name=%AppData%</ConnectionString>rn    <Interval>2000</Interval>rn    <Expiration>-1</Expiration>rn    <MaxCount>-1</MaxCount>rn  </BufferSettings>rn  <InProcessExpiration>120</InProcessExpiration>rn  <InboundFilters />rn  <OutboundFilters />rn  <OutboundFailPlugin>LogAndDiscard</OutboundFailPlugin>rn  <OutboundFailConfiguration>LogAndDiscard</OutboundFailConfiguration>rn  <OutboundRetryInterval>15000</OutboundRetryInterval>rn  <OutboundMaxRetries>5</OutboundMaxRetries>rn  <SendMessageMaxRetries>10</SendMessageMaxRetries>rn  <SendMessageRetryInterval>10000</SendMessageRetryInterval>rn  <InboundUri>C:\Program Files\Opcenter Connect MOM\Channel Adapter Host\inbound</InboundUri>rn  <OutboundUri>C:\Program Files\Opcenter Connect MOM\Channel Adapter Host\outbound</OutboundUri>rn  <ErrorUri></ErrorUri>rn  <InboundDriveToMap></InboundDriveToMap>rn  <InboundFilenameFilter>*.*</InboundFilenameFilter>rn  <OutboundDriveToMap></OutboundDriveToMap>rn  <MaxReadRetries>10</MaxReadRetries>rn  <RetryDelay>15000</RetryDelay>rn  <DeleteInterval>15</DeleteInterval>rn  <EncodingName>UTF-8</EncodingName>rn</RESTTestResponse>","contents":"<RESTTestResponse TestTimestamp="2021/10/05 11:57:05">rn  <Name>miotestadapter</Name>rn  <IsActive>true</IsActive>rn  <Description>Added during MIO install for confirmation test.  Version=3.0.0.0</Description>rn  <type>ChannelAdapterConfiguration</type>rn  <AdapterConfigurationTypeName>FileAdapterConfiguration</AdapterConfigurationTypeName>rn  <AdapterType>FileAdapter</AdapterType>rn  <AdapterVersion>1</AdapterVersion>rn  <AdapterKind>default</AdapterKind>rn  <ContentFormat>Xml</ContentFormat>rn  <ChannelSourceName>miochannelsource</ChannelSourceName>rn  <MessageChannelName>miotestchannel</MessageChannelName>rn  <MomConnectionName></MomConnectionName>rn  <OutboundNameFormat>{messagename}</OutboundNameFormat>rn  <InboundNameFormat>{messagename}</InboundNameFormat>rn  <DoNotSend>false</DoNotSend>rn  <DoNotSendOutbound>false</DoNotSendOutbound>rn  <ShutdownMaxTime>15</ShutdownMaxTime>rn  <IsXml>true</IsXml>rn  <WakeUpInterval>-1</WakeUpInterval>rn  <UserName></UserName>rn  <Password></Password>rn  <DomainName></DomainName>rn  <VerifyUnique>true</VerifyUniqu',
   0)
   
   insert into "LOGMESSAGE" values(
   'd8dcd593-af52-425a-8bf2-d93f78a601c6','e>rn  <UniqueIncludesTimestamp>true</UniqueIncludesTimestamp>rn  <UniqueCacheExpiration>0</UniqueCacheExpiration>rn  <ClearOriginalContents>true</ClearOriginalContents>rn  <BufferSettings>rn    <Description></Description>rn    <Kind>Persistent</Kind>rn    <ConnectionName></ConnectionName>rn    <ConnectionString>folder name=%AppData%</ConnectionString>rn    <Interval>2000</Interval>rn    <Expiration>-1</Expiration>rn    <MaxCount>-1</MaxCount>rn  </BufferSettings>rn  <InProcessExpiration>120</InProcessExpiration>rn  <InboundFilters />rn  <OutboundFilters />rn  <OutboundFailPlugin>LogAndDiscard</OutboundFailPlugin>rn  <OutboundFailConfiguration>LogAndDiscard</OutboundFailConfiguration>rn  <OutboundRetryInterval>15000</OutboundRetryInterval>rn  <OutboundMaxRetries>5</OutboundMaxRetries>rn  <SendMessageMaxRetries>10</SendMessageMaxRetries>rn  <SendMessageRetryInterval>10000</SendMessageRetryInterval>rn  <InboundUri>C:\Program Files\Opcenter Connect MOM\Channel Adapter Host\inbound</InboundUri>rn  <OutboundUri>C:\Program Files\Opcenter Connect MOM\Channel Adapter Host\outbound</OutboundUri>rn  <ErrorUri></ErrorUri>rn  <InboundDriveToMap></InboundDriveToMap>rn  <InboundFilenameFilter>*.*</InboundFilenameFilter>rn  <OutboundDriveToMap></OutboundDriveToMap>rn  <MaxReadRetries>10</MaxReadRetries>rn  <RetryDelay>15000</RetryDelay>rn  <DeleteInterval>15</DeleteInterval>rn  <EncodingName>UTF-8</EncodingName>rn</RESTTestResponse>","empty":false,"contentsformat":"Xml","contentshash":"64882DB2ED9382F3075C94A0657C3034","hash":"135D1EEE963EC392868AF2AFF7DE5711","priority":1,"outbound":true,"request":false,"response":true,"requestid":"06da6532-bfd0-4a77-9637-b4da6f48b23a","events":[],"attributes":{"filename":"miogetadapter001.xml","restcommand":"/api/channeladapters","httpverb":"GET","querystring":"name=miotestadapter"},"tagdata":"","adaptertagdata":{"properties":"{rn  "encoding": "UTF-8",rn  "source": "miochannelsource",rn  "adapter": "miotestadapter",rn  "directory": "C:\\Program Files\\Opcenter Connect MOM\\Channel Adapter Host\\inbound"rn}"},"status":"","correlationid":"","maxretrycount":10,"retrycount":0,"automapped":false,"express":false,"inhibitEvent":false,"eventonly":false,"eventttl":0,"check":{"timestamp":"2021-10-05T06:27:04.8626024Z","stages":[{"name":"Created","machineName":"VM-VDIP49-03","timestamp":"2021-10-05T06:27:04.8626024Z","time":0,"duration":0},{"name":"Adapter Host Added","machineName":"VM-VDIP49-03","timestamp":"2021-10-05T06:27:04.8645993Z","time":1,"duration":1},{"name":"Adapter Host Dispatch","machineName":"VM-VDIP49-03","timestamp":"2021-10-05T06:27:04.869603Z","time":7,"duration":6,"dispatcher":"BrokerBalanced"},{"name":"Broker Received","machineName":"VM-VDIP49-03","timestamp":"2021-10-05T06:27:04.8905926Z","time":27,"duration":20},{"name":"Broker Added","machineName":"VM-VDIP49-03","timestamp":"2021-10-05T06:27:04.8925959Z","time":29,"duration":2},{"name":"Broker Dispatch","machineName":"VM-VDIP49-03","timestamp":"2021-10-05T06:27:04.9285978Z","time":65,"duration":36,"availThreads":24,"dispatcher":"fifowithpredecessors"},{"name":"Broker Added","machineName":"VM-VDIP49-03","timestamp":"2021-10-05T06:27:05.0966056Z","time":234,"duration":169},{"name":"Broker Dispatch","machineName":"VM-VDIP49-03","timestamp":"2021-10-05T06:27:05.1066034Z","time":244,"duration":10,"availThreads":24,"dispatcher":"fifowithpredecessors"},{"name":"Adapter Host Received","machineName":"VM-VDIP49-03","timestamp":"2021-10-05T06:27:05.1216049Z","time":259,"duration":15},{"name":"Adapter Host Send","machineName":"VM-VDIP49-03","timestamp":"2021-10-05T06:27:05.1486105Z","time":286,"duration":27}]}}',
   1)

   
   insert into "LOGENTRY"
   values ('d8dcd593-af52-425a-8bf2-d93f78a601c6')

**Tried Query**
 SELECT M.Id,RTRIM(XMLAGG(XMLELEMENT(E,M.SerializedMessage,',').EXTRACT('//text()') ORDER BY M.SerializedMessage).GetClobVal(),',') AS LIST1,M.Series
         from LOGENTRY E INNER JOIN LOGMESSAGE M ON E.Id = M.Id group by M.Id,M.series;

**Error**
ORA-00932: inconsistent datatypes: expected CHAR got LONG
00932. 00000 -  "inconsistent datatypes: expected %s got %s"
 

Длинный тип данных сам по себе имеет множество ограничений и в целом очень сложен для работы. Есть причина, по которой он устарел, и вы просто даете себе дополнительную работу, находя способы обойти его, вместо того, чтобы использовать клобы.

Но чтобы ответить на ваш вопрос, я думаю, что это, вероятно, то, чего вы хотели:

 SELECT listagg(a.linetext, '') within group (order by rownum)
FROM
(
    SELECT 'a' linetext from dual
    UNION ALL
    SELECT 'a' linetext from dual
    UNION ALL
    SELECT '2' linetext from dual
    UNION ALL
    SELECT 'b' linetext from dual
) a
 

Замените подзапрос запросом вашей таблицы, и он объединит ваши результаты, которые вы получите в отдельных строках.