Custom function? 1

[DugsDMan]

I have an Oracle table that contains information on the control cards on our mainframe. The data in it looks like this:
[tt]
ROW_ID State Cust Type ID DETAIL_DESC
------ ----- ---- ---- --- -------------------------------------
347621 AR COA A 10 SIGNON ESF=YES
347622 AR COA A 20 SUBMIT PROC=TAPESEND SNODE=BLAH.D.BLAH
347623 AR COA A 30 HOLD=NO
347624 AR COA A 40 SNODEID=(MY,ID)
347625 AR COA A 50 RETAIN=NO
347626 AR COA A 60 &NODE=PNODE
347627 AR COA A 70 &DSN1=A.B.C.D.ECOAARAH.NDM.BDTFILE(0)
347628 AR COA A 80 &DSN2=A.B.C.D.T9533.RARK.WCM(+1)
347629 AR COA A 90 &DISP2=(NEW,CATLG,DELETE)
347630 AR COA A 100 SIGNOFF
[/tt]

What I need to do is create an output file that looks like this:
[tt]
./* ADD NAME=ECOAARA
SIGNON ESF=YES
SUBMIT PROC=TAPESEND SNODE=BLAH.D.BLAH -
HOLD=NO -
SNODEID=(MY,ID) -
RETAIN=NO -
&NODE=PNODE -
&DSN1=A.B.C.D.ECOAARAH(0) -
&DSN2=A.B.C.D.T9533.RARK.WCM(+1) -
&DISP2=(NEW,CATLG,DELETE)
SIGNOFF
[/tt]

I imagine most of this would not be too difficult, using a function, to process each row and see if it begins with SIGNON, SIGNOFF, or SUBMIT. If not, then indent, check to see if a dash is needed, etc. However, I also need to create the line that has the NAME parameter in it, then process that line to create the variable (which is equal to E & Cust & State & Type) and also create the SIGNON card or whatever is on the first line.

Does anyone have an opinion on where I should start looking into the best way to do this? Possibly a link to good function tutorials or something?

Thanks for any help!

 

[gunjan14]

Hi,
You mean something like this

create or replace function return_formatted (g_name in varchar2) 
return varchar2
as
    lStr   varchar2(1000);
    lStr1  varchar2(50); 
    lCount number;
    lcntr  number;
begin
    lStr := './*   ADD          NAME=E'||g_name;
    select detail_desc into lStr1
    from   test1 
    where  (cust||state||type) = g_name
    and    upper(detail_desc) like 'SIGNON%';
    lStr := lstr||chr(10)||lStr1;

    select detail_desc into lStr1
    from   test1 
    where  (cust||state||type) = g_name
    and    upper(detail_desc) like 'SUBMIT%';
    lStr := lstr||chr(10)||lStr1;

    select count(*) into lcount
    from   test1
    where  (cust||state||type) = g_name
    and    upper(detail_desc) not like 'SIGNON%'
    and    upper(detail_desc) not like 'SUBMIT%'
    and    upper(detail_desc) not like 'SIGNOFF%';

    lcntr := 0;
    for r in (select detail_desc 
              from test1 
              where (cust||state||type) =  g_name
              and    upper(detail_desc) not like 'SIGNON%'
              and    upper(detail_desc) not like 'SUBMIT%'
              and    upper(detail_desc) not like 'SIGNOFF%') loop
        lcntr := lcntr + 1;
        if lcntr = lcount then
            lStr := lstr||chr(10)||'    '||r.detail_desc;
        else
            lStr := lstr||chr(10)||'    '||r.detail_desc||'  - ';
        end if;
    end loop;

    select detail_desc into lStr1
    from   test1 
    where  (cust||state||type) = g_name
    and    upper(detail_desc) like 'SIGNOFF%';
    lStr := lstr||chr(10)||lStr1;

    return lStr;

end;
/

Now take the example

create table test1
(
    row_id      number,
    state       varchar2(5),
    cust        varchar2(5),
    type        varchar2(5),
    id          number,
    detail_desc varchar2(50)
);

insert into test1 values (347621, 'AR', 'COA', 'A', 10, 'SIGNON ESF=YES');
insert into test1 values (347622, 'AR', 'COA', 'A', 20, 'SUBMIT PROC=TAPESEND SNODE=BLAH.D.BLAH');
insert into test1 values (347623, 'AR', 'COA', 'A', 30, 'HOLD=NO');
insert into test1 values (347624, 'AR', 'COA', 'A', 40, 'SNODEID=(MY,ID)');
insert into test1 values (347625, 'AR', 'COA', 'A', 50, 'RETAIN=NO');
insert into test1 values (347626, 'AR', 'COA', 'A', 60, 'NODE=PNODE');
insert into test1 values (347627, 'AR', 'COA', 'A', 70, 'DSN1=A.B.C.D.ECOAARAH.NDM.BDTFILE(0)');
insert into test1 values (347628, 'AR', 'COA', 'A', 80, 'DSN2=A.B.C.D.T9533.RARK.WCM(+1)');
insert into test1 values (347629, 'AR', 'COA', 'A', 90, 'DISP2=(NEW,CATLG,DELETE)');
insert into test1 values (347630, 'AR', 'COA', 'A', 100, 'SIGNOFF');

commit;

SQL> col detail_desc format a40
SQL> select * from test1;
    ROW_ID STATE CUST  TYPE          ID DETAIL_DESC
---------- ----- ----- ----- ---------- ----------------------------------------
    347621 AR    COA   A             10 SIGNON ESF=YES
    347622 AR    COA   A             20 SUBMIT PROC=TAPESEND SNODE=BLAH.D.BLAH
    347623 AR    COA   A             30 HOLD=NO
    347624 AR    COA   A             40 SNODEID=(MY,ID)
    347625 AR    COA   A             50 RETAIN=NO
    347626 AR    COA   A             60 NODE=PNODE
    347627 AR    COA   A             70 DSN1=A.B.C.D.ECOAARAH.NDM.BDTFILE(0)
    347628 AR    COA   A             80 DSN2=A.B.C.D.T9533.RARK.WCM(+1)
    347629 AR    COA   A             90 DISP2=(NEW,CATLG,DELETE)
    347630 AR    COA   A            100 SIGNOFF


***********
Now issue the query

[b]
SQL> select return_formatted(nm)
  2  from   (select distinct cust||state||type nm
  3          from   test1
  4         )
  5  ;
[/b]

./*   ADD          NAME=ECOAARA
SIGNON ESF=YES
SUBMIT PROC=TAPESEND SNODE=BLAH.D.BLAH
    HOLD=NO  -
    SNODEID=(MY,ID)  -
    RETAIN=NO  -
    NODE=PNODE  -
    DSN1=A.B.C.D.ECOAARAH.NDM.BDTFILE(0)  -
    DSN2=A.B.C.D.T9533.RARK.WCM(+1)  -
    DISP2=(NEW,CATLG,DELETE)
SIGNOFF

This is very crude but I guess it will help you

Regards,
Gunjan

 

[DugsDMan]

Thanks! I think that will pretty well cover it. I'm sure there will be a few changes (users always change their minds ) before it's all done, but that does what we need at the moment.

Thanks!
Doug