We help IT Professionals succeed at work.

Bind variable in SELECT statement

Arnee_Senthil
on
4,972 Views
Last Modified: 2013-12-19
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;

Open in new window

Comment
Watch Question

programmer-analyst
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.