Topic: SQL*Plus and PL/SQL >> How to create a dyanamic query in PL/SQL from XML
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: How to create a dyanamic query in PL/SQL from XML |
| kris |
Posted: Mar 18, 2007 09:25:34 PM |
Total Post: 12
Joined: Jan, 2007
|
I am having an XML file with tags. I am controlling my JSP page layout with these tags and it is working fine. But I need to send the Orange colour nodes to a pl/sql in the JSP page to construct the query as per the XML
<?xml version="1.0"?>
<Jobmanage>
<Customer name="StGeorge">
<Column>
<Name>QM_NUMBER</Name>
<Title>Job Number</Title>
<Width>10</Width>
<Font>Arial</Font>
<Size>10</Size>
<Align>center</Align>
<Display>Yes</Display>
<cloumn1> QM_NUMBER <column1>
</Column>
<Column>
<Name>QM_JOB_STATUS</Name>
<Title>Job Status</Title>
<Width>10</Width>
<Font>Arial</Font>
<Size>105</Size>
<Align>center</Align>
<Display>Yes</Display>
<column2>QM_JOB_STATUS</column2>
</Column>
<Column>
<Name>QM_USER_STATUS</Name>
<Title>User Status</Title>
<Width>10</Width>
<Font>Arial</Font>
<Size>10</Size>
<Align>center</Align>
<Display>Yes</Display>
<column3>QM_USER_STATUS</column3>
</Column>
</Customer>
</Jobmanage>
The select statement I am using n the JSP page is
SELECT Q.QM_NUMBER, Q.QM_JOB_STATUS, Q.QM_USER_STATUS FROM QMI Q WHERE ((Q.QM_JOB_STATUS IN (5, 6, 7, 8, 9, 10)) OR (Q.QM_JOB_STATUS IN (12) AND Q.QM_CLOSE_DATE >= SYSDATE - INTERVAL '6' MONTH))
Can you give me instructions how to create the above SQL statement dynamically as per the XML file. I want the select statement in a pl/sql
BEGIN
SELECT Q.QM_NUMBER, Q.QM_JOB_STATUS, Q.QM_USER_STATUS FROM QMI Q WHERE ((Q.QM_JOB_STATUS IN (5, 6, 7, 8, 9, 10)) OR (Q.QM_JOB_STATUS IN (12) AND Q.QM_CLOSE_DATE >= SYSDATE - INTERVAL '6' MONTH))
END
So, when ever the columns hirerachy changed in the XML file it should show theat effect in the PL/SQl. Thats hoe I am aiming to develop.
Thankx for you time.
Cheers,
Krishna
|
|
|
 kris |
| Posted: Mar 18, 2007 09:30:58 PM |  |
|
Total Post: 12
Joined: Jan, 2007
|
Hi,
Small change to my thread. The orange colored nodes are
<cloumn1> QM_NUMBER <column1>
<column2>QM_JOB_STATUS</column2>
<column3>QM_USER_STATUS</column3>
cheers,
Krishna.
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Vinoth Kumar | 357 |
| Gopu Gopi | 340 |
| Gitesh Trivedi | 322 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
