Home » SQL & PL/SQL » SQL Experts » Break the Lines from a table and insert into new table (Oracle 9i)
| Break the Lines from a table and insert into new table [message #305566] |
Tue, 11 March 2008 05:12  |
jayarago
Messages: 10
Registered: December 2006
Location: Chennai |
Junior Member |
|
|
Hi,
I am working in oracle9i . i am having table (TEST) with datatype as Long
TEST Table contains :
$$Last Run Summary Report: Filename$$Interface: filename$$loads the data$$Step :$$ Link File = file.knx$$Connect 5.1.6$$Last Run Summary Report: TSG_DAN_REPORT.LRS
$$Link: TSG_DAN_REPORT.KNX$$ PC name: OHCVGB01PKRON$$ Working directory: $$F:\Kronos\Connect\Interfaces\TSGProjectAllocation$$ Start time: Wednesday, February 27, 2008 at 8:16 AM$$ End time: Wednesday, February 27, 2008 at 8:16 AM
$$ Elapsed time: 0:00:00$$$$ Run from: Command line$$ Source file(s): ../../Data/Source/TSG_DAN_ALLOCATION.CSV$$ Created Wednesday, February 27, 2008 at 8:16 AM$$Selected options: Delimited by ','$$ Number of records to skip: 1$$ Output file: ../../Data/Output/TSG_DAN_ERROR_LOG.CSV$$Selected options: Delimited by ','$$There were 1 records processed.$$ 2 records created.
$$ 0 errors.$$ 0 exclusions.$$=================================================
$$ - is the static value in the TEST Table
i need the data after $$contains Last Run Summary Report:(filename) and $$Interface: (filename)
Kindly suggest me the optimized way to proceed.
|
|
|
| Re: Break the Lines from a table and insert into new table [message #305577 is a reply to message #305566 ] |
Tue, 11 March 2008 05:23  |
Michel Cadot
Messages: 14937
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
Use PL/SQL to convert to VARCHAR2 and use SUBSTR, INSTR functions.
If data are longer than 32K, you have to create your own program in C, Java or whatever.
Regards
Michel
|
|
|
| Re: Break the Lines from a table and insert into new table [message #308549 is a reply to message #305566 ] |
Mon, 24 March 2008 15:29 |
Kevin Meade
Messages: 323
Registered: November 2001 |
Senior Member |
|
|
try dbms_long. No matter how you slice and dice, you will have to write some code for this one. Longs have always been a pain in the butt.
You might also wish to google for "how to convert a long to a clob". In the end though if your value is longer than 32k-1 bytes, you will still have to write some nasty code. you would want to google up DBMS_LOB at this point.
Good luck, longs suck, Kevin
|
|
|
| Re: Break the Lines from a table and insert into new table [message #308550 is a reply to message #308549 ] |
Mon, 24 March 2008 15:52 |
Michel Cadot
Messages: 14937
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
I think you meant DBMS_LOB but it does not work on LONG datatype.
Regards
Michel
|
|
|
| Re: Break the Lines from a table and insert into new table [message #308551 is a reply to message #305566 ] |
Mon, 24 March 2008 16:20 |
Kevin Meade
Messages: 323
Registered: November 2001 |
Senior Member |
|
|
no, I meant dbms_long, but I was being stupid. There never was a dbms_long package. In my haste I got confused.
LET ME REDEEM MYSELF
There was a time when working with longs longer than 32k-1 bytes was done using DBMS_SQL. Here is a post from ASKTOMHOME that shows you how. Pay particular attention to the DBMS_SQL.COLUMN_LONG_VALUE call. This is essentially how to get substrings from longs. I believe there was someone who wrote a utility package using DBMS_SQL to work with longs but unfortunately I can't find it anywhere. Maybe someone else knows where it is?
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:665224430110
Sorry for the confusion, thanks for the corrections, longs still suck, Kevin
|
|
|
| Re: Break the Lines from a table and insert into new table [message #308556 is a reply to message #305566 ] |
Mon, 24 March 2008 18:41 |
 |
Barbara Boehmer
Messages: 3926
Registered: November 2002
Location: California, USA |
Senior Member |
|
|
If you are allowed to modify the column type, then you can:
ALTER TABLE test MODIFY (your_column CLOB);
to change the datatype, then you can select from it easily. Otherwise, you can use CREATE TABLE AS SELECT ... TO_LOB ... as demonstrated below, to create a temporary or staging table, then select from that. This is only one of many methods. It is fairly simple and although I have tested on 11g, I think I have remembered correctly what would work in 9i.
SCOTT@orcl_11g> CREATE TABLE test
2 (id NUMBER,
3 your_column LONG)
4 /
Table created.
SCOTT@orcl_11g> INSERT INTO test (id, your_column) VALUES (1,
2 '$$Last Run Summary Report: Filename$$Interface: filename$$loads the data$$Step :$$ Link File = file.knx$$Connect 5.1.6$$Last Run Summary Report: TSG_DAN_REPORT.LRS
3 $$Link: TSG_DAN_REPORT.KNX$$ PC name: OHCVGB01PKRON$$ Working directory: $$F:\Kronos\Connect\Interfaces\TSGProjectAllocation$$ Start time: Wednesday, February 27, 2008 at 8:16 AM$$ End time: Wednesday, February 27, 2008 at 8:16 AM
4 $$ Elapsed time: 0:00:00$$$$ Run from: Command line$$ Source file(s): ../../Data/Source/TSG_DAN_ALLOCATION.CSV$$ Created Wednesday, February 27, 2008 at 8:16 AM$$Selected options: Delimited by '',''$$ Number of records to skip: 1$$ Output file: ../../Data/Output/TSG_DAN_ERROR_LOG.CSV$$Selected options: Delimited by '',''$$There were 1 records processed.$$ 2 records created.
5 $$ 0 errors.$$ 0 exclusions.$$=================================================')
6 /
1 row created.
SCOTT@orcl_11g> CREATE TABLE temp_table AS
2 SELECT id, TO_LOB (your_column) temp_column
3 FROM test
4 /
Table created.
SCOTT@orcl_11g> DESCRIBE temp_table
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
TEMP_COLUMN CLOB
SCOTT@orcl_11g> CREATE TABLE new_table
2 (id NUMBER,
3 line NUMBER,
4 new_column CLOB)
5 /
Table created.
SCOTT@orcl_11g> INSERT INTO new_table (id, line, new_column)
2 SELECT id, rn,
3 DBMS_LOB.SUBSTR
4 (temp_column || '$$',
5 DBMS_LOB.INSTR (temp_column || '$$', '$$', 1, rn + 1)
6 - DBMS_LOB.INSTR (temp_column || '$$', '$$', 1, rn) - 2,
7 DBMS_LOB.INSTR (temp_column || '$$', '$$', 1, rn) + 2)
8 FROM temp_table,
9 (SELECT ROWNUM rn
10 FROM DUAL
11 CONNECT BY LEVEL <= 1000)
12 WHERE DBMS_LOB.INSTR (temp_column, '$$', 1, rn) > 0
13 /
28 rows created.
SCOTT@orcl_11g> SELECT * FROM new_table ORDER BY id, line
2 /
ID LINE NEW_COLUMN
---------- ---------- ------------------------------------------------------------
1 1 Last Run Summary Report: Filename
1 2 Interface: filename
1 3 loads the data
1 4 Step :
1 5 Link File = file.knx
1 6 Connect 5.1.6
1 7 Last Run Summary Report: TSG_DAN_REPORT.LRS
1 8 Link: TSG_DAN_REPORT.KNX
1 9 PC name: OHCVGB01PKRON
1 10 Working directory:
1 11 F:\Kronos\Connect\Interfaces\TSGProjectAllocation
1 12 Start time: Wednesday, February 27, 2008 at 8:16 AM
1 13 End time: Wednesday, February 27, 2008 at 8:16 AM
1 14 Elapsed time: 0:00:00
1 15
1 16
1 17 Run from: Command line
1 18 Source file(s): ../../Data/Source/TSG_DAN_ALLOCATION.CSV
1 19 Created Wednesday, February 27, 2008 at 8:16 AM
1 20 Selected options: Delimited by ','
1 21 Number of records to skip: 1
1 22 Output file: ../../Data/Output/TSG_DAN_ERROR_LOG.CSV
1 23 Selected options: Delimited by ','
1 24 There were 1 records processed.
1 25 2 records created.
1 26 0 errors.
1 27 0 exclusions.
1 28 =================================================
28 rows selected.
SCOTT@orcl_11g>
|
|
|
Goto Forum:
Current Time: Fri May 9 09:48:59 CDT 2008
Total time taken to generate the page: 0.08106 seconds |
]
Members
Search
Help
Register
Login
Home
