Re: Need help really urgent
From: Chris Albright <chrisalbright_at_earthlink.net>
Date: Sat, 31 Jul 1999 14:04:33 -0500
Message-ID: <7nvhfc$pjm$1_at_birch.prod.itd.earthlink.net>
Date: Sat, 31 Jul 1999 14:04:33 -0500
Message-ID: <7nvhfc$pjm$1_at_birch.prod.itd.earthlink.net>
There is a package supplied by Oracle called UTL_FILE with which you can read and write ASCII files to one or more directories on the server. These directories are defined by a parameter in the INIT[sid].ORA file called "UTL_FILE_DIR" So for example, if you are running Oracle on NT, and the directory you want to store these files is "C:\EXCEL" then the parameter should look like this:
"UTL_FILE_DIR=C:\EXCEL"
--- Chris Albright ---------- In article <7npsja$quh_at_dfw-ixnews5.ix.netcom.com>, "Rao" <brao_at_csscinc.com> wrote:Received on Sat Jul 31 1999 - 21:04:33 CEST
>Hi Gurus,
>
>I am in the process of learning PL/SQL. I have following two problem, I
>really need someone who can help me
>
>(1) Requirement is using PL/SQL, I should be able to output the results into
>an ASCII file (flat file) and this flat file will be read using EXCEL.
> I Wrote the PL/SQL, but I am not sure how to create a flat file, which
>can be read into EXCEL ?
>
>(2) The program begins by accepting two differerent dates (from & to dates,
>using PROMPT command). These two dates are being used within the program to
>read the various tables to select records that fall between two given dates.
>These dates are not being used while creating the cursor at the very
>begining of the program. The records from the cursor are read and for each
>employee, employee history is being accessed between those dates to sum up
>some information. Also, another table is also being accessed between those
>dates for some other information. My problem is when I execute the PL/SQL
>program I get an error saying
>
>ERROR at line 1:
>ORA-01847: day of month must be between 1 and last day of month
>ORA-06512: at line 222
>
>sample of the code...
>
>ACCEPT FR_DT DATE PROMPT "Enter From Date (DD-MON-YYYY) :"
>ACCEPT TO_DT DATE PROMPT "Enter To Date (DD-MON-YYYY) :"
>
>Declare
> Cursor c_employee is
> select ....
> from employee
> where ....;
>
>all variables are declared
>
>Begin
> For v_employee in c_employee Loop
> v_totoal := 0;
> select sum(field_name) into v_total from employee_history eh
> where eh.comp_no = ?? and
> eh.create_date between '&fr_dt' and '&to_dt';
>
> v_totoal_1 := 0;
> select sum(field_name) into v_total_1 from salary_history sh
> where sh.comp_no = ?? and
> sh.start_date between '&fr_dt' and '&to_dt';
>
> end loop;
>end;
>
>After I execute, I clearly see that the variables are being replaced with
>proper dates.
>But, still comes with the above error and stops.
>
>SQL*PLUS version is 3.3.4.0.0.
>
>Oracle7 Server Release 7.2.3
>
>PLEASE Help
>
>
>Thanks in advance..
>
>Rao
>
>
>
>