Home » SQL & PL/SQL » SQL & PL/SQL » oracle large query text (11gr2 on rhel (11.2.0.4.0))
oracle large query text [message #670146] Mon, 11 June 2018 09:48 Go to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Hi Guys,

I want to test one query,i have sql id,using it i exracted query text its large query may be 65000 + lines
after spooling or extracting text i am not able to run it due to formatting issues like on one line part of column name on next line
as below column department is half up and half on next line (this is just for example ,query which i am using is very large
(select name,id,depart
ment from dept))



I tried using many options like used v$sqlarea,v$sqltext,v$sqltext_on_new_line
tried plsql dbms_output code
but nothing worked

in simple words i want to spool this large query text and run it

or i want to run and check execution of query manually

If you guys faced this please share


Thanks
Re: oracle large query text [message #670147 is a reply to message #670146] Mon, 11 June 2018 09:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How did you extract the SQL text? If you'd use CLOB column there should be no such problem.

Re: oracle large query text [message #670148 is a reply to message #670147] Mon, 11 June 2018 10:11 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you using sqlplus?
If so this is a case where sqldeveloper or other such GUIs are definitely better.
Re: oracle large query text [message #670149 is a reply to message #670146] Mon, 11 June 2018 10:13 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SET LINESIZE 32767
Re: oracle large query text [message #670150 is a reply to message #670149] Mon, 11 June 2018 11:03 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
i used clob,used sql developer,line size 32767 but no option working getting one or other formatting issue like line break

even tried using plsql and - sql line continution character
Re: oracle large query text [message #670151 is a reply to message #670150] Mon, 11 June 2018 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is the size of the whole query text?

Re: oracle large query text [message #670154 is a reply to message #670151] Tue, 12 June 2018 02:22 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
in number of lines 25800 lines
Re: oracle large query text [message #670155 is a reply to message #670154] Tue, 12 June 2018 02:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Not number of lines (which does not match your initial post "65000 + lines") but size, in characters or bytes as you want.

Re: oracle large query text [message #670156 is a reply to message #670155] Tue, 12 June 2018 03:17 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you are using SQL Developer you really shouldn't have a problem. When the results come up in the grid you should be able to click on the pencil icon on the right of the column and that'll bring up a dialogue box with the full query without line breaks in weird places.
Re: oracle large query text [message #670176 is a reply to message #670156] Wed, 13 June 2018 18:01 Go to previous message
ramd2530
Messages: 1
Registered: June 2018
Junior Member
Oracle large query text is a very important thing to learn.
Previous Topic: How To display last day of a century
Next Topic: Commit Process
Goto Forum:
  


Current Time: Thu Mar 28 06:33:28 CDT 2024