SQLplus | spool without file extension

Posted on 2007-07-26
Last Modified: 2013-12-18
Our enterprise system will only import files without a extension... Is there a way to SPOOL from sqlplus without a extension? I know how to change the extension and I know if none is specified it addes .LST but how can I just spool a filename like "myfile"? Thanks!
Question by:MuddyMojo
    LVL 5

    Accepted Solution


    4.2.2 Running Operating System Commands from SQL*Plus
    Using the HOST command or an exclamation point (!) as the first character after the SQL*Plus prompt causes subsequent characters to be passed to a subshell. The SHELL environment variable sets the shell used to run operating system commands. The default shell is the Bourne shell. If the shell cannot be run, then SQL*Plus displays an error message.

    To return to SQL*Plus, run the exit command or press Ctrl+d.

    For example, to run a single command, use the following command syntax:

    SQL> ! command

    Why not try using a HOST command within the SQL*Plus script and rename the file after the spool off command but before exiting the script?
    LVL 76

    Assisted Solution

    by:slightwv (䄆 Netminder)
    There was a trick many versions ago where you could just use 2 periods:  ex./ spool myfile..

    I've been unable to get it to work with (Windows) so, it was either a 'feature' they fixed or a new 'bug' they've introduced.

    I'll play a little and see if I can find a new trick.  Otherwise I was thinking of the host/rename method like robyncoffey suggested.
    LVL 4

    Expert Comment

    Why not set a job which polls the folder you spool to and just make the job rename the file?

    This meets the extensionless requirement as well as enabling you to avoid doing it by hand over and over.


    Author Comment

    I split the points because slightwv answered my question about the extension and robyncoffey offered a efficient work around. Thank you both and I hope you feel the split was resonable.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
    This video shows how to recover a database from a user managed backup

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now