extra "s appearing in results of a query

[ckubitsc]

I've written a query to generate a set of queries. My problem is that when I copy all the results each cell is surrounded by double quotes ("). If I only copy one cell I don't have this problem.

Is there something I can do or set up differently to stop the double quotes from appearing?

Here's a simplified version of my query to show what's happening.

with Bob as
(select 'one' Row_Num
from Dual
union
select 'two'
from Dual)
select '-- tab=' || Row_Num || Chr(10) || 'select ' || 10 || ' "Query" from dual;'
from Dual,
Bob;

Results in

"-- tab=one
select 10 ""Query"" from dual;"
"-- tab=two
select 10 ""Query"" from dual;"

but I need

-- tab=one
select 10 "Query" from dual;
-- tab=two
select 10 "Query" from dual;

I didn't have this problem until I added the '-- tab=' || Row_Num || Chr(10) || at the top. I also tried '/* tab=' || Row_Num || ' */' || Chr(10) || with the same results.

I need the comment so I can run the queries, copy the results into Excel, and easily recognize each of the 150 tabs I'm creating.

I'm running version 11.0.6.1796 (64 bit) on a Win7 machine. I'm copying by selecting the column by clicking on the heading and then Ctrl-C. I'm pasting with Ctrl-V into a new SQL Window.

Thanks for any suggestions.

 

[Gustavo]

I always extract code from production instance by copying the text column from the following query:

Select text
From   dba_source
Where  name = 'PROGRAM_NAME'
And    type = 'PACKAGE BODY'
Order By line;

When I paste the text column content to a new window, the following happens:
1. Every " is replaced with ""
2. Every line is prefixed with "
3. A new line with a single " is inserted between each line from the result set.

I've built a macro to remove this, but it is pretty annoying.

Regards,
Gustavo

 

[ckubitsc]

Yes, that is very similar. If you copy a single row from the text column it doesn't have the extra ".

 

[IncredibleMrT]

Yes, it is annoying. It's all down the CR/LF within your string. Whenever I do anything similar, I have to make sure that the string i'm building is all on one line.

Obviously, you can't do this because of the --tab part.

Would be great if we could get a fix.

 

[ckubitsc]

I've stumbled upon a workaround for my problem. The blindingly obvious - replace one kind of comment for another!

with Bob as
(select 'one' Row_Num
from Dual
union
select 'two'
from Dual)
select '/* tab=' || Row_Num || '*/ ' || 'select ' || 10 || ' "Query" from dual;'
from Dual,
Bob;

The result,

/* tab=one*/ select 10 "Query" from dual;
/* tab=two*/ select 10 "Query" from dual;

, works just fine.

I've run into Gustavo's problem as well but I don't have a macro to remove all the extras.

 

[Claus Pedersen]

@Gustavo: I think your problem arrives from the fact that the view dba_source puts a line feed (ASCII 10) at the end of each source line.
You can remove the extra line feed by using the following select:

Select CASE WHEN ASCII(SUBSTR(text, LENGTH(text), 1))='10'
            THEN SUBSTR(text, 1, LENGTH(text)-1) ELSE text END
From   dba_source
Where  name = 'PROGRAM_NAME'
And    type = 'PACKAGE BODY'
Order By line;

This works OK in my setup.