Re: SQL help

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Fri, 08 Jan 1999 00:00:26 GMT
Message-ID: <369749bd.28258403@inet16.us.oracle.com>

On Thu, 7 Jan 1999 10:02:28 -0000, "Martin Lynch" <mlynch_at_bfsec.bt.co.uk> wrote:

>Hi
>
>Im fairly new to oracle and ive been asked to do something like this.
>
>Define a table (table A) which has one column, but this column contains 1 or
>more fields concatenated.
>
>Then define another table (table B) which describes the first table in terms
>of field length and type etc.
>
>EG TABLE A
> row1 1234567890 (10 chars in the column)
>
> TABLE B
>row1 field 1 length 4
>row2 field 2 length 6
>
>
>A program would then be written which would look at table B and get the data
>definations in it and use this to manipulate the data in table A and display
>it in thenecessary form. Something like this
>
>OUTPUT
>part number 1234
>amount available 67890
>
>It is this program i need help with. Hope some one can help
>
>marty
>

something like this might do the trick...

given the tables

SQL> create table A(
  2 data varchar2(10) )
  3 /
Table created.

SQL> create table B(
  2 field number,
  3 len number )
  4 /
Table created.

we populate them with...

SQL> insert into a values ( '1234567890' ); 1 row created.

SQL> insert into a values ( '0987654321' ); 1 row created.

SQL> insert into a values ( 'ABCDEFGHIJ' ); 1 row created.

SQL> insert into b values ( 1, 4 );
1 row created.

SQL> insert into b values ( 2, 6 );
1 row created.

We will use a type like...

SQL> create or replace
  2 type sizeTable as table of number
  3 /
Type created.

Then the procedure...

SQL> create or replace
  2 procedure get_data as
  3 l_start number;
  4 l_sizes sizeTable := sizeTable();   5 begin
  6 for c in ( select len

  7                 from b
  8                order by field )
  9    loop
 10      l_sizes.extend;
 11      l_sizes(l_sizes.count) := c.len;

 12 end loop;
 13
 14 dbms_output.put_line( 'OUTPUT' );  15 for c in ( select data from a ) loop

 16      l_start := 1;
 17      for i in 1 .. l_sizes.count loop
 18        dbms_output.put( substr( c.data, l_start, l_sizes(i) ) || '
' );
 19        l_start := l_sizes(i)+1;
 20      end loop;
 21      dbms_output.new_line;

 22 end loop;
 23 end get_data;
 24 /
Procedure created.

gives us this result.

SQL> exec get_data
OUTPUT

1234     567890
0987     654321
ABCD     EFGHIJ

PL/SQL procedure successfully completed.

hope this helps.

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jan 07 1999 - 18:00:26 CST