Can someone please let me know what is going wrong with this query. I am trying to convert the long to CLOB. I wanted to convert the long to varchar2 and in order to do, I am converting the long to clob and clob to varchar2.
The query works fine when do a rownum=1 within the dynamic query but I would like to read through all the constriants so tried the below and its failing with an error message for unexpected end of SQL command.
-- with rownum
SELECT dbms_lob.substr( longtoclob,4000,1) text
from dual,
(select (dbms_xmlgen.getxml('SELECT t.search_condition ALIASLONG
FROM All_Constraints t WHERE rownum=1')) longtoclob
from dual) Y where DBMS_LOB.GETLENGTH(Y.longtoclob) > 0;
-- generic query
select (SELECT(dbms_xmlgen.getxml(Q'[select search_condition from all_contstaints where cosntraint_name = ']' || constraint_name || Q'[']')) text, constraint_name from
all_constraints
where rownum <=3;