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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

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

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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create table from select - oracle 6 68
Oracle - SQL Query with Function 3 66
oracle collections 2 29
looking for guidance on Oracle Sql Formatting standards 9 30
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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.

733 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