Home » SQL & PL/SQL » Client Tools » SP2-0223: No lines in SQL buffer (Oracle 9i)
SP2-0223: No lines in SQL buffer [message #343080] Tue, 26 August 2008 04:13 Go to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
I am trying to run an SQL in SQL Plus but it is giving the below error:

Quote:

SP2-0223: No lines in SQL buffer



But while executing the same in Toad it returns the output.


Need your suggesion.

Regards,
Oli
Re: SP2-0223: No lines in SQL buffer [message #343082 is a reply to message #343080] Tue, 26 August 2008 04:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Without seeing what you're actually doing in SQL*Plus, we can't really help.
Re: SP2-0223: No lines in SQL buffer [message #343089 is a reply to message #343082] Tue, 26 August 2008 04:33 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Sorry for not being able to provide the code.If I could.But its not possible.

SQL> ed
Wrote file afiedt.buf

SP2-0223: No lines in SQL buffer.
SQL> select
  2  
SQL> ed
Wrote file afiedt.buf    --- Here I pasted the sql  then save and tried to execute


SP2-0223: No lines in SQL buffer.
SQL> 




In Toad, I am being able to see the output.But not in SQL PLUS for that particular SQL code.



Other than that I can be able to execute some other test SQL without giving me the above mentioned error.


Regards,
Oli
Re: SP2-0223: No lines in SQL buffer [message #343109 is a reply to message #343089] Tue, 26 August 2008 05:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
--- Here I pasted the sql then save and tried to execute

Prove it: execute "list", copy your save, in short copy and paste the whole session as it really is.

Regards
Michel
Re: SP2-0223: No lines in SQL buffer [message #343411 is a reply to message #343109] Wed, 27 August 2008 04:17 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Michel Cadot wrote on Tue, 26 August 2008 05:32
Quote:
--- Here I pasted the sql then save and tried to execute

Prove it: execute "list", copy your save, in short copy and paste the whole session as it really is.

Regards
Michel



What need to prove? I am finding this error for a particular SQL query and the same is running in Toad without any error.

Is it because that the sql query is too big?



SQL> list
  1* select dummy from dual
SQL> ed
Wrote file afiedt.buf 

SP2-0223: No lines in SQL buffer.
SQL> list
SP2-0223: No lines in SQL buffer.




Regards,
Oli
Re: SP2-0223: No lines in SQL buffer [message #343617 is a reply to message #343411] Wed, 27 August 2008 11:50 Go to previous messageGo to next message
tyler_durden
Messages: 14
Registered: August 2008
Location: http://tinyurl.com/63fmwx
Junior Member
I don't know about TOAD, but this is what I think is happening in your sqlplus session:

(1)
SQL> list
  1* select dummy from dual


You had this query in the buffer and hence the "list" command listed it.

(2)
SQL> ed
Wrote file afiedt.buf 

SP2-0223: No lines in SQL buffer.


The "ed" command would've invoked your default text editor (usually Notepad in Windows; or it could be something else), and you probably deleted all the contents of the file before saving it.

Maybe you did it inadvertently; we don't know.
But the fact is, if the saved file (afiedt.buf) is of 0 bytes, then that SP2-0223 error is returned.

(3)
SQL> list
SP2-0223: No lines in SQL buffer.


At this point, you try to list the contents of "afiedt.buf", which is of 0 bytes, and hence you see this error.

You may want to go to the directory from where you invoked command-line sqlplus and check the size of "afiedt.buf" at this point.

If you clicked on the icon of the GUI-counterpart of sqlplus, then your afiedt.buf would most probably be in %ORACLE_HOME%\bin or wherever the executable "sqlplusw.exe" resides.

HTH
tyler_durden
Re: SP2-0223: No lines in SQL buffer [message #343769 is a reply to message #343617] Thu, 28 August 2008 01:59 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Quote:

The "ed" command would've invoked your default text editor (usually Notepad in Windows; or it could be something else), and you probably deleted all the contents of the file before saving it.

Maybe you did it inadvertently; we don't know.





I had already mentioned that I am getting the error for a particular sql code (which executed successfully in Toad).
I did not say that I was getting error while trying to execute the
sql after saving in afiedt.buf


The steps I followed:
sql> select dummy from dual;
sql> ed
1. erase the content "select dummy from dual;" in the notepad editor (filename: afiedt.buf)
2. paste the SQL
3. save
SQL> ed
Wrote file afiedt.buf

SP2-0223: No lines in SQL buffer.




I am not saying that I am not being able to run other sql. And this sql text contains so many lines.

I understand that its very difficult for you to determine without the sql but yes, its also not possible for me to provide the sql.

Wanted to know if you find such situation. Thanks for giving your time.

Regards,
Oli


Re: SP2-0223: No lines in SQL buffer [message #344014 is a reply to message #343769] Thu, 28 August 2008 10:25 Go to previous messageGo to next message
tyler_durden
Messages: 14
Registered: August 2008
Location: http://tinyurl.com/63fmwx
Junior Member
Must say, I've yet to encounter a situation like yours.

Ok, how about this ?

(1) When you do a "File -> Save" in your editor (which I assume is Notepad), you do actually see your long query, right ?

(2) After you see that SP2-0223 error in sqlplus, what is the size of the latest "afiedt.buf" file in your filesystem ?

(3) If you open that afiedt.buf (from the filesystem) in an editor, do you see your long query in it ?

Maybe I am not able to understand your problem fully, but do I hope these points help you investigate and zero-in on your issue.

tyler_durden
Re: SP2-0223: No lines in SQL buffer [message #344098 is a reply to message #343769] Thu, 28 August 2008 14:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Are you very very very sure that you save it as afiedt.buf? Notepad has the annoying habit of adding .txt to the filename..
Re: SP2-0223: No lines in SQL buffer [message #344226 is a reply to message #343080] Fri, 29 August 2008 02:32 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
@tyler_durden
Quote:

(1) When you do a "File -> Save" in your editor (which I assume is Notepad), you do actually see your long query, right ?



1. When I do a "File -> Save" in your editor ),I can see the long query


The moment I save it ( after File-> save I can see my sql) and close the editor window.I am getting below error.

SQL> ED
Wrote file afiedt.buf

SP2-0223: No lines in SQL buffer.
SQL>




Quote:

2.After you see that SP2-0223 error in sqlplus, what is the size of the latest "afiedt.buf" file in your filesystem ?



2. The size its showing is 3kb
Quote:

3.If you open that afiedt.buf (from the filesystem) in an editor, do you see your long query in it ?



Yes, I can be able to view the SQL query content which I saved for execution.


Please make a note that I am not finding any problem for other queries that I executed same way.The above query is executed
in Toad without any error and can view the result.



@Frank
Quote:

Are you very very very sure that you save it as afiedt.buf? Notepad has the annoying habit of adding .txt to the filename..


100%!!
Thanks for the concern.When I paste the sql content,the titlebar in the editor is showing "afiedt.buf - Notepad"



Thanks for giving your valuable time.


Regards,
Oli










[Updated on: Fri, 29 August 2008 02:33]

Report message to a moderator

Re: SP2-0223: No lines in SQL buffer [message #344235 is a reply to message #344226] Fri, 29 August 2008 03:02 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What happens if you go through the same steps and issue
SQL> get afiedt.buf

Also, please attach the afiedt.buf (after pasting/saving) to your message. (If .buf files are not allowed, rename to .txt, but please tell us so)
Re: SP2-0223: No lines in SQL buffer [message #344250 is a reply to message #344235] Fri, 29 August 2008 03:59 Go to previous messageGo to next message
Olivia
Messages: 519
Registered: June 2008
Senior Member
Again followed the steps that I did so many times for the same SQL and after that I ran the below command as you suggested.

Please make a note that I can still see the content in \oracle\ora92\bin\afiedt.buf

SQL> get afiedt.buf
SP2-0223: No lines in SQL buffer.


Don't know why for this particular sql only while the same is executing and giving results.

Sorry for not being able to provide the code as its unethical to share .Hope you will understand.

I appreciate for giving your valuable time.

Thanks,
Oli


Regards,
Oli
Re: SP2-0223: No lines in SQL buffer [message #344344 is a reply to message #343080] Fri, 29 August 2008 07:58 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Just clutching at straws here as I have no idea, but have you tried issuing a $cmd from sqlplus to show which directory it's running from? I've tried this myself (I don't normally use sqlplus on Windows, I use it in UNIX) and the directory I see is definately the one that my editor (which is also notepad) writes to, and I can see afiedt.buf in there.
Re: SP2-0223: No lines in SQL buffer [message #344553 is a reply to message #343080] Sat, 30 August 2008 09:17 Go to previous message
govindu
Messages: 3
Registered: August 2008
Location: UK
Junior Member
What ever your SQL statement in script or other wise, try adding '/' on the next line rather than termintating the satement on the same line.

Govind
orafind. blogspot.com
Previous Topic: removing spaces between columns in sqlplus reports
Next Topic: Column headings in sql plus
Goto Forum:
  


Current Time: Tue Dec 06 02:44:34 CST 2016

Total time taken to generate the page: 0.14888 seconds