Home » SQL & PL/SQL » SQL & PL/SQL » converting ibatis sql code to pl/sql (oracle 10g)
converting ibatis sql code to pl/sql [message #416335] Fri, 31 July 2009 16:18 Go to next message
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 #416337 is a reply to message #416335] Fri, 31 July 2009 17:02 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> Please let me know ASAP
Yes, SIR.
We are here to service your every whim.
Your request has been added to the priority queue.
Re: converting ibatis sql code to pl/sql [message #416339 is a reply to message #416337] Fri, 31 July 2009 17:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #416348 is a reply to message #416339] Fri, 31 July 2009 19:10 Go to previous messageGo to next message
harish7447
Messages: 22
Registered: July 2009
Junior Member
ThomasG wrote on Fri, 31 July 2009 18:15
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.


Dear Thomas

Thanks for your reply...I would really appreciate it if you can explain more in detail Smile thanks in advance
Re: converting ibatis sql code to pl/sql [message #416349 is a reply to message #416348] Fri, 31 July 2009 19:15 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
It can be done like this, basically:

select .....
 where ... 
   and ( v_param is null or dp.DEMO_NAME = v_param )
   and ...


without the need to change the query itself.
Re: converting ibatis sql code to pl/sql [message #416415 is a reply to message #416335] Sat, 01 August 2009 17:22 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
OPEN c_cur for '<string>
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #416446 is a reply to message #416335] Sun, 02 August 2009 19:25 Go to previous messageGo to next message
harish7447
Messages: 22
Registered: July 2009
Junior Member
thanks for all your repluies..i think i would be able to do it (not yet complete) using dynamic sql...though it is tedious and i'm not a master of that...thanks again Smile
Re: converting ibatis sql code to pl/sql [message #416638 is a reply to message #416446] Mon, 03 August 2009 11:11 Go to previous messageGo to next message
harish7447
Messages: 22
Registered: July 2009
Junior Member
harish7447 wrote on Sun, 02 August 2009 20:25
thanks for all your repluies..i think i would be able to do it (not yet complete) using dynamic sql...though it is tedious and i'm not a master of that...thanks again Smile


Dear All

Please help me again. I'm using dynamic sql and checking for if the param is null and adding to the where clause, it works fine but the problem is in the bind variables, if one of the conditions is not met (that is the param is null), then i need to change the list of bind variables. but the where clause is tedious and i cannot do the same for the bind variables also. is there anyway to make this simple?


thanks

Harish
Re: converting ibatis sql code to pl/sql [message #416639 is a reply to message #416335] Mon, 03 August 2009 11:24 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>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?
Re: converting ibatis sql code to pl/sql [message #416648 is a reply to message #416639] Mon, 03 August 2009 13:35 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 #416659 is a reply to message #416655] Mon, 03 August 2009 14:24 Go to previous messageGo to next message
harish7447
Messages: 22
Registered: July 2009
Junior Member
ThomasG wrote on Mon, 03 August 2009 15:11
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.



the logic i need to convert is


procedure(id in, phone in, cursor out)
select * from blah, blah
from blah
where blah blah

if (id is not null) [logic]
and table.id = id or table.mnl_id = id [sql code]
if (phone is not null)
or table.phone = phone
end if;
end if;


just have to add upon the basic syntax you had given earlier Sad...hope u understand


Re: converting ibatis sql code to pl/sql [message #416660 is a reply to message #416335] Mon, 03 August 2009 14:30 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>.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.
Re: converting ibatis sql code to pl/sql [message #416666 is a reply to message #416660] Mon, 03 August 2009 15:13 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3189
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 Go to previous messageGo to next message
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 Smile

[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 Go to previous message
BlackSwan
Messages: 25050
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.
Previous Topic: Users with access to a function
Next Topic: Values b/w 0 and 1 needs to be decoded
Goto Forum:
  


Current Time: Sat Dec 10 22:48:48 CST 2016

Total time taken to generate the page: 0.07501 seconds