asked on

FRM-1403: ORA-01403 No Data Found when calling TEXT I/O procedure

Hello Forms6i experts,
I've been trying to resolve this problem for 2 days now. Our oracle database is version 9.206. I checked the filenames, the sql in my cursor and it
returns data. But when I call this from forms6i, I'm getting a no data found. I don't understand it. There's another option of creating an XML file. I can
call & create the XML file from a stored procedure, but I don't know how to do it. Can someone give me the logic?

Below is my TEXT I/O procedure:

PROCEDURE XML_FILE_UPDATE

IS

l_in_file            TEXT_IO.FILE_TYPE;
l_id                  NUMBER        := name_in('ORGANIZATIONS.ORG_ID');
l_cmp_id            NUMBER        := name_in('ORGANIZATIONS.ORG_ID');
l_event                  VARCHAR2(1) := 'I';
l_filename            VARCHAR2(200) := l_id||'_'||l_cmp_id||'.xml';
l_the_file             VARCHAR2(1000) := 'C:\TEMP\PW_XML\'||l_filename;
l_output_line            VARCHAR2(2000);

CURSOR c_company IS
SELECT '<DBEvent'||CHR(10)||' '
||'<Master>'||'C'||'</Master>'||CHR(10)||' '
||'<ID>'||to_char(l_id)||'</ID>'||CHR(10)||' '
||'<CompID>'||to_char(l_cmp_id)||'</CompID>'||CHR(10)||' '
||'Event<>'||'U'||'</Event>'||CHR(10)
||'</DBEvent>'
FROM organizations
WHERE org_id = l_id;

BEGIN

/* Initialize and open the file for writing */
l_in_file := text_io.fopen(l_the_file, 'w');

/* Get all the records and write to file */
OPEN c_company;
FETCH c_company
INTO l_output_line;

text_io.put_line(l_in_file,l_output_line);

/* Close the cursor */
CLOSE c_company;

/* Close the file */
text_io.fclose (l_in_file);

END;

Thanks,
Terry

ram_0218

I feel that you'r not getting the value of the attribute really. Try this

instead of this line,
l_id NUMBER := name_in('ORGANIZATIONS.ORG_ID');

have this line,
after the begin statement.
l_id NUMBER := NVL(name_in('ORGANIZATIONS.ORG_ID'),<some_org_id_that_you_know>);

ram_0218

Be sure that move the declaration before begin and initialization after the begin.

TComandante

ASKER

I'm sure I'm getting the values of all the attributes because I did some Messages in the form and it returns all the values including the full filename.
It seems that it cannot write to a file. Is there a changed in Forms 6i using Oracle 9.206 in Text I/O?

Or is there a way to create an XML file from a package or stored procedure?

Thanks,
Terry

ram_0218

That is very much possible. You can see the examples as in here and you post questions if you dont seem to be understanding any.

http://www.oracle.com/technology/oramag/oracle/03-may/o33xml.html

TComandante

ASKER

All I need is just a simple XML File:
master := "C' -- for Company
id := 25; -- from organizations table
comp_id := 10; -- from organizations table
event := 'I' -- for Insert

Example below is an XML File to be written to a file ( C:\temp\pw_xml\25_10.xml )

<DBEvent>
<Master>C</Master>
<ID>25</ID>
<CompID>10</CompID>
<Event>'I'</Event>
</DBEvent>

TComandante

ASKER

I know what the problem is on this. It's just permission to a file server where I'm writing the file to.
I can give 50 points to RAM 0128 for givimg some comments on this.

Thanks,
Terry

TComandante

ASKER

This can be closed.

ASKER CERTIFIED SOLUTION

DarthMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial