Home » Other » Client Tools » SQL plus query result display (oracle 11.2.g)
SQL plus query result display [message #606830] Wed, 29 January 2014 18:49 Go to next message
anncao
Messages: 87
Registered: August 2013
Member
I am new to use sqlplus. I run a script which has some sql udpate statement as well as select statement.

the script was long, but when it runs in sql plus window, it goes so quickly, and I see an error word but when the script done with running, I can only see last page,which has my select result, how can I browse back to see previous result pages?
Any settings I can use?

Thanks,
Re: SQL plus query result display [message #606832 is a reply to message #606830] Wed, 29 January 2014 19:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
At the top of the file that contains all the SQL statements add a new first line which says as below:
spool capture.txt

At the end of the file that contains all the SQL statements add a new last line which says as below:
spool off

after exiting from sqlplus, the file "capture.txt" will contain all the SQL any any error which occurred


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: SQL plus query result display [message #606861 is a reply to message #606832] Thu, 30 January 2014 02:49 Go to previous messageGo to next message
gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK
Senior Member
What operating system?
Re: SQL plus query result display [message #606906 is a reply to message #606861] Thu, 30 January 2014 11:00 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Windows
Re: SQL plus query result display [message #606907 is a reply to message #606906] Thu, 30 January 2014 11:10 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Thank you all, the reason why I asked the question is at first I executed a sql script that have multiple update statements, and also a couple of select statements, these select statements is special is because they actually use a select a function, the function is to update a customized field.
We have to use this way for vendor told us so. The scripts run fine in oracle sql developer until I test it. what I found out is the select .... which actually an update, only updates the first 5000 records, I think that is because sql developer can only show the first 5000 records of select in the result window unless
I do a control +end thing, but since the script has other multiple statements, I cannot do a control+end during the middle of the running of the script.

Is there any thing I can do in oracle sql developer to make it work correctly?

Or in this case, I should use oracle sql plus.

I will try blackswan's answer to add a capture text first

[Updated on: Thu, 30 January 2014 11:15]

Report message to a moderator

Re: SQL plus query result display [message #606908 is a reply to message #606907] Thu, 30 January 2014 11:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
So to be clear - you're running a select statement that calls a function.
And that function contains an update statement.

Re: SQL plus query result display [message #606909 is a reply to message #606908] Thu, 30 January 2014 11:36 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
Yes, that function is a vendor's function used to update customized field in the application,it has pretty complex logic in it. And there is an update statement in it. and also a commit in the function.
what we do is something like below:
select ps_set_customfields.setCF('students', ID, 'TYProgramID',NYProgramID) from students

Oracle sql developer will only do the first 5000 records when running at UI.

[Updated on: Thu, 30 January 2014 11:38]

Report message to a moderator

Re: SQL plus query result display [message #607205 is a reply to message #606909] Mon, 03 February 2014 17:50 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Then I don't want any software written by your vendor.

There should be a setting in sql developer that'll control how many records it fetches at once, change it to fetch all.
Or stick with sqlplus.
Re: SQL plus query result display [message #607206 is a reply to message #607205] Mon, 03 February 2014 18:05 Go to previous messageGo to next message
anncao
Messages: 87
Registered: August 2013
Member
cookiemonster wrote on Mon, 03 February 2014 17:50
Then I don't want any software written by your vendor.


Unfortunately this is an enterprise and primary software we used here. so do you mean, it is not a good practice to use select function to update data, or is there an alternative way to do it?

Quote:
There should be a setting in sql developer that'll control how many records it fetches at once, change it to fetch all.
Or stick with sqlplus.
I cannot find fetch all button in the version I have 3.2.
I think I will just stick with sqlplus then

[Updated on: Mon, 03 February 2014 18:07]

Report message to a moderator

Re: SQL plus query result display [message #607244 is a reply to message #607206] Tue, 04 February 2014 04:00 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
anncao wrote on Tue, 04 February 2014 00:05
so do you mean, it is not a good practice to use select function to update data,

Most people would regard it as a very bad practice. The problem you are encountering is just one of the reasons. Another is the fact that they've had to use an autonomous transaction - so there is no possibility of rolling back in the event of errors/mistakes.

anncao wrote on Tue, 04 February 2014 00:05

or is there an alternative way to do it?

Write a single procedure that updates all the required records and call that.


anncao wrote on Tue, 04 February 2014 00:05
I cannot find fetch all button in the version I have 3.2.

I said setting, not button, probably under preferences.
Previous Topic: set serverouput off in sql developer
Next Topic: Installation of Oracle SQL Developer hangs
Goto Forum:
  


Current Time: Thu Mar 28 16:46:46 CDT 2024