Normalising Legacy Data 3

Welshbird · Aug 29, 2007

I have an old legacy data-producing system that provides we data with some identifiers, and then 48 columns. The first 24 columns show units, and then the next 24 show values.

I had thought that maybe I could normalise these when loading with SQLLDR, but as these are comma-seperated rather than positional I thought maybe it would be best to load these into a temp table and then run a PL/SQL Loop to normalise them.

So, my loaded table looks like this:

Code:

GEOGRAPHY             CHAR(5)
CEG_PROD_CODE      VARCHAR2(14)
UM1                NUMBER(38)
UM2                NUMBER(38)
...
UM23               NUMBER(38)
UM24               NUMBER(38)
VM1                NUMBER
VM2                NUMBER
...
VM23               NUMBER
VM24               NUMBER
DATE_LOADED        DATE

The months that are provided in each of the 24 columns are a movable feast, but the month is loaded elsewhere, so I have at least worked out how to grab that.

This is what I have begun with:

Code:

DECLARE
 fee date;
 BEGIN
   select add_months(period_number,-24) into fee from ff_header;
      for i in 1..24
 loop
 insert into FF_board
 (GEOGRAPHY,CEG_CODE,MNTH,UNIT,VALUE)
 VALUES
 (select A.GEOGRAPHY, SUBSTR(A.CEG_PROD_CODE, 2,13),
 (SELECT TO_CHAR(ADD_MONTHS(FEE,i),'YYMM') FROM DUAL;),
 a.UM1, a.VM1
 from
 ff_results a, ff_territory b
 where a.brick = b.brick);
 end loop;
 end;

but already this doesn't work. I get the following error:
ERROR at line 10:
ORA-06550: line 10, column 3:
PL/SQL: ORA-00936: missing expression
ORA-06550: line 7, column 2:
PL/SQL: SQL Statement ignored
ORA-06550: line 12, column 54:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
begin case declare end exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier

And I still haven't got my brain around the way to pick the column names in some kind of loopy-cursor type deal.

I strikes me that someone else must have done this before - so any pointers you guys could give me would be really helpful.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

Welshbird · Aug 30, 2007

At the moment we don't know how often it will be run, but it's quite likely it will be weekly eventually.

I have a SQLLDR process that picks up the data from a FTP and loads it, and truncates this table at the same time, and so someone (even less techie than me) will be able to do that. and then run this without having to maintain any code. Or at least, that is the idea.

Tharg has just suggested to me that running this code in SQLDeveloper in debug mode may be a good plan, so I'm going to give that a go.

Thanks for all help so far guys!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

jimirvine · Aug 30, 2007

So just to confirm, the source table will have a rolling 24 months' worth of data i.e. starting at the load month and going back 24 months. and each week, you will truncate the target table and re-load from the source table(s)

Welshbird · Aug 30, 2007

Yes. And the load month (ie most recent month of data) is found in the field period_number in the table called ff_header

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

jimirvine · Aug 30, 2007

Final bit of confirmation, would the load month relate to vm1 or vm24?

jimirvine · Aug 30, 2007

OK, you will probably need to tweak a bit, but this should pretty much do it for you:

Code:

CREATE OR REPLACE PROCEDURE load_data IS
l_str clob:= 'INSERT ALL ';
start_date DATE := sysdate;
BEGIN

  SELECT period_number
  INTO start_date
  FROM ff_header;

  FOR i IN 1..24 LOOP
    
    l_str := l_str||' INTO FF_BOARD
                      VALUES (A.AREA
                            , A.REGION
                            , A.TERRITORY
                            , B.UGA_SECT
                            , B.REGION
                            , B.TERRITORY
                            , A.BRICK
                            , CG
                            , ''' ||TO_CHAR(ADD_MONTHS(start_date,-i),'YYMM')||
                         ''', a.UM'||i||
                            ', a.VM'||i||
                            ', sysdate)}';
  END LOOP;

  l_str := l_str||'SELECT   A.AREA
                          , A.REGION
                          , A.TERRITORY
                          , B.UGA_SECT
                          , B.REGION
                          , B.TERRITORY
                          , A.BRICK
                          , (SUBSTR(A.CEG_PROD_CODE, 2,13)) CG
                          , a.UM1
                          , a.VM1
                          , a.UM3
                          , a.VM3
                          , a.UM4
                          , a.VM4
                          , sysdate
                     FROM  ff_results a
                     JOIN ff_territory b
                     ON  a.brick = b.brick';
 dbms_output.PUT_LINE(l_str);
 end;

once the output string is correct, replace
dbms_output.PUT_LINE(l_str);
with
EXECUTE IMMEDIATE l_str;

HTH
Jim

Welshbird · Aug 30, 2007

Thanks very much for that Jim - just going to have a go now!

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

Welshbird · Aug 30, 2007

Ok - message:

Procedure created.

Elapsed: 00:00:00.30
PHARGREAVES>exec load_data
BEGIN load_data; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PHARGREAVES.LOAD_DATA", line 12
ORA-06512: at line 1

which seems to be the same issue I had with my (failed) attempt.

I'm so sure this is something scarily obvious - But I just cannot find it.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

jimirvine · Aug 30, 2007

OK, It works fine for me. a couple or 3 questions.
1. What version are you on?
2. Did you copy and paste my code exctly, or did you tweak it?
3. What datatype is the period_number column in the ff_header table?

Welshbird · Aug 30, 2007

1. Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
(copied directly just in case!)
2. I copied and pasted it directly. I thought that was best, and if I needed a tweak I could have tried later.
3.
PHARGREAVES>desc ff_header
Name Type
-------------------------------------------------
FH_TYPE CHAR(2)
CLIENT_CODE NUMBER
STUDY_ID VARCHAR2(5)
STUDY_LABEL VARCHAR2(30)
PERIOD_TYPE CHAR(1)
PERIOD_NUMBER DATE
DATA_TYPE CHAR(1)
DISPATCH_MODE CHAR(1)
CURRENCY CHAR(3)
DETAIL CHAR(1)
TERRITORY_STRUCTURE NUMBER
STRUCTURE_NAME VARCHAR2(30)
GEO_AGGREGATES VARCHAR2(5)
DATE_LOADED DATE

Seems to be a date.

(Thanks again)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

jimirvine · Aug 30, 2007

Hi,
can you try commenting out the select into statement and give it another try?

Welshbird · Aug 30, 2007

I've tried that, and I get a compilation error now.

Errors for PROCEDURE LOAD_DATA:

LINE/COL ERROR
-------- -----------------------------------------------------------------
25/3 PLS-00103: Encountered the symbol "END" when expecting one of the
following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
<a single-quoted SQL string> pipe
<an alternatively-quoted SQL string>

Phoey.

(Did you ever wish you hadn't started something?)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

Welshbird · Aug 30, 2007

AH!

I've just been searching about, and I found this page here:

http://forums.oracle.com/forums/thread.jspa?threadID=379327

This seems to say that this error occurs when one truncates a date.

Which is exactly what I am doing.

So, I'm going to test using the real date, and then I can always update a new column later - at least it will prove if it is the problem.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

Welshbird · Aug 30, 2007

Nope... that didn't help.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

jimirvine · Aug 30, 2007

Hi sorry, I'm not contributing just now. Got an issue with one of my report servers. I'll get back to yo.

Welshbird · Aug 30, 2007

Jim - you've helped loads - Thanks for all of it, and don't worry about me for a bit - I'll keep pluggin' away.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

jimirvine · Aug 31, 2007

Hi,
Your second error (the one 'encountered symbol end ....') Doesn't make sense really.
Can you try the following and post the results:

Code:

create or replace
PROCEDURE load_data IS
l_str clob:= 'INSERT ALL ';
start_date DATE := sysdate;
BEGIN

  FOR i IN 1..24 LOOP
    
    l_str := l_str||' INTO FF_BOARD
                      VALUES (A.AREA
                            , A.REGION
                            , A.TERRITORY
                            , B.UGA_SECT
                            , B.REGION
                            , B.TERRITORY
                            , A.BRICK
                            , CG
                            , ''' ||TO_CHAR(ADD_MONTHS(start_date,-i),'YYMM')||
                         ''', a.UM'||i||
                            ', a.VM'||i||
                            ', sysdate)}';
  END LOOP;

  l_str := l_str||'SELECT   A.AREA
                          , A.REGION
                          , A.TERRITORY
                          , B.UGA_SECT
                          , B.REGION
                          , B.TERRITORY
                          , A.BRICK
                          , (SUBSTR(A.CEG_PROD_CODE, 2,13)) CG
                          , a.UM1
                          , a.VM1
                          , a.UM3
                          , a.VM3
                          , a.UM4
                          , a.VM4
                          , sysdate
                     FROM  ff_results a
                     JOIN ff_territory b
                     ON  a.brick = b.brick';
 dbms_output.PUT_LINE(l_str);
 end;

And the link that you posted pertains to a PLW warning which is common when passing sysdate or (as you say) a truncated date as these are slightly different from 'normal' dates. It doesn't mention your PLS-06502.
1 other question, how many rows in the ff_header table?

Welshbird · Aug 31, 2007

Procedure created.

Elapsed: 00:00:00.12
PHARGREAVES>exec load_data
BEGIN load_data; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "PHARGREAVES.LOAD_DATA", line 8
ORA-06512: at line 1

The ff_header table only has one row. The original file I have has a header record, then 70-odd records of product information, then 87000-odd records of results. The Sqlldr process splits this file into the ff_header, ff_market(which we don't need at all for this purpose) and the other records get put into ff_results.

I have recreated ff_results to ensure that UM1..24 and VM1..24 columns are all number and are all not null.

And I've tried removing the sysdate and the column it would be inserted into incase that was the issue. Makes no difference.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

hoinz · Aug 31, 2007

Hello,

I think this ORA-06502: PL/SQL: numeric or value error comes from an overflow in l_str;
For a test leave out the many blanks at the beginning of the lines;
and maybe try with a shorter loop. (2 instead of 24, e.g.)

hope this helps

jimirvine · Aug 31, 2007

Hoinz, the variable l_str is of clob datatype, I doubt that it's an overflow. (That was my first thought too

, that's why I changed it to a clob)

Fee, are you still using dbms_output.put_line or have you changed it to execute immediate?
can you try the following and see what happens:

Code:

create or replace
PROCEDURE load_data IS
l_str clob:= 'INSERT ALL ';
start_date DATE := sysdate;
BEGIN

  FOR i IN 1..24 LOOP
    
    l_str := l_str||' INTO FF_BOARD
                      VALUES (A.AREA'||
                            ', A.REGION'||
                            ', A.TERRITORY'||
                            ', B.UGA_SECT'||
                            ', B.REGION'||
                            ', B.TERRITORY'||
                            ', A.BRICK'||
                            ', CG'||
                            ', ''' ||TO_CHAR(ADD_MONTHS(start_date,-i),'YYMM')||
                         ''', a.UM'||i||
                            ', a.VM'||i||
                            ', sysdate)
                            ';
  END LOOP;

  l_str := l_str||'SELECT   A.AREA'||
                          ', A.REGION'||
                          ', A.TERRITORY'||
                          ', B.UGA_SECT'||
                          ', B.REGION'||
                          ', B.TERRITORY'||
                          ', A.BRICK'||
                          ', (SUBSTR(A.CEG_PROD_CODE, 2,13)) CG'||
                          ', a.UM1'||
                          ', a.VM1'||
                          ', a.UM3'||
                          ', a.VM3'||
                          ', a.UM4'||
                          ', a.VM4'||
                          ', sysdate'||
                     'FROM  ff_results a'||
                     'JOIN ff_territory b'||
                     'ON  a.brick = b.brick';
 
 end;

(I had also left a Brace in the string which would have caused issues later.

jimirvine · Aug 31, 2007

sorry, I should have added in, that I will try your suggestion Hoinze. Cheers

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Normalising Legacy Data 3

IS-IT--Management

IS-IT--Management

Instructor

IS-IT--Management

Instructor

Instructor

IS-IT--Management

IS-IT--Management

Instructor

IS-IT--Management

Instructor

IS-IT--Management

IS-IT--Management

IS-IT--Management

Instructor

IS-IT--Management

Instructor

IS-IT--Management

MIS

Instructor

Instructor

Similar threads

Log in

Part and Inventory Search

Sponsor