Toad 12.12.0.39 x64 (latest prod)
Used in grid custom query definition. when a query contain commented line, Toad use it as a column name:
I’m not sure how that happened but Toad does not determine the column names. Oracle does.
Did you paste the query in from word? maybe those dashes got changed to the long dash. Just a guess.
Hi John,
No, it was pasted from notepad++ (ANSI encoding). and the source of code is:
WITH
** q**
** AS**
__ ( SELECT /+ PARALLEL(SQL_PERF_HIST, 2) /__
** TO_CHAR (monitored_date, ‘YYYY.MM’) monitored_month,*
** LAG (TO_CHAR (monitored_date, ‘YYYY.MM’)) OVER (ORDER BY TO_CHAR (monitored_date, ‘YYYY.MM’)) AS prev_monitored_month**
** FROM SQL_PERF_HIST**
** WHERE monitored_date >= TO_DATE (‘01.12.2016’, ‘dd.mm.yyyy’)**
** GROUP BY TO_CHAR (monitored_date, ‘YYYY.MM’))**
__ SELECT /+ PARALLEL(H, 2) /__
** ‘NEW’ stat_type,*
** monitored_month,**
** --h.MODULE,**
** h.sql_id,**
** SUM (NR_EXECS),**
** SUM (ELA_SECS),**
** SUM (NR_ROWS),**
** SUM (NR_LOG_READS)**
** --,DBMS_LOB.SUBSTR (SQL_TEXT, 220) **
** FROM Q383211.SQL_PERF_HIST h, q**
** WHERE 1 = 1**
** AND PARSING_SCHEMA != ‘Q383211’**
** AND TO_CHAR (monitored_date, ‘YYYY.MM’) = monitored_month**
** AND prev_monitored_month is not null**
** AND PLAN_HASH_VALUE is not null --mostly queries that were executed on some tem tables which were dropped**
** AND NOT EXISTS (select 1**
** FROM Q383211.SQL_PERF_HIST h2**
** WHERE 1 = 1 AND TO_CHAR (h2.monitored_date, ‘YYYY.MM’) <= prev_monitored_month and h2.sql_id=h.sql_id)**
GROUP BY monitored_month,
** h.SQL_ID**
** --,DBMS_LOB.SUBSTR (SQL_TEXT, 220)**
**having monitored_month = (select max(TO_CHAR (monitored_date, ‘YYYY.MM’)) from SQL_PERF_HIST) **
ORDER BY SUM (ELA_SECS) desc;
where sql_perf_hist is defined as:
CREATE TABLE SQL_PERF_HIST
(
** MONITORED_DATE DATE,**
** MODULE VARCHAR2(64 BYTE),**
** SQL_ID VARCHAR2(13 BYTE),**
** PLAN_HASH_VALUE NUMBER,**
** NR_EXECS NUMBER(18),**
** ELA_SECS NUMBER,**
** AVG_EXEC NUMBER GENERATED ALWAYS AS (ROUND(CASE WHEN NVL(“NR_EXECS”,(-1))>0 THEN “ELA_SECS”/“NR_EXECS” ELSE (-1) END ,6)),**
** NR_ROWS NUMBER,**
** AVG_ROWS NUMBER GENERATED ALWAYS AS (ROUND(CASE WHEN (NVL(“NR_EXECS”,(-1))>0 AND “NR_ROWS”>(-1)) THEN “NR_ROWS”/“NR_EXECS” ELSE 0 END ,6)),**
** NR_LOG_READS NUMBER(18),**
** AVG_LOG_READS NUMBER GENERATED ALWAYS AS (ROUND(CASE WHEN NVL(“NR_LOG_READS”,(-1))>0 THEN “NR_LOG_READS”/“NR_EXECS” ELSE 0 END ,4)),**
** PARSING_SCHEMA VARCHAR2(30 BYTE),**
** SQL_TEXT CLOB,**
** DESCRIPTION VARCHAR2(256 CHAR),**
** WINDOW NUMBER**
)
LOB (SQL_TEXT) STORE AS SECUREFILE (
** ENABLE STORAGE IN ROW**
** CHUNK 8192**
** DEDUPLICATE**
** COMPRESS MEDIUM**
** NOCACHE**
** LOGGING**
** STORAGE (**
** INITIAL 104K**
** NEXT 1M**
** MINEXTENTS 1**
** MAXEXTENTS UNLIMITED**
** PCTINCREASE 0**
** BUFFER_POOL DEFAULT**
** FLASH_CACHE DEFAULT**
** CELL_FLASH_CACHE DEFAULT**
** ))**
LOGGING
NOCOMPRESS
NOCACHE
RESULT_CACHE (MODE DEFAULT)
NOPARALLEL
MONITORING
/
so you can test alone.
You did not provide a column alias to the column “SUM (NR_LOG_READS)”, so Oracle used your comment as the column name. Best practice should always be to name your columns.
So this:
SUM (NR_LOG_READS)
** --,DBMS_LOB.SUBSTR (SQL_TEXT, 220) **
Should be turned into this (or something like this):
SUM (NR_LOG_READS) SUM_NR_LOG_READS
** --,DBMS_LOB.SUBSTR (SQL_TEXT, 220) **
Thanks
David
From: Damir Vadas [mailto:bounce-damirvadas_250@toadworld.com]
Sent: Friday, November 03, 2017 8:09 AM
Subject: [External Email] RE: [Toad for Oracle - Discussion Forum] Query parser small bug in custom sql definition for the grid (wrong column name for last column)
EXTERNAL EMAIL
RE: Query parser small bug in custom sql definition for the grid (wrong column name for last column)
Reply by Damir Vadas
Hi John,
No, it was pasted from notepad++ (ANSI encoding). and the source of code is:
WITH
** q**
** AS**
__ ( SELECT /*+ PARALLEL(SQL_PERF_HIST, 2) */__
** TO_CHAR (monitored_date, ‘YYYY.MM’) monitored_month,**
** LAG (TO_CHAR (monitored_date, ‘YYYY.MM’)) OVER (ORDER BY TO_CHAR (monitored_date, ‘YYYY.MM’)) AS prev_monitored_month**
** FROM SQL_PERF_HIST**
** WHERE monitored_date >= TO_DATE (‘01.12.2016’, ‘dd.mm.yyyy’)**
** GROUP BY TO_CHAR (monitored_date, ‘YYYY.MM’))**
__ SELECT /*+ PARALLEL(H, 2) */__
** ‘NEW’ stat_type,**
** monitored_month,**
** --h.MODULE,**
** h.sql_id,**
** SUM (NR_EXECS),**
** SUM (ELA_SECS),**
** SUM (NR_ROWS),**
** SUM (NR_LOG_READS)**
** --,DBMS_LOB.SUBSTR (SQL_TEXT, 220) **
** FROM Q383211.SQL_PERF_HIST h, q**
** WHERE 1 = 1**
** AND PARSING_SCHEMA != ‘Q383211’**
** AND TO_CHAR (monitored_date, ‘YYYY.MM’) = monitored_month**
** AND prev_monitored_month is not null**
** AND PLAN_HASH_VALUE is not null --mostly queries that were executed on some tem tables which were dropped**
** AND NOT EXISTS (select 1**
** FROM Q383211.SQL_PERF_HIST h2**
** WHERE 1 = 1 AND TO_CHAR (h2.monitored_date, ‘YYYY.MM’) <= prev_monitored_month and h2.sql_id=h.sql_id)**
GROUP BY monitored_month,
** h.SQL_ID**
** --,DBMS_LOB.SUBSTR (SQL_TEXT, 220)**
**having monitored_month = (select max(TO_CHAR (monitored_date, ‘YYYY.MM’)) from SQL_PERF_HIST) **
ORDER BY SUM (ELA_SECS) desc;
where sql_perf_hist is defined as:
CREATE TABLE SQL_PERF_HIST
(
** MONITORED_DATE DATE,**
** MODULE VARCHAR2(64 BYTE),**
** SQL_ID VARCHAR2(13 BYTE),**
** PLAN_HASH_VALUE NUMBER,**
** NR_EXECS NUMBER(18),**
** ELA_SECS NUMBER,**
** AVG_EXEC NUMBER GENERATED ALWAYS AS (ROUND(CASE WHEN NVL(“NR_EXECS”,(-1))>0 THEN “ELA_SECS”/“NR_EXECS” ELSE (-1) END ,6)),**
** NR_ROWS NUMBER,**
** AVG_ROWS NUMBER GENERATED ALWAYS AS (ROUND(CASE WHEN (NVL(“NR_EXECS”,(-1))>0 AND “NR_ROWS”>(-1)) THEN “NR_ROWS”/“NR_EXECS” ELSE 0 END ,6)),**
** NR_LOG_READS NUMBER(18),**
** AVG_LOG_READS NUMBER GENERATED ALWAYS AS (ROUND(CASE WHEN NVL(“NR_LOG_READS”,(-1))>0 THEN “NR_LOG_READS”/“NR_EXECS” ELSE 0 END ,4)),**
** PARSING_SCHEMA VARCHAR2(30 BYTE),**
** SQL_TEXT CLOB,**
** DESCRIPTION VARCHAR2(256 CHAR),**
** WINDOW NUMBER**
)
LOB (SQL_TEXT) STORE AS SECUREFILE (
** ENABLE STORAGE IN ROW**
** CHUNK 8192**
** DEDUPLICATE**
** COMPRESS MEDIUM**
** NOCACHE**
** LOGGING**
** STORAGE (**
** INITIAL 104K**
** NEXT 1M**
** MINEXTENTS 1**
** MAXEXTENTS UNLIMITED**
** PCTINCREASE 0**
** BUFFER_POOL DEFAULT**
** FLASH_CACHE DEFAULT**
** CELL_FLASH_CACHE DEFAULT**
** ))**
LOGGING
NOCOMPRESS
NOCACHE
RESULT_CACHE (MODE DEFAULT)
NOPARALLEL
MONITORING
/
so you can test alone.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle Forum notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
***** This email is from an external sender outside of the CenterPoint Energy network. Be cautious about clicking links or opening attachments from unknown sources. *****
thx.
Found out that sqlplus is showing the same behavior us as toad-my mistake.
case closed.