Home » SQL & PL/SQL » SQL & PL/SQL » 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 Go to next message
jayarago
Messages: 12
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
try dbms_long.

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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
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 Go to previous message
Barbara Boehmer
Messages: 8620
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> 


Previous Topic: mutating trigger function
Next Topic: update records
Goto Forum:
  


Current Time: Fri Dec 02 12:07:40 CST 2016

Total time taken to generate the page: 0.09454 seconds