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- SPOOL offThat's it. The contents of MyTxtFile.txt will be exactly what was returned to the screen (including headings etc).
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
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.