Problem solve Get help with specific problems with your technologies, process and projects.

Piping SQL*Plus query results to a text file

I am running Rational's Visual Test 6.5 (VT) to test our interface accessing an Oracle database. What I want to do is submit a SQL "select -x- from -y- where -z- to SQL*Plus, and somehow pipe or otherwise get the query results to a text file to compare to a standard result file. VT has the ability of comparing files and explicitly defining where differences exist. I've tried SQL*Plus' copy cmd but that will only create another table with the results (I think). What has worked is to manually block the results, use a CTRL-C to copy the active screen, and paste the results in a text file. But I want to do this automatically so that I can run these series of tests and get a PASS/FAIL result. Trying to script mouse-like actions on the active SQL*Plus screen doesn't copy what I want. SQL has a "bcp" utility that allows query results to be copied to a text file (sounds like what I want) but SQL*Plus doesn't recognize the "bcp" lead-in (eg, bcp select -x- from -y- where -z- queryout MyTxtFile.txt). I don't know where to go with this. I've looked for piping commands and come up empty. I've searched a number of Web sites and there have been others that have had a similar problem. Any help would be appreciated.

You'll be surprised at how easy this is after all.... All you need to do is use the SPOOL command prior to submitting your SQL statement.

SPOOL MyTxtFile.txt
select -x- from -y- where -z-
That's it. The contents of MyTxtFile.txt will be exactly what was returned to the screen (including headings etc).

For More Information

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.