SQL Plus  hangs

Posted on 2004-08-26
Last Modified: 2008-02-01
I am creating stored procs using SQL Plus  by copying and pasting the code. It works great with most sprocs. If sproc is more then 135-150 lines long, SQL Plus stops responding and I have to restart it. Sproc is not created. What's the problem and how can I get around it? OS is WinXP. I'd like to run 'show err' after that to see compilation errors.
Question by:quasar_ee
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

oratim earned 250 total points
ID: 11906572

Just create SQL file using any editor, such as notepad,  then run it from the SQL prompt

SQL> @C:\files\SQL\Scripts\Procedures.sql

If you do that, make sure there are no spaces in the path, or enclose the entire path in quotes.

LVL 23

Expert Comment

ID: 11906599
did you put a '/' at the end?

Expert Comment

ID: 11906674
Yes, put the / at the end of the file
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 23

Expert Comment

ID: 11907630
A maximum of 3625 characters can be pasted in SQL*Plus Interface

That's what I prefere to use the command-line interface.
1. Go to your sql*plus icon, right click and select properties
2. Remove the w from C:\oracle\ora92\bin\sqlplusw.exe  to make it like C:\oracle\ora92\bin\sqlplus.exe
3. You can also add C:\oracle\ora92\bin\sqlplus.exe username/password@databasename @commands . To log you in and set parameters. You need to load the commands file in the same path your sqlplus.exe is or if it's in your local machine, you need to specify the path ( for example: @c:/commands.txt ).
4. You can then make the command-line look like sql*plus interface right clicking in the icon an make changes to the layout, options and colors.
5. Make sure in the options tab ( under Edit Options)  you select QuickEdit mode, so you can paste to the command-line with one right click and copy from it selecting the area to be copied and pressing the enter key.

Hope this helps.

Expert Comment

ID: 11909804
The best thing is to save the SQL code in a script file and execute it from the command line or from SQL PLUS. It will be easy for you to do any changed, if required in the file itself and execute in again.

 As oratim as mentioned use the following command to execute the script file in the SQL PLUS.

@ filname.sql

Give the full path after the @ symbol.

Author Comment

ID: 11915133
>>Just create SQL file using any editor, such as notepad,  then run it from the SQL prompt
It works. Thank you.
>>A maximum of 3625 characters can be pasted in SQL*Plus Interface
Looks I can't paste more than 140 characters. Any idea how to increase this number?
LVL 23

Expert Comment

ID: 11915396
your comments --->Looks I can't paste more than 140 characters. Any idea how to increase this number?

I think this has nothing to do with SQLPLUS environment. it's a DOS windows property.

can you try this:

in DOS windows, click the top left corner of the dos window, go to properties----->click options tab,

in this window, increase buffer size and number of buffers in command history


Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

726 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