How to run .sql file from Oracle Procedure.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: How to run .sql file from Oracle Procedure.

Hybrid View

  1. #1
    Join Date
    Dec 2000
    Posts
    255

    How to run .sql file from Oracle Procedure.

    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cannot run *sql in pl/sql

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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 . . .

  4. #4
    Join Date
    Dec 2000
    Posts
    255
    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;
    --------------------------------------

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  6. #6
    Join Date
    Dec 2000
    Posts
    255
    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

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    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

  8. #8
    Join Date
    Nov 2003
    Location
    Ohio
    Posts
    51
    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.
    ____________________
    Pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width