Home » SQL & PL/SQL » SQL & PL/SQL » Define a Record Layout (merged 3 topics, deleted 2)
icon5.gif  Define a Record Layout (merged 3 topics, deleted 2) [message #310905] Wed, 02 April 2008 15:51 Go to next message
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 [message #310928 is a reply to message #310905] Wed, 02 April 2008 20:04 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Did you expect to get more/better responses by spamming this forum with three copies of the same post?

http://www.mydatabasesupport.com/forums/oracle-errors/290510-pls-00206-type-must-applied-variable-column-field-attribute-not-string.h tml

It appears you would benefit from remedial forum use & GOOGLE search training

[Updated on: Wed, 02 April 2008 20:35] 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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

icon14.gif  Re: Define a Record Layout (merged 3 topics, deleted 2) [message #311448 is a reply to message #311139] Fri, 04 April 2008 09:19 Go to previous messageGo to next message
pcd_lou
Messages: 63
Registered: October 2007
Location: Florida
Member

Barbara,

I got pulled off onto another project yesturday.

That code is what I was looking for. The Options record is not comma delimited but I do understand what you're doing.

I was hoping that there was some way to just get the Options Record and move it into the group location so that the sub-defined fields would line up and I could use each field. I don't know if you know COBOL, but it is easily done in that language. I need to address a location in my declarations and just put starting position of the record there with the sub-defined fields addressing the subsequent locations. Like this:

Group_Field;
Dept_Number Char(02);
Dept_Name Char(14);
Dept_Loc Char(13);
End_Group;

UTL_File.Get_Line (W_OptFile, dept_text);
Group_Field := dept_text;

At this point the dept_text record data would be put into the Group_Field location and Dept_Number would start in position 1 for 2 bytes, Dept_Name from position 3 for 14 bytes, etc.

I was hoping there was some way to do this in PL/SQL.

Thanks for your help. If you know of some way to do this cleanly, Please let me know.

Thanks again,
Lou
Re: Define a Record Layout (merged 3 topics, deleted 2) [message #311464 is a reply to message #311448] Fri, 04 April 2008 10:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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 Go to previous message
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
Previous Topic: Order By length of RecName field...is it possible? (merged)
Next Topic: Convert Rows into Columns
Goto Forum:
  


Current Time: Fri Dec 09 17:34:36 CST 2016

Total time taken to generate the page: 0.17280 seconds