I have the query below. When I write it in a code, I can execute it.

sSql  CLOB  := 'SELECT SUBSTR(NVL((SELECT b.DESC FROM LOCATION b WHERE b.LOCATION_ID = e.LOCATION_ID),0) || ''          '', 1, 10) AS BEZ, 
                           TO_CHAR(TO_DATE(MY_DAY,''j''), ''yyyymmdd'') AS TAG, 
                           SUBSTR(''000000'' || TO_CHAR(MY_CNT), -5, 5) AS MY_CNT
                      FROM (SELECT a.LOCATION_ID, 
                                   a.MY_DAY, 
                                   a.MY_CNT 
                              FROM (SELECT COUNT(*) as MY_CNT, MY_DAY, LOCATION_ID
                                      FROM (SELECT DISTINCT MY_DAY, SENDUNGSNR, LOCATION_ID
                                              FROM TAB_1 
                                             WHERE TAB_1_ID = :n1
                                               AND ISTGP_ID != 20545
                                               AND LOCATION_ID IN (SELECT LOCATION_ID FROM LOCATION WHERE M1 = 745)
                                           ) 
                                  GROUP BY LOCATION_ID, MY_DAY) a 
                           ) e 
                ORDER BY NVL((SELECT DESC FROM LOCATION b WHERE b.LOCATION_ID = e.LOCATION_ID), 0), MY_DAY';

This works fine

dbms_sql.parse(nCursorId, sSql, dbms_sql.native);
DBMS_SQL.BIND_VARIABLE (nCursorId, 'n1', '25');

But I wish to read this query from the table. How should I save it in a table field? I have tried many various ways(with apostrophe at the beginning, without apostrophe at the beginning etc). But every time the parsing fails.

1. How should I save it to be able to execute it?
2. How can I show where the query is incorrect?

CodePudding user response：

How can I show where the query is incorrect?

DESC is a reserved word, you cannot use it as an (unquoted) identifier.

How should I save it to be able to execute it?

You do not need to save it to be able to execute it, just execute it using EXECUTE IMMEDIATE or DBMS_SQL:

DECLARE
  sSql  CLOB  := 'SELECT SUBSTR(NVL((SELECT b."DESC" FROM LOCATION b WHERE b.LOCATION_ID = e.LOCATION_ID),0) || ''          '', 1, 10) AS BEZ, 
                           TO_CHAR(TO_DATE(MY_DAY,''j''), ''yyyymmdd'') AS TAG, 
                           SUBSTR(''000000'' || TO_CHAR(MY_CNT), -5, 5) AS MY_CNT
                      FROM (SELECT a.LOCATION_ID, 
                                   a.MY_DAY, 
                                   a.MY_CNT 
                              FROM (SELECT COUNT(*) as MY_CNT, MY_DAY, LOCATION_ID
                                      FROM (SELECT DISTINCT MY_DAY, SENDUNGSNR, LOCATION_ID
                                              FROM TAB_1 
                                             WHERE TAB_1_ID = :n1
                                               AND ISTGP_ID != 20545
                                               AND LOCATION_ID IN (SELECT LOCATION_ID FROM LOCATION WHERE M1 = 745)
                                           ) 
                                  GROUP BY LOCATION_ID, MY_DAY) a 
                           ) e 
                ORDER BY NVL((SELECT "DESC" FROM LOCATION b WHERE b.LOCATION_ID = e.LOCATION_ID), 0), MY_DAY';
  v_bez SYS.ODCIVARCHAR2LIST;
  v_tag SYS.ODCIVARCHAR2LIST;
  v_cnt SYS.ODCIVARCHAR2LIST;
BEGIN
  EXECUTE IMMEDIATE sSQL BULK COLLECT INTO v_bez, v_tag, v_cnt USING 1;
  
  FOR i IN 1 .. v_bez.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE( v_bez(i) || ', ' || v_tag(i) || ', ' || v_cnt(i) );
  END LOOP;
END;
/

If you do want to save it then:

DECLARE
  sSQL CLOB := '<your query>';
BEGIN
  INSERT INTO table_name (clob_column) VALUES (sSql);
END;
/

db<>fiddle here