Bind variable in SELECT statement

Hi experts,

I have a task in my hand - Have to generate XML file from the result set generated by a select query. The business req'mt is like...user will provide one input value say FILING_ID. For the given filing ID i have to execute some 20 predefined SELECT queries. Now the SELECT queries are stored in a ora table column as varchar2. In each select query I have a string named as "V_FILE_ID". I have given the code snippet that I am currently using in...

* I run a select query to get a DB column value and replace the variable V_FILE_ID which is embedded in the SELECT query stored in a db column.
* After doing the above step, I'll get a SELECT query with FilingID data.
* I have to use this Query to run  a XML generation command as given in the code snippet.

I know replacing the variable as I do gives a performance issue on ora server. I would like to introduce Bind Variables instead of hard-coded-variable-and-replace.

I seen many examples for INSERT and UPDATE queries with Bind Variables. But all INSERT and UPDATE commands are executed by EXECUTE IMMEDIATE command. I dont know how to do that in my code using SELECT query with bind var. I would like to know how to write a select query in the situation as I mentioned.

I hope, have provided required input. If I am not clear please feel free to post your comment.

FYI..The queries will generate min of 70K record.
 

DECLARE
  v_xml             CLOB;
  v_ctx             DBMS_XMLGen.ctxHandle;
  v_SQLQry          VARCHAR2(4000) :='';
  v_ParentNode      VARCHAR2(50) :='';
  v_ChildNode       VARCHAR2(50) :='';
  v_FileName        VARCHAR2(100):='';
BEGIN
  ---------------------------------------------------
  -- Load the query string from the database table
  -- based on queryid and filingtype input value
  ---------------------------------------------------
  SELECT REPLACE(QRY, 'V_FILE_ID', v_filingID ) AS QRY, 
   ParentNode, ChildNode, FileName INTO v_SQLQry, v_ParentNode, 
   v_ChildNode, v_FileName 
  FROM <<TABLE1>> 
   WHERE QueryID=v_queryID;
 
  ---------------------------------------------------
  -- I WOULD LIKE LIKE TO DO THE BIND VARIABLE OPERATION
  -- HERE.
  ----------------------------------------------------
  v_ctx := DBMS_XMLGen.newContext(v_SQLQry);
 
  ---------------------------------------------------  
  --Declaration of Parent node and child 
  --node definition for all query 
  ---------------------------------------------------    
  DBMS_XMLGen.setRowsetTag(v_ctx, v_ParentNode); 
  DBMS_XMLGen.setRowTag(v_ctx, v_ChildNode); 
  
  v_xml := DBMS_XMLGen.GetXML(v_ctx);
 
 
 
  ---------------------------------------------------
  -- Call a Procedure to write CLOB to XML file
  -- NOT INCLUDED IN THE CODE SNIPPET
  ---------------------------------------------------
  POC_Write_CLOB_To_XML();
  DBMS_XMLGen.closeContext(v_ctx);
END;

Select allOpen in new window