Hi Dapi

I dont have to read file here. My SQL file is as follows.

There are N number of selects in the below SQL. I just want to know that can I call this in stored Procedure. Then I want this procedure to be called from VB 6.0 . I have Oracle 8.0.5 . I searched earlier on this ground but only I found something similar that was EXECUTE IMMEDiATE but how in this case it will work ?


-----------------------------
undefine fdt;
undefine tdt;
undefine lno;
accept fdt prompt 'From Date:'
accept tdt prompt 'To Date:'
accept lno prompt 'Line No:'
Rem
SET VERIFY OFF
SET TERMOUT OFF
SET ECHO OFF
SET WRAP OFF
SET SPACE 1
SET LINESIZE 132
SET PAGESIZE 64
SET NEWPAGE 0
SPOOL c:\windows\desktop\abc.txt;
select
a.line_no line,
a.shift_no shift,
decode(a.shift_no,3, to_char(a.prod_date - 1,'MM-DD-YY'), to_char(a.prod_date,'MM-DD-YY')) proddate,
a.die_no dieno,
c.die_desc diedesc,
1.50 setupplanned,
b.total_time setupactual
from prod_downtime_hd a,
prod_downtime_dt b,
prod_diemast_t c
where
a.line_no = upper('&lno')
and a.line_no = b.line_no
and a.prod_date = b.prod_date
and a.shift_no = b.shift_no
and a.die_no = b.die_no
and b.die_no = c.die_no
and b.category_cd= 'SET'
and a.prod_date = to_date('&fdt', 'MM-DD-YY')
and a.shift_no in (1,2)
union
select
a.line_no line,
a.shift_no shift,
decode(a.shift_no,3, to_char(a.prod_date - 1,'MM-DD-YY'), to_char(a.prod_date,'MM-DD-YY')) proddate,
a.die_no dieno,
c.die_desc diedesc,
1.50 "Setup Planned",
b.total_time "Setup Actual"
from prod_downtime_hd a,
prod_downtime_dt b,
prod_diemast_t c
where
a.line_no = upper('&lno')
and a.line_no = b.line_no
and a.prod_date = b.prod_date
and a.shift_no = b.shift_no
and a.die_no = b.die_no
and b.die_no = c.die_no
and b.category_cd= 'SET'
and a.prod_date > to_date('&fdt','MM-DD-YY')
and a.prod_date <= to_date('&tdt','MM-DD-YY')
union
select
a.line_no line,
a.shift_no shift,
decode(a.shift_no,3, to_char(a.prod_date - 1,'MM-DD-YY'), to_char(a.prod_date,'MM-DD-YY')) proddate,
a.die_no die,
c.die_desc diedesc,
1.50 "Setup Planned",
b.total_time "Setup Actual"
from prod_downtime_hd a,
prod_downtime_dt b,
prod_diemast_t c
where
a.line_no = upper('&lno')
and a.line_no = b.line_no
and a.prod_date = b.prod_date
and a.shift_no = b.shift_no
and a.die_no = b.die_no
and b.die_no = c.die_no
and b.category_cd= 'SET'
and a.shift_no = 3
and a.prod_date = to_date('&tdt','MM-DD-YY') + 1;
select '65.00' target,
decode(cal_uia_avail_hrs('&fdt','&tdt',upper('&lno'),'%'),0,0,cal_uia_uptime_hrs('&fdt','&tdt',upper('&lno')) / cal_uia_avail_hrs('&fdt','&tdt',upper('&lno'),'%')) Utilization,
cal_defective_rate('&fdt','&tdt',upper('&lno')) "Defective Rate",
cal_quality_rate('&fdt','&tdt',upper('&lno')) Quality,
cal_uia_downtime_hrs('&fdt','&tdt',upper('&lno')) "DownTime Hrs"
from dual;
SELECT
'1' shift,
'65.00' Target,
decode(cal_uia_avail_hrs('&fdt','&tdt',upper('&lno'),'%'),0,0,cal_shiftwise_uptime_hrs('&fdt','&tdt',upper('&lno'),1)/cal_uia_avail_hrs('&fdt','&tdt',upper('&lno'),'%')) util,
cal_shiftwise_defective_rate('&fdt','&tdt', upper('&lno'),1) defectiverate,
cal_shiftwise_downtime_hrs('&fdt','&tdt',upper('&lno'),1) downtime,
cal_shiftwise_downtime_hrs('&fdt','&tdt',upper('&lno'),1) odrhrs,
decode(cal_shiftwise_downtime_hrs('&fdt','&tdt',upper('&lno'),1),0,0,(cal_shiftwise_odr_hrs('&fdt','&tdt',upper('&lno'),1)/ cal_shiftwise_downtime_hrs('&fdt','&tdt',upper('&lno'),1)) * 100) odrhrsper
from dual
union
SELECT
'2' shiftno,
'65.00' Target,
decode(cal_uia_avail_hrs('&fdt','&tdt',upper('&lno'),'%'),0,0,cal_shiftwise_uptime_hrs('&fdt','&tdt',upper('&lno'),2)/cal_uia_avail_hrs('&fdt','&tdt',upper('&lno'),'%')) util,
cal_shiftwise_defective_rate('&fdt','&tdt',upper('&lno'),2) defectiverate,
cal_shiftwise_downtime_hrs('&fdt','&tdt',upper('&lno'),2) downtime,
cal_shiftwise_odr_hrs('&fdt','&tdt',upper('&lno'),2) odrhrs,
decode(cal_shiftwise_downtime_hrs('&fdt','&tdt',upper('&lno'),2),0,0,(cal_shiftwise_odr_hrs('&fdt','&tdt',upper('&lno'),2)/ cal_shiftwise_downtime_hrs('&fdt','&tdt',upper('&lno'),2)) * 100) odrhrsper
from dual
union
SELECT
'3' shiftno,
'65.00' Target,
decode(cal_uia_avail_hrs('&fdt','&tdt',upper('&lno'),'%'),0,0,cal_shiftwise_uptime_hrs('&fdt','&tdt',upper('&lno'),3)/cal_uia_avail_hrs('&fdt','&tdt',upper('&lno'),'%')) util,
cal_shiftwise_defective_rate('&fdt','&tdt',upper('&lno'),3) defectiverate,
cal_shiftwise_downtime_hrs('&fdt','&tdt',upper('&lno'),3) downtime,
cal_shiftwise_odr_hrs('&fdt','&tdt',upper('&lno'),3) odrhrs,
decode(cal_shiftwise_odr_hrs('&fdt','&tdt',upper('&lno'),3),0,0,(cal_shiftwise_odr_hrs('&fdt','&tdt',upper('&lno'),3)/ cal_shiftwise_downtime_hrs('&fdt','&tdt',upper('&lno'),3)) * 100) odrhrsper
from dual;
select line_no lineno,
decode(shift_no,3, to_char(prod_date - 1,'MM-DD-YY'), to_char(prod_date,'MM-DD-YY')) prod_date,
shift_no shiftno,
'Y' sicpresent,
no_ops_present opsno,
no_cas_present casno
from prod_downtime_hd
where line_no = upper('&lno')
and prod_date = to_date('&fdt', 'MM-DD-YY')
and shift_no in (1,2)
UNION
select line_no lineno,
decode(shift_no,3, to_char(prod_date - 1,'MM-DD-YY'), to_char(prod_date,'MM-DD-YY')) prod_date,
shift_no shiftno,
'Y' sicpresent,
no_ops_present opsno,
no_cas_present casno
from prod_downtime_hd
where line_no = upper('&lno')
and prod_date > to_date('&fdt','MM-DD-YY')
and prod_date <= to_date('&tdt','MM-DD-YY')
UNION
select line_no lineno,
decode(shift_no,3, to_char(prod_date - 1,'MM-DD-YY'), to_char(prod_date,'MM-DD-YY')) prod_date,
shift_no shiftno,
'Y' sicpresent,
no_ops_present opsno,
no_cas_present casno
from prod_downtime_hd
where line_no = upper('&lno')
and shift_no = 3
and prod_date = to_date('&tdt','MM-DD-YY') + 1;
select cal_cumm_dieindex('&fdt',upper('&lno')) cummdieindex from dual;
select cal_dieindex('&fdt','&tdt',upper('&lno')) dieindex from dual;
spool off;
SET VERIFY ON;
SET TERMOUT ON;
exit;
--------------------------------------