Home » SQL & PL/SQL » SQL & PL/SQL » Define a Record Layout (merged 3 topics, deleted 2)
Define a Record Layout (merged 3 topics, deleted 2) [message #310905] |
Wed, 02 April 2008 15:51  |
pcd_lou
Messages: 63 Registered: October 2007 Location: Florida
|
Member |
|
|
Hi all,
I need to define a Flat File Layout and Read the record into the layout to use the fields that are subdefined. I have read some manuals and some web sites (like: http://www.cs.umbc.edu/help/oracle8/server.815/a67842/04_colls.htm#7658 ) and come up with this below but I get a compile error.
Can anyone see the problem? This is the first time I'm trying this.
In the Declarations area:
TYPE Options_Record_Layout IS RECORD (
O_Pub_Code Char(03), -- Publication Code
O_Key_Cde Char(08), -- Key Code
O_Sub_Amount Char(24), -- Price Per Individual Sub
O_dnr_nbr Char(12), -- Sponsor's Account Number
O_Dnr_Typ Char(01), -- Individual/Gift/Agency
O_Bil_Sts Char(01), -- Billed/Paid
O_Cir_Sts Char(01), -- Regular/Comp
O_Sub_Cls Char(01), -- Perpetual Flag
O_Nbr_Isu Char(03), -- Term
O_Exempt_Duplicate Char(01), -- Exempt from Duplication
O_Promote_Adr Char(01), -- Promote Address
O_Promote_Phn Char(01), -- Promote Phone
O_Promote_Fax Char(01), -- Promote Fax
O_Promote_Eml Char(01), -- Promote E-Mail
O_Start_Date Char(08), -- Start Date
O_Wrap_Code Char(10), -- Wrap Code
O_Lcl_Cur Char(04), -- Currency
O_wrh_nbr Char(03), -- Warehouse
O_Language Char(05), -- Language
O_Dlv_Cde Char(02), -- Delivery Code
O_Trm_Nbr Char(04) -- Term Number
);
Opt_Rec Options_Record_Layout;
178: Opt_Record Options_Record_Layout%TYPE;
In the Process area:
-- Get the Options Record.
W_OptFile := UTL_FILE.Fopen( W_Environment, W_OptFile_Name, 'R', 100);
UTL_File.Get_Line (W_OptFile, W_OptRec);
356: Opt_Record := W_OptRec;
357: Opt_Rec := Opt_Record;
UTL_FILE.FClose (W_OptFile); -- Close File
The Fields W_Environment and W_OptFile_Name are defined.
I get this error:
SQL> show err
Errors for PROCEDURE CRT_CIR125_MUL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
178/16 PL/SQL: Item ignored
178/16 PLS-00206: %TYPE must be applied to a variable, column, field or attribute, not to "OPTIONS_RECORD_LAYOUT"
356/2 PL/SQL: Statement ignored
356/2 PLS-00320: the declaration of the type of this expression is incomplete or malformed
357/2 PL/SQL: Statement ignored
357/16 PLS-00320: the declaration of the type of this expression is incomplete or malformed
Any Ideas?????
Thanks,
Lou
[Updated on: Wed, 02 April 2008 23:49] by Moderator Report message to a moderator
|
|
|
|
Re: Define a Record Layout (merged 3 topics, deleted 2) [message #311120 is a reply to message #310905] |
Thu, 03 April 2008 08:10   |
pcd_lou
Messages: 63 Registered: October 2007 Location: Florida
|
Member |
|
|
Well that explains the Compile Error Message, but doesn't solve my issue.
The definition is not of Table Row. It is of a FLAT SEQUENTIAL file that I get with a
UTL_File.Get_Line (W_OptFile, W_OptRec);
The Record Length is 96 and I want to sub-define it rather than getting the record in a work area and SubStringing each field out.
Is this the way to do it?
What's wrong with the Definition?
Any help would be appreciated.
Thanks,
Lou
|
|
|
Re: Define a Record Layout (merged 3 topics, deleted 2) [message #311139 is a reply to message #311120] |
Thu, 03 April 2008 09:42   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
External tables and SQL*Loader are utilities intended for loaded comma-delimited text files into tables. If you want to use utl_file, then I don't think you are going to be able to avoid using substr and instr to parse out each field. You could use a function to make parsing easier, as demonstrated below.
-- contents of dept.txt:
86,CONTROL,SOMEWHERE
99,CHAOS,NOWHERE
SCOTT@orcl_11g> SELECT * FROM dept ORDER BY deptno
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\oracle11g'
2 /
Directory created.
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION list_element
2 (p_string VARCHAR2,
3 p_element INTEGER,
4 p_separator VARCHAR2 DEFAULT ',')
5 RETURN VARCHAR2
6 AS
7 v_string VARCHAR2 (32767);
8 BEGIN
9 v_string := p_separator || p_string || p_separator;
10 v_string := SUBSTR (v_string,
11 INSTR (v_string, p_separator, 1, p_element)
12 + LENGTH (p_separator));
13 RETURN SUBSTR (v_string, 1, INSTR (v_string, p_separator) - 1);
14 END list_element;
15 /
Function created.
SCOTT@orcl_11g> DECLARE
2 W_OptFile UTL_FILE.FILE_TYPE;
3 W_Environment VARCHAR2 (30) := 'MY_DIR';
4 W_OptFile_Name VARCHAR2 (30) := 'dept.txt';
5 dept_text VARCHAR2 (100);
6 TYPE Options_Record_Layout IS RECORD (
7 DEPTNO NUMBER(2),
8 DNAME VARCHAR2(14),
9 LOC VARCHAR2(13));
10 Opt_Rec Options_Record_Layout;
11 BEGIN
12 W_OptFile := UTL_FILE.Fopen (W_Environment, W_OptFile_Name, 'R', 100);
13 LOOP
14 BEGIN
15 UTL_File.Get_Line (W_OptFile, dept_text);
16 Opt_Rec.deptno := list_element (dept_text, 1);
17 Opt_Rec.dname := list_element (dept_text, 2);
18 Opt_Rec.loc := list_element (dept_text, 3);
19 INSERT INTO dept VALUES opt_rec;
20 EXCEPTION
21 WHEN NO_DATA_FOUND THEN EXIT;
22 END;
23 END LOOP;
24 UTL_FILE.FClose (W_OptFile);
25 END;
26 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> SELECT * FROM dept ORDER BY deptno
2 /
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
86 CONTROL SOMEWHERE
99 CHAOS NOWHERE
6 rows selected.
SCOTT@orcl_11g>
[Updated on: Thu, 03 April 2008 09:46] Report message to a moderator
|
|
|
|
Re: Define a Record Layout (merged 3 topics, deleted 2) [message #311464 is a reply to message #311448] |
Fri, 04 April 2008 10:07   |
 |
Barbara Boehmer
Messages: 9104 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I vaguely remember PIC clauses from the college COBOL classes I attended many years ago. It sounds like what you really need is external tables or SQL*Loader, as I said previously. Trying to do it in PL/SQL using UTL_FILE is doing things the hard way. Here is a brief demonstration using an external table, to convince you. Once you have created the external table, then you can just select from it from SQL or PL/SQL and do whatever you want with the data. You can access the data in the text file through the external table or insert it into a regular table from the external table.
-- contents of c:\oracle11g\dept.txt (fixed format, not delimited):
86CONTROL SOMEWHERE
99CHAOS NOWHERE
-- create Oracle directory object and external table:
SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY my_dir AS 'c:\oracle11g'
2 /
Directory created.
SCOTT@orcl_11g> CREATE TABLE group_field
2 (dept_number NUMBER(2),
3 dept_name VARCHAR2(14),
4 dept_loc VARCHAR2(13))
5 ORGANIZATION external
6 (TYPE oracle_loader
7 DEFAULT DIRECTORY MY_DIR
8 ACCESS PARAMETERS
9 (RECORDS DELIMITED BY NEWLINE
10 LOGFILE 'MY_DIR':'test.log'
11 FIELDS LDRTRIM
12 (dept_number (1:2) CHAR(2),
13 dept_name (3:16) CHAR(14),
14 dept_loc (17:29) CHAR(13)))
15 LOCATION ('dept.txt'))
16 /
Table created.
-- results:
SCOTT@orcl_11g> SELECT * FROM group_field
2 /
DEPT_NUMBER DEPT_NAME DEPT_LOC
----------- -------------- -------------
86 CONTROL SOMEWHERE
99 CHAOS NOWHERE
SCOTT@orcl_11g>
|
|
|
Re: Define a Record Layout (merged 3 topics, deleted 2) [message #311548 is a reply to message #311464] |
Fri, 04 April 2008 14:25  |
pcd_lou
Messages: 63 Registered: October 2007 Location: Florida
|
Member |
|
|
Barbara,
Thanks for the code. I guess I'll have to do it that way. I just hoped that there was an easier way within the progamming language to address a location with multiple fields and overlaping a field within another.
Thank you very much for your help.
Have a great weekend.
Thanks,
Lou
|
|
|
Goto Forum:
Current Time: Sun Feb 16 19:41:29 CST 2025
|