converting ibatis sql code to pl/sql [message #416335] |
Fri, 31 July 2009 16:18  |
harish7447
Messages: 22 Registered: July 2009
|
Junior Member |
|
|
Dear All
We are in the process of converting a complex select sql from ibatis to pl/sql. In this I have a few problems.
The Ibatis code is:
<isNotNull prepend="and" property="Name">
dp.DEMO_NAME = #Name:VARCHAR#
</isNotNull>
What it does it it checks if name is not null, if not null then adds to the sql code like:
and dp.Demo_Name = name;
I'm able to send the name as in param (eventhough I'm reqd to send an object, I'm simplyfying it). Can you please help me in how to convert this code to pl/sql...the pl/sql is code is one select command and I have to add this to the where clause. Hope I'm clear. Please let me know ASAP.
Thanks
Harry
|
|
|
|
Re: converting ibatis sql code to pl/sql [message #416339 is a reply to message #416337] |
Fri, 31 July 2009 17:15   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
The Oracle way would be to use NVLs in the where clause with appropriate and / or conditions. I might have explained in a little more detail, but that would have taken more time than you seem to have.
|
|
|
|
|
Re: converting ibatis sql code to pl/sql [message #416415 is a reply to message #416335] |
Sat, 01 August 2009 17:22   |
harish7447
Messages: 22 Registered: July 2009
|
Junior Member |
|
|
dear thomas et all
here is the ibatis code:
for AND in where clause:
<isNotNull prepend="and" property="criteria.Id" open="(" close=")">
dp.ID = #criteria.Id:VARCHAR# or
dpx.MNL_ID = #criteria.Id:VARCHAR#
<isNotNull prepend="or" property="criteria.phoneno">
rtrim(ltrim(dpca.PHN_NUM)) = #criteria.phoneNo:VARCHAR#
</isNotNull>
</isNotNull>
<isNotNull prepend="and" property="criteria.Name">
lower(rtrim(ltrim(dp.NAME))) = #criteria.Name:VARCHAR#
</isNotNull>
<isNotNull prepend="and" property="criteria.StateId">
dp.STATE_ID = #criteria.StateId:DECIMAL#
</isNotNull>
for order by in select statement:
<dynamic prepend="ORDER BY">
<isEqual property="criteria.sortField" compareValue="Name">
upper(dp.NAME)
</isEqual>
<isEqual property="criteria.sortField" compareValue="EhrId">
dp.ID
</isEqual>
<isEqual property="criteria.sortField" compareValue="StateId">
dp.STATE_ID
</isEqual>
<isEqual property="criteria.sortField" compareValue="PhoneNo">
dpca.PHN_NUM
</isEqual>
please let me know the corresponding oracle pl/sql code for the above. Please help
Thanks
|
|
|
Re: converting ibatis sql code to pl/sql [message #416417 is a reply to message #416415] |
Sat, 01 August 2009 20:20   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
This looks to me like you should be looking at using a ref cursor, and the syntax.
That wat you could build up a select statement a bit at a time, exactly like your source code is douing, and then fetch the rows from it.
|
|
|
Re: converting ibatis sql code to pl/sql [message #416444 is a reply to message #416417] |
Sun, 02 August 2009 17:11   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Or you could just do the "and" part as I showed your, and the "Order by part" with a decode in the order by.
There is no real "corresponding" syntax in Oracle, you would have to think about what the code has to achieve, and then re-code the application the Oracle way.
|
|
|
|
|
|
Re: converting ibatis sql code to pl/sql [message #416648 is a reply to message #416639] |
Mon, 03 August 2009 13:35   |
harish7447
Messages: 22 Registered: July 2009
|
Junior Member |
|
|
BlackSwan wrote on Mon, 03 August 2009 12:24 | >I'm using dynamic sql and checking for if the param is null and adding to the where clause
WHY doing dynamic SQL when you've been shown how to code without it?
|
But I'm not able to apply the sample code (dont know) shown to my logic:
for ex. if i want to check a param is not null and assign it to a column, then corresponding code is:
and (param is null or column = param)
But my logic is:
if(param1 is not null) then
and (column1 = param1 or column2=param1)
if(param2 is not null) then
or (column3 = param2)
end if1;
end if2;
can someone help me in gettting a corrsponding oracle pl/sql using the sameple code for this....please
Harish
|
|
|
Re: converting ibatis sql code to pl/sql [message #416655 is a reply to message #416648] |
Mon, 03 August 2009 14:11   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
Your logic makes no sense. You have an "and" right after the "then". Next you have an "or" right after then "then".
You have to figure out what valid logic you want before you can write it in PL/SQL. Perhaps it would be best to forget the Ibatis code, and start coding PL/SQL from the original specifications.
|
|
|
|
|
Re: converting ibatis sql code to pl/sql [message #416666 is a reply to message #416660] |
Mon, 03 August 2009 15:13   |
harish7447
Messages: 22 Registered: July 2009
|
Junior Member |
|
|
BlackSwan wrote on Mon, 03 August 2009 15:30 | >.hope u understand
U is not a member of this forum & does not understand.
We understand, but harish7447 does not understand.
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Post sample test cases showing input parameters & expected/desired results.
If you are lucky, someone might show mercy & do your job for you.
|
sorry that was meant to be hope you understand what i mean to convey.....and also I'm new to Pl/SQL and look up to such forums to someone to show mercy and hope there are lot of people here who do that to newbies like me......i will read the guideline...thanks
|
|
|
Re: converting ibatis sql code to pl/sql [message #416669 is a reply to message #416666] |
Mon, 03 August 2009 15:55   |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
The problem is that porting a database application IS complicated. You can not simply "translate" statements from one platform to the other, most of the time you have to adjust how the application works. Especially if you want to transfer something that has so far been done in an data mapping framework like Ibatis into a data processing language like PL/SQL.
A "newbie" can most likely not do it on it's own without somebody locally guiding him. You can't just jump in in the middle of a complicated project, you have to learn the basics first.
[Updated on: Mon, 03 August 2009 15:57] Report message to a moderator
|
|
|
Re: converting ibatis sql code to pl/sql [message #416678 is a reply to message #416669] |
Mon, 03 August 2009 21:18   |
harish7447
Messages: 22 Registered: July 2009
|
Junior Member |
|
|
ThomasG wrote on Mon, 03 August 2009 16:55 | The problem is that porting a database application IS complicated. You can not simply "translate" statements from one platform to the other, most of the time you have to adjust how the application works. Especially if you want to transfer something that has so far been done in an data mapping framework like Ibatis into a data processing language like PL/SQL.
A "newbie" can most likely not do it on it's own without somebody locally guiding him. You can't just jump in in the middle of a complicated project, you have to learn the basics first.
|
Yeah i understand that...I'm a java developer but my job demands i do this pl/sql and no others in my office to help in this.......so no other go for me
[Updated on: Mon, 03 August 2009 21:19] Report message to a moderator
|
|
|
Re: converting ibatis sql code to pl/sql [message #416680 is a reply to message #416335] |
Mon, 03 August 2009 21:24  |
 |
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
I suggest you manually compose the SQL which will need to be run for a couple of test example.
Once you clearly can see functioning SQL, then you attempt to build statements "dynamically".
My reality is that I still do not clearly know what are input parameters & what is specific desired results.
Break the task into simple identifiable steps.
Do not worry about step #2 until step #1 works as needed.
I suspect that almost all can be done with hard coded SQL.
|
|
|