• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 976
  • Last Modified:

plsql procedure for fixlength dbms, pipe delimited

i have a data, stored in excel
i want to a plsql to call the data in fixlength,

and also i want a plsql procedure for pipe delimited
0
iamtechnical
Asked:
iamtechnical
  • 6
  • 4
1 Solution
 
slightwv (䄆 Netminder) Commented:
To produce this output from Oracle tables or load this output into Oracle tables?

Is file on the database serer or on a client machine?
0
 
iamtechnicalAuthor Commented:
its on client machine in the form of excel , i shd write procedure
0
 
slightwv (䄆 Netminder) Commented:
Again: Loading from Excel into an Oracle table or creating a file from Oracle to load into Excel?

On a client machine, you probably cannot use a stored procedure. Stored procedures can only write to files on the database server.

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
iamtechnicalAuthor Commented:
ok let me make it clear

i have a data in the excel, so i want write a procedure to call the data from excel and store it in the form of pipe delim for and use for reporting
0
 
slightwv (䄆 Netminder) Commented:
There really isn't a way for Oracle to open en Excel binary, process it and spit out delimited rows in text format.

Is there a reason you just don't save the Excel file in TXT format?
0
 
iamtechnicalAuthor Commented:
hey please correct me if i am wrong


CREATE OR REPLACE PROCEDURE testfile
IS
    xfilehandleout          UTL_FILE.file_type;
    xfiledir                VARCHAR2 (200);
    xfilenameout            VARCHAR2 (5000);
BEGIN

    xfiledir := 'C:\CRA Working.data.txt';

    --SELECT SYSDATE||'test1.xml' INTO xfilenameout from dual;

    xfilenameout := 'test1.xml';

    --xfilehandleout := UTL_FILE.fopen ('OUTBOUND_DIR', xfilenameout, 'w');

    xfilehandleout := UTL_FILE.fopen (xfiledir, 'test.txt', 'w');




    FOR EMPREC IN (SELECT user#, emp#, salary, country FROM emp)
    LOOP

       UTL_FILE.put_line (xfilehandleout, EMPREC.user#||'|'||'|'||EMPREC.emp#||'|'||EMPREC.salary||'|'||EMPREC.country);

    END LOOP;



    UTL_FILE.fclose (xfilehandleout);

EXCEPTION
         WHEN utl_file.invalid_path THEN
             dbms_output.put_line('Path not found on DB-server.');
         WHEN OTHERS THEN
           dbms_output.put_line('Others: '|| SQLERRM);

END;
0
 
slightwv (䄆 Netminder) Commented:
>>xfiledir := 'C:\CRA Working.data.txt';

That is the "C" drive of the database server not the remote client machine.

That example also is taking rows from the EMP table and creating a pipe delimited file.

You said in http:#a35010171 that you have an excel file and want to create a pipe delimited file.  That is not what that code you posted is doing.
0
 
iamtechnicalAuthor Commented:
yeah understood

thank you
0
 
slightwv (䄆 Netminder) Commented:
Do you still need help with this?
0
 
slightwv (䄆 Netminder) Commented:
I'm afraid I have to object.  What part do you still ned help with?    I feel I answered everything outstanding.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now