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

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

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle query output question 4 47
SQL Query 34 115
Field name with special character (Ñ) in Oracle 11 86
oracle DR - data guard failover. 18 45
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

839 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