9.4.1 Passing parameters from Excel to Process

[rfielden]

Any recommended practice for passing parameters located within the Excel spreadsheet to a TI process using sql. End result would be to update an Oracle table with these parameters that are used in another process. This is the final key to making our users (customer) happy!

For info...
1. This process will populate a parameter table in Oracle.
2. A TI process (Oracle procedure) has been written, using the parameter table, to pull history from Oracle, create summary cube, which in turn drives other spreadsheet data and calcs.

[David Usherwood]

Either
a Send the value(s) to a TI process which includes
odbcoutput(dsn, 'update <table> set <parm> = ''' | value | '''');

or
b send the value directly from Excel to Oracle - but this needs a VBA routine and a local configured ODBC dsn.

NB Don't trust my quotes on the odbcoutput statement - I generally have to play around using asciioutput to get them right.

[rfielden]

Thanks to David's reply we resolved our issue.

Wrote sql proc as follows:

CREATE OR REPLACE procedure RDWTST30.populate_lsm3(P0 date, P1 number, P2 number, P3 number, P4 varchar, P5 number,
P7 number, P8 number, P9 number, P10 varchar, P11 number,
P13 number, P14 number, P15 number, P16 varchar, P17 number,
P19 number, P20 number, P21 number, P22 varchar, P23 number,
P25 number, P26 number, P27 number, P28 varchar, P29 number,
P31 number, P32 number, P33 number, P34 varchar, P35 number,
P37 number, P38 number, P39 number, P40 varchar, P41 number,
P43 number, P44 number, P45 number, P46 varchar, P47 number,
P49 number, P50 number, P51 number, P52 varchar, P53 number,
P55 number, P56 number, P57 number, P58 varchar, P59 number,
P61 number, P62 number, P63 number, P64 varchar, P65 number,
P67 number, P68 number, P69 number, P70 varchar, P71 number
) IS
begin

delete from lsm_parameters;
commit;

insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(1,P1,P2,P3,P4,P5,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(2,P7,P8,P9,P10,P11,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(3,P13,P14,P15,P16,P17,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(4,P19,P20,P21,P22,P23,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(5,P25,P26,P27,P28,P29,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(6,P31,P32,P33,P34,P35,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(7,P37,P38,P39,P40,P41,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(8,P43,P44,P45,P46,P47,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(9,P49,P50,P51,P52,P53,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(10,P55,P56,P57,P58,P59,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(11,P61,P62,P63,P64,P65,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(12,P67,P68,P69,P70,P71,P0);
commit;
end;
/

Then created TI process to call procedure and parameters as:
CREATE OR REPLACE procedure RDWTST30.populate_lsm3(P0 date, P1 number, P2 number, P3 number, P4 varchar, P5 number,
P7 number, P8 number, P9 number, P10 varchar, P11 number,
P13 number, P14 number, P15 number, P16 varchar, P17 number,
P19 number, P20 number, P21 number, P22 varchar, P23 number,
P25 number, P26 number, P27 number, P28 varchar, P29 number,
P31 number, P32 number, P33 number, P34 varchar, P35 number,
P37 number, P38 number, P39 number, P40 varchar, P41 number,
P43 number, P44 number, P45 number, P46 varchar, P47 number,
P49 number, P50 number, P51 number, P52 varchar, P53 number,
P55 number, P56 number, P57 number, P58 varchar, P59 number,
P61 number, P62 number, P63 number, P64 varchar, P65 number,
P67 number, P68 number, P69 number, P70 varchar, P71 number
) IS
begin

delete from lsm_parameters;
commit;

insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(1,P1,P2,P3,P4,P5,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(2,P7,P8,P9,P10,P11,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(3,P13,P14,P15,P16,P17,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(4,P19,P20,P21,P22,P23,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(5,P25,P26,P27,P28,P29,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(6,P31,P32,P33,P34,P35,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(7,P37,P38,P39,P40,P41,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(8,P43,P44,P45,P46,P47,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(9,P49,P50,P51,P52,P53,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(10,P55,P56,P57,P58,P59,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(11,P61,P62,P63,P64,P65,P0);
insert into lsm_parameters (slno,dept,class,subclass,month,year,cut_off_date)
values(12,P67,P68,P69,P70,P71,P0);
commit;
end;
/

Thanks again David for leading us down the right path.

[rfielden]

Sorry, here is the TI process:

BEGIN
RDWTST30.populate_lsm3
( '?pCOD?',
'?pDept1?','?pClass1?','?pSubclass1?','?pPeriod1?','?pYear1?',
'?pDept2?','?pClass2?','?pSubclass2?','?pPeriod2?','?pYear2?',
'?pDept3?','?pClass3?','?pSubclass3?','?pPeriod3?','?pYear3?',
'?pDept4?','?pClass4?','?pSubclass4?','?pPeriod4?','?pYear4?',
'?pDept5?','?pClass5?','?pSubclass5?','?pPeriod5?','?pYear5?',
'?pDept6?','?pClass6?','?pSubclass6?','?pPeriod6?','?pYear6?',
'?pDept7?','?pClass7?','?pSubclass7?','?pPeriod7?','?pYear7?',
'?pDept8?','?pClass8?','?pSubclass8?','?pPeriod8?','?pYear8?',
'?pDept9?','?pClass9?','?pSubclass9?','?pPeriod9?','?pYear9?',
'?pDept10?','?pClass10?','?pSubclass10?','?pPeriod10?','?pYear10?',
'?pDept11?','?pClass11?','?pSubclass11?','?pPeriod11?','?pYear11?',
'?pDept12?','?pClass12?','?pSubclass12?','?pPeriod12?','?pYear12?'
);
END;