Loading EBCDIC data directly into the Oracle Database using SQL*Loader
Specify the Characterset WE8EBCDIC500 for the EBCDIC data. The following example shows the SQL*Loader Controlfile to load a fixed length EBCDIC record into the Oracle Database.
LOAD DATA
CHARACTERSET WE8EBCDIC500
INFILE data.ebc "fix 86 buffers 1024"
BADFILE data.bad'
DISCARDFILE data.dsc'
REPLACE
INTO TABLE temp_data
(
field1 POSITION (1:4) INTEGER EXTERNAL,
field2 POSITION (5:6) INTEGER EXTERNAL,
field3 POSITION (7:12) INTEGER EXTERNAL,
field4 POSITION (13:42) CHAR,
field5 POSITION (43:72) CHAR,
field6 POSITION (73:73) INTEGER EXTERNAL,
field7 POSITION (74:74) INTEGER EXTERNAL,
field8 POSITION (75:75) INTEGER EXTERNAL,
field9 POSITION (76:86) INTEGER EXTERNAL
)
USEFUL EXCEL MACRO TO EXTRACT AND DELIMIT YOUR DATA Excel TEXT WRITE macro (FREE!) If
you have comma's embedded in your data (address, names, etc) Excel will not make
a sufficient delimited file too easy to produce. I came across this macro and
been using it since 2001 to produce files for SQL*Loader.
Also I suggest using "|" as the delimiter especially if you might have commas in any text fields, plus its easier on your eyes
Other 3rd Party Sofware needed for installation and operation of Oracle
MKS ToolKit - http://www.mkssoftware.com need for upgrading
Oracle does not include a shell interpreter with Oracle E-Business Suite Release 11i or Oracle9i Application Server. MKS Toolkit (http://www.mkssoftware.com ), a third-party set of utilities that includes several UNIX shell interpreters and Perl, is recommended. MKS Toolkit for System Administrators, MKS Toolkit for Developers, and any other MKS Toolkit suites that include UNIX shell interpreters and Perl are sufficient to support installation of Discoverer 4i content for Release 11i.
SQL*Loader - utility that loads flat sequential files into database
tables NOTE: Runs in DOS mode, unsure about Unix or other
platforms Discoverer - good for adhoc reporting, even extracts
report into an Excel spreadsheet. Can select/omit with and without parameters.
Handles some logic and formatting but not flexible enough for complex,
customized reports and forms. Use SQL, PL/SQL, Report/Form Builder (see below)
for complex & customized reports and forms such as Schedule Outstanding
Bills, custom invoices and statements. Report Builder (Reports) -
Builds, processes customized screens/reports/forms for input/output Form
Builder (Forms) - derivative of above SQL*Plus - Oracle's SQL
interface PL/SQL - Oracle's advanced SQL with cursors, complex
logic
Building Intelligent Databases with Oracle PL/SQL, Triggers
& Stored Procedures by Kevin T. Owens, Prentice-Hall, 1996, ISBN
0-13-443-631-8
Oracle PL/SQL Developer's Workbook, Steven Feuerstein with
Andrew Odewahn, O'Reilly, 2000
Oracle PL/SQL Programming, Steven Feuerstein with Bill Pribyl,
O'Reilly, 1997
Oracle Desk Reference, Guy Harrison, Prentice-Hall, 2000, ISBN
0-13-013292-2
Oracle Web Applications: PL/SQL Developer's Introduction, Andrew
Odewahn, O'Reilly, 1999
Oracle SQL*Loader: The Definitive Guide, Jonathan Gennick,
Sanjay Mishra, April 2001 ISBN 1-56592-948-9
TOAD Pocket Reference for Oracle, Jim McDaniel, Patrick McGrath
August 2002, ISBN 0-596-00337-4
Oracle E-Business Suite 11i: Implementing Core Financial
Applications, Susan Foster, 2001, Wiley & Son's, ISBN 0-471-41205-8
Oracle Utilities: Using Hidden Programs, Import/Export,
SQL*Loader, oradebug, tkprof, & More (Oracle In-Focus series) by Dave
Moore, Donald K. Burleson
Q: TEXT_IO doesn't work in SQL+ or PL/SQL why ? A: TEXT_IO
can only be run in a PL/SQL script inside oracle FORMS only, not a database
package/procedure called from forms.
The Pl/SQL script has to be a
program unit from within forms or in a forms library.
Q: I'm not happy with Oracle's "canned" Invoices and
other output, what can I do ? A: Various products exist as shown
below. I myself wrote a PL/SQL program to handle this. (contact me if
interested) Oracle created these views which have all the data you would
need: AR_INVOICE_HEADER_V, AR_INVOICE_LINE_V. Check eTRM (on Metalink)for
the other views you might need (taxes, etc). See NOTE: Note:68149.1 on
Metalink also
Q: How can I load data into Oracle tables: A:
1. SQL Loader 2. TEXT_IO at FORM level, this package use to read from file
3. UTL_FILE, At PL/SQL level package for read text file.
Q: What causes SQL*Loader-625: Multibyte character error in
control file when I run SQL*Loader? A: You might have pressed
backspace, or an unprintable character somewhere in your control file. You might
want to re-type the lines using NOTEPAD
SQL*Loader: Release 8.1.7.0.0 - Production on Thu May 2 10:07:31 2002
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL*Loader-625: Multibyte character error in control file.
Example for correction:
LOAD DATA
INFILE 'C:\MY SQL\cust_apr30.txt' <- you might need to fully re-type this line
Q: SQL*Loader doesn't run after I click the MS-DOS prompt in
Windows, why ?
A: see below
How to execute SQL*LOADER
Run your MS-DOS prompt using the Command Prompt as shown below:
You cannot run this DOS program through the START ' RUN method!!!!!
INTERFACE AND ERROR's DEBUGGING under Oracle Apps
Q: I’m trying to run Customer Interface (OR OTHER INTERFACES)
and I can’t make out what the error’s mean in the report
A: Do the following:
You are running Customer Interface and there is an error message in the
INTERFACE_STATUS column, but you don't know what it means.
SQL> select interface_status from
ra_customers_interface_all; ? change to appropriate table
INTERFACE_STATUS
B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1,
B0,O3,E2,D1,D2,D3,D4,D5,D6,D7,D0,E1,
NOTE: THIS APPLIES TO ALL INTERFACE TABLES!!!!!!!!!!!!!!!
EACH TABLE HAS THIS FIELD
Look on Metalink, and search for Document ID: 1073634.6 or see link below
See Note:230754.1 on Metalink: titled "Different uses of RACUST Customer Interface">
Most tables end with _INTERFACE or _ALL (single or multi-org) examples below
Oracle Financials – Payables - Invoices
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
Oracle Financials – Receivables - Customers
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL
HZ_PARTY_INTERFACE
Q: Differences between Interface's and API's
INTERFACE's: use for mass loads, migrations,conversions; you can populate interface tables with many records and then start interface any time, so it's asynchronius; if any record fails, it will stay in the interface tables till either fixed or purged
API's for: synchronious tasks, like integrations or web site calls; you normally would only be processing one record at the time and get results right away; also, you would have to handle situations where Error status is returned
Q: What does the date value of 31-DEC-4712 mean ?
A: it is reserved for "future closed date" indicating the transaction is still open. see NOTE: 1014791.102
Related Oracle products
Question: Difficulty EXPORTING Discoverer Reports into PDF format
"Internal Error in Generating Report Export Failed"
Use either of these two products and instead of (F)ile->(E)xport, use (F)ile->(P)rint then select the PDF printer
Question: What versions are loaded on my machine ?
SQL> select substr(product,1,15) product,substr(version,1,15) version,substr(status,1,15) status
from product_component_version
PRODUCT VERSION STATUS
--------------- --------------- ---------------
NLSRTL 3.4.1.0.0 Production
Oracle8i Enterp 8.1.7.3.0 Production
PL/SQL 8.1.7.3.0 Production
TNS for 32-bit 8.1.7.3.0 Production
also,
SQL> select * from v$version where banner like 'Oracle%';
BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
Question: How to produce a delimited file from Oracle using SQL ?
this is for file transfer between databases, spreadsheets, platforms, etc NOTE: TOAD & Discoverer also have this feature
filename: xxx.sql
set heading off
set linesize 100
set pagesize 0
set feedback off
spool c:\test.txt
select jobno ||'","',nyctpm ||'","', descr || '","' from jobxref;
spool off
- - - - - - - - - - output - - - - - - - - -
outfile: test.txt
95578"," LIONEL SAINT LOUIS"," CORRECT-FALLING DEBRIS,ICE,WAT","
- - - - - OR - - - - - - - -
SET SERVEROUTPUT ON
SET ECHO OFF
SET VERIFY OFF
SET Heading OFF
SET LINESIZE 2000
SET NEWPAGE NONE
SET PAGESIZE 100
SET Heading OFF
SET COLSEP , <- this saves you the headache of coding each column!!!
spool c:\myfile.txt
select * from tablename <---- select all from your table
spool off
NOTE: if any of the columns have ',' embedded in them like the address you might be in a bit of a bind. So you might need a unique delimiter like '|' or ']'
You must include tick marks with this delimiter !!! See below line:
SET COLSEP '|' or SET COLSEP ']'
your file will look something like this:
92877|S SHECTER |EMGINEERING & TECHNICAL FIELD
92881|S SHECTER |ENGINEERING & TECHNICAL FIELD
Question: What Applications/Versions/Patch Levels are loaded on my machine ?
this query gives more details than the one below
select substr(a.APPLICATION_NAME,1,60) "Application Name"
, substr(i.PRODUCT_VERSION,1,4) "Version"
, i.PATCH_LEVEL "Patch Level"
, i.APPLICATION_ID "Application ID"
, i.LAST_UPDATE_DATE "Last Update"
from APPS.FND_PRODUCT_INSTALLATIONS i
, APPS.FND_APPLICATION_ALL_VIEW a
where i.APPLICATION_ID = a.APPLICATION_ID
-- not all applications update the next field correctly
-- and i.PATCH_LEVEL like '11i%'
-- these are the applications that concern me most
-- and i.APPLICATION_ID in ('0','140','260','101','200','275','201','222','185')
order by a.APPLICATION_NAME
Question: What patches are loaded on my machine ?
select patch_name,
patch_type,
applied_patch_id,
rapid_installed_flag,
maint_pack_level
from ad_applied_patches
where patch_name like '%'
order by 1
OR
select * from ad_bugs
This SQL lists the objects under owner APPS ( PKG, VIEW, etc)
select owner,object_name,object_type from dba_objects where owner='APPS'
How do I obtain CCID ( code combination id) / Chart of Account data for General Ledger
select
substr(gl.code_combination_id,1,5) ccid,
substr(gl.segment1,1,5) Auth,
substr(gl.segment2,1,8) Account,
substr(gl.segment3,1,5) RC,
substr(gl.segment4,1,5) Func,
substr(gl.segment5,1,5) Job
FROM gl.gl_code_combinations gl
order by code_combination_id
Question: How many transactions exist by GL DATE (period) ?
select gl_date,count(gl_date)
from ra_cust_trx_line_gl_dist_All
where account_class = 'REV'
group by gl_date
How to find your current GL Period END DATE
select a.END_DATE
from GL_PERIOD_STATUSES a
where a.application_id = '222' and
a.closing_status = 'O' and
a.start_date =
(select max(b.start_date) from gl_period_statuses b where
b.application_id = '222' and b.closing_status = 'O')
select max(gl_date) from
ra_cust_trx_line_gl_dist_all
SQL> /
MAX(GL_DATE)
---------
31-MAR-05
Question: What DB version is on my machine ?
SQL> VARIABLE VERSION VARCHAR2(50)
SQL> VARIABLE COMPATIBILITY VARCHAR2(50)
SQL> EXEC DBMS_UTILITY.DB_VERSION(:VERSION,:COMPATIBILITY)
PL/SQL procedure successfully completed.
SQL> PRINT VERSION
VERSION
----------------------------------------
8.0.4.0.0
SQL> PRINT COMPATIBILITY
COMPATIBILITY
----------------------------------------
8.0.0
Question: What machine, server or instance am I using ??
SQL> Select name from v$database;
SQL> select instance_name from v$instance;
SQL> select * from global_name;
SQL> SELECT VALUE FROM V$PARAMETER WHERE NAME='db_name';
SQL> select to_number(translate(substr(version,1,9),'1.$','1')) from v$instance;
SQL> select s.machine from v$session s where s.audsid = userenv('sessionid');
SQL> select global_name from global_name;
NAME
---------
ARGP
also you can use this:
SQL> select sys_context('USERENV','DB_NAME') AS instance from dual;
INSTANCE
---------------------------------------------------------------------
ARGP
SQL> select substr(release_name,1,7) Version,
2 substr(rpad(MULTI_ORG_FLAG,2,' '),1,2) "MO",
3 substr(rpad(MULTI_CURRENCY_FLAG,3,' '),1,3) "MRC"
4 from apps.fnd_product_groups;
VERSION MO MRC
------- -- ---
11.5.7 N N
SQL> select arp_util.ar_server_patch_level from dual;
AR_SERVER_PATCH_LEVEL
-------------------------------------------------------------------------
11i.AR.H
SQL> select distinct patch_level from apps.fnd_product_installations
2 where patch_level like '%AR%'
PATCH_LEVEL
------------------------------
11i.AR.H
This variation will give you the machine name you are running on:
SQL> Select sys_context('USERENV','TERMINAL') from dual;
SYS_CONTEXT('USERENV','TERMINAL')
--------------------------------------------------------------
AR0669
Question: How can I retrieve a random number ?
SQL> select dbms_random.random from dual;
RANDOM
----------
495129087
Question: What patches are loaded on our machine ?
SQL> select * from AD_APPLIED_PATCHES
Question: How do I derive the user name from the 4-digit reference number ?
Use the FND_USER table as shown below
select user_id,substr(user_name,1,20) username,
substr(description,1,20) fullname,last_logon_date,start_date from fnd_user
USER_ID USERNAME FULLNAME LAST_LOGO START_DAT
--------- -------------------- -------------------- --------- ---------
-1 ANONYMOUS Anonymous user name 01-JAN-51
1 AUTOINSTALL This application use 05-MAR-00 01-JAN-51
4 CONCURRENT MANAGER This application use 01-JAN-51
3 FEEDER SYSTEM Use this id for data 01-JAN-51
2 INITIAL SETUP Dummy user for initi 01-JAN-51
5 APPSMGR User for routine mai 01-JAN-51
0 SYSADMIN System Administrator 19-JAN-05 01-JAN-51
1003 IEXADMIN Oracle Collections A 08-FEB-00 28-SEP-99
1007 ASGUEST AS Guest 06-MAR-00 16-FEB-00
1008 IBE_ADMIN iStore Administrator 06-DEC-00 26-APR-00
1005 OP_SYSADMIN OP System Administra 15-JUN-00 27-DEC-99
1006 OP_CUST_CARE_ADMIN Customer Care Admini 05-JAN-00 27-DEC-99
Question: What Family Patch Levels are on my machine ?
SELECT FA.APPLICATION_SHORT_NAME APP,
FPI.PATCH_LEVEL
FROM FND_PRODUCT_INSTALLATIONS FPI,
FND_APPLICATION FA
WHERE FA.APPLICATION_ID = FPI.APPLICATION_ID;
APP PATCH_LEVEL
-------------------------------------------------- ------------------
ABM 11i.ABM.F
AMV 11i.AMV.E
AR 11i.AR.H
Question: Am I setup for Multi-Org ?
select multi_org_flag from fnd_product_groups;
Useful DATE output SQL
This format is yymmdd = year month day | hh24mi = 24 hour clock and minutes
select to_char(sysdate,'yymmddhh24mi')
from dual
TO_CHAR(SY
----------
0409141005
select to_char(sysdate,'hh24:mi:ss')
from dual
TO_CHAR(
--------
10:11:14
In SQL*Plus or in your script add this line before your excute
SQL> set serveroutput on
SQL> execute xxxxxxxxxxx.yyyyyyyyyyyyy;
and you will see this: PL/SQL procedure successfully completed.
Question: Alignment, word wrapping and leading spaces are not displayed correct when I
run PL/SQL through SQL*Plus instead of running through TOAD
Add FORMAT WRAP to the set serveroutput on as shown below, see Notes: 1008252.6 and 159951.1 on Metalink
SQL> set serveroutput on F0RMAT WRAP
SQL> execute xxxxxxxxxxx.yyyyyyyyyyyyy;
Click this link also
Question: Upon compiling my PL/SQL I see this:
expected symbol name is missing - Why ?
This is becuase you have an ampersand ( & ) embedded in your text or code and oracle thinks its a variable and expects a variable name after it. You probably commented using the "&" as I have numerous time.
Miscellaneous
Question: Using SQL*Plus how can I produce
a flat file without headings, feedback, etc ?
Try these set commands just before your spool command.
set newpage 0
set space 0
set linesize 80
set pagesize 0
set echo off
set feedback off
set heading off
Creating a duplicate TABLE of an existing Table
This isn’t copying the data or entire database but rather making an exact copy of a table’s structure
to test a load or copy the data into.
create table
as select * from ;
Example:
SQL> create table temp_cust_int
AS Select * from ra_customers_interface;
Another option is :
SQL> create table x as select * from emp where 1=2;
SQL> select count(*) from jobxref;
COUNT(*)
---------
4227
SQL> copy from apps/apps@argp -
> replace jobxref - or create jobxref -
> using select * from jobxref;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table JOBXREF dropped.
Table JOBXREF created.
4255 rows selected from apps@argp.
4255 rows inserted into JOBXREF.
4255 rows committed into JOBXREF at DEFAULT HOST connection.
SQL> select count(*) from jobxref;
COUNT(*)
---------
4255
Creating and viewing user sequence numbers
SQL> create sequence crmemo_counter
2 minvalue 0
3 maxvalue 999999999999
4 start with 1
5 increment by 1
6 cache 20
7 /
Sequence created.
SQL> select * from user_sequences;
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ --------- --------- ------------ - - ---------- -----------
CRMEMO_COUNTER 0 1.000E+12 1 N N 20 1
INVOICE_COUNTER 0 1.000E+12 1 N N 20 1
POR_REQ_NUMBER_S 1 1.000E+27 1 N N 0 100000
RA_TRX_NUMBER_1000_S 1 999999999 1 N Y 20 2
How to list table CONSTRAINTS (child tables)
SELECT TABLE_NAME, CONSTRAINT_NAME, R_CONSTRAINT_NAME
FROM USER_CONSTRAINTS
WHERE TABLE_NAME like ('%AR%')
TABLE_NAME CONSTRAINT_NAME R_CONSTRAINT_NAME
------------------------------ ------------------------------ ------------------------------
ABMBV_ACT_ACCT_DATA_VARIANCE SYS_C0090705
ABMBV_ACT_RATE_DATA_VARIANCE SYS_C0090707
ABMBV_RE_ACCT_DATA_VARIANCE SYS_C0090709
ARBV_ADJUSTMENT_DISTRIBUTIONS SYS_C0062119
ARBV_ADJ_DISTS_ALT_CRNCY SYS_C00117692
ARBV_APPLICATION_DISTRIBUTIONS SYS_C00117685
Sending any ASCII code using DBMS_OUPUT in PL/SQL
dbms_output.put_line(chr(12)); = ASCII 12 for Form Feed
I run my PL/SQL through SQL*Plus but I dont see anything? Why ?
Before you Execute the PL/SQL do the following:
SQL> set serveroutput on
SQL> execute xxxxxxxxxxxxxx.xxxxxxxxxxx; dont forget the semicolon
How can I VIEW PL/SQL code
use the package body name as shown below and enter it when prompted.
CREATE OR REPLACE PACKAGE BODY apps_ar_forms_dup_trackcm AS
select line, text
from user_source
where upper(name) = upper('&PLSQL_NAME')
order by line
Also you can use TOAD
Click Database, then Schema Browser, wait a few minutes. Then click the PROCS tab and look for your FUNCTION, PROCEDURE or PACKAGE. click here for a screen print example
How can I date/time stamp the spool filename ?
copy and paste this and save it as a xxx.sql file into your "bin" directory
example: on Sep 16, 2004 at 2:17pm the filename will be: argp_inv0409161417.txt
Prompt ====================================
Prompt
Prompt Creating the SIMPLEX Invoice file
Prompt
Prompt ====================================
set serveroutput on format wrap
set linesize 150
column dt new_value _dt <--- date column to store yymmddhh value for the filename
column txt new_value _txt <--- stores ".txt" for DOS file extension
select to_char(sysdate,'yymmddhh24mi') dt from dual; <--- stores the system date/time into "date"
select to_char('.txt') txt from dual; <--- stores ".txt" into the txt field
spool argp_inv&_dt&_txt <--- opens spool file with dynamic filename
execute apps.apps_ar_forms_dup_track.print_invoice;
SPOOL OFF
Prompt ====================================
Prompt Invoice file to be printed is completed
Prompt
Prompt The file is created and exists in your
Prompt bin directory
Prompt ====================================
SQL*Plus substitution variables (used to store database columns as variables in SQL*Plus) ?
link above lets you view, search and post these groups
Usenet
comp.databases.oracle comp.databases.oracle.marketplace
comp.databases.oracle.misc comp.databases.oracle.server.* (1) comp.databases.oracle.tools.* (1)
FND - Apps Foundation Level CM - Cash Management
GL - General Ledger INV - Inventory
AP - Accounts Payable OPM - Process Manufacturing
AR - Accounts Receivable BOM - Bill of Material
PO - Purchasing
Metalink: 1012626.102 ( GL to AR Mapping ) ; 1011799.6 ( GL to AP Mapping)
Check
Barnes and Noble for Oracle books !!!
Check
Amazon for Oracle books !!
click here for a screen print example