Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

UTL_FILE Variable for Record Type 2

Status
Not open for further replies.
cwsstins

cwsstins

MIS
Aug 10, 2004
412
US
I have an issue where I need to create a very large .xml from data in Oracle db. I've got everything working just fine, except that I need to specify a record type...where each record is an A(dd), C(hange), or D(elete).

I'm extracting a total of 8 fields, but only 5 fields will actually display in the file...the last 3 are to be used to determine the record type. The code that is giving me trouble is the portion in bold below. How can I produce a variable to be used in the first dynamic "utl_file.put_line"?

The code below produces the error:
then stg_action = 'A'
*
ERROR at line 162:
ORA-06550: line 162, column 15:
PLS-00103: Encountered the symbol "=" when expecting one of the following:
:= . ( @ % ;


Code: 
set feedback off;
set long 99000;
set serveroutput on size 1000000;


declare 
   cursor c1 is 
	select 
	 fld1,
	 fld2,
	 fld3,
	 fld4,
	 fld5,
	 fld6,
	 fld7,
	 fld8
        from tblA
	where fld1 = 'Value';

	 fld1 tblA.fld1%;
	 fld2 tblA.fld2%;
	 fld3 tblA.fld3%;
	 fld4 tblA.fld4%;
	 fld5 tblA.fld5%;
	 fld6 tblA.fld6%;
	 fld7 tblA.fld7%;
	 fld8 tblA.fld8%;

  l_file_hdl utl_file.file_type;
BEGIN
  l_file_hdl:= utl_file.fopen('/u01/app/data','ccs45.txt','W',32767);

utl_file.put_line(l_file_hdl,'<?xml version="1.0" encoding="UTF-8"?>');
utl_file.put_line(l_file_hdl,'<merchant_import xsi:noNamespaceSchemaLocation="[URL unfurl="true"]http://xml.here_v1.2.xsd"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">');[/URL]


open c1;
loop
   fetch c1 into 
	 stg_fld1,
	 stg_fld2,
	 stg_fld3,
	 stg_fld4,
	 stg_fld5,
	 stg_fld6,
	 stg_fld7,
	 stg_fld8;

exit when c1%notfound;

[b]

stg_action:=

if stg_fld6 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld8 is null
	then stg_action = 'A';

else

if stg_fld6 <> TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld8 is null
	then stg_action = 'C';

else 

if stg_fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   not(stg_fld8 is null)
	then stg_action = 'D';

End if
[/b]
 utl_file.put_line(l_file_hdl, '<merchant>');
	utl_file.put_line(l_file_hdl, '	<action>' || [b]stg_action[/b] || '</action>');
	utl_file.put_line(l_file_hdl, '	<alternate_id>' || stg_fld1 || '</alternate_id>');
	utl_file.put_line(l_file_hdl, '	<customer_id>' || stg_fld2 || '</customer_id>');
	utl_file.put_line(l_file_hdl, '	<merchant_id>' || stg_fld3 || '</merchant_id>');
	utl_file.put_line(l_file_hdl, '	<merchant_name>' || stg_fld4 || '</merchant_name>');
	utl_file.put_line(l_file_hdl, '	<merchant_add1>' || stg_fld5 || '</merchant_add1>');
 utl_file.put_line(l_file_hdl, '</merchant>');

end loop;

utl_file.put_line(l_file_hdl, '</merchant_import>');

utl_file.fflush(l_file_hdl);

close c1;

utl_file.fclose(l_file_hdl);

end;
/
 
Sort by date Sort by votes
How about:
Code: 
.
.
.
exit when c1%notfound;

if stg_fld6 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld8 is null
    then stg_action := 'A';
else
if stg_fld6 <> TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld8 is null
    then stg_action := 'C';
else 
if stg_fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   not(stg_fld8 is null)
    then stg_action := 'D';
End if
 utl_file.put_line(l_file_hdl, '<merchant>');
    utl_file.put_line(l_file_hdl, '    <action>' || stg_action || '</action>');
.
.
.
 
Upvote 0 Downvote

You need to use a colon ::
Code: 
...
  then stg_action [red]:[/red]= 'A';

else

if stg_fld6 <> TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld8 is null
    then stg_action [red]:[/red]= 'C';

else 

if stg_fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   not(stg_fld8 is null)
    then stg_action [red]:[/red]= 'D';
[3eyes]




----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote

Ooops, carp too quick for me. [dazed]


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
Thanks guys, got past that error...but now I'm getting two new ones...what am I missing here?

end loop;
*
ERROR at line 319:
ORA-06550: line 319, column 5:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:
if
ORA-06550: line 330, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the
following:
begin function package pragma procedure form
 
Upvote 0 Downvote

Maybe you need two more End if; [ponder]

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
I tried taking the "/" out at the end of the script because of that end-of-file error message. Now my script runs with no error, but it just kinda hangs. It doesn't produce any file and it doesn't end. It should complete in about 2 minutes. I let it run for 15 before killing it.

Tried adding two more "End if;" as well, that resulted in the same thing.
 
Upvote 0 Downvote


OK, your code should look like this:
Code: 
...
if stg_fld6 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld8 is null
    then stg_action := 'A';
else
if stg_fld6 <> TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   stg_fld8 is null
    then stg_action := 'C';
else 
if stg_fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
   not(stg_fld8 is null)
    then stg_action := 'D';
    End if;
  End if;
End if;
...
PS: Notice the semi-colons (;), and remove the line with stg_action:=
[thumbsup2]





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb
 
Upvote 0 Downvote
OK, I got too focused in on the original code, including its ELSE IFs!
Let's try this:
Code: 
set feedback off;
set long 99000;
set serveroutput on size 1000000;

DECLARE
   CURSOR c1 IS 
    SELECT 
     fld1,
     fld2,
     fld3,
     fld4,
     fld5,
     fld6,
     fld7,
     fld8
    FROM tblA
    WHERE fld1 = 'Value';

  l_file_hdl utl_file.file_type;

BEGIN
  l_file_hdl:= utl_file.fopen('/u01/app/data','ccs45.txt','W',32767);

utl_file.put_line(l_file_hdl,'<?xml version="1.0" encoding="UTF-8"?>');
utl_file.put_line(l_file_hdl,'<merchant_import xsi:noNamespaceSchemaLocation="[URL unfurl="true"]http://xml.here_v1.2.xsd"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">');[/URL]

FOR i IN c1 LOOP
 IF i.fld6 = TO_CHAR(SYSDATE,'YYYY-MM-DD') AND
    i.fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') AND
    i.fld8 IS NULL THEN 
    stg_action = 'A';
 ELSIF i.fld6 <> TO_CHAR(SYSDATE,'YYYY-MM-DD') and
       i.fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
       i.fld8 is null
    THEN stg_action = 'C';
 ELSIF i.fld7 = TO_CHAR(SYSDATE,'YYYY-MM-DD') and
       i.fld8 is not null
    THEN stg_action = 'D';
 END IF;

 utl_file.put_line(l_file_hdl, '<merchant>');
    utl_file.put_line(l_file_hdl, '    <action>' || stg_action || '</action>');
    utl_file.put_line(l_file_hdl, '    <alternate_id>' || i.fld1 || '</alternate_id>');
    utl_file.put_line(l_file_hdl, '    <customer_id>' || i.fld2 || '</customer_id>');
    utl_file.put_line(l_file_hdl, '    <merchant_id>' || i.fld3 || '</merchant_id>');
    utl_file.put_line(l_file_hdl, '    <merchant_name>' || i.fld4 || '</merchant_name>');
    utl_file.put_line(l_file_hdl, '    <merchant_add1>' || i.fld5 || '</merchant_add1>');
 utl_file.put_line(l_file_hdl, '</merchant>');
END LOOP;

utl_file.put_line(l_file_hdl, '</merchant_import>');
utl_file.fflush(l_file_hdl);
utl_file.fclose(l_file_hdl);

end;
/
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

George L
  • Locked
  • Question
web app not saving &quot;all&quot; records suddenly
Replies
3
Views
361
carp
carp
KeyserSoze
  • Locked
  • Question
TOAD for Oracle - Icons
Replies
1
Views
169
Andrzejek
Andrzejek
Andrzejek
  • Locked
  • Question
Insert multiple records 1
Replies
9
Views
397
mikrom
mikrom
Andrzejek
  • Locked
  • Question
Pick latest records 1
Replies
2
Views
277
Andrzejek
Andrzejek
LarrySteele
  • Locked
  • Question
Searching multiple fields for any/all words from a search string.
Replies
2
Views
104
LarrySteele
LarrySteele

Part and Inventory Search

Sponsor

Back
Top