Execute text SQL in Oracle

  
Hi, I need to execute a Text SQL (include physical table name).

In SQL Server, I did this by using EXEC command like 'EXEC(@Sql)'.
But in Oracle, I can't find the way to solve this.
EXECUTE IMMIDIATE command needs INTO variable and doesn't work.

Does anyone know the solution?
Hi Nishihara,
I also tried "EXECUTE IMMIDIATE" but doesnt work . Do you have any specific requirment to do that?.


Regards
-PJ-
Hi, PJ

Yes, I want to use Static Entity records at client side by converting them to JSON object.
To realize this, I access to Entity(OSSYS_ENTITY) and get physical table name of specific static entities and get their records by XML format using "dbms_xmlgen.getxml" like select dbms_xmlgen.getxml(@Sql) from dual.
But I think this way isn't good from performance perspective.
So I would like to know the smart way to realize my purpose without using XML conversion.

Hi,

if you are using Oracle 12c you could use the following advanced query that uses the WITH clause to define a function that returns the output for the specified query  as XML. Hope this helps.


SyntaxEditor Code Snippet

WITH
  FUNCTION get_xml(pSQL IN VARCHAR2) RETURN CLOB IS
    vXML CLOB;
  BEGIN
    EXECUTE IMMEDIATE 'SELECT DBMS_XMLGEN.GETXML('''||pSQL||''') FROM DUAL' INTO vXML;
    RETURN vXML;
  END;
SELECT get_xml(@Sql) xml FROM DUAL