SQL*Plus file finder

Here's a handy way to find a script within an Oracle SQL*Plus session.

You are in a SQL*Plus (Windows) session. Somewhere on your machine is the one script you need, but where? The script...

name is something like my*query.sql, but you're not sure. What do you do? Here's a handy way find it fast.

-------------------------------------------------------------------------------
-- SQLPlus_FF.sql 
------------------------------------------------------------------------------- 
-- Author:  Dusan Djuric 
-- mail:    dusan.djuric@iii.hr 
-- http:    www.CroGuide.com 
-- 
-- Date:    25.01.2002 
-- 
-- Purpose: SQLPlus File Finder for Windows 
-- 
-- Usage: 
--       1. In SQLPlus prompt type @c:sqlplus_ff 
--       2. Input data ona ACCEPTs 
--       3. edit windows pops to show you results 
-- 
-- Tips: 
--       1. Put SQLPlus_FF.sql script on C: root to grab it faster! 
--       2. To start finded skript 
--          - add '@' at SQLPlus prompt 
--          - copy/past file name from edit window. 
-- 
-- Tested environment: 
--                    SQL*Plus: Release 8.1.7.0.0 
--                    Oracle8i  Release 8.1.7.2.1 
--                    Windows 98 SE client 
-- 
------------------------------------------------------------------------------- 

ACCEPT ROOT char prompt 'Enter root disk : ' 
ACCEPT FNAME char prompt 'Enter file name : ' 

host cd\|&ROOT|dir/a-d/o/s/b/l &FNAME > &ROOT\SQLhost_FF_out.txt 

EDIT &ROOT\SQLhost_FF_out.txt

As an example, try this:

SQL>@c:\SQLPlus_FF.SQL 
Enter root disk : c: 
Enter file name : *.sql 

Reader Feedback

Jeff W. writes: This tip appealed to me, but when I tried to use it, I had some difficulties. The problems were likely caused by my environment. I worked through the concept and was able to get it working. I am submitting the changes I had to make to get it to work for my environment: Windows 2000 client, Sqlplus 8.0.6.0.0, and Oracle 7.3.4.4.0:

ACCEPT ROOT  char prompt 'Enter directory (eq: c:sql)    : '
ACCEPT FNAME char prompt 'Enter file name (eq: border.sql): '

host dir/a-d/b/s &ROOT&FNAME > c:tempsqlplus_ff.out

EDIT  c:tempsqlplus_ff.out

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in January 2002

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close