How to run .sql file from Oracle Procedure.

[amolik]

Hi All

I have set of complex queries in a .sql file and I need to run it
from Oracle procedure. That SQL file also creates a spool file of output on the specified drive.

How can I use something like EXECUTE_IMMEDIATE here. I am using Oracle 8.0.1 .. Therefore output is not supporting REFCURSOR. Is there any way that I can get the output of these queries in file from a Stored Procedure.

I need to do that way because I need to call this procedure from VB.

Your quick help will be appreciated.

Amol

[pando]

you cannot run *sql in pl/sql

[DaPi]

I'm not 100% sure what you want to do. If you want to read a file of SQL statements and produce a file of output, have a look at UTL_FILE. You will have to read the input with UTL_FILE and construct the SQL statement in memory and recognise the end of the statement to know when to do an EXECUTE IMMEDIATE - the SQL will need to produce a single character string as output (otherwise you will have a **** of job constructing an INTO clause) - you then write the output with UTL_FILE.

BUT I suspect you can avoid all this if you look a what your real requirements are . . .

[amolik]

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;
--------------------------------------

[DaPi]

I'm afraid I have no idea how to call PL/SQL from VB6 . . . for example how many rows of your big UNION query can you return at a time?

Multi row queries can use a simple cursor to fill the output array - http://www.csee.umbc.edu/help/oracle..._oview.htm#767
Single row queries use an INTO clause: SELECT xyz INTO my_var FROM dual;

Thanks for simplifying your code before posting it

[amolik]

Sorry for not simplyfying it but I dont expect all to look at SQL.
It is just a Query which I am currently using @file.sql.

But My Front End users want it to run from VB as a . For that they want in Procedure. They say there is no way that @file can be run from VB.

In query there are many different queries and result has to be on one page. (Our user's are biggies ... they want everything in their formats)

Amol

[davey23uk]

you cannot use sql*plus settings in a proc, you surely must run the sql then ure vb program must format the ouptut - just like sql*plus does

[rigatoni]

I agree with davey. If you were able to run it in PL/SQL or call the sqlplus script from the operating system, the result file would probably end up on the server and not the client. If you want it on the client, then it looks like you'll have to use VB or something on the client.