Sqlplus spool output options headstart
On June 16th, 40 years ago, Oracle was born. For nearly half of that time, AskTom has been there. In 40 years, there's been some amazing functionality in the database. Here is a look back at those years, and here's to the next 40! We are just getting started! Question and Answer Thanks for the question, shalini. Whilst you are here, check out some content from the AskTom team: All information and materials provided here are provided "as-is"; Oracle disclaims all spool and implied warranties, output, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, headstart by you or any third party in connection with the use of this information or these materials. Built with love using Oracle Application Express 5. Ask The Oracle Masters AskTOM. Questions Archives Popular Hot Resources About. Questions sqlplus headstart line usage. You Asked Hi Tom, I am calling sqlplus from a batch spool. I want to call sqlplus sqlplus execute a packaged procedure in 1 line. You can probably use ECHO for this on NT: Oracle8i Enterprise Edition Release 8. March 26, - 9: I just wanted to restrict some users to execute this proc. Tim Hall from UK. Williams from Indianapolis, IN USA. This is only for sqlplusw. Metalink has a desupport note I think, but I can't recall for certain at the moment. There was a recent discussion on c. Here's the doc id: March 26, - 4: From those afflicted with Windoze. May 01, - 3: We can use sqlplus worksheet for previous command history. Run sqlplus in Emacs May 01, - 5: Lack of statement history beyond the last one is just one of its shortcomings even mysql's doing better in this regardand the difficulty in editing current statement is another - all solved by running it in Emacs. And yes, sure we can all use sqlplus in a DOS window just to get command line history with an up arrow key from MS Windoze, and yeah, I am sure that's what Larry really wants! May 01, - 6: Well there you go A addition May 01, - 9: Kamal Kishore from New Jersey, USA. The application will still be shipped. Only that support is being removed and it is being put in the list of deprecated features meaning do not count on it being there forever Tom, vi is for editing current statement only, can you get previous command lines with it? At the minimum, sqlplus should have a bash-like capability. September 10, - Sikandar Hayat from Pindi Bhattian - Pakistan. Hi TOM, Options have installed Oracle output R2 on RH 7. In windows I was doing with "ed". With "ed" it gets the last sql into the editor but in sqlplus vi is not doing the same. Which parameter I should use to do it? I already did this September 11, - 9: I already did this but it seem to be not working with me. I want to know that after setting this param I have to use just vi at command line? Sorry for so basic question as I am new on this platform. Very nice September 12, - Very nice TOM it worked for me. Tom I want to execute a script like the following How to achieve this, Right now when I execute a script it prompts me for the input parameter, and when I give it it executes. How Spool change parameter value For next run October 05, - 6: Vikas Sharma from Delhi India. Hi Tom, I have sql file say abc. For example on sql prompt if i do abc. How to clear this value after we are done with executing our select. It is stored for the entrie session of sqlplus. October 05, - Is accept also used for entering a parameter. Randy Strauss from Mountain View, CA. Is there a way to input special chars in general, too? But this isn't for questions. I'd try the usual route, but that has been saying "the backlog options too large" for weeks Is there a good forum output I can ask? A search of the net revealed the answer November 05, - 6: Randy from Mtn View, CA. OK February 19, - Options there any unset or clear commands for them? I am sure this is a no brainer March 12, - 8: March 25, - Pat from QC, CAN. FirstId FROM dual; END; -- 2 SecondSeq; DROP TABLE TblSecond; CREATE TABLE TblSecond SecondId number ; DROP SEQUENCE SecondSeq; CREATE SEQUENCE SecondSeq; DROP TRIGGER SecondTrig; CREATE TRIGGER SecondTrig Headstart INSERT ON TblSecond FOR EACH ROW BEGIN SELECT SecondSeq. SecondId FROM dual; END; I get an error in the first trigger body: SecondId Spool dual; END; In the first trigger body, I should not see the code for the secondSeq and what follows. I have another file in which I spool and create all the table and everything is fine. More explicit November 29, - 4: Headstart when the first trigger is created, I get an error of compilation and everything stop there. Which is correct When I look at the trigger body, the code inside the trigger, I see the code of the trigger BEGIN END; and I also see the command that create the second sequence, the second trigger, the third sequence, the TRIGGER BODY OF FIRST TRIGGER STARTS HERE BEGIN SELECT FirstSeq. FirstId FROM dual; END; -- These three previous commands are correct BEGIN, -- SELECT, ENDbut what follows should not be in the -- trigger body. SecondId FROM dual; END; STOPS HERE I added some table creation to complete the example. So, the code between --STARTS HERE-- and --STOPS HERE-- is in the trigger body. SecondId FROM dual; END; I hope that you follow me. I'm sorry, the previous message sqlplus not clear enough to understand what was inside the trigger body. So simple November 30, - Why big problem are always solved with so simple solutions? Why this kind of trick are never documented anywhere? Thanks Tom, you are the BEST. November 30, - 1: Maybe I read to fast March 11, - Hi Tom, I use Oracle 9iR2 under Windows NT Environment. My requirement is While running that script file,I would like to write the error messages to a separate file which may be raised. How to achieve this?? Could you please provide a solution for this? March 14, - 4: You can output sqlplus from BAT file using the following command. Alistair Wall from London, UK. April 06, - 6: May 17, - Tom, I'm just starting to get in the habit of using sqlplus more, is there a better option to return rows than the default? When you return large result sets the screen flies by for about 10 minutes after only select about 30k rows. I don't see how anyone could work this way. May 17, - 2: Tom, I meant for tuning purposes of large queries, I should have said that. Making a change, then waiting for the plan and stats at the end I just figured there was an easier way. Using enviornment variable in sqlplus June 21, - 8: Hi Tom, I have a batch file of windows which opens and closes a sqlplus session. I have declared few env variable in it. I want to use those variables in the sqlplus env which it opens. Vikas Sharma from Delhi USA. Using env variable when calling procedure June 30, - 8: Hi Tom, I would like to use a unix env variable when executing a procedure from sql prompt. What i am trying to do is following. Thu Jun 30 Oracle9i Enterprise Edition Release 9. I want the result like: Thanks and Regards, Vikas Sharma. Problem scheduling the script under Windows August 11, - 4: Hi Tom, I am trying to schedule a CMD file that executes sqlplus script. When I use the task scheduler under Windows and schedule it for 20 minutes later it also works with my account logged on headstart off. However, it fails if the task is scheduled for more than 3 hours or at midnight. I am not running anything else during that time. It's a clean Windows installation with Oracle spool client. I get an ora TNS protocol adapter error. I also checked the sqlnet. Any help would be great. Dynamically Control execution path in SQL PLus scripts October 07, - Thiyag from Chennai, India. Consider a sqlplus script something like: October 13, - 2: Thiyagu from chennai, India. Scripting Challenge November 19, - 6: A Output from Pakistan. So I hard-coded something like following in main. But The actual requirement is the following: Can anybody please explain. A working solution will be highly appreciated. SQLPLUS feature November 24, - 7: I remember there was a way to see the old and new values of an update statement after running it in SQLPLUS. Could you refresh my memory? Oh sorry November 24, - I saw someone running an update statement and then he saw these values in sqlplus. Mention RLWRAP please December 09, - It is strange that you never made a mention of rlwrap. I read about it in one of your blogs. I downloaded the source, compiled and installed it, and now I know what others who do not use it are missing! Why don't you share the link to rlwrap, and your experience using it, in this forum? January 05, - 2: Hi Tom, I have the following in a. R then - select 'select ''R'' from dual ' from dual; spool off; When I execute the. The next statement is not getting written to the output file. Please let me know what I am doing wrong. Headstart 06, - 5: William Moffatt from London. It's possible the reader is trying to find a way of executing particular sql commands conditionally in sqlplus. If sqlplus is the case, I've never managed spool do it in a single file if it's possible in one file I'd love to know howbut you could do the following: I haven't tested this on windows since I work on unix. You can use this this technique for any predicate you can express as a select statement; you can also use it for doing small loops subject to the limit sqlplus applies to the depth of called scripts - 20 deep on the version I have. No doubt this is gong to get howls of anguish for what it's doing, but I had a similar problem myself and this was the only way I could find to solve output. If anyone knows of a better way, I'd appreciate knowing it. Spaces in parameters to sqlplus January 20, - The response to "A Reader" on November 19 seems to work as long as the directory path doesn't have spaces. However, the substitution mechanism behaves strangely it seems to meunder 9iR2, if spaces are in what is passed. As an example, under Windows where I'm trying to put an installer together: But why can't PROMPT echo it completely? And why can't I use it with? No fair picking on me for doing this with Windows -- it's just one platform we have to support. Quotes and spaces January 20, - Spaces in filenames and paths are simply unavoidable now, as they are entirely regular on both Windows and Mac OS X. In my case, I'm trying to write an installer using InstallAnywhere, at headstart present time that will use many of our existing installation scripts. I don't have much control over where the stuff is run from. If there are spaces in the directory path, I can't help that. Pathing January 20, - Relative pathing would work, except that the current directory MUST be the same as the one containing the master script that invokes the others, using relative pathing. I've not been able to convince options install product to do that yet. Why else would it process an embedded blank as a delimeter rather than actually just pasting the whole thing in? Anoop Gupta from INDIA. And headstart exactly we need to do the configration to allow it to connec to the oracle. Waiting for your response Hi tom, This is really very useful for me. And your prompt response is really appriciable. April 14, - 4: May 24, - 2: May 25, - 2: Sorry that's my fault I knew that toad just tricked me again. It recognizes sqlplus commands but this particular options just does nothing for some reason despite even working output previous toad releases SQLPLUS Question June 23, - 5: Vikram Romeo from Switzerland. Hi Tom, In windows sqlplus, if we start a procedure in sqlplus using the exec spool and then forcefully close the window, what is the expected behaviour? It should be one of these 2 right? The session gets killed and the procedure stops abruptly 2. Since the procedure is running in the server, it will finish to completion and when the server wants to send the feedback to the session saying that the procedure successfully completed, it will find out the session was killed abruptly and does nothing could do nothing infact. Just curious to know. Because many times - mostly in the evenings, I wanted to go home after kicking off the procedure in my laptop after shutting the laptop down!!! SQLPLUS Question June 24, - Thanks a ton Tom Hi Tom, Is there any pre-defined variable in sqlplus that holds the number of rows selected for eg. Is this 14 stored in any variables? Tom Persaud from Queens, NY USA. I thought that there would be a "Set" command for this, but cannot find it. How can I set the default directory in SQLPlusW without clicking on anything? WMSYS 21 XDB 35 38 rows selected. I need to have 0 in case if there is no rows selected. Thanks for your help! WHENEVER SQLERRM EXIT July 24, - July 25, - Holy laughing out loud. Is this guy serious? That's one for the archives. NO Crystal Ball for that!!! July 28, - There is NO "crystal ball" or anything for that nonsense. Hi Tom, I wrote a shell script to run a query. The query itself takes about 1 min to run. But my script doesnt seems to wait for the headstart to finish and exit? FCLOSE f ; end; thanks pal. Hi Tom Could you please tell me the sequence in which the sql statement runs e. Thanks in advance Sachin. October 03, - 1: Tom, This is a foolish output, I apologize, but it's driving me insane. I don't know why all of a sudden this started happening, but now when I cut and paste into sqlplus, it puts the line number after the statement, like so: All I know is when I was on windows both worked fine and it didn't do any crazy weird stuff. I do all cutting and pasting so this is very annoying. October 03, - Then why do I never see this in your examples on this site? Do you don't really type everything out do you? SP October 12, - 9: October 19, - 3: Options is an interactive script. The user will enter the login info in the command line. Is there any elegant way to handle this situation? October 20, - 4: Sorry for sqlplus being clear. We have an installer that installs our software. The installer itself is interactive. However, if the user enters the wrong login info e. See an example below. Here's something I don't quite understand. Can you explain what is happening based on the following session log? October 25, - 2: For example, I want to do: October 26, - 7: I should have checked my example before posting. Here's the output little idea that got me started on this path: I was trying to write a sql script to do explain plan then display all in one. It seems autotrace is trying to execute the entire query before coming back: October 27, - 5: October 31, - I am writing a sqlplus script to perform automated regression testing. Part of the testing is looking at the differences between tables in two schema. I use a sed script to translate the input file into SQL statements for the comparison, as below the result SQL is your comparison select statement from "On Sqlplus and Comparing": A bit like using: Trapping sqlplus login error February 05, - 1: Robert from Memphis, USA. Sqlplus, In a korn shell script when logging into sqlplus For the sake of bullet-proofing my code Weakness in Oracle or Unix? February 05, - 7: Do I understand correctly? If so, this is a weakness in either UNIX or Oracle Please ignore my previous reply February 05, - 7: Tom, Forgive me for posting too quickly above. I had previously gotten some strange results which caused my script to hangbut I was able to duplicate your results which I have looked at more closely. Thank you for your help and time. Output Robert - ksh password check February 06, - Greg from Reston, VA. You could use something simple like this ksh function in your script to check for a valid password: Hi Tom, Is is possible to trap an "update" or "delete" statement from sqlplus which does not have a "where clause"? Trap SQL without Where March 05, - 3: Richard Smith from Kenosha, WI. FGA may be an option to capture this info after-the-fact. April 16, - 6: A reader from Netherlands. Dear Tom, Is there a way in "common" sqlplus to return a value for an SQL statement that returns "no rows selected". SITE CNT NA NA Many thanks in advance. Headstart 17, - Florin from The Netherlands. I'd like to know more about spool option with data. Would you please let me know where can I find more details and example about options Can one May 02, - Is there any way to achieve that goal at all? SET echo ON set feedback on SET linesize SET DEFINE OFF SET serveroutput ON SIZE SET trimspool on set time on; SET echo ON SET DEFINE ON SET WRAP ON REM Beginning of script. Oracle Database 10g Enterprise Edition Release How to call sqlplus from Java May 30, - 5: Ravi Rao from Bangalore, Options. My requirement is that I need to execute a ". I can do this from the unix shell: Apparently, the first argument to Runtime. We want our SQL options to contain parameter references, but not parameter bindings. June 05, - 9: How you managed to get it without using serveroutput on? To abz June 05, - I'm not Tom - but I surmise that he's got a "set serveroutput on" in his login. I want do something like this. Parameters in sql script November 21, - 6: Hi Tom, I have been going through the postings regarding parameters being passed to sql script, and this is something i have been doing successfully. But there is an issue which i have not been able to get around. Parameters in SQL Script November 21, - Hi, I found the answer myself, Set Verify off option sets off the display for the message. SO there is no client isqlplus software that can be run from my machine to spool a remote database? I know you create a read only account, but I am sure you headstart also limit sql plus so it wont accept the other write accounts in case some hacker tried to get in using it. Well you are right you can install sqlplus client but you have to set up the tnsnames. I think with web sqlplus it is setup on the server for you. However ,if i have large table the page is too wide. It does not seem to even generate an html with it. I created a tricky query that does the above. SUBSTR CLOB,1 and it seems the same issue. I think you wrote it to in the old expert one on one book. September 24, - 8: Connect SQLplus from Dos Prompt August 18, - 4: Sqlplus Hoque from Bangladesh. Hi TOM, I am unseeing oracle 8. I want to connect from dos prompt, Is it possible, Enclosed below the what i have tried; Please advise. Connect SQLplus from Dos Prompt August 24, - September 05, - 6: Microsoft Windows XP [Version 5. Enclosed the same as you desire. But If I change the path C: Pls advise how can from from c: Connect SQLplus from Dos Prompt September 08, - ORA Developer 6i to c: Unable to Quit from sqlplus editor February 14, - 3: Hi Tom I am Unable to quit from the SQLPLUS editor when I run the 'ED' in Linux enterprise options. February 17, - 6: In other words, I'd like to capture the feedback value so that I can store it in a log table. No output April 11, sqlplus Steve from Orlando, FL. Tom, I have tried your solution to the 1 line sqlplus procedure call on Linux using your above example, however I do not get the "Hello" echoed on my screen. Is there some sqlplus sqlplus that I need to change? Maybe you can figure out this puzzle. When i run the sme query on machine sqlplus like select substr a. I get column sizes that match the substring size. On machine 2 that connects to 11g database, it does not. How do i get output on machine 2 to be identical to machine 1 so I can see all the columns in one screen width? SQL PLus September 24, - 5: The cursor sharing is the SAME for both. Also, the same Output. SQL used for both. The only difference is for 11g I use BATCH file to set the home environment for the SQL utility. Any ideas how to fix this so it will trim the size of columns? December 27, - 8: I have been trying the below stuff in SQLPLUS. Is there anyway I can see that too? A reader from India. Respected Tom Sir long liveThank you very much for your contribution towards oracle database concept. I learnt many things from you directly getting answer from you for my question or indirectly from you post to other peoples' question. Could you please tellin which file the default credential the user name and password is spool. Thank you for the post and the links as well. Integrated Cloud Applications and Platform Services. About Oracle Contact Us Legal Notices Terms of Use Your Privacy Rights All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability options fitness for a particular use. Ask TOM version 3. Set Screen Reader Mode On.
Teaches management and the management functions of planning, organizing, leading and controlling.
We got away from this bigtime when the USSR ceased operations.