首页 > 代码库 > utl_file包的使用
utl_file包的使用
首先看一下oracle 脚本
/* # $Header: HTMomse12.sql 12.0.4 20121015 Support $
#+======================================================================================+
#| Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA |
#| Oracle Support Services. All rights reserved |
#+======================================================================================+
#========================================================================================
# PURPOSE: This script will collect information related to a sales order
# transaction and it's Workflow processes. This script should be
# run whenever there are problems related to a sales order
# transaction, including processes like Pick Release, Shipping,
# Invoice interface.
#
# FILE NAME: HTMomse12.sql
# FILE VERSION: 12.0.4
# PRODUCT: Oracle Order Management
# PRODUCT VERSIONS: 12.0 (and above)
# PLATFORM: Generic
# PARAMETERS: Order_Number, Header_Id (opt), Line_Id (opt)
#========================================================================================
# USAGE: sqlplus apps/apps @HTMomse12.sql
#
# This script requires a header_id to be passed in as a parameter ONLY if there are
# multiple header_id's for the order requested. If there is only one header_id returned
# after inputting the order number, simply hit enter and this header_id will be assumed.
#
# Entering a line_id is only required when you wish to have output for one order line.
# Otherwise, hit enter without entering a line_id and all lines will be output in the
# script.
#
# The script writes to an output file named HTMomse12_<ordernumber>.html on DB Server
# under 'utl_file_dir', this file may be viewed in any HTML browser.
#
*/
set term off;
/*
#=========================================================================================
# CHANGE HISTORY:
This script is based on HTMomse11i version 7.5.1, for the complete
change history please see HTMomse11i (Note 133464.1)
From HTMomse11i:
31-MAR-00 Created rnmercer
12-DEC-00 Major modifications to the look of the report including rnmercer
abreviated column names for flags, etc. A legend cross
referencing the abreviations is included in the output.
Got rid of the need to input the org_id. It is set via
derived data from the header_id.
17-MAY-01 Fix the line number to display all 5 numbers rnmercer
line.shipment.option.component.service
05-DEC-01 Changed format of output filename as part of the iTar
automated scripting initiative. rodavid
13-JUN-02 Modified to change version numbering to match Repository rodavid
02-APR-04 Modified to create HTML output file and included basic
14-APR-04 verifications to help the Analysis rijames
14-APR-04 include table ONT_WF_SKIP_LOG on header and lines rijames
01-NOV-07 Changed output function from DBMS_OUTOUT for UTL_FILE, rijames Ver. 7.5
due to size limitations on DBMS_OUTPUT function.
Output file will now be located on DB Server under 'utl_file_dir'.
Script will printout the filename and location.
Added some columns as requested by Support.
For HTMomse12:
09-JAN-08 Created as copy of HTMomse11i 7.5.1 rijames
11-JAN-08 Modified set environment using rodavid script rijames
Exclude IC tables (for OPM) as now OPM information is
contained on OM/WSH base tables.
14-JAN-08 Corrected conditions for sections to use UPPER() when rijames
answer is provided on lower case.
04-FEB-08 Move INVentory and OM Interface Flags after the Release rijames Ver 12.0.2
status field.
Correction on decode of the Hold Entity Code, label for
code 'W' is changed to 'Warehouse' (from 'Workflow').
06-FEB-08 Add outer join to MTL_SALES_ORDERS to print when Order rijames
Type definition has been deleted.
Move Parameters summary to top of output.
Modify to handle Orders with no lines, only Header
information will be printed.
07-FEB-08 Add Hostname on printout for ouput location. rijames
05-MAR-08 Add OPM fields on ORDER_LINES and most MTL queries, rijames
MTL_TRANSACTION_LOT_NUMBERS table included on MTL queries.
06-SEP-11 Correct Header on RA_INTERFACE_TABLE field rijames Ver 12.0.3
RAI.INTERFACE_LINE_ATTRIBUTE6 Line_ID_6 (was Line_ID_5)
06-SEP-11 Add transaction date and transaction interface id to rijames
MTL_TRANSACTIONS_INTERFACE.
06-SEP-11 Add transaction date to MTL_MATERIAL_TRANSACTIONS_TEMP. rijames
06-SEP-11 Add transaction date to MTL_MATERIAL_TRANSACTIONS. rijames
06-SEP-11 Add transaction date to MTL_UNIT_TRANSACTIONS. rijames
16-SEP-11 Add table MTL_SERIAL_NUMBERS. rijames
22-SEP-11 Add option to All or only first 10 records from tables rijames
MTL_UNIT_TRANSACTIONS (MUT), WSH_SERIAL_NUMBERS (WSN)
and MTL_SERIAL_NUMBERS
26-SEP-11 Add multiple Close and Reopen of Output file, so in case rijames
of SQL failure only a few tables are not printed.
29-SEP-11 Add additional fields to ORGANIZATIONS listing. rijames
17-OCT-11 Add 'SERIAL NUMBER Details' on Parameters Listing rijames
17-OCT-11 Modify title for column Transaction_Interface_Id for rijames
table MTL_TRANSACTIONS_INTERFACE
17-OCT-11 Modify title color for column INV_INTERFACED_FLAG for rijames
table WSH_DELIVERY_DETAILS, also the title bar will be
printed every 35 rows instead of every 50.
16-NOV-11 Split printout for ORGANIZATIONS and ACCOUNTING PERIODS rijames
10-FEB-12 Correct print sequence on Order Lines (Ordered Item) rijames
10-FEB-12 Print Column Headers for Order Lines every 35 lines rijames
10-FEB-12 Modify query for ACCOUNTING PERIODS to include Period rijames
for lines not Shipped yet.
14-MAR-12 Modify 'Serial Num. Details' title on Parameters Listing rijames
14-MAR-12 Add new close/open after ACCOUNTING PERIODS table rijames
04-MAY-12 Correct printout for 'UNPICKED LINES' table. rijames
11-OCT-12 Include WMS Rules tables. rijames
11-OCT-12 Include link to table WMS Rules on Tables Index. rijames
11-OCT-12 Change version to 12.0.4 rijames
15-OCT-12 Include line number and Internal Result code on lines rijames
Workflow table
15-OCT-12 Include Internal Result code on header Workflow table rijames
15-OCT-12 Correct OE_LINES FULFIL_QTY field printed rijames
15-MAR-13 Set WMS Information to Default to 'N' as the file rijames
created is too extense.
14-OCT-13 Sorting for Workflow lines corrected rijames
14-OCT-13 Change Version numbering to include date .yymmdd rijames
24-MAR-14 Change SQL to obtain utl_file_dir value rijames
24-MAR-14 Add field Source_line_set_id to Delivery Details query rijames
10-JUN-14 Correct WSH_DELIVERY_DETAILS (DET) table Headers rijames
10-JUN-14 Correct WSH_DELIVERY_DETAILS (CONTAINERS) columns order rijames
10-JUN-14 Add field Source Line Set Id to WSH_DELIVERY_DETAILS rijames
#
#========================================================================================
References
----------
For documentation and white papers on the Order Management product suite go to Metalink,
http://metalink.us.oracle.com, click on Top Tech Docs button -> E-Business Suite: ERP ->
Distribution/Supply Chain -> OM Suite: Order Management.
*/
variable v_order_num varchar2(100);
variable v_error number;
variable v_line_tot number;
variable v_line_cnt number;
variable sales_ord_id number;
variable v_op_unit number;
variable v_head_cnt number;
variable v_header_id number;
variable v_head_only varchar2(1);
variable r_line_t varchar2(100);
variable r_res_q number;
variable r_wdd number;
variable r_flag varchar2(100);
variable r_pro_na varchar2(100);
variable r_act_na varchar2(100);
variable r_result varchar2(100);
variable r_act_s varchar2(100);
variable r_error number;
variable is_opm varchar2(2);
variable all_or_top10 varchar2(100);
set arraysize 4;
set pagesize 58;
set term on;
set linesize 145;
set underline =;
set verify off;
set serveroutput on size 1000000;
set feedback off;
-- OE_ORDER_HEADERS
column HEADER_ID format 99999999;
column ORGANIZATION_NAME format A30;
column TYPE_ID format 99999999;
column TYPE_NAME format A15;
column ORD_DATE_TYPE format a15;
column ORD_NUM format 999999999;
column VERS format 9999;
column ORD_TYPE_ID format 999999999;
column FLOW_CODE format a22;
column CUST_NAME format A20;
column CUST_ID format 999999999;
column SHIP_TO_ID format 999999999;
column SHIP_TO format A12;
column SOLD_TO_ID format 999999999;
column PO_NUMBER format A09;
column DATE_ORD format A15;
column PL_ID format 99999;
column PRICE_LIST_NAME format A15;
column PL_NAME format A15;
column SHIP_PART format A09;
column ORGANIZATION format 9999999;
column WH_ID format 99999;
column DEMAND_CLASS format A12;
column OP format A05;
column CN format A05;
column BK format A05;
column DRP_SHP format A07;
column CYCLE_ID format 99999999;
column ORD_SRC_ID format 99999999;
column TRANS_TYPE format A12;
column CATEGORY format A08;
column SP format A05;
column CAT format A06;
-- WF_NOTIFICATIONS
column TO_USER format a22;
column ORIG_RECIP format a10;
column RECIP_ROLE format a10;
column MAIL_STAT format a9;
column MESSAGE_NAME format a25;
column SUBJECT format a45;
-- OE_TRANSACTION_TYPES_ALL
column ORDER_TYPE_NAME format A25;
column TYPE_NAME format A15;
column CONSTANTOE format A15;
column SHIP_PRIORITY format A13;
column SYS_REQD format A08;
-- OE_ORDER_LINES_ALL
column LINE_ID format 99999999;
column LINE format A08;
column LINE_NUM format A08;
column LINE_CTG format A09;
column CONFIG_HDR_ID format B99999999;
column SSCHED format B9999;
column PRT_LINE_ID format B99999999;
column ATO_LINE_ID format B99999999;
column LNK_LINE_ID format B99999999;
column SHP_LINE_ID format B99999999;
column SRC_LINE_ID format B99999999;
column SRV_LINE_ID format B99999999;
column ITEM_ID format 99999999;
column ITEM format A17;
column ORD_Q format 999999.99;
column ORG_Q format 999999.99;
column REQ_Q format 999999.99;
column PRICE format $999999999.99;
column SHP_Q format 999999.99;
column Q_INC format 999999.99;
column SHN_Q format 999999.99;
column SHP_Q format 999999.99;
column FUL_Q format 999999.99
column RES_Q format 999999.99;
column INC_Q format 999999.99;
column CAN_Q format 999999.99;
column LN_SET_TY format A11;
column SH_SET_TY format A11;
column AR_SET_TY format A11;
column REQUEST_D format A15;
column SCHEDUL_D format A15;
column SOURCE_TYPE format A11;
column DEM_CLASS format A10;
column LINE_DETAIL_ID format 9999999999;
--column OPEN_FL format A07;
--column CANC_FL format A07;
--column BOOKED format A07;
column SH format A05;
column VD format A05;
column FF format A05;
column SUBINV format A10;
column SSC format A05;
column SI format A05;
column II format A05;
column INVC_INT_STAT format A14;
column SHIPPABLE format A08;
column TRANSACTABLE format A08;
column RESERVABLE format A08;
column RELEASED format A08;
column FOR_REVENUE format A08;
column DELIVERY format 9999999999;
column SET_TYPE format A15;
column SET_NAME format A10;
column STATUS format A06;
column SCH_SHP_DT format A10;
column SCH_ARV_DT format A10;
column CARRIER format A15;
column SHIP_METHOD format A15;
column wf_act_code format a11;
column wf_result format a9;
column hist_comments format a45;
column hist_type format a12;
-- column PRICE format $9999999999.99;
-- WORKFLOW TABLES
column ITEM_KEY format A08;
column FLOW_PROCESS format A22;
column RESULT_CODE format A15;
column RESULT format A15;
column ASSIGNED_USER format A10;
column ERROR_NAME format A19;
column PROCESS_NAME format A25;
column ACTIVITY_NAME format A25;
column ERROR_ACTIVITY_NAME format A31;
column ACT_STATUS format A10;
column HEAD_ID format A08;
column LIN_ID format A08;
column NOTIF_ID format 99999999;
column ERROR_NAME format A14;
column ERR_RETRY_ROLE format A14;
column ERR_RETRY_USER format A14;
column BEGIN_DATE format A18;
column END_DATE format A18;
column ORD_TYPE_NAME format A20;
column CONSTANTOE format A12;
column DEMAND_ID format 9999999999;
column PARDEM_ID format 9999999999;
column DS_HEADER_ID format 99999999;
column DS_LINE format A08;
column RSV_QTY format 9999999.99;
column SAT_QTY format 9999999.99;
column PND_QTY format 9999999.99;
column REQUIRD_D format A15;
column TY format 99999;
column UP format 99999.99;
column MRP format 99999.99;
column ATP format 99999.99;
column PICKING_HEADER_ID format 9999999999;
column PICK_SLIP format 9999999999;
column BATCH_ID format 99999999;
column STATUS_CODE format A11;
column BATCH_NAME format A30;
column PICKING_LINE_ID format 9999999999;
column CONFIRMED format A09;
column RA_INTERFACE format A12;
column IN_INTERFACE format A15;
column PICK_LN_DTL_ID format 9999999999;
column SUBINVENTORY format A12;
column REV format A05;
column LOT_NUMBER format A15;
column SERIAL_NUMBER format A20;
column LOCATION_ID format 9999999999;
column ORG format A05;
column ATO format A05;
column OPT format A05;
column CFG format A05;
column SHIP_MC format A07;
column SHIP_SET format 99999999;
column ITEM_TYPE format A09;
column LINE_TYPE format A09;
column ENTER format A10;
column CANCEL_ORD format A10;
column CANCEL_LIN format A10;
column CTG format A05;
column INCL format A05;
column CONF format A05;
column WIP_RSV format 9999999;
column WIP_COM format 9999999;
column COMP_CODE format A10;
column SRC_TYP format 9999999;
column DEM_TYP format 9999999;
column WIP_ID format B99999999;
column JOB_NAME format A15;
column JOB_STAT format A12;
column UOM format A5;
column REL_FL format A06;
column SHIP_FL format A07;
column FOR_REV format A07;
column AUTOSCH format A06;
column DEP_NAME format a10;
column ORG_ID format 99999;
column WSH_ID format 9999999;
column SOURCE_CD format a12;
column STAT_CODE format A11;
column CLOSED_DT format A09;
column FREIGHT format A09;
column PLN_DEP_DT format A10;
column ACT_DEP_DT format A10;
column PLN_DEP_ID format 9999999999;
column ACT_DEP_ID format 9999999999;
column LOAD_ORD_FLG format A12;
column DEL_SEL format A07;
column DEP_SEL format A07;
column REL_STAT format A08;
column LIN_STAT format A08;
column WIP_ENT_ID format 9999999999;
column CONFIRM_D format A09;
column INVC_STAT format A10;
column INVC_INTF format A10;
column SHIP_STAT format A10;
column SHIP_INTF format A10;
column LOT format A10;
column REV format A05;
column SERIAL_NUM format A10;
column LOC_ID format 99999999;
column RESV_FL format A07;
column TRAN_FL format A07;
column SCHED_STAT format A10;
column INVOICE_TRIGGER format A15;
column SOURCING_STATUS format A15;
column INVOICING_STATUS format A16;
column CANCELLED_FLAG format A14;
column OPEN_FLAG format A09;
column CONSOLIDATION_STATUS_CODE format A25;
column CONSOLIDATION_PREF_FLAG format A23;
column PURCHASE_ORDER_NUM format A18;
column DEL_ID format 9999999;
column DEL_NAME format A10;
-- PO_REQUISITION_INTERFACE_ALL
column AUTH_STATUS format A11;
column DEST_TYPE format A10;
column SRC_CODE format A11;
column SRC_TYPE_CODE format A13;
-- PO_REQUISITION_HEADERS_ALL
column REQ_NUMBER format A10;
column SUMMARY format A07;
column XFR_OE_FLAG format A11;
column REQ_TYPE format A11;
column ENABLED format A07;
column ITEM_DESC format A40;
column CANC format A05;
column ENC_FL format A06;
column RFQ format A05;
column SRC_TYPE format a09;
column SRC_ORG format 9999999;
-- PO_HEADERS
column PO_NUM format A06;
-- MTL_SUPPLY
column SUP_TYPE format a08;
-- MTL_RESERVATIONS
column SHIP_READY format A12;
column SS_TYPE_ID format A11;
-- WSH_TRIPS
column TRIP_ID format 9999999;
column TRIP_NAME format A10;
column PLND format A05;
column VEH_NUM format A10;
column CARR_ID format 9999999;
column DET_Q format 99999;
column DS_TYPE format 9999999;
column ROUTE_ID format 99999999;
column VEH_ORG_ID format 9999999999;
-- WSH_TRIP_STOPS
column STOP_ID format 99999999;
column SEQ_NUM format 9999999;
column STOP_LOC_ID format 99999999999;
column PLN_DEP_DATE format A15;
column PLN_ARV_DATE format A15;
column ACT_DEP_DATE format A15;
column ACT_ARV_DATE format A15;
column PEND_INTERF format A11;
-- WSH_DELIVERY_LEGS
column LEG_ID format 9999999;
column LOAD_TENDER_STAT format A15;
-- WSH_NEW_DELIVERIES DEL
column DEL_NAME format A12;
column BOOKING_NUM format A12;
column WAYBILL format A12;
column ACCEPTED format A15;
column PICKUP_DT format A15;
column DROPOFF_DT format A15;
-- WSH_DELIVERY_ASSIGNMENTS ASG
column ACTIVE format A06;
-- WSH_DELIVERY_DETAILS
column REL_STATUS format A17;
column STA format 99999;
column STB format 99999;
column HOLD_CODE format A09;
column SMC format A05;
column SUB format A09;
column CUR_SUB format A09;
column ORG_SUB format A09;
column REV format A05;
column LOT format A05;
column SERIAL format A10;
column LOC_ID format 99999999;
column SHIP_METH format A10;
column OMI format A05;
column INI format A05;
column MVT_STATUS format A10;
column INV_INT format A07;
column CONT_NAME format A15;
column CONT_TYPE format A10;
column UNIT_NUM format A08;
column CONTAINER format A09;
column REQ_DATE format A15;
column MVT_STAT format A08;
column DEL_DET_ID format 9999999999;
column RELEASE_STAT format A15;
column REQ_Q format 999999.99;
column DLV_Q format 999999.99;
column SRQ_Q format 999999.99;
column BO_Q format 999999.99;
--column CAN_QTY format 9999999;
column SHIP_TO_ID format 999999999;
column LINE_REQ_QTY format 999999999999;
column TYPE format A05;
column MO_LINE_ID format 9999999999;
column DELIV_ID format 999999999;
column SH_FROM_ID format 999999999;
column SH_TO_ID format 99999999;
--WSH_PICK_SLIP_V
column LINE_STAT format A11;
column FROM_SUB format A09;
column TO_SUB format A09;
column DETL_DATE format A15;
column LINE_STATUS format A11;
--MTL_TXN_REQUEST_LINES_V
column REQ_NUM format A10;
column MV_LINE_STAT format A14;
column MO_NUMBER format A09;
column MOVE_TYPE_NAME format A14;
column TRNS_SRC_TYPE format A13;
column TRNS_TYPE_NAME format A30;
column TRNS_ACTION format A15;
column LOT_NUM format A10;
--MTL_MATERIAL_TRANSACTIONS_TEMP
column ERROR_EXPL format A10;
-- RA_CUSTOMER_TRX
-- RA_CUSTOMER_TRX_LINES
column ORD_LINE_NUM format 999999999999;
column TRX_NUMBER format A10;
column CONTEXT format A12;
column Order_Num_1 format A11;
column Order_Type_2 format A12;
column Delivery_3 format A10;
column WayBill_4 format A09;
column Line_ID_5 format A09;
column Pick_Line_Id_7 format A14;
column Bill_Lading_8 format A13;
column Warehouse_10 format A12;
column SOURCE format A30;
column TAX_FL format A06;
column QTY_ORD format 9999999.99;
column QTY format 9999999.99;
column ORD_LINE_NUM format A12;
column HEAD_CAT format A8;
column CURR format A4;
column TAX_EX_FL format A09;
column TERR_SEG1 format A09;
column TERR_SEG2 format A09;
column TERR_SEG3 format A09;
column MESSAGE_TEXT format A145;
column INVALID_VALUE format A30;
column STATUS format a8;
column ERR_TYPE_KEY format a14;
column ASGND_USER format a10;
column ERR_PROCESS_NAME format a18;
column ERR_ACTIVITY_NAME format a22;
column hold_name format a22;
column hold_type format a12;
column WF_ACTIVITY format a15;
column ENTITY format a8;
column ENTITY2 format a8;
column HOLD_UNTIL format a18;
column RELEASE_REASON format a14;
column H_REL format a5;
column S_REL format a5;
column AF format A5;
column LIST_TYPE_CODE format a14;
column UA format A5;
column UF format A5;
column AP format A5;
column ARITH_OP format a8;
column TAX_CODE format a12;
column INF format A5;
column EF format A5;
column CHG_TY_CD format a9;
column AC format A5;
column PI format A5;
column CD format A5;
column LIST_LN_NO format a10;
column LK format 99999;
column PP format 99999;
column MOD_LVL format a7;
column PERC format 9999;
column STATUS format a6;
column msg_Source format a14;
column error format a30;
column ITEM_DESC format a25;
column PRICE format $999999999.99
column REQ_NUM_IK format A9;
column ACCEPT_REQD format A10;
column CLS_STAT format A08;
column CONF_ORD format A08;
column CURR_CODE format A05;
column ENABLED format A07;
column FROZEN format A06;
column SUMM format A05;
column TYPE format A10;
column ITEM_KEY format A09;
column SHIP_TO format 9999999;
column PO_LINE format 9999;
column FIRM format A05;
column ITEM_DESC format A40;
column PO_NUM_IK format A9;
column ERROR_N format A7;
column SRC_DOC format a9;
column SHP_LINE_STATUS format a15;
column RCV_Q format 99999;
column TRANS_TYPE format A10;
column INSPECT_STAT format A13;
column INTF_SRC format A11;
column SRC_DOC_CODE format A12;
column QTY format 9999;
column PROC_MODE format a10;
column DEST_TYPE format a9;
column INSP_STAT format a13;
column INSP_SRC format a8;
column PROC_STAT format a9;
column RCPT_SRC format a8;
column TRNS_STAT format a9;
column TRNS_TYPE format a9;
column error_wie format a100;
column status format a9;
column firm format 9999;
column WIP_SUP_TYPE format a13;
column JOB_NAME format a8;
column TRANS_TYPE format a19;
column SRC_CODE format a8;
column PHS format 99999;
column STAT_TY format 9999999;
column STAT format 9999;
column LOAD format 9999;
column status format a9;
column firm format 9999;
column WIP_SUP_TYPE format a13;
column JOB_NAME format a8;
column TRANS_TYPE format a19;
column SRC_CODE format a8;
column PHS format 99999;
column STAT_TY format 9999999;
column STAT format 9999;
column LOAD format 9999;
column STOP_DESCRIPTION format A58;
column GRS_WT format 999999;
column FL_PER format 999.99;
column VOL format 9999;
column VOL_UOM format a07;
column CF format A05;
column WT_UOM format a06;
column FOB_CODE format a11;
column FRT_TERMS format a9;
column LCK format 99999;
column PROCESS format 9999999;
column ERROR_CODE format A30;
column ERROR_EXPL format A60;
column FRT_NAME format a30;
column FRT_TYPE format a15;
column FRT_LEVEL format a15;
column QTY format 9999;
column PRM_Q format 99999;
column DLV_Q format 99999;
column DTL_Q format 99999;
column LCK format A05;
column PROCESS format A07;
column ERROR_CODE format A11;
column ERROR_EXPL format A30;
column TRANS_TYPE format A11;
column QTY format 9999;
column LINE_NUM format 99999999;
column BATCH_SOURCE format a20;
column SO_LIN format a06;
column AR_ID format 99999;
column IR_ID format 99999;
column WH_ID_10 format a08;
column PA_ID_11 format a08;
column C_RATE format 999999;
column TR format 9999;
column EF format A05;
column INTF_LINE_ID format 99999999999;
column CRD_Q format 99999;
column PRICE format $9999999.99;
column PO_NUMBER format A09;
column COMP_FL format a07;
column EXTD_AMT format $999,999.99;
column REV_AMT format $999,999.99;
column TF format A05;
column SOURCE format A11;
column INV_Q format 99999;
column SHIP_VIA format A12;
Set heading on
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
prompt
accept order_number_selected prompt 'Please enter Sales Order number: '
prompt
begin
:v_error := 0;
:v_order_num := '&order_number_selected';
select count(*)
into :v_head_cnt
from oe_order_headers_all
where
order_number = :v_order_num;
if :v_head_cnt = 0 then
RAISE no_data_found;
end if;
if :v_head_cnt = 1 then
select header_id
into :v_header_id
from oe_order_headers_all
where order_number = :v_order_num;
end if;
exception
when no_data_found then
dbms_output.put_line('ERROR - Invalid order number entered');
dbms_output.put_line('ACTION - Script execution must be aborted');
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
:v_error := 1;
raise;
when others then
dbms_output.put_line('ERROR - Unable to retrieve order due to error: '||SQLERRM);
dbms_output.put_line('ACTION - Script execution must be aborted');
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
:v_error := 1;
raise;
end;
/
-- spool &out_file;
/* Display list of header_ids matching the order number entered */
select
ORD.HEADER_ID HEADER_ID,
TYP.NAME ORDER_TYPE_NAME,
ORD.ORDER_CATEGORY_CODE CATEGORY,
ORD.ORG_ID ORG_ID,
(select name
from hr_operating_units
where organization_id = nvl(ORD.ORG_ID,-99)) ORGANIZATION_NAME
from
OE_ORDER_HEADERS_ALL ORD,
OE_TRANSACTION_TYPES_TL TYP,
FND_LANGUAGES FLA
where
ORD.ORDER_NUMBER = :v_order_num
and TYP.LANGUAGE = FLA.LANGUAGE_CODE
and FLA.INSTALLED_FLAG = 'B'
and ORD.ORDER_TYPE_ID = TYP.TRANSACTION_TYPE_ID
and :v_error = 0;
prompt
accept header_id_selected prompt 'Please enter HEADER_ID from list above (optional): '
prompt
/* Set client info context based on the org_id from sales order */
begin
select org_id
into :v_op_unit
from oe_order_headers_all ord
where
ORD.HEADER_ID = nvl('&header_id_selected',:v_header_id);
mo_global.set_policy_context(p_access_mode => 'S', p_org_id => :v_op_unit); -- by rodavid
exception
when no_data_found then
dbms_output.put_line('ERROR - Invalid order number entered');
dbms_output.put_line('ACTION - Script execution must be aborted');
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
:v_error := 1;
raise;
when others then
dbms_output.put_line('ERROR - Unable to retrieve order due to error: '||SQLERRM);
dbms_output.put_line('ACTION - Script execution must be aborted');
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
:v_error := 1;
raise;
end;
/
begin
select count(*)
into :v_line_tot
from oe_order_lines_all
where header_id = nvl('&header_id_selected',:v_header_id);
if :v_line_tot = 0 then
dbms_output.put_line('WARNING - Order selected does not have any lines');
dbms_output.put_line('WARNING - Please hit CTL-C to abort');
dbms_output.put_line('WARNING - If you chose to continue only Header information will be printed,');
dbms_output.put_line('WARNING - all additional entries will be ignored.');
:v_head_only := 'Y';
else
:v_head_only := 'N';
end if;
end;
/
/* Display all line_ids associated with this order number */
select
substr(LIN.LINE_ID,1,15) LINE_ID,
substr(to_char(LIN.line_number) ||
decode(LIN.shipment_number, null, null, '.' || to_char(LIN.shipment_number))||
decode(LIN.option_number, null, null, '.' || to_char(LIN.option_number)) ||
decode(LIN.component_number, null, null,
decode(LIN.option_number, null, '.',null)||
'.'||to_char(LIN.component_number))||
decode(LIN.service_number,null,null,
decode(LIN.component_number, null, '.' , null) ||
decode(LIN.option_number, null, '.', null ) ||
'.'|| to_char(LIN.service_number)),1,10) LINE_NUM,
ITM.SEGMENT1 ITEM,
substr(LIN.LINE_CATEGORY_CODE,1,10) lin_cat,
nvl(LIN.ORDERED_QUANTITY,0) Order_QTY,
LIN.ORDER_QUANTITY_UOM uom,
substr(LIN.FLOW_STATUS_CODE,1,20) Line_status,
nvl(LIN.SHIPPED_QUANTITY,0) shipped,
nvl(LIN.FULFILLED_QUANTITY,0) fulfilled,
nvl(LIN.INVOICED_QUANTITY,0) invoiced,
nvl(LIN.CANCELLED_QUANTITY,0) cancelled
from
OE_ORDER_LINES LIN,
MTL_SYSTEM_ITEMS ITM
where
LIN.HEADER_ID = nvl('&header_id_selected',:v_header_id)
and LIN.SHIP_FROM_ORG_ID = ITM.ORGANIZATION_ID(+)
and LIN.INVENTORY_ITEM_ID = ITM.INVENTORY_ITEM_ID(+)
and LIN.OPTION_NUMBER IS NULL
and LIN.ITEM_TYPE_CODE <> 'INCLUDED'
order by
NVL(LIN.ATO_LINE_ID, LIN.LINE_ID),
NVL(LIN.SORT_ORDER, '0000'),
NVL(LIN.LINK_TO_LINE_ID, LIN.LINE_ID),
NVL(LIN.SOURCE_DOCUMENT_LINE_ID, LIN.LINE_ID),
NVL(LIN.SERVICE_REFERENCE_LINE_ID, LIN.LINE_ID),
LIN.LINE_ID;
/* Display any header or line sets associated with this order */
PROMPT
PROMPT OE_SETS (SET)
select ST1.SET_ID SET_ID,
ST1.SET_NAME SET_NAME,
ST1.SET_TYPE SET_TYPE,
ST1.HEADER_ID HEADER_ID,
ST1.INVENTORY_ITEM_ID ITEM_ID,
ST1.ORDERED_QUANTITY_UOM UOM,
ST1.LINE_TYPE_ID LINE_TYPE_ID,
nvl(LST.SYSTEM_REQUIRED_FLAG,'N') SYS_REQD,
ST1.SET_STATUS STATUS,
to_char(ST1.SCHEDULE_SHIP_DATE,'DD-MON-RR_HH24:MI:SS') SCH_SHP_DT,
to_char(ST1.SCHEDULE_ARRIVAL_DATE,'DD-MON-RR_HH24:MI:SS') SCH_ARV_DT,
ST1.SHIP_FROM_ORG_ID SHIP_FROM,
ST1.SHIP_TO_ORG_ID SHIP_TO_ID,
ST1.SHIPMENT_PRIORITY_CODE SHIP_PRIORITY,
ST1.FREIGHT_CARRIER_CODE CARRIER,
ST1.SHIPPING_METHOD_CODE SHIP_METHOD,
ST1.SHIP_TOLERANCE_ABOVE STA,
ST1.SHIP_TOLERANCE_BELOW STB
from OE_SETS ST1,
OE_LINE_SETS LST
where ST1.SET_ID = LST.SET_ID(+)
and ST1.HEADER_ID = nvl('&header_id_selected',:v_header_id);
prompt
accept line_id_selected prompt 'Please enter LINE_ID from list above(leave blank for all lines): '
begin
if :v_line_tot > 0 then -- check for line_id
begin
select count(*)
into :v_line_cnt
from oe_order_lines_all
where header_id = nvl('&header_id_selected',:v_header_id)
and nvl('&line_id_selected',0) in (0,line_id);
if :v_line_cnt = 0 then
RAISE no_data_found;
end if;
exception
when no_data_found then
dbms_output.put_line('ERROR - Invalid line_id entered');
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
dbms_output.put_line('.');
:v_error := 1;
raise;
when others then
dbms_output.put_line('ERROR - Unable to retrieve order line due to error: '||SQLERRM);
dbms_output.put_line('ACTION - Please hit CTL-C to exit');
dbms_output.put_line('.');
:v_error := 1;
raise;
end;
end if;
end;
/
WHENEVER SQLERROR CONTINUE;
prompt
accept do_analysis prompt 'Do you want validation performed on this order (Default=Y): '
prompt
prompt
accept prt_wf prompt 'Do you want WorkFlow information printed? (Default=Y): '
accept prt_price prompt 'Do you want Pricing information printed? (Default=Y): '
accept prt_po prompt 'Do you want Purchasing information printed? (Default=Y): '
accept prt_rec prompt 'Do you want Receiving information printed? (Default=Y): '
accept prt_wip prompt 'Do you want Work In Progress information printed? (Default=Y): '
accept prt_inv prompt 'Do you want Inventory information printed? (Default=Y): '
accept prt_ar prompt 'Do you want Receivables information printed? (Default=Y): '
prompt
prompt Listing of tables for WMS could be extense, *** Note Default is No ***
accept prt_wms prompt 'Do you want WMS information printed? (Default=N): '
prompt
prompt Listing of tables MTL_UNIT_TRANSACTIONS (MUT), WSH_SERIAL_NUMBERS (WSN)
prompt and MTL_SERIAL_NUMBERS could be extense.
accept det_cnt prompt 'Do you want complete listing (Y-All records N-only the first 10 records)? (Default=Y): '
prompt
set heading off
def version_code='12.0.4.140610'
def c1='_'
def pref='HTMomse12'
def suff='.html'
def out_file='&pref&c1&order_number_selected&suff'
-- Printout the filename and location
column DUMMY new_value out_dir noprint
column DUMMY2 new_value host_nam noprint
select substr(value,1,instr(value||',',',')-1) DUMMY
from v$parameter where name = 'utl_file_dir';
select substr(machine,1,20) DUMMY2
from v$session where upper(program) like '%SMON%';
Prompt Output file created on Database Server: "&host_nam"
Prompt Complete path and filename: "&out_dir/&out_file"
set define '!'
-- Tables
def std='<table width=100% border=1 cellpadding=1 cellspacing=1>'
def et=</Table>
-- headers
def sh='<TR bgcolor="#cccc99"><td><font face="arial" color="#336699"> <b><i>'
def dh='</i></b></font></td><td><font face="arial" color="#336699"> <b><i>'
def dhr='</i></b></font></td><td><font face="arial" color="#ff0000"> <b><i>'
def eh='</i></b></font></td></tr>'
-- def sh='<TR bgcolor="#cccc99"><td><b><i>'
-- def dh='</i></b></td><td><b><i>'
-- def eh='</i></b></td></tr>'
-- lines
def sld='<tr bgcolor="#f7f7e7"><td><font face="arial" size="-1">'
def d='</font></td><td><font face="arial" size="-1">'
def el='</font></td></tr>'
-- def sld='<tr bgcolor="#f7f7e7"><td>'
-- def d='</td><td>'
-- def el='</td></tr>'
-- otros
def f=<br> -- eol
def b=<b> -- bold
def eb=</b> -- end bold
def sp=' ' -- space
set null ' '
set define '&'
-- Start of printout
DECLARE
handle UTL_FILE.FILE_TYPE;
dirname varchar2(1000);
text varchar2(1000);
function n(v varchar2) return varchar2 is -- to print fields
begin
if v is null then
return '&sp';
else
return v;
end if;
end n;
function n2(v varchar2,v2 varchar2) return varchar2 is -- to print parameters
begin
if v is null then
return v2;
else
return v;
end if;
end n2;
begin
-- Creation of output file
-- SQL fails when parameter is loo long
-- select substr(value,1,instr(value||',',',')-1) into dirname
-- from v$parameter where name = 'utl_file_dir';
select substr(value,1,decode(instr(value,','),0,length(value),instr(value,',')-1)) into dirname
from v$parameter where name = 'utl_file_dir';
-- handle := UTL_FILE.FOPEN(dirname,dirname||'/&out_file','W',32000);
handle := UTL_FILE.FOPEN('&out_dir','&out_dir/&out_file','W',32000);
/* HTML header information */
UTL_FILE.PUT_LINE(handle,'<html>');
UTL_FILE.PUT_LINE(handle,'<head>');
UTL_FILE.PUT_LINE(handle,'<title> &out_file </title>');
UTL_FILE.PUT_LINE(handle,'</head>');
UTL_FILE.PUT_LINE(handle,'<body background=#ffffff>');
UTL_FILE.PUT_LINE(handle,'<font face="arial" color="#336699"> <b><i>');
-- DataBase name, creation date
-- User and time script execution
UTL_FILE.PUT_LINE(handle,'&std &sh DATABASE IDENTIFICATION &dh SCRIPT EXECUTION (Ver. &version_code) &eh');
select '&sld &b DB Name: &eb '||name||' - &b Release: &eb '||substr(release_name,1,10)||' &d'||
'&b Script run at Date/Time: &eb'||to_char(sysdate,'DD-MON-RR_HH24:MI:SS')||'&el'||
'&sld &b Creation Date: &eb'||to_char(created,'DD-MON-RR_HH24:MI:SS')||'&d'
into text
from V$DATABASE, fnd_product_groups;
UTL_FILE.PUT_LINE(handle,text);
select '&b USER is: '||username into text from user_users;
UTL_FILE.PUT_LINE(handle,text);
UTL_FILE.PUT_LINE(handle,'&eb &el &et &f &f');
UTL_FILE.PUT_LINE(handle,'&std &sh The test will be run with the following parameters &dh Sections being printed: &dh &sp &eh');
UTL_FILE.PUT_LINE(handle,'&sld &b Order Number = &eb &order_number_selected &d WorkFlow = '||n2('&prt_wf','Y')||' &d Pricing = '||n2('&prt_price','Y')||' &el');
UTL_FILE.PUT_LINE(handle,'&sld &b Header Id = &eb &header_id_selected &d Purchasing = '||n2('&prt_po','Y')||' &d Receiving = '||n2('&prt_rec','Y')||' &el');
if :v_head_only = 'Y' then
UTL_FILE.PUT_LINE(handle,'&sld &b Line Id = &eb '||n2('','Header Only')||' &d Work in Progress = '||n2('&prt_wip','Y')||' &d Inventory = '||n2('&prt_inv','Y')||' &el');
else
UTL_FILE.PUT_LINE(handle,'&sld &b Line Id = &eb '||n2('&line_id_selected','All Lines')||' &d Work in Progress = '||n2('&prt_wip','Y')||' &d Inventory = '||n2('&prt_inv','Y')||' &el');
end if;
UTL_FILE.PUT_LINE(handle,'&sld &b Validation = &eb '||n2('&do_analysis','Y')||' &d Receivables = '||n2('&prt_ar','Y')||' &d Serial Num. Details = '||n2('&det_cnt','Y')||' &el');
UTL_FILE.PUT_LINE(handle,'&sld &sp &d WMS info = '||n2('&prt_wms','N')||' &d &sp &el');
UTL_FILE.PUT_LINE(handle,'&et &f &f');
-- Index for Major tables direct access
UTL_FILE.PUT_LINE(handle,'&std &sh INDEX FOR MAJOR TABLES DIRECT ACCESS &dh &sp &eh');
UTL_FILE.PUT_LINE(handle,'&sld <a HREF=http://www.mamicode.com/"#OE_SETS">OE_SETS (SET) &d WSH_TRIPS (TRP) &el');>
此脚本用于诊断销售订单的所有信息，运行完成后生产html报表，放在服务器上；
好了下面开始研究UTL_FILE包
包中主要的函数
FOPEN
IS_OPEN
GET_LINE
PUT
NEW_LINE
PUT_LINE
PUTF
FFLUSH
FCLOSE
FCLOSE_ALL
使用utl_file包之前应先建立目录
create or replace directory BFILE_DIR as
‘f:/home/oracle/bfiletest‘;
给用户读写该目录的权限
grant read，write on directory BFILE_DIR to lunar;
GRANT EXECUTE ON utl_file TO wangyh
UTL_FILE.FOPEN
用法
FOPEN会打开指定文件并返回一个文件句柄用于操作文件。
FUNCTION UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;
参数
location
文件地址
filename
文件名
openmode
打开文件的模式（参见下面说明）
max_linesize
文件每行最大的字符数，包括换行符。最小为1，最大为32767
3种文件打开模式：
R 只读模式。一般配合UTL_FILE的GET_LINE来读文件。
W 写（替换）模式。文件的所有行会被删除。PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用
A 写（附加）模式。原文件的所有行会被保留。在最末尾行附加新行。PUT, PUT_LINE, NEW_LINE, PUTF和FFLUSH都可使用
打开文件时注意以下几点：
文件路径和文件名合起来必须表示操作系统中一个合法的文件。
文件路径必须存在并可访问；FOPEN并不会新建一个文件夹。
如果你想打开文件进行读操作，文件必须存在；如果你想打开文件进行写操作，文件不存在时，会新建一个文件。
如果你想打开文件进行附加操作，文件必须存在。A模式不同于W模式。文件不存在时，会抛出INVALID_OPERATION异常。
FOPEN 会抛出以下异常
UTL_FILE.INVALID_MODE
UTL_FILE.INVALID_OPERATION
UTL_FILE.INVALID_PATH
UTL_FILE.INVALID_MAXLINESIZE
UTL_FILE.IS_OPEN用法
如果文件句柄指定的文件已打开，返回TRUE，否则FALSE
FUNCTION UTL_FILE.IS_OPEN (file IN UTL_FILE.FILE_TYPE) RETURN BOOLEAN;
UTL_FILE只提供一个方法去读取数据：GET_LINE
UTL_FILE.GET_LINE用法
读取指定文件的一行到提供的缓存。
PROCEDURE UTL_FILE.GET_LINE
(file IN UTL_FILE.FILE_TYPE,
buffer OUT VARCHAR2);
file
由FOPEN返回的文件句柄
buffer
读取的一行数据的存放缓存
buffer必须足够大。否则，会抛出VALUE_ERROR 异常。行终止符不会被传进buffer。
异常
NO_DATA_FOUND
VALUE_ERROR
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.READ_ERROR
UTL_FILE.PUT用法
在当前行输出数据
PROCEDURE UTL_FILE.PUT
(file IN UTL_FILE.FILE_TYPE,
buffer OUT VARCHAR2);
file
由FOPEN返回的文件句柄
buffer
包含要写入文件的数据缓存；Oracle8.0.3及以上最大允许32kB，早期版本只有1023B
UTL_FILE.PUT输出数据时不会附加行终止符。
UTL_FILE.PUT会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
UTL_FILE.NEW_LINE
在当前位置输出新行或行终止符，必须使用NEW_LINE来结束当前行，或者使用PUT_LINE输出带有行终止符的完整行数据。
PROCEDURE UTL_FILE.NEW_LINE
(file IN UTL_FILE.FILE_TYPE,
lines IN NATURAL := 1);
file
由FOPEN返回的文件句柄
lines
要插入的行数
如果不指定lines参数，NEW_LINE会使用默认值1，在当前行尾换行。如果要插入一个空白行，可以使用以下语句：
UTL_FILE.NEW_LINE (my_file, 2);
如果lines参数为0或负数，什么都不会写入文件。
NEW_LINE会产生以下异常
VALUE_ERROR
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
例子
如果要在UTL_FILE.PUT后立刻换行，可以如下例所示：
PROCEDURE add_line (file_in IN UTL_FILE.FILE_TYPE, line_in IN VARCHAR2)
IS
BEGIN
UTL_FILE.PUT (file_in, line_in);
UTL_FILE.NEW_LINE (file_in);
END;
UTL_FILE.PUT_LINE
输出一个字符串以及一个与系统有关的行终止符
PROCEDURE UTL_FILE.PUT_LINE
(file IN UTL_FILE.FILE_TYPE,
buffer IN VARCHAR2);
file
由FOPEN返回的文件句柄
buffer
包含要写入文件的数据缓存；Oracle8.0.3及以上最大允许32kB，早期版本只有1023B
在调用UTL_FILE.PUT_LINE前，必须先打开文件。
UTL_FILE.PUT_LINE会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
例子
这里利用UTL_FILE.PUT_LINE从表emp读取数据到文件：
declare
fileID UTL_FILE.FILE_TYPE;
BEGIN
fileID := UTL_FILE.FOPEN (‘BFILE_DIR‘, ‘emp.TXT‘, ‘W‘);
/* Quick and dirty construction here! */
FOR emprec IN (SELECT * FROM emp)
LOOP
UTL_FILE.PUT_LINE
(FILEID,TO_CHAR (emprec.empno) || ‘,‘ ||
emprec.ename || ‘,‘ ||
TO_CHAR (emprec.deptno));
END LOOP;
UTL_FILE.FCLOSE (fileID);
END;
/
GRANT READ ON DIRECTORY BFILE_DIR TO SCOTT;
GRANT write ON DIRECTORY BFILE_DIR TO SCOTT;PUT_LINE相当于PUT后加上NEW_LINE；也相当于PUTF的格式串"%s\n"。
UTL_FILE.PUTF
以一个模版样式输出至多5个字符串，类似C中的printf
PROCEDURE UTL_FILE.PUTF
(file IN FILE_TYPE
,format IN VARCHAR2
,arg1 IN VARCHAR2 DEFAULT NULL
,arg2 IN VARCHAR2 DEFAULT NULL
,arg3 IN VARCHAR2 DEFAULT NULL
,arg4 IN VARCHAR2 DEFAULT NULL
,arg5 IN VARCHAR2 DEFAULT NULL);
file
由FOPEN返回的文件句柄
format
决定格式的格式串
argN
可选的5个参数，最多5个
格式串可使用以下样式
%s
在格式串中可以使用最多5个%s，与后面的5个参数一一对应
\n
换行符。在格式串中没有个数限制
%s会被后面的参数依次填充，如果没有足够的参数，%s会被忽视，不被写入文件
UTL_FILE.PUTF会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
declare
fileID UTL_FILE.FILE_TYPE;
BEGIN
fileID := UTL_FILE.FOPEN (‘BFILE_DIR‘, ‘emp.TXT‘, ‘W‘);
/* Quick and dirty construction here! */
FOR emprec IN (SELECT * FROM emp)
LOOP
UTL_FILE.PUTf
(FILEID,TO_CHAR (emprec.empno) || ‘,‘ ||
emprec.ename || ‘,‘ ||
TO_CHAR (emprec.deptno),‘,‘);
utl_file.new_line(fileid);
END LOOP;
UTL_FILE.FCLOSE (fileID);
END;
/
UTL_FILE.FFLUSH
确保所有数据写入文件。
PROCEDURE UTL_FILE.FFLUSH (file IN UTL_FILE.FILE_TYPE);
file
由FOPEN返回的文件句柄
操作系统可能会缓存数据来提高性能。因此可能调用put后，打开文件却看不到写入的数据。在关闭文件前要读取数据的话可以使用UTL_FILE.FFLUSH。
典型的使用方法包括分析执行进度和调试纪录。
UTL_FILE.FFLUSH会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.INVALID_OPERATION
UTL_FILE.WRITE_ERROR
UTL_FILE.FCLOSE
关闭文件
PROCEDURE UTL_FILE.FCLOSE (file IN OUT FILE_TYPE);
file
由FOPEN返回的文件句柄
注意file是一个IN OUT参数，因为在关闭文件后会设置为NULL
当试图关闭文件时有缓存数据未写入文件，会抛出WRITE_ERROR异常
UTL_FILE.FCLOSE会产生以下异常
UTL_FILE.INVALID_FILEHANDLE
UTL_FILE.WRITE_ERROR
UTL_FILE.FCLOSE_ALL
关闭所有已打开的文件
PROCEDURE UTL_FILE.FCLOSE_ALL;
在结束程序时要确保所有打开的文件已关闭，可使用FCLOSE_ALL
也可以在EXCEPTION使用，当异常退出时，文件也会被关闭。
EXCEPTION
WHEN OTHERS
THEN
UTL_FILE.FCLOSE_ALL;
... other clean up activities ...
END;
注意：当使用FCLOSE_ALL关闭所有文件时，文件句柄并不会标记为NULL，使用IS_OPEN会返回TRUE。但是，那些关闭的文件不能执行读写操作（除非你再次打开文件）。
UTL_FILE.FCLOSE_ALL会产生以下异常
UTL_FILE.WRITE_ERROR
其他的资料
0.为避免目录修改导致程序的修改，目录可以定义为一个常量，或ORACLE的directory。
1.fopen的模式： R（只读），W（读写，且首先清除原有数据），A（读写，原有数据基础上追加数据）。
fopen的限制：
（1）目录和文件名必须合法
（2）目录必须存在
（3）若为R模式，文件必须存在
（4）若为W模式，若文件不存在，则自动创建
（5）若为A模式，则文件必须存在
2.is_open：检查文件是否打开（其实只检查句柄是否为空，比如fclose_all关闭的文件，is_open仍返回true）。
3.get_line：读取一行数据到varchar2变量中。
nvarchar2数据使用get_line_nchar；raw数据使用get_raw。
读取到文件末尾，产生no_data_found异常。还有如下三种情况也会产生no_data_found异常
（1）无返回行的select
（2）pl/sql集合中未定义的行
（3）使用dbms_lob读取bfile文件至末尾。
所以以上四种no_data_found情况在一个pl/sql块中，要区分捕获异常
4.put
9.fclose：若关闭前缓冲区中仍有未写入文件的数据，则触发write_error异常
.fclose_all:关闭所有打开的文件。关闭后，所有文件的句柄不变（仍为非NULL），例如测试中
步骤3使用fclose_all关闭文件，而is_opened仍为TRUE。但此时文件以不可读写。
原因：fclose传入文件句柄参数，且为IN OUT模式，调用后将句柄设置为NULL，而fclose_all
并未传入任何文件句柄参数，所以并未修改文件句柄的值（仍保持原值）。
6.frename：可以重命名文件，也可重命名路径（相当于FCOPY+FREMOVE），也可都改变
7.putf：put format
8.fgetattr：获取文件属性（是否存在，大小，块大小）
*/
PROCEDURE prc_utl_file
IS
file_read_handle utl_file.file_type;
file_write_handle utl_file.file_type;
is_opened BOOLEAN;
v_one_line VARCHAR2(1000);
b_file_exist BOOLEAN;
n_file_length NUMBER(10,2);
bi_block_size BINARY_INTEGER;
BEGIN
--1.读/读写模式打开文件
file_read_handle := utl_file.fopen(‘TEST_UTL_FILE_DIR_READ‘, ‘orcl_ora_396.trc‘, ‘R‘);
file_write_handle := utl_file.fopen(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE.txt‘, ‘W‘);
--2.检查文件是否打开
is_opened := utl_file.is_open(file_read_handle);
IF is_opened THEN
dbms_output.put_line(‘file is opened‘);
ELSE
dbms_output.put_line(‘file is not opened‘);
END IF;
--3.读文件
LOOP
BEGIN
utl_file.get_line(file_read_handle, v_one_line);
dbms_output.put_line(v_one_line);
-- 4.将读入结果写入新文件中
utl_file.put(file_write_handle, v_one_line);
utl_file.new_line(file_write_handle, 2);
--utl_file.put_line(file_write_handle, v_one_line);
--utl_file.put_line(file_write_handle, v_one_line, TRUE);
EXCEPTION
WHEN no_data_found THEN
EXIT;
WHEN OTHERS THEN
dbms_output.put_line(‘error1:‘||SQLERRM);
EXIT;
END;
END LOOP;
--5.关闭文件
utl_file.fclose(file_read_handle);
--6确认所有未决的数据都写到物理文件中
--utl_file.fflush(file_write_handle);
utl_file.fclose(file_write_handle);
--utl_file.fclose_all;
--6.检查文件是否关闭
is_opened := utl_file.is_open(file_read_handle);
IF is_opened THEN
dbms_output.put_line(‘file is still opened‘);
ELSE
dbms_output.put_line(‘file is already closed‘);
END IF;
--7.拷贝文件
utl_file.fcopy(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE.txt‘, ‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY.txt‘, 1, 10);
--8.删除文件
utl_file.fcopy(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE.txt‘, ‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY_DELETE.txt‘, 1, 10);
utl_file.fremove(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY_DELETE.txt‘);
--9.重命名
--utl_file.frename(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY.txt‘, ‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY_DELETE_RENAME.txt‘, FALSE);
--10.获取重命名后的文件属性
utl_file.fgetattr(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY.txt‘,b_file_exist,n_file_length, bi_block_size);
IF b_file_exist THEN
dbms_output.put_line(‘n_file_length:‘||n_file_length||‘\n‘||‘bi_block_size‘||bi_block_size);
END IF;
END;
utl_file包的使用
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。