tag:blogger.com,1999:blog-34020393.post2332538092353082615..comments2024-03-27T10:59:38.307-07:00Comments on Jim's PeopleSoft Journal: Using JDBC to Execute Stored Procedures with Output ParametersJim Marionhttp://www.blogger.com/profile/12995110203807924786noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-34020393.post-79522038270518047432016-01-21T05:50:11.229-08:002016-01-21T05:50:11.229-08:00Hi Jim,
Thank you very much for the post, we have...Hi Jim,<br /><br />Thank you very much for the post, we have same scenario where we would like to call procedure using Java in Peoplecode but it is not working.<br />My requirment is to drop a DB Link using procedure.<br /><br /><br />So created below procedure in Database:<br /><br />create or replace PROCEDURE dropdblink(dlink in varchar2) AS<br />BEGIN<br />EXECUTE IMMEDIATE 'drop database link '||dlink;<br />END dropdblink;<br /><br />Executed procedure in SQLDeveloper and it worked.<br /><br />EXEC dropdblink('DBL_ITSHRDEV');<br />--Result: anonymous block completed<br /><br />So tried calling this procedure using java in Peoplecode but its giving error as showed below. <br /><br />********Code Start*****<br /><br />Local JavaObject &driver = CreateJavaObject("oracle.jdbc.OracleDriver");<br />Local JavaObject &info = CreateJavaObject("java.util.Properties");<br /><br />&info.put("user", "*****");<br />&info.put("password", "*****");<br /><br />Local JavaObject &conn = &driver.connect("jdbc:oracle:thin:Host:port:server", &info);<br />Local JavaObject &stmt = &conn.createStatement();<br /><br />Local JavaObject &proc = &conn.prepareCall("{call dropdblink(?)}");<br />Local JavaObject &types = GetJavaClass("java.sql.Types");<br />&proc.setstring(1, "");<br />&proc.execute();<br />rem &proc.executequery();<br /><br />********Code End*****<br />Error: Java method executequery not found for class java.sql.CallableStatement. (2,760) <br /><br />I tried different combinations but nothing is working. So please suggest.<br /><br />Thanks,<br />MNAnonymoushttps://www.blogger.com/profile/01621231380557852448noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-11465973115644761182009-04-24T10:25:00.000-07:002009-04-24T10:25:00.000-07:00@Paul, you are exactly right. That is how I do Aja...@Paul, you are exactly right. That is how I do Ajax (IScripts/JavaScript). If you are just loading HTML into a DIV, then you might want to take a look at $("#div_id").get("IScript URL"). Thank you for sharing your code! I've been meaning to write an Ajax/PeopleSoft tutorial, I just haven't gotten around to it yet.Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-61396649620679699822009-04-24T07:14:00.000-07:002009-04-24T07:14:00.000-07:00Jim, You are correct - my question was "how would ...Jim, You are correct - my question was "how would you access an iScript from JavaScript". <br /><br />The missing piece, for me, was the vehicle for connecting to the iScript functionality. One of the Ajax posts you pointed me to sent me in the right direction, thankfully.<br /><br />I ended up using something similar to the following, where the bind value is the URL of the iScript. <br />$.ajax({ <br />type: "POST", <br />url: "%BIND(:3)", <br />data: "%BIND(:4)", <br />success: function(){alert( "success");<br />} <br />});<br /><br />Without using ajax, I was stuck at redirecting the location to the iScript URL, to execute my code, and then redirecting the location back to the original page. It worked, but it was ugly. <br /><br />thanks for the ajax pointer!Unknownhttps://www.blogger.com/profile/11724300872535035532noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-6856188538495878012009-04-23T10:17:00.000-07:002009-04-23T10:17:00.000-07:00@Paul, can you elaborate? I'm not sure I follow yo...@Paul, can you elaborate? I'm not sure I follow your line of thought. You asked, "How would you access an iScript from javascript, instead of the other way around?" If I follow the logic in your question correctly, the "other way around" would be accessing JavaScript from an IScript? You can execute JavaScript from an IScript using the Apache Bean Scripting framework (I blogged about this in my post <A HREF="http://jjmpsj.blogspot.com/2007/06/scripting-peoplesoft.html" REL="nofollow">Scripting PeopleSoft</A>), but I don't think that is what you are asking.<br /><br />If your question really is, "How would you access an iScript from JavaScript," then the answer is <A HREF="http://jjmpsj.blogspot.com/search/label/Ajax" REL="nofollow">Ajax</A>. I use <A HREF="http://jquery.com" REL="nofollow">jQuery</A> to make Ajax calls. jQuery dramatically simplifies Ajax.<br /><br />Basic steps for Ajax:<br /><br />1. Create an <A HREF="http://jjmpsj.blogspot.com/search/label/IScripts" REL="nofollow">IScript</A> data source<br /><br />2. Create a Derived/Work record with the field HTMLAREA<br /><br />3. Add an HTML Area to a page<br /><br />4. Bind the page's HTML Area to the Derived/Work HTMLAREA field<br /><br />5. Use RowInit of the level containing the HTML Area to set the value of the Derived Work record's HTMLAREA field<br /><br />I generally use an HTML definition to store the HTML, JavaScript, etc that is required to call the IScript. I place an HTML Bind, %Bind(:n), in the place where the IScript URL should be called from JavaScript and use <A HREF="http://download.oracle.com/docs/cd/E13292_01/pt849pbr0/eng/psbooks/tpcl/htm/tpcl02.htm#d0e25691" REL="nofollow">GenerateScriptContentURL</A> to obtain a reference to the IScript's URL.Jim Marionhttps://www.blogger.com/profile/12995110203807924786noreply@blogger.comtag:blogger.com,1999:blog-34020393.post-83215185624729917752009-04-23T07:00:00.000-07:002009-04-23T07:00:00.000-07:00This post got me thinking of how to communicate th...This post got me thinking of how to communicate the other way with an iScript. How would you access an iScript from javascript, instead of the other way around? I imagine you would be able to pass the iScript URL as a function, but I haven't worked out how just yet. Have you come across a way to do so?Unknownhttps://www.blogger.com/profile/11724300872535035532noreply@blogger.com