nrajasekhar7
asked on
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
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.
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.
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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?