Code generated from queries has double quotes

[cassiusdrow]

I write queries in order to generate other queries or code as a column in the output. Then I select the column and copy it and paste it back into the SQL window. This has worked fine before, but since installing version 7, if any of the generated lines of code contain line feeds, that piece of code will have double quotes around it.

Run this example:

SELECT 'SELECT COUNT(*) cnt' || DECODE( t.owner, 'SYSTEM', chr(10) || '  ', ' ') ||
       'FROM ' || LOWER( t.owner || '.' || t.table_name) || ';' cd
  FROM SYS.dba_tables t
 WHERE t.owner IN ('SYS', 'SYSTEM')
   AND t.table_name LIKE 'AQ%'
 ORDER BY t.owner, t.table_name

Copy the CD column and paste in the SQL Window. You will get something like this:

SELECT COUNT(*) cnt FROM sys.aq$_message_types;
SELECT COUNT(*) cnt FROM sys.aq$_pending_messages;
SELECT COUNT(*) cnt FROM sys.aq$_propagation_status;
SELECT COUNT(*) cnt FROM sys.aq$_queue_statistics;
SELECT COUNT(*) cnt FROM sys.aq$_queue_table_affinities;
SELECT COUNT(*) cnt FROM sys.aq$_schedules;
"SELECT COUNT(*) cnt
  FROM system.aq$_queues;"
"SELECT COUNT(*) cnt
  FROM system.aq$_queue_tables;"
"SELECT COUNT(*) cnt
  FROM system.aq$_schedules;"

Notice the last few lines have double quotes around them.

Is this behavior intended? Can it be changed?

 

[Marco Kalter]

You have included a linebreak (chr(10)) in your query result, and this causes the quotes. These quotes are necessary to identify the cell contents.

If you ommit the chr(10) it should work okay.

 

[cassiusdrow]

When I am generating code in this manner, I use the linebreaks to format the code, so they are necessary.

Can an option be added to turn this behavior off?

 

[Roeland]

Have you tried chr(13) instead of chr(10)?

 

[cassiusdrow]

Using chr(13) instead of chr(10) produces the same effect.

 

[CTzen]

If I run your query in Command Window, no double quotes get generated. May be you can use this approach.

PL/SQL Developer 7.0.0.1050

CD
--------------------------------------------------------------------------------
SELECT COUNT(*) cnt FROM sys.aq$_message_types;
SELECT COUNT(*) cnt FROM sys.aq$_pending_messages;
SELECT COUNT(*) cnt FROM sys.aq$_propagation_status;
SELECT COUNT(*) cnt FROM sys.aq$_publisher;
SELECT COUNT(*) cnt FROM sys.aq$_queue_statistics;
SELECT COUNT(*) cnt FROM sys.aq$_queue_table_affinities;
SELECT COUNT(*) cnt FROM sys.aq$_replay_info;
SELECT COUNT(*) cnt FROM sys.aq$_schedules;
SELECT COUNT(*) cnt FROM sys.aq_event_table;
SELECT COUNT(*) cnt FROM sys.aq_srvntfn_table;
SELECT COUNT(*) cnt
  FROM system.aq$_internet_agents;

SELECT COUNT(*) cnt
  FROM system.aq$_internet_agent_privs;

SELECT COUNT(*) cnt
  FROM system.aq$_queues;

SELECT COUNT(*) cnt
  FROM system.aq$_queue_tables;

SELECT COUNT(*) cnt
  FROM system.aq$_schedules;

15 rows selected

 

[cassiusdrow]

That code was just a simple example to illustrate the problem. Typically the queries have multiple columns, only one of which has code in it to extract. I know I could throw another select around the query to pull out the column I need, but the issue here is that I believe those quotes should not exist at all. I'm guessing they were added to allow a paste into Excel to put each field into a cell, but I'm not pasting into Excel and the quotes do not exist in the data.