Devildib
asked on
Oracle select with function call
Hi experts, i have this table called employee with 4 columns.name varchar, role varchar, salary varchar and details clob (xml).
Eg of clob xml : <employee><detail><add1>ab
<add2>abc</add2><add3>abc<
Until now i was able to select name, role, salary and xml node add1 using a select query with xmltable function.Now as per new requirement, i need to extract add2 and add3 aswel.What i need is generic function which i can use in my select query that takes in the details clob column and internally would call xmltable function and return all three nodes add1,add2,add3.using this function in my slect i want to return add1,add2,add3 along with name , role and salary.Pls help.
Eg of clob xml : <employee><detail><add1>ab
<add2>abc</add2><add3>abc<
Until now i was able to select name, role, salary and xml node add1 using a select query with xmltable function.Now as per new requirement, i need to extract add2 and add3 aswel.What i need is generic function which i can use in my select query that takes in the details clob column and internally would call xmltable function and return all three nodes add1,add2,add3.using this function in my slect i want to return add1,add2,add3 along with name , role and salary.Pls help.
I'm sure I'm missing the actual requirement but to extract additional values from the XML, just add the nodes to the XMLTABLE list of columns.
Just like your last question:
Sample data and expected results help a lot.
Just like your last question:
Sample data and expected results help a lot.
ASKER
How do we return all three nodes along with other columns using select statement? I have given you the data.
You already have an XMLTABLE call that returns add1.
Returning add2 and add3 is just adding two more columns clauses in the XMLTABLE call.
Returning add2 and add3 is just adding two more columns clauses in the XMLTABLE call.
ASKER
Select name, role, salary , oldfunction(clob data) from eployee
Select name, role, salary, newfunction(clobdata) from employee
Will the second select give me what i want? How is newfunction going to return add1,add2, add3??dunction normally returns single value right?
Select name, role, salary, newfunction(clobdata) from employee
Will the second select give me what i want? How is newfunction going to return add1,add2, add3??dunction normally returns single value right?
I don't know what oldfunction and newfunction are.
You referenced XMLTABLE. That isn't used inline as a column function.
There is no need for a 'custom' function.
In the select you have in the XMLTABLE piece you should have add1 under the columns section. Just copy and paste that line two more times and change the two new lines to add2 and add3.
Post the query you have and I can add the two new columns.
You referenced XMLTABLE. That isn't used inline as a column function.
There is no need for a 'custom' function.
In the select you have in the XMLTABLE piece you should have add1 under the columns section. Just copy and paste that line two more times and change the two new lines to add2 and add3.
Post the query you have and I can add the two new columns.
ASKER
I am trying to help you in making the data easy and simple.anyways oldfunction is what i already have.withing oldfunction the xmltable logic is written to extract add1.and i am using this custom function in order to handle some exceptions.in newfunction i can build on by adding add2 and add3 in the xmltable select.But finally i would require this function return all three add1,add2and add3 values.hope you are getting me now.in nutshell, how do i return multiple values out of a function that is used in a select query?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks.but thats not the case.i have given you all whats needed for this question.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for flyhighrohit12's comment #a41216174
for the following reason:
All ok
Accepted answer: 0 points for flyhighrohit12's comment #a41216174
for the following reason:
All ok
ASKER
All ok
I realize you understand the problem you want solved. What you have provide might not be all that clear to the people reading it on the other end.
If you have written a custom function that does some 'magic' to return one value and not you need to return three:
As I mentioned above:
1: Have the function return the three values as XML and use XMLTABLE in the calling query.
or
2: Change the function to allow you to pass in the node value you want, then return the single value. The problem here is you would need to call the function three times:
Select name, role, salary, newfunction(clobdata,'add1
If you have written a custom function that does some 'magic' to return one value and not you need to return three:
As I mentioned above:
1: Have the function return the three values as XML and use XMLTABLE in the calling query.
or
2: Change the function to allow you to pass in the node value you want, then return the single value. The problem here is you would need to call the function three times:
Select name, role, salary, newfunction(clobdata,'add1
ASKER
<add2>abc</add2><add3>abc<