Home » SQL & PL/SQL » SQL & PL/SQL » Split CLOB column to multiple columns within a row (Oracle 11g)
|
|
| Re: Split CLOB column to multiple columns within a row [message #633451 is a reply to message #633449] |
Thu, 19 February 2015 01:19   |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Welcome to the forum!
Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.
You could use SUBSTR. Earlier versions needed DBMS_LOB.SUBSTR.
For example,
SQL> DROP TABLE t;
Table dropped.
SQL>
SQL> CREATE TABLE t(col CLOB);
Table created.
SQL>
SQL> DESC t;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL CLOB
SQL>
SQL> INSERT INTO t VALUES('1001 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":1781}}');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> SELECT * FROM t;
COL
--------------------------------------------------------------------------------
1001 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":1781
SQL>
SQL> SELECT substr(col, 1, 4)contract_id FROM t;
CONTRACT_ID
--------------------------------------------------------------------------------
1001
SQL>
Regards,
Lalit
[Updated on: Thu, 19 February 2015 01:24] Report message to a moderator |
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633452 is a reply to message #633449] |
Thu, 19 February 2015 02:12 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
For the first 2 columns (and using Lalit's test case you should post next time):
SQL> select substr(col, 1, instr(col,' ')-1) contract_id,
2 substr(col,
3 instr(col, '"CNCL_DT":') + length('CNCL_DT')+4,
4 instr(col, '"', instr(col, '"CNCL_DT":') + length('CNCL_DT')+4)
5 - instr(col, '"CNCL_DT":') - (length('CNCL_DT')+4)
6 ) CNCL_DT
7 from t
8 /
CONTRACT_ID CNCL_DT
----------- ------------------------
1001 2015-01-09T04:35:08.305Z
For the other columns you just have to replace CNCL_DT by the column name.
[Updated on: Thu, 19 February 2015 02:13] Report message to a moderator |
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633454 is a reply to message #633452] |
Thu, 19 February 2015 02:22 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Note that my query assumes all values are between "; this is not the case for your last one, so you have to change a little bit the query.
With this assumption the generic query is:
SQL> select * from t;
COL
------------------------------------------------------------------------------------
1001 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":"1781"}}
1 row selected.
SQL> def col=CNCL_DT
SQL> select substr(col, 1, instr(col,' ')-1) contract_id,
2 substr(col,
3 instr(col, '"&col":') + length('&col')+4,
4 instr(col, '"', instr(col, '"&col":') + length('&col')+4)
5 - instr(col, '"&col":') - (length('&col')+4)
6 ) &col
7 from t
8 /
CONTRACT_ID CNCL_DT
----------- ------------------------
1001 2015-01-09T04:35:08.305Z
1 row selected.
SQL> def col=CNCLBY
SQL> /
CONTRACT_ID CNCLBY
----------- --------
1001 CONSUMER
1 row selected.
SQL> def col=DAY_REMAIN
SQL> /
CONTRACT_ID DAY_REMAIN
----------- ----------
1001 1781
1 row selected.
[Updated on: Thu, 19 February 2015 02:23] Report message to a moderator |
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633485 is a reply to message #633454] |
Thu, 19 February 2015 09:08 |
 |
as1986
Messages: 5
Registered: February 2015
|
Junior Member |
|
|
Hello
Thank you for your reply. I am sorry for not putting the formatted code before. Below is the formatted code and explanation in detail:
SQL> DROP TABLE TBL_A;
Table dropped.
SQL>
SQL> CREATE TABLE TBL_A(CONTRACT_ID NUMBER,CANCEL_QUOTE CLOB);
Table created.
SQL>
SQL> DESC TBL_A;
Name Null? Type
----------------------------------------- -------- ----------------------------
CONTRACT_ID NUMBER
CANCEL_QUOTE CLOB
SQL>
SQL> INSERT INTO TBL_A VALUES(1001, '{"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":1781}}');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> SELECT * FROM TBL_A ;
CONTRACT_ID CANCEL_QUOTE
---------------- ----------------------------------------------------------------
1001 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":1781}}
Basically the idea is to create a view on top of TBL_A so that it flattens out the value under CANCEL_QUOTE. So, the expected result would look something like:
-- Assuming the new view created is called as V_TBLA. So, selecting the view should give results something like
SQL> SELECT * FROM V_TBLA;
CONTRACT_ID CNCL_DT CNCLBY DAY_REMAIN
----------- ------------- ------------ ----------
1001 2015-01-09T04:35:08.305Z CONSUMER 1781
I am not sure if its possible but i want to avoid using hard coded column names since its a JSON format there could be records where CANCEL_QUOTE can up with more columns and column values. Like
SQL>
SQL> INSERT INTO
TBL_A VALUES(1002, '{"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":1781,"NEW_COL":1001}}');
--And Expected result should look like
SQL> SELECT * FROM V_TBLA;
CONTRACT_ID CNCL_DT CNCLBY DAY_REMAIN NEW_COL
----------- ------------- ------------ ---------- --------
1001 2015-01-09T04:35:08.305Z CONSUMER 1781 NULL
1002 2015-01-09T04:35:08.305Z CONSUMER 1781 1001
Thanks Again for helping me out with this!!
|
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633489 is a reply to message #633485] |
Thu, 19 February 2015 09:53 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
In SQL, column names are required (or defaulted from the expression which is the same), the only thing you can do is to name them col1, col2, col3...
Or you can have the columns contract_id, column_name and column_value and a row per "name":"value" in your string; rows you can pivot but once again to pivot in SQL you will have to know the column names.
Something like:
SQL> select contract_id,
2 trim(both '"' from regexp_substr(val,'"[^"]*"')) column_name,
3 trim(both '"' from regexp_substr(val,'"[^"]*"',1,2)) column_value
4 from ( select contract_id,
5 ltrim(rtrim(regexp_substr(replace(vals,',',',,'),'[{,][^,]*[,}]', 1, column_value),
6 ',}'), '{,') val
7 from ( select substr(col, 1, instr(col,' ')-1) contract_id,
8 regexp_substr(col, '{[^}]*}') vals
9 from t ) t,
10 table(cast(multiset(select level from dual connect by level <= regexp_count(vals,',')+1)
11 as sys.OdciNumberList))
12 )
13 /
CONTRACT_ID COLUMN_NAME COLUMN_VALUE
----------- --------------- ------------------------------
1001 CNCL_DT 2015-01-09T04:35:08.305Z
1001 CNCLBY CONSUMER
1001 DAY_REMAIN 1781
But wait a bit, maybe Solomon will come in a couple of hours with a solution using XML insterface.
|
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633490 is a reply to message #633485] |
Thu, 19 February 2015 09:58 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
With your second row (still with all values between " to simplify the code):
SQL> select * from t;
COL
-----------------------------------------------------------------------------------------------------------
1001 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":"1781"}}
1002 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":"1781","NEW_COL":"1001"}}
2 rows selected.
SQL> select contract_id,
2 trim(both '"' from regexp_substr(val,'"[^"]*"')) column_name,
3 trim(both '"' from regexp_substr(val,'"[^"]*"',1,2)) column_value
4 from ( select contract_id,
5 ltrim(rtrim(regexp_substr(replace(vals,',',',,'),'[{,][^,]*[,}]', 1, column_value),
6 ',}'), '{,') val
7 from ( select substr(col, 1, instr(col,' ')-1) contract_id,
8 regexp_substr(col, '{[^}]*}') vals
9 from t ) t,
10 table(cast(multiset(select level from dual connect by level <= regexp_count(vals,',')+1)
11 as sys.OdciNumberList))
12 )
13 /
CONTRACT_ID COLUMN_NAME COLUMN_VALUE
----------- --------------- ------------------------------
1001 CNCL_DT 2015-01-09T04:35:08.305Z
1001 CNCLBY CONSUMER
1001 DAY_REMAIN 1781
1002 CNCL_DT 2015-01-09T04:35:08.305Z
1002 CNCLBY CONSUMER
1002 DAY_REMAIN 1781
1002 NEW_COL 1001
7 rows selected.
|
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633491 is a reply to message #633490] |
Thu, 19 February 2015 10:06 |
|
as1986
Messages: 5
Registered: February 2015
|
Junior Member |
|
|
Thank you for your reply sir. I believe any value of number type is not coming with "" around it but that can be taken care of. As you said it is possible to split the column values to rows but i am not sure if there is way to split it as columns. I heard there is a way through XMl that this can be done which i am still trying to figure it out.
Thanks
[Updated on: Thu, 19 February 2015 10:10] Report message to a moderator |
|
|
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633533 is a reply to message #633489] |
Fri, 20 February 2015 15:26 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 19 February 2015 10:53
maybe Solomon will come in a couple of hours with a solution using XML insterface.
Well, XML will not help here. And frankly, it is not a good design to begin with. Anyway, dynamic number of columns can be dove via ODCI interface. Something like:
CREATE OR REPLACE
TYPE dynamic_column_type
AUTHID CURRENT_USER
AS OBJECT(
fetch_more varchar2(1),
tmt SYS.ANYTYPE,
num_cols INTEGER,
str VARCHAR2(4000),
STATIC FUNCTION ODCITableStart(
sctx IN OUT dynamic_column_type,
str IN VARCHAR2
)
RETURN PLS_INTEGER,
STATIC FUNCTION ODCITablePrepare(
sctx OUT dynamic_column_type,
tf_info IN SYS.ODCITabFuncInfo,
str IN VARCHAR2
)
RETURN PLS_INTEGER,
MEMBER FUNCTION ODCITableFetch(
self IN OUT dynamic_column_type,
nrows IN NUMBER,
objSet OUT SYS.ANYDATASET
)
RETURN PLS_INTEGER,
MEMBER FUNCTION ODCITableClose(
self IN dynamic_column_type
)
RETURN PLS_INTEGER,
STATIC FUNCTION ODCITableDescribe(
rtype OUT SYS.ANYTYPE,
str IN VARCHAR2
)
RETURN PLS_INTEGER
)
/
CREATE OR REPLACE
TYPE BODY dynamic_column_type
IS
STATIC FUNCTION ODCITableStart(
sctx IN OUT dynamic_column_type,
str IN VARCHAR2
)
RETURN PLS_INTEGER
IS
BEGIN
RETURN ODCICONST.SUCCESS;
END;
STATIC FUNCTION ODCITablePrepare(
sctx OUT dynamic_column_type,
tf_info IN SYS.ODCITabFuncInfo,
str IN VARCHAR2
)
RETURN PLS_INTEGER
IS
rtn number ;
prec PLS_INTEGER;
scale PLS_INTEGER;
len PLS_INTEGER;
csid PLS_INTEGER;
csfrm PLS_INTEGER;
elem_typ SYS.ANYTYPE;
aname VARCHAR2(30) ;
num_cols INTEGER;
BEGIN
num_cols := regexp_count(str,',|{|}') - 1;
rtn:=tf_info.RetType.GetAttreleminfo(
1,
prec,
scale,
len,
csid,
csfrm,
elem_typ,
aname
);
sctx:= dynamic_column_type('Y',elem_typ,num_cols,str);
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCITableFetch(
self IN OUT dynamic_column_type,
nrows IN NUMBER,
objSet OUT SYS.ANYDATASET
) RETURN PLS_INTEGER
IS
elem_typ SYS.ANYTYPE;
pair VARCHAR2(4000);
col_val VARCHAR2(30);
BEGIN
SYS.ANYDATASET.BeginCreate(
SYS.DBMS_TYPES.TYPECODE_OBJECT,
tmt,
objSet
);
IF fetch_more = 'Y'
THEN
fetch_more := 'N';
objSet.Addinstance;
objSet.PieceWise;
FOR i IN 1..self.num_cols LOOP
pair := regexp_substr('CONTRACT_ID:' || str,'[^,{}]+',1,i);
col_val := regexp_substr(pair,':"?(.+?)"?$',1,1,null,1);
objSet.SetVarchar2(
col_val,
CASE
WHEN i = self.num_cols
THEN
TRUE
ELSE
FALSE
END
);
END LOOP;
objSet.Endcreate;
ELSE
objSet:=null;
END IF;
RETURN ODCICONST.SUCCESS;
END;
MEMBER FUNCTION ODCITableClose(
self IN dynamic_column_type
) RETURN PLS_INTEGER
IS
BEGIN
RETURN ODCICONST.SUCCESS;
END;
STATIC FUNCTION ODCITableDescribe(
rtype OUT SYS.ANYTYPE,
str IN VARCHAR2
) RETURN PLS_INTEGER
IS
mt SYS.ANYTYPE;
tmt SYS.ANYTYPE;
num_cols INTEGER;
col_name VARCHAR2(30);
col_len INTEGER;
pair VARCHAR2(4000);
BEGIN
num_cols := regexp_count(str,',|{|}') - 1;
SYS.ANYTYPE.BeginCreate(
SYS.DBMS_TYPES.TYPECODE_OBJECT,
mt
);
FOR i IN 1..num_cols LOOP
pair := regexp_substr('CONTRACT_ID:' || str,'[^,{}]+',1,i);
col_name := regexp_substr(pair,'^"?(.+?)"?:',1,1,null,1);
col_len := length(regexp_substr(pair,':"?(.+?)"?$',1,1,null,1));
mt.AddAttr(
col_name,
SYS.DBMS_TYPES.TYPECODE_VARCHAR2,
NULL,
NULL,
col_len,
NULL,
NULL
);
END LOOP;
mt.EndCreate;
SYS.ANYTYPE.BeginCreate(
SYS.DBMS_TYPES.TYPECODE_NAMEDCOLLECTION,
tmt
);
tmt.SetInfo(
NULL,
NULL,
NULL,
NULL,
NULL,
mt,
DBMS_TYPES.TYPECODE_OBJECT,
0
);
tmt.EndCreate;
rtype:=tmt;
RETURN ODCICONST.SUCCESS;
END;
END;
/
CREATE OR REPLACE
FUNCTION dynamic_column(p_str VARCHAR2) RETURN ANYDATASET
PIPELINED
USING dynamic_column_type;
/
Now:
SQL> select *
2 from table(
3 dynamic_column(
4 '1001 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":1781}}'
5 )
6 )
7 /
CONTR CNCL_DT CNCLBY DAY_
----- ------------------------ -------- ----
1001 2015-01-09T04:35:08.305Z CONSUMER 1781
SQL> select *
2 from table(
3 dynamic_column(
4 '1002 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":1781,"NEW_COL":1001}}'
5 )
6 )
7 /
CONTR CNCL_DT CNCLBY DAY_ NEW_
----- ------------------------ -------- ---- ----
1002 2015-01-09T04:35:08.305Z CONSUMER 1781 1001
SQL>
SY.
|
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633536 is a reply to message #633533] |
Fri, 20 February 2015 15:43 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:Anyway, dynamic number of columns can be dove via ODCI interface.
Yes, I have a package that does that (and many other things like PIVOT and TRANSPOSE tables with dynamic columns, see below) but it raises many questions about SGA and persistence of the query in the SQL area which may lead to wrong results.
select *
from table(pkg_pivot.pivot(
'(select deptno, sal, comm, extract(year from hiredate) year from EMP)
pivot (sum(sal+nvl(comm,0)) "sumsal"
for (deptno) in (select deptno from dept order by deptno desc))'
))
order by year
/
YEAR sumsal_40 sumsal_30 sumsal_20 sumsal_10
---------- ---------- ---------- ---------- ----------
1980 800
1981 11600 5975 7450
1982 1300
1987 4100
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (year,deptno)
in (any ASC, any DESC)
)'
))
/
1980_20 1981_30 1981_20 1981_10 1982_10 1987_20
---------- ---------- ---------- ---------- ---------- ----------
800 11600 5975 7450 1300 4100 |
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633564 is a reply to message #633536] |
Sat, 21 February 2015 10:07 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Try this:
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (year,deptno)
in (any ASC, any DESC)
)'
))
/
DELETE EMP WHERE extract(year from hiredate) = 1981
/
SELECT *
from table(pkg_pivot.pivot(
'(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
pivot (
sum(sal+nvl(comm,0))
for (year,deptno)
in (any ASC, any DESC)
)'
))
/
SY.
|
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633567 is a reply to message #633564] |
Sat, 21 February 2015 14:09 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> SELECT *
2 from table(pkg_pivot.pivot(
3 '(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
4 pivot (
5 sum(sal+nvl(comm,0))
6 for (year,deptno)
7 in (any ASC, any DESC)
8 )'
9 ))
10 /
1980_20 1981_30 1981_20 1981_10 1982_10 1987_20
---------- ---------- ---------- ---------- ---------- ----------
800 11600 5975 7450 1300 4100
1 row selected.
SQL> DELETE EMP WHERE extract(year from hiredate) = 1981
2 /
10 rows deleted.
SQL> SELECT *
2 from table(pkg_pivot.pivot(
3 '(SELECT deptno, sal, extract(year from hiredate) year, comm from emp)
4 pivot (
5 sum(sal+nvl(comm,0))
6 for (year,deptno)
7 in (any ASC, any DESC)
8 )'
9 ))
10 /
1980_20 1981_30 1981_20 1981_10 1982_10 1987_20
---------- ---------- ---------- ---------- ---------- ----------
800 1300 4100
1 row selected.
This is what I said, due to the persistence of the SQL in the SQL Area some steps are not executed (I wrote this 3 years ago, the package also works in 10g, I think they are ODCITableDescribe and ODCITablePrepare but I'm not sure).
Yes, there is a workaround, it is to flush out the sql from the SGA in ODCITableClose using DBMS_SHARED_POOL.PURGE (if it is possible, something I didn't try, as you're trying to flush out the statement that is currently executed) but then you lose all the work you did and have to do it again in the next call which may be expensive if the query is complex and contains subqueries and is often executed.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633775 is a reply to message #633533] |
Tue, 24 February 2015 10:33 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
@SY,
I get some strange thing with your test case.
Here's mine:
create table TBL_A (val varchar2(4000));
truncate table TBL_A;
insert into TBL_A values ('1001 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":1781}}');
insert into TBL_A values ('1002 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":1781,"NEW_COL":1001}}');
commit;
SQL> select *
2 from TBL_A a, table(dynamic_column(a.val))
3 where substr(a.val, 1, 4) = '1001'
4 /
select *
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MICHEL.DYNAMIC_COLUMN_TYPE", line 107
ORA-06512: at line 4
SQL> select *
2 from table(dynamic_column((select val from TBL_A where substr(val, 1, 4) = '1001')))
3 /
select *
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "MICHEL.DYNAMIC_COLUMN_TYPE", line 107
ORA-06512: at line 4
But using the value as a constant it works:
SQL> select val from TBL_A where substr(val, 1, 4) = '1001'
2 ;
VAL
---------------------------------------------------------------------------------------------------------------------
1001 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":1781}}
1 row selected.
SQL> select *
2 from table(dynamic_column('1001 {"CNCL_DT":"2015-01-09T04:35:08.305Z","CNCLBY":"CONSUMER","DAY_REMAIN":1781}}'))
3 /
CONTR CNCL_DT CNCLBY DAY_
----- ------------------------ -------- ----
1001 2015-01-09T04:35:08.305Z CONSUMER 1781
1 row selected.
SQL> @v
Version Oracle : 11.2.0.1.0
[Updated on: Tue, 24 February 2015 10:35] Report message to a moderator |
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #633783 is a reply to message #633775] |
Tue, 24 February 2015 14:04 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Obviously it will fail. ODCITableDescribe is:
a) called on hard parse only
b) only LITERAL parameter values are passed to ODCITableDescribe. Otherwise NULL is passed (which is understandable - we are just parsing and we can't execute pieces of query yet)
So ODCITableDescribe is passed NULL and not results of select val from TBL_A where substr(val, 1, 4) = '1001'). And we end up with NULL number of columns.
SY.
|
|
|
|
|
|
|
|
|
|
| Re: Split CLOB column to multiple columns within a row [message #635482 is a reply to message #633806] |
Mon, 30 March 2015 23:43 |
|
as1986
Messages: 5
Registered: February 2015
|
Junior Member |
|
|
|
Sorry to get back late on this. Since we are currently on 11g which doesn't support json but supports xml type data. The solution we came up with is to use perl and convert the json value to xml type within the incoming file and used sql loader to load it. And now we create a view on top of the table which can parse XML type data stored in the table. Thanks again to every one who tried to help me out with this.
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 20:16:34 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
