store procedeure output inot a file

Question

store procedeure output inot a file

uttam.paul

SSC Rookie

Points: 25
More actions
June 17, 2008 at 12:53 am

#189139

I want to write a store procedure or function on the oracle. I have to use more than one select statement and all the resulting record set of select statements should be in a file. So, I can check this file any time. means, what are the output of all the select statement.
right now, I am using a sql file having multiple select statement. and I am executing like this:
sql> spool abc.txt
sql> @xyz.sql
sql> exit
So, I got all the output of select statement written the xyz.sql file in the abc.txt file. instead of this, I want to write in a function or store procedure.
ASAP please provide the solution
Thanks in advance

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

You are on a SQL Server site in a SQL 2005 Analysis Services forum. This is an inappropriate place for Oracle questions.

lrosales SSCommitted Points: 1821 More actions · Answer 2

can you tell me more about what the store procedure is looking like?

"We never plan to Fail, We just fail to plan":)

uttam.paul SSC Rookie Points: 25 More actions · Answer 3

Just I want to a store procedure or function that does not take any parameter. Inside this I want to execute simple select statements more than on like:

select * from A;

select a, b, c from B;

etc..

and the output of the both select statement should be store in a .txt file.

let me know if you have any other clarification.

Thanks,

Uttam

lrosales SSCommitted Points: 1821 More actions · Answer 4

is the oracle server linked to SQL?

"We never plan to Fail, We just fail to plan":)

uttam.paul SSC Rookie Points: 25 More actions · Answer 5

Yes, it is link to Oracle. But if u provide the solution for other database Postgres etc. I will be try to convert in my oracle server.

Thanks

lrosales SSCommitted Points: 1821 More actions · Answer 6

friend, sorry it took me a long time. even to this is not the forum for Oracle, I will give you the answer. the linkserver works to get data from oracle to SQL Server, but not the other way around.

if you think about it, for a second you will get it. the answer to the problem you have is to create an ODBC connection from Oracle to SQLServer and then Access the Tables from SQLServer to Oracle. does this make sense? Hope this helps.

😎

"We never plan to Fail, We just fail to plan":)