How to write the PL/SQL procedure from the select query and store the output of the query to be in the form of insert statemets in text file

Hi ,
I Need the clear steps to write the PL/SQL procedure from the select statemet
say  Select *  from emp where job like 'CLERK'
 I need to pass the Parameter for JOB  the out of the query will give 20 records  and all the 20 records  insert statement records should store in text file.
I need help ,
How to write the procedure using UTL_FILE _DIR,
do i need to set up any parameters for UTL_FILE;
need help to excute the procedure step by step Please.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert GOracle dbaCommented:
what have you got so far for all those questions ?

we can help you extend on what you have
Mark GeerlingsDatabase AdministratorCommented:
This part of your explanation is clear:
"I need help,"

All of the rest is either: incomplete, confusing or inconsistent.

Please describe what you are trying do (or get) without listing technical details that may or may not be required.

It looks like you want to get a list of records out of an Oracle table, and write them to a text file  There are multiple ways of doing this, and not all of them involve either PL\SQL or utl_file.  And, if you do use UTL_FILE, the resulting file will be written on the database server, *NOT* on a client PC. Is that what you want?

Then you mention "insert statements".  Where are these coming from?  Do you want the data from the records you query to be written out to the file as insert statements?  Which table should they be inserted into?

You also mentioned the number 20.  Was that just an example, or do you want exactly 20 records to be written?

Is this a one-time task that you need to do?  Or, will this be an activity that needs to be repeated on some interval?
Must the output be insert statements?  Could it be CSV and a self-contained sql*loader parameter file with embedded DATA section?

Why are you trying to generate insert statements for data?  Is your procedure intended to be data extraction tool for use in migrating data?  If so, while inserts are reusable they are an expensive method of extracting (meaning large output file) and expensive method of importing (meaning lots of sql statements so slow.)

For 20 rows it probably won't matter much, but as a general purpose approach this is likely to be a bad way to go.

What is your end goal?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

nrajasekhar7Author Commented:
Let me explain you , What i want

Form the select query sending the parameter to that query ,the result of that query ouput
should store in the text file in the form of insert statements of XYZ table

For eg: Select * from emp where job like 'Clerk'
It is giving the result of some  records
the result of the query should store in text file in the form of insert statements .
Means ,I can run those insert statemets.
Mark GeerlingsDatabase AdministratorCommented:
I assume that you will delete your other two questions, and keep this one as the one you are hoping to get help with.  

Your latest explanation here helps a bit:
"the result of the query should [be] store[d] in text file in the form of insert statements .
 Means ,I can run those insert statements."

This looks like a very inefficient way to copy data from one table to another.  Also, this can work for a one-time data transfer, but this does *NOT* look like a good approach at all for something that will be used multiple times.

What is the business problem you are trying to solve?  Are you sure that you need to use a text file in the process?  Are the two tables in the same database, or in two different databases but on the same server or network?  Or, are you trying to copy data from one system to a second, but totally-separate system?
>> Let me explain you , What i want

You have restated your original question but you haven't explained the real end goal.
WHY do you want to have insert statements?

It would be silly of us to try to answer a question that will give you a result that is a bad solution for what you are trying to do.  But we don't know what you're trying to do so it's hard to judge.

That is, we could give you what you asked for, but not what you need.
What are you trying to do?

This is an example of how to do what you asked for, but again, it's not necessarily what you should be doing.

CREATE OR REPLACE PROCEDURE create_emp_inserts(p_job IN emp.job%TYPE)
    v_file UTL_FILE.file_type;
    v_file :=

    FOR x IN (SELECT *
                FROM emp
               WHERE job = p_job)
               'insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values ('
            || NVL(TO_CHAR(x.empno), 'NULL')
            || ','''
            || x.ename
            || ''','''
            || x.job
            || ''','
            || NVL(TO_CHAR(x.mgr), 'NULL')
            || ','
            || 'to_date('''
            || TO_CHAR(x.hiredate, 'yyyy-mm-dd hh24:mi:ss')
            || ''',''yyyy-mm-dd hh24:mi:ss'')'
            || ','
            || NVL(TO_CHAR(x.sal), 'NULL')
            || ','
            || NVL(TO_CHAR(x.comm), 'NULL')
            || ','
            || NVL(TO_CHAR(x.deptno), 'NULL')
            || ');'


Open in new window

note I used a directory object name,  NOT an explicit path from utl_file_dir.
You really should not use utl_file_dir,  instead, create directory objects and use them with appropriate permissions

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.