Migrating from Oracle to SQL
-
Hi I am trying to convert an Oracle query to SQL. But i am totally confused thinking how to proceed. For some of the oracle packages , am not able to find an equivalent SQL function Here is my Oracle query cursor c_fields(c_xml XMLTYPE) is select DBMS_XMLGEN.convert(extract(value(x), '//field/@name').getstringval(),1) FLD_NAME, extract(value(x), '//field/@resultCol').getstringval() RESULT_COL, extract(value(x), '//field/fieldConfiguration/@isAttachmentAllowed').getnumberval() AllOW_ATTACHMENT, extract(value(x), '//field/@key').getnumberval() FLD_ID from TABLE(XMLSEQUENCE(EXTRACT(c_xml, '//table/field'))) x; I need to convert this to SQL query. In SQL we dont have parameterized cursor concepts. However to some extent we can achieve the functionality by using "WHERE" condition. But in this query, am not able to trace out how can i write the equivalent SQL functionality since "FROM" part itself is using the parameterized cursor. Please help. Thanks Arudya
-
Hi I am trying to convert an Oracle query to SQL. But i am totally confused thinking how to proceed. For some of the oracle packages , am not able to find an equivalent SQL function Here is my Oracle query cursor c_fields(c_xml XMLTYPE) is select DBMS_XMLGEN.convert(extract(value(x), '//field/@name').getstringval(),1) FLD_NAME, extract(value(x), '//field/@resultCol').getstringval() RESULT_COL, extract(value(x), '//field/fieldConfiguration/@isAttachmentAllowed').getnumberval() AllOW_ATTACHMENT, extract(value(x), '//field/@key').getnumberval() FLD_ID from TABLE(XMLSEQUENCE(EXTRACT(c_xml, '//table/field'))) x; I need to convert this to SQL query. In SQL we dont have parameterized cursor concepts. However to some extent we can achieve the functionality by using "WHERE" condition. But in this query, am not able to trace out how can i write the equivalent SQL functionality since "FROM" part itself is using the parameterized cursor. Please help. Thanks Arudya
My first choice would be to refactor the database unless that code is touched very seldom. That sort of meta data solution is very inefficient. But after that I would use dynamic sql. You create a varchar with equivalent sql in it, specifically the extracted table name. I would probably extract the rest as well. Then you use 'execute' to execute the varchar.