Solved

plsql procedure for fixlength dbms, pipe delimited

Posted on 2011-03-01
11
957 Views
Last Modified: 2013-12-07
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
Comment
Question by:iamtechnical
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35010031
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
 

Author Comment

by:iamtechnical
ID: 35010065
its on client machine in the form of excel , i shd write procedure
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35010105
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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

Author Comment

by:iamtechnical
ID: 35010171
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35010265
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
 

Author Comment

by:iamtechnical
ID: 35010543
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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 125 total points
ID: 35010585
>>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
 

Author Comment

by:iamtechnical
ID: 35011958
yeah understood

thank you
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35011977
Do you still need help with this?
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35151664
I'm afraid I have to object.  What part do you still ned help with?    I feel I answered everything outstanding.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle function to insert records? 15 83
Read XML values 8 70
return value in based on value passed 6 50
pl/sql parameter is null sometimes 2 39
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question