tag:blogger.com,1999:blog-33206689212049550112024-03-12T23:05:24.842-07:00Kaushik's Teradata \ Oracle BlogContains information about Teradata,BTEQ,FASTLOAD,FASTEXPORT, Oracle Pl/SQL,forms, JAVA open source related topics and discussions<br>
<a href="http://forums.oracle.com/forums/forum.jspa?forumID=82">
Oracle forms and reports</a><br>
<a href="http://forums.oracle.com/forums/forum.jspa?forumID=61">Oracle database</a> GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.comBlogger16125tag:blogger.com,1999:blog-3320668921204955011.post-46372957662929738782012-10-22T14:39:00.000-07:002012-10-22T14:39:37.486-07:00Strategic and Operational intelligence<div dir="ltr" style="text-align: left;" trbidi="on">
Good to blog after a long 3 years :-)<br />
<br />
Normally anyone within Datawarehosuing would be be finding harder to get in the actual meaning of many hard definitions.<br />
<br />
From which I am going to blog two words today<br />
<br />
<b>1. Strategic Intelligence</b><br />
<br />
Normally we pile up a lot of data within the warehouse to analyze the same , get the trends from the data and define strategies for Business.<br />
<br />
These load of data would normally be used by pile of back office workers<br />
using the BI tools<br />
<br />
<br />
<b>2. Operational Intelligence</b><br />
<br />
When the operational workers such as Call center workers get the insight of the data or the analytic capablity, then it is termed as operational intelligence<br />
<br />
<br />
Find below the link which provides more content on the above two words<br />
<br />
<a href="http://www.teradata.com/resources/white-papers/Enabling-the-Agile-Enterprise-with-Active-Data-Warehousing-eb4931/">http://www.teradata.com/resources/white-papers/Enabling-the-Agile-Enterprise-with-Active-Data-Warehousing-eb4931/</a><br />
<br />
<br />
<br />
<br />
<img alt="EB4931-fig1" src="http://www.teradata.com/uploadedImages/Resources-HTML/White_Papers/Images/EB4931-fig1.JPG" title="EB4931-fig1" /></div>
GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com0tag:blogger.com,1999:blog-3320668921204955011.post-78054744429875187902009-07-31T20:54:00.000-07:002009-08-16T05:37:34.781-07:00Oracle SQL Tuning<div style="text-align: right;"><span class="Apple-tab-span" style="white-space:pre"> </span> <a href="http://gskaushik.blogspot.com/2009/07/oracle-sql-tuning.html#comments"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8S3xfR6YxPFFZXmHJObg_itEM6OqGK2tKiJsHJwLbA6N37BUgHx87gt-XuSbtxT6-RfYh6v6H_F-DAJ8lA26zfRUrJdyQhnE-X0VwBsx_rhaqIW-rqe_ja5aTzQoTGRX3N6ifsdA9ejz8/s800/commentGreyMedium.png" /></a><span class="Apple-tab-span" style="white-space:pre"> </span></div><div>In many scenarios, I have been changing and executing SQL statement to meet the performance requirement posed by client.</div><div><br /></div><div>But normaly, i did not have any direction how to tune and whom to ask,</div><div><br /></div><div>So I write an adhoc query and try to execute it, but I will not be sure whether Iam in right path or not.</div><div><br /></div><div>In this scenario, I used Oracle database to give the tuning recommendations, rather than explaining all the tables and schemas to another person and trying adhoc queries.</div><div><br /></div><div>From Oracle database 1og we have a package called <b><a href="http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_sqltun.htm">DBMS_SQLTUNE</a></b> to tune a query and get the recommendations from the database in form of a report.</div><div><br /></div><div> So let's Start. <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdYVfQ4n2GquwuXf-bJDPqAq1ZhxkXNe8BbJKZ6JJziCO3gbxdBqPGpUT2bVMW5V_HezIDC-bRm6Q1Ygiox9dZDhkz-Jr1j919qxl53dOCd_6BkaF4qyASxanIuCLtAfjAppI0IGua3_PX/s1600-h/SQL_TUNING1.png"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhdYVfQ4n2GquwuXf-bJDPqAq1ZhxkXNe8BbJKZ6JJziCO3gbxdBqPGpUT2bVMW5V_HezIDC-bRm6Q1Ygiox9dZDhkz-Jr1j919qxl53dOCd_6BkaF4qyASxanIuCLtAfjAppI0IGua3_PX/s200/SQL_TUNING1.png" border="0" alt="" id="BLOGGER_PHOTO_ID_5364845291329025666" style="cursor: pointer; width: 200px; height: 143px; " /></a> </div><div><br /></div><div><b><span class="Apple-style-span" style="text-decoration: underline;">Following are the steps</span><span class="Apple-style-span" style=""> <span class="Apple-style-span" style="font-weight: normal; "><span class="Apple-tab-span" style="white-space: pre; "> </span> <span class="Apple-tab-span" style="white-space: pre; "> </span> <a href="http://gskaushik.blogspot.com/2009/07/oracle-sql-tuning.html#comments"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8S3xfR6YxPFFZXmHJObg_itEM6OqGK2tKiJsHJwLbA6N37BUgHx87gt-XuSbtxT6-RfYh6v6H_F-DAJ8lA26zfRUrJdyQhnE-X0VwBsx_rhaqIW-rqe_ja5aTzQoTGRX3N6ifsdA9ejz8/s800/commentGreyMedium.png" /></a><span class="Apple-tab-span" style="white-space: pre; "> </span></span></span></b></div><div><span class="Apple-style-span" style="text-decoration: underline;"><b><br /></b></span></div><div>1. Getting the Grants for accessing the SQL Tuning Advisor</div><div><span class="Apple-style-span" style="text-decoration: underline;"><b><br /></b></span></div><div>2. Create a SQL Tuning task.</div><div><br /></div><div>3. Execute a SQL Tuning Task.</div><div><br /></div><div>4. Display the results of SQL Tuning task.</div><div><br /></div><div>5. Implement the recommendations.</div><div><br /></div><div><b>1.<span class="Apple-style-span" style=""><span class="Apple-style-span" style="text-decoration: underline;">Getting the Grants for accessing the SQL Tuning Advisor</span> </span></b></div><div><span class="Apple-style-span" style="text-decoration: underline;"><b><br /></b></span></div><div><span class="Apple-style-span" style="font-size:small;">We need to get the following Grants before accesing the SQL Advisor</span></div><div><span class="Apple-style-span" style="font-size:small;">features of Oracle 10g.</span></div><div><div><span class="Apple-style-span" style="font-family:Georgia;"><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 681px; font-family:'Andale Mono', 'Lucida Console', Monaco, fixed, monospace;font-size:12px;"><span class="Apple-style-span" style="line-height: normal; white-space: normal; font-family:'times new roman';"><pre><span class="Apple-style-span" style="color: rgb(0, 102, 0); "><span class="Apple-style-span" style="font-size:medium;">//Grants to be given</span></span></pre><pre><span class="Apple-style-span" style="color:#006600;">// Log in as sys and give the following grants to the developer users</span></pre><pre><span class="Apple-style-span" style="color:#006600;">// Here Iam using SCOTT as the Developer user</span></pre><pre><span class="Apple-style-span" style="color:#000099;">GRANT ADVISOR to SCOTT;</span></pre><pre><span class="Apple-style-span" style="color:#000099;">GRANT EXECUTE ON DBMS_SQLTUNE to SCOTT;</span></pre><pre><span class="Apple-style-span" style="color:#000099;">GRANT SELECT ON DBMS_ADVISOR_LOG to SCOTT;</span></pre></span></pre></div></span></div></div><div><span class="Apple-style-span" style="font-weight: bold; "><span class="Apple-style-span" style="text-decoration: underline;">2. Create a SQL Tuning task</span></span></div><div> Lets take for example the following is the SQL Statement to be tuned.</div><div><div><span class="Apple-style-span" style="font-family:Georgia;"><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 681px; font-family:'Andale Mono', 'Lucida Console', Monaco, fixed, monospace;font-size:12px;"><span class="Apple-style-span" style="line-height: normal; white-space: normal; font-family:'times new roman';"><pre><span class="Apple-style-span" style="color: rgb(0, 102, 0); "><span class="Apple-style-span" style="font-size:medium;">//SQL Statement to be Tuned</span></span></pre><pre><span class="Apple-style-span" style="color:#006600;"><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style="color: rgb(51, 51, 204); font-weight: bold; "><span class="Apple-style-span" style="font-family:georgia;"><span class="Apple-style-span" style="font-size:small;">SELECT e.*, d.* </span></span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style="color: rgb(51, 51, 204); font-weight: bold; "><span class="Apple-style-span" style="font-family:georgia;"><span class="Apple-style-span" style="font-size:small;">FROM emp e </span></span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style="color: rgb(51, 51, 204); font-weight: bold; "><span class="Apple-style-span" style="font-family:georgia;"><span class="Apple-style-span" style="font-size:small;">JOIN dept d </span></span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style="color: rgb(51, 51, 204); font-weight: bold; "><span class="Apple-style-span" style="font-family:georgia;"><span class="Apple-style-span" style="font-size:small;">ON e.deptno = d.deptno </span></span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span class="Apple-style-span" style="color: rgb(51, 51, 204); font-weight: bold; font-family:georgia;"><span class="Apple-style-span" style="font-size:small;">WHERE NVL(empno, ''0'') = :empno;</span><span class="Apple-style-span" style="color: rgb(0, 0, 0); font-weight: normal; white-space: normal; font-family:Georgia;font-size:16px;"> </span></span></p></span></pre></span></pre></div></span></div></div><div>After identifying the SQL statement to be tuned, </div><div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSTrG4IyDDHeZG6y-bZqHOmmx1WiVExHP08GLto8iCRG24nwsx7FiY0Umfi4Fkm8Sn2oWpPUCfHGJZozBz2MWZiReyvU5meE8k9QiYMrLOGAl2cis5_XdrR2rhHjlQkresfwMRevn0DEek/s1600-h/create_task.PNG"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSTrG4IyDDHeZG6y-bZqHOmmx1WiVExHP08GLto8iCRG24nwsx7FiY0Umfi4Fkm8Sn2oWpPUCfHGJZozBz2MWZiReyvU5meE8k9QiYMrLOGAl2cis5_XdrR2rhHjlQkresfwMRevn0DEek/s200/create_task.PNG" border="0" alt="" id="BLOGGER_PHOTO_ID_5370094807478890370" style="cursor: pointer; width: 150px; height: 174px; " /></a></div><div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSTrG4IyDDHeZG6y-bZqHOmmx1WiVExHP08GLto8iCRG24nwsx7FiY0Umfi4Fkm8Sn2oWpPUCfHGJZozBz2MWZiReyvU5meE8k9QiYMrLOGAl2cis5_XdrR2rhHjlQkresfwMRevn0DEek/s1600-h/create_task.PNG"></a>create an anonymous block</div><div>as below and call the <b>'CREATE_TUNING_TASK'</b> function of the </div><div><b>'DBMS_SQL_TUNE'</b> package.</div><div><div><div><span class="Apple-style-span" style="font-family:Georgia;"><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 681px; font-family:'Andale Mono', 'Lucida Console', Monaco, fixed, monospace;font-size:12px;"><span class="Apple-style-span" style="line-height: normal; white-space: normal; font-family:'times new roman';"><pre><span class="Apple-style-span" style="color: rgb(0, 102, 0); "><span class="Apple-style-span" style="font-size:medium;">//Creating the tuning task</span></span></pre><pre><span class="Apple-style-span" style="color:#006600;"><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;">DECLARE</span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"> l_sql VARCHAR2(500);</span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"> l_sql_tune_task_id VARCHAR2(100);</span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;">BEGIN</span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"> l_sql := </span><b><span class="Apple-style-span" style="font-size:small;">'SELECT e.*, d.* ' ||'FROM emp e JOIN dept d ON e.deptno = d.deptno '</span></b></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><b><span class="Apple-style-span" style="font-size:small;"> ||'WHERE NVL(empno, ''0'') = :empno'</span></b><span class="Apple-style-span" style="font-size:small;">;</span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"> l_sql_tune_task_id := </span><b><span class="Apple-style-span" style="font-size:small;">DBMS_SQLTUNE.create_tuning_task</span></b><span class="Apple-style-span" style="font-size:small;"> (</span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"> sql_text => l_sql,</span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"> bind_list => sql_binds(anydata.ConvertNumber(100)),</span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"> user_name => 'scott',</span></span></p><p style="text-indent: 0px;margin-top: 6.6pt; margin-bottom: 0pt; margin-left: 0.7in; text-align: left; direction: ltr; unicode-bidi: embed; vertical-align: baseline; "><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-family:-webkit-monospace;color:#006600;"><span class="Apple-style-span" style="font-size:small;"> </span></span><span class="Apple-style-span" style="font-size:small;"> scope => DBMS_SQLTUNE.scope_comprehensive,</span></span></p><p style="text-indent: 0px;margin-top: 6.6pt; margin-bottom: 0pt; margin-left: 0.7in; text-align: left; direction: ltr; unicode-bidi: embed; vertical-align: baseline; "><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-family:-webkit-monospace;color:#006600;"><span class="Apple-style-span" style="font-size:small;"> </span></span><span class="Apple-style-span" style="font-size:small;"> time_limit => 60,</span></span></p><p style="text-indent: 0px;margin-top: 6.6pt; margin-bottom: 0pt; margin-left: 0.7in; text-align: left; direction: ltr; unicode-bidi: embed; vertical-align: baseline; "><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-family:-webkit-monospace;color:#006600;"><span class="Apple-style-span" style="font-size:small;"> </span></span><span class="Apple-style-span" style="font-size:small;"> task_name =></span><b><span class="Apple-style-span" style="font-size:small;"> 'emp_dept_tuning_task'</span></b><span class="Apple-style-span" style="font-size:small;">,</span></span></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"></p><div style="text-indent: 0px;"><span class="Apple-style-span" style="color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-family:-webkit-monospace;color:#006600;"><span class="Apple-style-span" style="font-size:small;"> </span></span><span class="Apple-style-span" style="font-size:small;"> description =>'Tuning task for an EMP to DEPT join query.');</span></span></div><p></p><p style="language:en-US;margin-top:6.6pt;margin-bottom:0pt;margin-left:.7in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"> DBMS_OUTPUT.put_line('l_sql_tune_task_id: '|| l_sql_tune_task_id);</span></span></p><span class="Apple-style-span" style="color: rgb(0, 0, 0); white-space: normal; font-family:Georgia;"><span class="Apple-style-span" style=" color: rgb(0, 102, 0); white-space: pre; font-family:-webkit-monospace;"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;">END</span></span><span style=" color: rgb(8, 8, 8); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;">;</span></span></span></span></span></pre></span></pre></div></span></div></div></div><div><span class="Apple-style-span" style="font-weight: bold; ">3. <span class="Apple-style-span" style="text-decoration: underline;">Execute a SQL Tuning Task</span></span></div><div> After the creation of the SQLtuning task, we can execute the SQL Tuning task </div><div>to be given to the SQL Tuning Advisor present in the Oracle database 10g,</div><div>to get the tuning recommendations.</div><div><div><div><span class="Apple-style-span" style="font-family:Georgia;"><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 681px; font-family:'Andale Mono', 'Lucida Console', Monaco, fixed, monospace;font-size:12px;"><span class="Apple-style-span" style="line-height: normal; white-space: normal; font-family:'times new roman';"><pre><span class="Apple-style-span" style="color: rgb(0, 102, 0); "><span class="Apple-style-span" style="font-size:medium;">//Executing a SQL Tuning task</span></span></pre><pre><span class="Apple-style-span" style="color:#006600;"><p style="margin-top: 6.6pt; margin-bottom: 0pt; margin-left: 0.7in; text-indent: -0.31in; text-align: left; direction: ltr; unicode-bidi: embed; vertical-align: baseline; "><span class="Apple-style-span" style="font-family:georgia;font-size:130%;color:#3333CC;"><span class="Apple-style-span" style="font-size:16px;"><b></b></span></span></p><span class="Apple-style-span" style="font-family:georgia;font-size:130%;color:#3333CC;"><b><p style="language:en-US;margin-top:9.6pt;margin-bottom:0pt;margin-left:.5in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-weight: normal;"><span class="Apple-style-span" style="font-size:small;">BEGIN</span></span></span></p><p style="language:en-US;margin-top:9.6pt;margin-bottom:0pt;margin-left:.5in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-weight: normal;"><span class="Apple-style-span" style="font-size:small;"> </span></span><span class="Apple-style-span" style="font-size:small;">DBMS_SQLTUNE.EXECUTE_TUNING_TASK</span><span class="Apple-style-span" style="font-weight: normal;"><span class="Apple-style-span" style="font-size:small;">( task_name =></span></span><span class="Apple-style-span" style="font-size:small;">'emp_dept_tuning_task</span><span class="Apple-style-span" style="font-weight: normal;"><span class="Apple-style-span" style="font-size:small;">');</span></span></span></p><p style="language:en-US;margin-top:9.6pt;margin-bottom:0pt;margin-left:.5in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-weight: normal;"><span class="Apple-style-span" style="font-size:small;">END;</span></span></span></p></b></span></span></pre></span></pre></div></span></div></div></div><div>Did you notice above that we call the 'EXECUTE_TUNING_TASK' function of the 'DBMS_SQLTUNE' package.</div><div><br /></div><div>You could see that we are giving some inputs to the 'EXEUTE_TUNING_TASK' function </div><div>which is the name of the task we created in the previous step.</div><div><br /></div><div>4. <b><span class="Apple-style-span" style="text-decoration: underline;">Generating the report</span></b></div><div><b> </b></div><div> We have executed the Tuning task </div><div> and what happened to my Tuning report. </div><div> Will it be text format or excel format or directlywritten to database.... </div><div><br /></div><div> <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://keenlg.bay.livefilestore.com/y1pIDOW6y4bGU2nUW-smvwlgyqdGACpAnHTAxyR6rgxjTLpNONooh2KAg-ZW_FTGYQZbUKSY4XOpqrnCtiWPMn8Cqojv4_JgsM8/Thinking_dog.bmp"><img src="https://keenlg.bay.livefilestore.com/y1pIDOW6y4bGU2nUW-smvwlgyqdGACpAnHTAxyR6rgxjTLpNONooh2KAg-ZW_FTGYQZbUKSY4XOpqrnCtiWPMn8Cqojv4_JgsM8/Thinking_dog.bmp" border="0" alt="" style="cursor: pointer; width: 92px; height: 115px; " /></a></div><div><br /></div><div>To check the status of the SQL Tuning task , we can use the following SQL statement.</div><div><div><div><div><span class="Apple-style-span" style="font-family:Georgia;"><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 681px; font-family:'Andale Mono', 'Lucida Console', Monaco, fixed, monospace;font-size:12px;"><span class="Apple-style-span" style="line-height: normal; white-space: normal; font-family:'times new roman';"><pre><span class="Apple-style-span" style="color: rgb(0, 102, 0); "><span class="Apple-style-span" style="font-size:medium;">//To check the status of the SQL Tuning task</span></span></pre><pre><span class="Apple-style-span" style="color:#006600;"><p style="margin-top: 6.6pt; margin-bottom: 0pt; margin-left: 0.7in; text-indent: -0.31in; text-align: left; direction: ltr; unicode-bidi: embed; vertical-align: baseline; "><span class="Apple-style-span" style="font-family:georgia;font-size:130%;color:#3333CC;"><span class="Apple-style-span" style="font-size:16px;"><b></b></span></span></p><span class="Apple-style-span" style="font-family:georgia;font-size:130%;color:#3333CC;"><b><p style="margin-top: 9.6pt; margin-bottom: 0pt; margin-left: 0.5in; text-indent: -0.31in; text-align: left; direction: ltr; unicode-bidi: embed; vertical-align: baseline; "><span class="Apple-style-span" style="font-family:Arial;font-size:100%;"><span class="Apple-style-span" style=" font-weight: normal;font-size:13px;"></span></span></p><span class="Apple-style-span" style="font-family:Arial;font-size:100%;"><p style="language:en-US;margin-top:9.6pt;margin-bottom:0pt;margin-left:.5in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;">SELECT task_name, status FROM dba_advisor_log WHERE owner = 'SCOTT';</span></span></p></span><p></p></b></span></span></pre></span></pre></div></span></div></div></div></div><div><br /></div><div>To have the report in text format . log in into SQL * plus and run the following statement</div><div><div><div><div><span class="Apple-style-span" style="font-family:Georgia;"><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 681px; font-family:'Andale Mono', 'Lucida Console', Monaco, fixed, monospace;font-size:12px;"><span class="Apple-style-span" style="line-height: normal; white-space: normal; font-family:'times new roman';"><pre><span class="Apple-style-span" style="color: rgb(0, 102, 0); "><span class="Apple-style-span" style="font-size:medium;">//To get the report for the SQL Tuning task</span></span></pre><pre><span class="Apple-style-span" style="color:#006600;"><p style="margin-top: 6.6pt; margin-bottom: 0pt; margin-left: 0.7in; text-indent: -0.31in; text-align: left; direction: ltr; unicode-bidi: embed; vertical-align: baseline; "><span class="Apple-style-span" style="font-family:georgia;font-size:130%;color:#3333CC;"><span class="Apple-style-span" style="font-size:16px;"><b></b></span></span></p><span class="Apple-style-span" style="font-family:georgia;font-size:130%;color:#3333CC;"><b><p style="margin-top: 9.6pt; margin-bottom: 0pt; margin-left: 0.5in; text-indent: -0.31in; text-align: left; direction: ltr; unicode-bidi: embed; vertical-align: baseline; "><span class="Apple-style-span" style="font-family:Arial;font-size:100%;"><span class="Apple-style-span" style=" font-weight: normal; font-size:13px;"></span></span></p><span class="Apple-style-span" style="font-family:Arial;font-size:100%;"><p style="margin-top: 9.6pt; margin-bottom: 0pt; margin-left: 0.5in; text-indent: -0.31in; text-align: left; direction: ltr; unicode-bidi: embed; vertical-align: baseline; "><span class="Apple-style-span" style="font-size:100%;"><span class="Apple-style-span" style="font-size:13px;"></span></span></p><span class="Apple-style-span" style="font-size:100%;"><p style="language:en-US;margin-top:9.6pt;margin-bottom:0pt;margin-left:.5in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"><span class="Apple-style-span" style="font-weight: normal;">SET LONG 1000 </span></span></span></p><p style="language:en-US;margin-top:9.6pt;margin-bottom:0pt;margin-left:.5in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"><span class="Apple-style-span" style="font-weight: normal;">SET LONGCHUNKSIZE 1000 </span></span></span></p><p style="language:en-US;margin-top:9.6pt;margin-bottom:0pt;margin-left:.5in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"><span class="Apple-style-span" style="font-weight: normal;">SET LINESIZE 100 </span></span></span></p><p style="language:en-US;margin-top:9.6pt;margin-bottom:0pt;margin-left:.5in; text-indent:-.31in;text-align:left;direction:ltr;unicode-bidi:embed;vertical-align: baseline;mso-line-break-override:restrictions;punctuation-wrap:simple"><span style=" color: rgb(51, 51, 204); font-family:Arial;"><span class="Apple-style-span" style="font-size:small;"><span class="Apple-style-span" style="font-weight: normal;">SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task') FROM DUAL;</span></span></span></p></span><p></p></span></b></span></span></pre></span></pre></div></span></div></div></div></div><div><br /></div><div><br /></div><div>We would be getting the report in the following format in the SQL * plus interface.</div><div><div><div><div><span class="Apple-style-span" style="font-family:Georgia;"><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 681px; font-family:'Andale Mono', 'Lucida Console', Monaco, fixed, monospace;font-size:12px;"><span class="Apple-style-span" style="line-height: normal; white-space: normal; font-family:'times new roman';"><pre><span class="Apple-style-span" style="color: rgb(0, 102, 0); "><span class="Apple-style-span" style="font-size:medium;">// report generated from SQL * plus <span class="Apple-style-span" style="color: rgb(0, 0, 0); white-space: normal; font-family:Georgia;font-size:16px;"><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://keenlg.bay.livefilestore.com/y1pI1o-JvHLxnIRm0H5CUzWd7Sy4qNpCWWmpwjxAgRT6EA-msqjL-6tfJRXWXTjey_lBMvhiptL93egWFKduacncaKHiFLYEI1o/REPORT.PNG"><img src="https://keenlg.bay.livefilestore.com/y1pI1o-JvHLxnIRm0H5CUzWd7Sy4qNpCWWmpwjxAgRT6EA-msqjL-6tfJRXWXTjey_lBMvhiptL93egWFKduacncaKHiFLYEI1o/REPORT.PNG" border="0" alt="" style="cursor: pointer; width: 80px; height: 81px; " /></a></span></span></span></pre><pre><span class="Apple-style-span" style="font-family:Georgia;"><span class="Apple-style-span" style=" white-space: normal;"><span class="Apple-style-span" style="font-size:x-small;"><span class="Apple-style-span" style="font-size:small;">RECOMMENDATIONS<br /><br />--------------------------------------------------------------------------------<br /><br />GENERAL INFORMATION SECTION<br /><br />-------------------------------------------------------------------------------<br /><br />Tuning Task Name : emp_dept_tuning_task<br /><br />Scope : COMPREHENSIVE<br /><br />Time Limit(seconds): 60<br /><br />Completion Status : COMPLETED<br /><br />Started at : 05/06/2004 09:29:13<br /><br />Completed at : 05/06/2004 09:29:15<br /><br /><br /><br />-------------------------------------------------------------------------------<br /><br />SQL ID : 0wrmfv2yvswx1<br /><br />SQL Text: SELECT e.*, d.* FROM emp e JOIN dept d ON e.deptno = d.deptno<br /><br />WHERE NVL(empno, '0') = :empno<br /><br /><br /><br />-------------------------------------------------------------------------------<br /><br />FINDINGS SECTION (2 findings)<br /><br />-------------------------------------------------------------------------------<br /><br /><br /><br /><br /><br /><br />1- Statistics Finding<br /><br />---------------------<br />Table "SCOTT"."EMP" and its indices were not analyzed.<br /><br /><br /><br />Recommendation<br /><br />--------------<br /><br />Consider collecting optimizer statistics for this table and its indices.<br /><br />execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =><br /><br />'EMP', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,<br /><br />method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE)<br /><br /><br /><br />Rationale<br /><br />---------<br /><br />The optimizer requires up-to-date statistics for the table and its indices<br /><br />in order to select a good execution plan.<br /><br /><br /><br />2- Restructure SQL finding (see plan 1 in explain plans section)<br /><br />----------------------------------------------------------------<br /><br />The predicate NVL("E"."EMPNO",0)=:B1 used at line ID 2 of the execution plan<br /><br />contains an expression on indexed column "EMPNO". This expression prevents<br /><br />the optimizer from selecting indices on table "SCOTT"."EMP".<br /><br /><br /><br />Recommendation<br /><br />--------------<br /><br />Rewrite the predicate into an equivalent form to take advantage of<br /><br />indices. Alternatively, create a function-based index on the expression.<br /><br /><br /><br />Rationale<br /><br />---------<br /><br />The optimizer is unable to use an index if the predicate is an inequality<br /><br />condition or if there is an expression or an implicit data type conversion<br /><br />on the indexed column.<br /><br /><br /><br />-------------------------------------------------------------------------------<br /><br />EXPLAIN PLANS SECTION<br /><br />-------------------------------------------------------------------------------<br /><br /><br /><br />1- Original<br /><br />-----------<br /><br />Plan hash value: 1863486531<br /><br /><br /><br />----------------------------------------------------------------------------------------<br /><br />| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |<br /><br />----------------------------------------------------------------------------------------<br /><br />| 0 | SELECT STATEMENT | | 1 | 107 | 4 (0)| 00:00:01 |<br /><br />| 1 | NESTED LOOPS | | 1 | 107 | 4 (0)| 00:00:01 |<br /><br />| 2 | TABLE ACCESS FULL | EMP | 1 | 87 | 3 (0)| 00:00:01 |<br /><br />| 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |<br /><br />| 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |<br /><br />----------------------------------------------------------------------------------------<br /><br />Note<br />-----<br />- dynamic sampling used for this statement<br />-------------------------------------------------------------------------------<br />1 row selected. </span></span></span></span></pre></span></pre></div></span></div></div></div></div><div><span class="Apple-style-span" style="font-size:small;">5. <b><span class="Apple-style-span" style="text-decoration: underline;">Implement the recommendations</span></b></span></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:small;">From the recommendations we found that the indexes for the tables were not used,</span></div><div><span class="Apple-style-span" style="font-size:small;">so it leads to the full scan of the table thus leading to the perfoemance degradation.</span></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:small;">So as per the tuning report create a function based index so the indexes are used, thus </span></div><div><span class="Apple-style-span" style="font-size:small;">reducing the time to fetch a row.</span></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:small;">So plung into action thus tuning your SQL statement.</span></div><div><br /></div><div><br /></div><div><div><div><span class="Apple-style-span" style="font-family:Georgia;"><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 681px; font-family:'Andale Mono', 'Lucida Console', Monaco, fixed, monospace;font-size:12px;"><span class="Apple-style-span" style="line-height: normal; white-space: normal; font-family:'times new roman';"><pre>For Complete Description about the Oracle Automatic SQL Tuning features</pre><pre>follow the link</pre><pre><span class="Apple-style-span" style="color:#006600;"><a href="http://www.oracle.com/technology/products/manageability/database/pdf/twp03/twp_manage_automatic_sql_tuning%2010gr2.pdf">Oracle Automatic SQL Tuning - Oracle Site</a></span></pre><pre><span class="Apple-style-span" style="color:#006600;"><a href="http://www.oracle.com/technology/obe/11gr1_db/manage/ast/ast.htm">Improving SQL statement usining Automatic SQL Tuning</a></span></pre><pre><span class="Apple-style-span" style="color:#006600;"><a href="http://www.oracle-base.com/articles/10g/AutomaticSQLTuning10g.php">Automatic SQL Tuning in Oracle database 10g - Oracle Base.com</a></span></pre><pre><span class="Apple-style-span" style=" ;font-size:large;"><b><span class="Apple-style-span" style="text-decoration: underline;">Note</span></b></span></pre><pre><span class="Apple-style-span"><b><span class="Apple-style-span" style="color:#FF0000;"><span class="Apple-style-span" style="font-size:large;">Kindly post your comments for any Typo errors or any ambiguities.</span></span></b></span></pre><pre><b><span class="Apple-style-span" style="color:#FF0000;"><span class="Apple-style-span" style="font-size:large;">Thanks in Advance</span></span></b></pre></span></pre></div></span></div></div></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com1tag:blogger.com,1999:blog-3320668921204955011.post-25472958469665094312009-06-30T08:32:00.000-07:002009-08-16T05:35:09.926-07:00Oracle SQL query execution hiearchy<div style="text-align: right;"><span class="Apple-tab-span" style="white-space:pre"> </span> <a href="http://gskaushik.blogspot.com/2009/06/oracle-sql-query-execution-hiearchy.html#comments"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8S3xfR6YxPFFZXmHJObg_itEM6OqGK2tKiJsHJwLbA6N37BUgHx87gt-XuSbtxT6-RfYh6v6H_F-DAJ8lA26zfRUrJdyQhnE-X0VwBsx_rhaqIW-rqe_ja5aTzQoTGRX3N6ifsdA9ejz8/s800/commentGreyMedium.png" /></a></div>When I was working yesterday, I found the execution hiearchy of execution of SQL statements<div><br /></div><div><code></code></div><code><div><span class="Apple-style-span" style="color:#3333FF;"><b>SELECT</b></span><b> </b>name,deptno </div><div><span class="Apple-style-span" style="color:#3333FF;"><b>FROM</b></span><b> </b>employee</div><div><span class="Apple-style-span" style="color:#3333FF;"><b>WHERE</b></span><b> </b>ID <span class="Apple-style-span" style="color:#3333FF;"><b>NOT </b><b>EXISTS</b></span>(<span class="Apple-style-span" style="color:#3333FF;"><b>SELECT </b></span>ID <span class="Apple-style-span" style="color:#3333FF;"><b>FROM </b></span>Bank_Customers)</div><div><span class="Apple-style-span" style="color:#3333FF;"><b>GROUP BY</b></span> Deptno;</div></code><div></div><div><br /></div><div><b><span class="Apple-style-span" style="text-decoration: underline;">Sequence of Execution</span></b></div><div>1. <span class="Apple-style-span" style="color:#000099;"><b><span class="Apple-style-span" style="color:#3333FF;">FROM</span> </b></span>Clause </div><div>2. <span class="Apple-style-span" style="color:#000099;"><b><span class="Apple-style-span" style="color:#3333FF;">WHERE</span> </b></span>Condition</div><div>3. <span class="Apple-style-span" style="color:#3333FF;"><b>GROUP </b></span>Clause</div><div>4. <span class="Apple-style-span" style="color:#3333FF;"><b>HAVING </b></span>Clause (<span class="Apple-style-span" style="font-size:small;">You DBA advises to filter data in WHERE clause I Suppose</span>)</div><div>5. <b><span class="Apple-style-span" style="color:#3333FF;">SELECT</span></b></div><div>6. <b><span class="Apple-style-span" style="color:#3333FF;">ORDER</span></b> Clause</div><div><br /></div><div><ul><li><span class="Apple-style-span" style="color: rgb(0, 0, 153); font-weight: bold; "><span class="Apple-style-span" style="color:#3333FF;">FROM</span> - </span>It fetch all the Data from the respective tables</li><li><span class="Apple-style-span" style="color: rgb(0, 0, 153); font-weight: bold; "><span class="Apple-style-span" style="color:#3333FF;">WHERE</span> - </span>It applies the data filter using the WHERE clause</li><li><span class="Apple-style-span" style="color: rgb(51, 51, 255); font-weight: bold; ">GROUP - </span>The Group clause is called to Group Data</li><li><span class="Apple-style-span" style="color: rgb(51, 51, 255); font-weight: bold; ">HAVING - </span>The Having clause applies data filter to the Grouped Data,When you apply the filter at this stage the performance decreases, So try to filter the maximum at the WHERE clause level itself</li><li><span class="Apple-style-span" style="color: rgb(51, 51, 255); font-weight: bold; ">SELECT - </span>The Select Clause selects the required columns (Thus Choosing one column or two Column does not have much impact on Database performance.But you should not apply '*' to retrieve all the rows as the huge amount of data leads to Network traffic and the performance of your application becomes slow.</li><li><b><span class="Apple-style-span" style="color:#3333FF;">ORDER</span></b> - Then the ORDER lause sorts the data and then gives back to the user to be shown in the application. </li></ul></div><div><br /></div><div style="text-align: center;"><span class="Apple-tab-span" style="white-space: pre; "> </span> <a href="http://gskaushik.blogspot.com/2009/06/oracle-sql-query-execution-hiearchy.html#comments"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8S3xfR6YxPFFZXmHJObg_itEM6OqGK2tKiJsHJwLbA6N37BUgHx87gt-XuSbtxT6-RfYh6v6H_F-DAJ8lA26zfRUrJdyQhnE-X0VwBsx_rhaqIW-rqe_ja5aTzQoTGRX3N6ifsdA9ejz8/s800/commentGreyMedium.png" /></a></div><div style="text-align: center;"><br /></div><div>*************************************************************************************</div><div><br /></div><div><span class="Apple-style-span" style="color:#FF0000;"><b>Kindly correct me if Iam wrong by leaving a comment or please drop me a mail to Subramanian.kaushik@hotmail.com</b></span></div><div><br /></div><div><br /></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com0tag:blogger.com,1999:blog-3320668921204955011.post-33201352196355448562009-05-23T00:47:00.000-07:002009-05-23T08:39:32.565-07:00Oracle Terminal Reference Forms 6i<div style="text-align: right;"><a href="http://gskaushik.blogspot.com/2009/05/need-to-upgrade-oracle-applications.html#comments"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8S3xfR6YxPFFZXmHJObg_itEM6OqGK2tKiJsHJwLbA6N37BUgHx87gt-XuSbtxT6-RfYh6v6H_F-DAJ8lA26zfRUrJdyQhnE-X0VwBsx_rhaqIW-rqe_ja5aTzQoTGRX3N6ifsdA9ejz8/s800/commentGreyMedium.png" /></a><br /></div><span class="Apple-style-span" style=" ;font-family:'times new roman';"><p><span class="Apple-style-span" style="font-size:medium;">I found that there was no documentation available properly about using the </span></p><p><span class="Apple-style-span" style="font-size:medium;">Oracle terminal tool for Oracle forms 6i.</span></p><p><br /></p><p><span class="Apple-style-span" style="font-size:medium;">I have summarized the posts present in different web sites and forums together .<br /></span></p><p>for using Oracle terminal.</p><p><span class="Apple-style-span" style="font-size:medium;"><br /></span></p><p><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size:medium;">Using the User Defined Key 0-9 </span></span></p><p><span class="Apple-style-span" style="font-size:medium;">In order to create a key binding for a key trigger, perform these actions:</span></p><ul><li><span class="Apple-style-span" style="font-size:medium;">In Form Builder, create your function key trigger.</span></li><li><span class="Apple-style-span" style="font-size:medium;">Using Oracle Terminal, open the appropriate Form Builder resource file (for MS Windows open FMRUSW.RES --> Normally present under the folder Oracle-Forms-Home\Forms60 folder).</span></li><li><span class="Apple-style-span" style="font-size:medium;">Choose Functions/Edit Key to invoke the Key Binding Editor.</span></li><li><span class="Apple-style-span" style="font-size:medium;">In the Key Binding Editor, double-click on windows-sqlforms to invoke Key Binding definition window.</span></li><li><span class="Apple-style-span" style="font-size:medium;">Select the Insert Row button and then enter the action that corresponds to your function key trigger. An example of the chart for MS Windows follows:<br /> – Key-Fn Trigger - KEY-F3<br /> – Microsoft Action - User defined Key 3<br /> – Microsoft Code - 85</span></li><li><span class="Apple-style-span" style="font-size:medium;">Specify the key binding for your trigger, then select OK.</span></li><li><span class="Apple-style-span" style="font-size:medium;">Select the Product Actions Editor.</span></li><li><span class="Apple-style-span" style="font-size:medium;">Double-click on the sqlforms category.</span></li><li><span class="Apple-style-span" style="font-size:medium;">Select the Insert Row button, enter the Action, Code and Description for your trigger, then select OK.</span></li><li><span class="Apple-style-span" style="font-size:medium;">Dismiss the Product Action and Key Binding Editors that invokes a help system by selecting OK for each, and then choose File/Save.</span></li><li><span class="Apple-style-span" style="font-size:medium;">Choose Functions/Generate to generate your modifications and create a new resource file that incorporates your changes. Save the new resource file.</span><span class="Apple-style-span" style=" ;font-family:Georgia;"><span class="Apple-style-span" style="font-size:medium;"> </span></span></li></ul><div><span class="Apple-style-span" style=" ;font-family:Georgia;"><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 100%; font-family:'Andale Mono', 'Lucida Console', Monaco, fixed, monospace;font-size:12px;"><span class="Apple-style-span" style=" line-height: normal; white-space: normal; font-family:'times new roman';"><pre><span class="Apple-style-span" style="color: rgb(0, 102, 0); "><span class="Apple-style-span" style="font-size:medium;">//To create a KEY-F3 trigger, enter into Action Field in Key Bind </span></span></pre><pre><span class="Apple-style-span" style="color: rgb(0, 102, 0); "><span class="Apple-style-span" style="font-size:medium;">//Definition window User Defined Key F3 </span></span></pre><pre><span class="Apple-style-span" style="color: rgb(0, 102, 0); "><span class="Apple-style-span" style="font-size:medium;">//Specify a key binding for your trigger, and then select OK </span></span></pre><pre><span class="Apple-style-span" style="color: rgb(51, 51, 255); "><span class="Apple-style-span" style="font-size:medium;"> Action Binding User Defined Key F3 Control+F2 </span></span></pre><pre><span class="Apple-style-span" style="color: rgb(0, 102, 0); "><span class="Apple-style-span" style="font-size:medium;">//Enter the following to Microsoft Windows Action Code </span></span></pre><pre><span class="Apple-style-span" style="color: rgb(51, 51, 255); "><span class="Apple-style-span" style="font-size:medium;"> Description User Defined Key F3 85 [Trigger description] </span></span></pre></span></pre></div><div><span class="Apple-style-span" style=" font-weight: bold; font-family:'times new roman';"><span class="Apple-style-span" style="font-size:medium;">Use Accelerators For Commonly Used Functions (Menu Items)</span></span><span class="Apple-style-span" style="font-size:medium;"><br /></span></div></span></div><p><span class="Apple-style-span" style="font-size:medium;">Accelerators are keyboard shortcuts for frequently performed actions (for example, Ctrl+P for print, Esc for escape or cancel). When possible, it is a good (but not mandatory) practice to provide keyboard accelerators. A good rule to use is to always set a keyboard accelerator for menu items that appear visually on a tool bar. Keyboard shortcuts allow users to bypass opening the menu by using a specific combination of keystrokes that perform the same function as a corresponding menu item.</span></p><p><span class="Apple-style-span" style="font-size:medium;">Developer/2000 provides for five logical accelerator keys, [Accelerator 1] through [Accelerator 5] that are set up with Oracle Terminal. To assign a key, find the menu item in the Object Navigator and display its property sheet. In the Property Palette, under the Functional node, type the name of the logical accelerator key you want to assign to the item in the Keyboard Accelerator property field. For example, enter Accelerator 1.</span></p><p><span class="Apple-style-span" style="font-size:medium;">In the Oracle Terminal, associate this logical accelerator key, for example, to a key sequence such as Ctrl+F to open the File menu item. Instead of pressing Alt+F, then S, to activate menu item File-Save, a user can just press Ctrl+S to execute the same function. Now the user is able to activate a menu item without going through the menu.</span></p><p><span class="Apple-style-span" style="font-size:medium;"><img src="http://www.section508.gov/IRSCourse/mod02/images/020407a-.jpg" alt="This graphic is a screen capture of the Oracle Forms Builder Property Palette. This screen capture displays a list of Oracle Form Properties along with their editable associated property fields. The middle of the screen displays the Keyboard Accelerator property, with Accelerator 1 typed in the column to the right, which is the Keyboard Accelerator property field. The Keyboard Accelerator property has 5 attributes associated with it. The attributes are visible in menu, visible in horizontal menu toolbar, visible in vertical menu toolbar, icon in menu, and icon filename. In the property field, yes has been selected for the visible in menu, and no is selected for the other attributes." /></span></p><p><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size:medium;">Use Access Keys to Select or Execute an Item (Mainly for Buttons or Textitems)</span></span><span class="Apple-style-span" style="font-size:medium;"><br />This technique applies to the buttons, radio buttons, menus and check boxes. It specifies the character that will be used as an access key, allowing the user to select or execute an item by pressing a key combination, such as Alt+C. In a Label Property on Property Palette place the name of the menu item with an ampersand after the first letter: for example “A&ction”. The access key is displayed with an underscore in the item label. Buttons with the iconic property set to “Yes” cannot have access keys.</span></p><p><span class="Apple-style-span" style="font-size:medium;">Push buttons, checkboxes, tabs, menu items and radio buttons should have an access key unless:</span></p><ul><li><span class="Apple-style-span" style="font-size:medium;">They are keyboard-navigable (an access key is still desirable in this case).</span></li><li><span class="Apple-style-span" style="font-size:medium;">An excessive number exist such that deriving a unique letter would be difficult (in which case the ones with no access key must be navigable).</span></li><li><span class="Apple-style-span" style="font-size:medium;">They are not absolutely critical to the functionality of the product.</span></li><li><span class="Apple-style-span" style="font-size:medium;">For Checkboxes and Radio Buttons: if they are part of a multi-row block and use the Prompt, not the Label attribute, they cannot render an access key.</span></li></ul><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size:medium;">List of Valid Action Codes which you can use in Oracle Terminal </span></span></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="border-collapse: collapse; font-weight: normal; line-height: 17px; white-space: pre-wrap; -webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family:Tahoma;"><span class="Apple-style-span" style="font-size:medium;">Here is a list of the numeric "action codes" that correspond to various product actions that are available in Oracle Forms. This list might not be complete nor current in respect to the latest version of Oracle Forms available, but it should be useful as a reference. </span></span></span></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="border-collapse: collapse; font-weight: normal; line-height: 17px; white-space: pre-wrap; -webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family:Tahoma;"><code><span class="Apple-style-span" style="font-size:medium;"> </span></code><span class="Apple-style-span" style="font-size:medium;"><code></code></span></span><span class="Apple-style-span" style="font-size:medium;"><code></code></span></span><span class="Apple-style-span" style="font-size:medium;"><code></code></span></div><code><div><span class="Apple-style-span" style="border-collapse: collapse; line-height: 17px; white-space: pre-wrap; -webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px;font-family:Tahoma;"><span class="Apple-style-span" style="font-size:medium;"><br /></span></span></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="border-collapse: collapse; font-weight: normal; line-height: 17px; white-space: pre-wrap; -webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family:Tahoma;"><span class="Apple-style-span" style="font-size:small;"><span class="Apple-style-span" style="border-collapse: separate; line-height: normal; white-space: normal; -webkit-border-horizontal-spacing: 0px; -webkit-border-vertical-spacing: 0px; font-family:Georgia;font-size:16px;"><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 100%; font-family:'Andale Mono', 'Lucida Console', Monaco, fixed, monospace;font-size:12px;"><span class="Apple-style-span" style="border-collapse: collapse; line-height: 17px; white-space: pre-wrap; -webkit-border-horizontal-spacing: 2px; -webkit-border-vertical-spacing: 2px; font-family:Tahoma;"><span class="Apple-style-span" style="font-size:medium;">1 Next Field <br />2 Previous Field <br />3 Clear Field <br />4 Left Arrow <br />5 Right Arrow <br />6 Up Arrow <br />7 Down Arrow <br />8 Goto Beginning Of Line <br />9 Goto End Of Line <br />10 Goto Extreme Vertical Up <br />11 Goto Extreme Vertical Down <br />12 Scroll Up <br />13 Scroll Down <br />14 Scroll Left <br />15 Scroll Right <br />16 Delete Character To Left <br />17 Delete Char On/Right <br />18 Delete Line <br />18 Terminate Input On Form <br />19 Move Cursor Left <br />20 Move Cursor Right <br />21 Scroll-Up <br />22 Invoke Editor For Field <br />23 Delete Character <br />23 Insert Line <br />24 Search <br />25 Toggle Insert/Replace Mode <br />26 Select Item <br />27 Return <br />28 Invoke Menu <br />29 List Of Values <br />30 Invoke Context-Sensitive Help <br />31 Redraw Screen <br />35 Show Keys <br />36 Accept <br />61 Next Key <br />62 Clear Record <br />63 Delete Record <br />64 Duplicate Record <br />65 Insert Record <br />66 Next Set <br />67 Next Record <br />68 Previous Record <br />69 Clear Block <br />70 Ask Block <br />71 Next Block <br />72 Previous Block <br />73 Duplicate Field <br />74 Clear Form <br />75 Enter <br />76 Enter Query <br />77 Execute Query <br />78 Error <br />79 Print <br />80 Clear Query <br />81 Update Record <br />82 User Defined Key 0 <br />83 User Defined Key 1 <br />84 User Defined Key 2 <br />85 User Defined Key 3 <br />86 User Defined Key 4 <br />87 User Defined Key 5 <br />88 User Defined Key 6 <br />89 User Defined Key 7 <br />90 User Defined Key 8 <br />91 User Defined Key 9 <br />92 Clear Eol <br />11004 Return To Application Menu <br />11005 Previous Menu <br />11006 Return To Main Menu <br />11007 Enter > 1 Os Commands <br />11008 Enter 1 Os Command <br />11009 Show Background Menu <br />11010 Background Menu Option 1 <br />11011 Background Menu Option 2 <br />11012 Background Menu Option 3 <br />11013 Background Menu Option 4 <br />11014 Background Menu Option 5 <br />11015 Background Menu Option 6 <br />11016 Background Menu Option 7 <br />11017 Background Menu Option 8 <br />11018 Background Menu Option 9 <br />11019 Background Menu Option 10 <br />11020 Re-Enter Application Parameters <br />11021 Re-Enter Menu Parameters <br />11022 Accelerator Key 1 <br />11023 Accelerator Key 2 <br />11024 Accelerator Key 3 <br />11025 Accelerator Key 4 <br />11026 Accelerator Key 5 <br />11027 Accelerator Key 6 <br />11028 Accelerator Key 7 <br />11029 Accelerator Key 8 <br />11030 Accelerator Key 9 <br />11031 Accelerator Key 10 </span></span><span class="Apple-style-span" style="font-size:medium;"><br /></span></pre></div><div><span class="Apple-style-span" style="font-size:medium;"> Kindly revert for any clarifications</span></div></span></span></span></span></div></code></span><code></code><br /><div style="text-align: center;"><a href="http://gskaushik.blogspot.com/2009/05/need-to-upgrade-oracle-applications.html#comments"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg8S3xfR6YxPFFZXmHJObg_itEM6OqGK2tKiJsHJwLbA6N37BUgHx87gt-XuSbtxT6-RfYh6v6H_F-DAJ8lA26zfRUrJdyQhnE-X0VwBsx_rhaqIW-rqe_ja5aTzQoTGRX3N6ifsdA9ejz8/s800/commentGreyMedium.png" /></a><br /></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com2tag:blogger.com,1999:blog-3320668921204955011.post-14643267000640953672009-05-19T20:54:00.000-07:002009-05-19T21:22:10.787-07:00Need to Upgrade Oracle applicationsI found a interesting article from Binarysematics which clearly explains what<div>are the <span class="Apple-style-span" style="color: rgb(0, 0, 153);"><span class="Apple-style-span" style="font-weight: bold;">advantages in migrating Oracle Forms application</span></span> and</div><div>Why the people are not doing so?</div><div><br /></div><div>Kindly check the following link</div><div><br /></div><div><span class="Apple-style-span" style="text-decoration: underline;"><a href="http://www.binarysemantics.com/itservices/pdf/WHY%20UPGRADE%20ORACLE%20FORMS%20APPLICATIONS.pdf" style="text-decoration: none;">Upgrading Oracle Forms Application</a></span><span class="Apple-tab-span" style="white-space: pre; "><a href="http://www.binarysemantics.com/itservices/pdf/WHY%20UPGRADE%20ORACLE%20FORMS%20APPLICATIONS.pdf" style="text-decoration: none;"> </a></span><a href="http://www.binarysemantics.com/itservices/pdf/WHY%20UPGRADE%20ORACLE%20FORMS%20APPLICATIONS.pdf"></a></div><div><span class="Apple-style-span" style="color: rgb(85, 26, 139); text-decoration: underline;"><br /></span></div><div><span class="Apple-style-span" style="color: rgb(85, 26, 139); text-decoration: underline;"><br /></span></div><div> </div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com0tag:blogger.com,1999:blog-3320668921204955011.post-80739074332453865292009-05-16T22:35:00.000-07:002009-05-17T05:00:32.465-07:00Calling Java procedures from ORACLE PL/SQL<div><span class="Apple-style-span" style="font-weight: bold;">Why we need to call JAVA procedures in PL/SQL?</span> <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_XqyclMMc8JBplIw6NFc0FSr-Hp1yMEonqAiTvE_1ZVSQZTF5nZBcQojZKWGYVVTQhg13FvfkRKqpnFXRaVCr7OwVKPcXUzvGQq5i-g7AB7eTNyFTEQMcdfhoUR29k4_dI-5K2nHMCT02/s1600-h/juggler_bean.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_XqyclMMc8JBplIw6NFc0FSr-Hp1yMEonqAiTvE_1ZVSQZTF5nZBcQojZKWGYVVTQhg13FvfkRKqpnFXRaVCr7OwVKPcXUzvGQq5i-g7AB7eTNyFTEQMcdfhoUR29k4_dI-5K2nHMCT02/s200/juggler_bean.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5336666466221062258" style="cursor: pointer; width: 47px; height: 47px; " /></a></div><div><br /></div>JAVA one of the oldest and type safe language ever seen.It was first introduced into the IT world when C++ was not able to call remote procedures.<div><br /></div><div>Almost there are thousands and thousands of logic from Hello world to complex logic fo aircrafts written in JAVA.So it is important to reuse the logic than rewriting from scratch in PL/SQL.</div><div><br /></div><div>I have summarized the steps below to call a JAVA method from ORACLE <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh31Tz4WEtAC4lRM97iZfEOo6aU9UhvVVERDLJjG194yGXEcEYNIITRxW2-oJuyF_8Sox2S56ijSizfXjjedwZpoSrVdVQacFHGPQzIzPSG05zuV4nscBJwFXmLVWZN8BEebzgqmjRdm0iY/s1600-h/oracle.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh31Tz4WEtAC4lRM97iZfEOo6aU9UhvVVERDLJjG194yGXEcEYNIITRxW2-oJuyF_8Sox2S56ijSizfXjjedwZpoSrVdVQacFHGPQzIzPSG05zuV4nscBJwFXmLVWZN8BEebzgqmjRdm0iY/s200/oracle.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5336667438317020306" style="cursor: pointer; width: 50px; height: 50px; " /></a></div><div><br /></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="text-decoration: underline;">Note</span></span></div><div><span class="Apple-style-span" style="font-weight: bold; "> Only STATIC methods can only be called from PL/SQL as it as Modular language</span></div><div><br /></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="text-decoration: underline;">Steps</span></span></div><div>1. Create a JAVA class to be loaded into Oracle database.</div><div><br /></div><div> Iam using our very old hello world program.Instead of printing <span class="Apple-style-span" style="font-weight: bold;">Hello World</span> to console, it would return the value to the user.</div><div><br /><pre style="border: 1px dashed rgb(153, 153, 153); padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%;"><div>public class Hello </div><div> { </div><div> public static String world() </div><div> { </div><div> return "Hello world"; </div><div> } </div><div> }<br /></div></pre><br /></div><div><br /></div><div>2. Compile the JAVA file using JAVAC</div><div><span class="Apple-style-span" style="font-weight: bold;"> <span class="Apple-style-span" style="text-decoration: underline;">Note</span></span></div><div> Kindly add <span class="Apple-style-span" style="font-weight: bold;">ORACLE_HOME/JDK/BIN</span> in your <span class="Apple-style-span" style="font-weight: bold;">PATH </span>variable</div><pre face="'Andale Mono', 'Lucida Console', Monaco, fixed, monospace" size="12px" style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 100%; "><div>Microsoft Windows XP [Version 5.1.2600] </div><div>(C) Copyright 1985-2001 Microsoft Corp. </div><div>C:\Documents and Settings\Administrator>javac H:\JDEV\Hello.java <span class="Apple-style-span" style=" line-height: normal; white-space: normal; font-family:Georgia;font-size:16px;"> </span></div></pre><div> </div><div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgF9iYfwpReYltC6z3fiVfqO-P8QeVCkVLagnmlIQrU2frzGOYP9FrtJJZN9FJtdRKe5YbsmdlRyROP0nBWO1LgBTEn3_RACpeHGLz-D1o2ABWSl2ZMYw_OcZIBeUFhHOz9SWKNIdIctMCt/s1600-h/Compiling+JAVA+Classes.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgF9iYfwpReYltC6z3fiVfqO-P8QeVCkVLagnmlIQrU2frzGOYP9FrtJJZN9FJtdRKe5YbsmdlRyROP0nBWO1LgBTEn3_RACpeHGLz-D1o2ABWSl2ZMYw_OcZIBeUFhHOz9SWKNIdIctMCt/s400/Compiling+JAVA+Classes.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5336692153672890322" style="cursor: pointer; width: 400px; height: 102px; " /></a><br /></div><div><br /></div><div>3. Load the JAVA files into Oracle database </div><div><span class="Apple-style-span" style="font-weight: bold;">Note</span></div><div><span class="Apple-style-span" style="font-weight: bold;"> </span> You should have added the path of ORACLE_HOME\JDK in the classpath or</div><div>it would show the driver not found error is throw<span class="Apple-style-span" style="">n</span></div><div><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; font-family: 'Andale Mono', 'Lucida Console', Monaco, fixed, monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%; "><div>C:\Documents and Settings\Administrator>loadjava -user store/store@shanthaguru H:\jdev\Hello.class </div><div>C:\Documents and Settings\Administrator> </div></pre><br /></div></div><div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYfNXgYXMiuK39YainB7rPQa2awNnIv4654s-H13WutnjbcFUp3bAcZadLQWVJ5Eb0lKFt0oQCHxqfLibAShMfNLBC34iTn8c_b_7rSkE8MVURhjBxnAkQ8V8TIoYG9MD3lxumnvqHs64T/s1600-h/loading+java.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjYfNXgYXMiuK39YainB7rPQa2awNnIv4654s-H13WutnjbcFUp3bAcZadLQWVJ5Eb0lKFt0oQCHxqfLibAShMfNLBC34iTn8c_b_7rSkE8MVURhjBxnAkQ8V8TIoYG9MD3lxumnvqHs64T/s400/loading+java.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5336685602678925762" style="cursor: pointer; width: 400px; height: 80px; " /></a><br /></div><div><br /></div><div><span class="Apple-style-span" style="font-weight: bold;">Note</span></div><div><span class="Apple-style-span" style="font-weight: bold;"> After loading it into database , you can verify it using SQL*plus</span></div><div><span class="Apple-style-span" style="font-weight: bold;"> or SQL Developer</span></div><div><span class="Apple-style-span" style="font-weight: bold;"> </span></div><div><span class="Apple-style-span" style="font-weight: bold;">SQL * PLUS</span></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style=" font-weight: normal; line-height: 14px; white-space: pre; font-family:'Andale Mono';font-size:12px;"><div><span class="Apple-style-span" style=" line-height: normal; white-space: normal; font-family:Georgia;font-size:16px;"><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; font-family: 'Andale Mono', 'Lucida Console', Monaco, fixed, monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%; "><div>SQL*Plus: Release 10.1.0.2.0 - Production on Sun May 17 13:07:14 2009 </div><div>Copyright (c) 1982, 2004, Oracle. All rights reserved. </div><div>Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - </div><div>Production With the Partitioning, OLAP and Data Mining options </div><div>SQL> select object_name from user_objects where object_type = 'JAVA CLASS'; </div><div>OBJECT_NAME </div><div>-------------------------------------------------------------------------------- </div><div>Hello </div><div><br /></div></pre></span></div></span></span></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-weight: normal; "><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3z_Au58rIIzt_WW-g5Q6i088oZoJMNCZ18P9DrlBj1R9Np6DTRg0lj7eHPJZ2cMZlbTk27n-sY1ra8RQbjGgULOTnllswj2sU2LZOeoqsA5Ij-Y5QciMTmwS4smK4KWwYzoXsEF-FvWTK/s1600-h/obj_type_query.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3z_Au58rIIzt_WW-g5Q6i088oZoJMNCZ18P9DrlBj1R9Np6DTRg0lj7eHPJZ2cMZlbTk27n-sY1ra8RQbjGgULOTnllswj2sU2LZOeoqsA5Ij-Y5QciMTmwS4smK4KWwYzoXsEF-FvWTK/s400/obj_type_query.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5336694289889878738" style="cursor: pointer; width: 400px; height: 181px; " /></a></span><br /></span></div><div><span class="Apple-style-span" style="font-weight: bold;"><br /></span></div><div><span class="Apple-style-span" style="font-weight: bold;">SQL DEVELOPER</span></div><div><span class="Apple-style-span" style="font-weight: bold;"> </span>Open the JAVA node under the corresponding schema and see the loaded</div><div>JAVA Classes</div><div><br /></div><div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVjBUrWJM1WRp1laUfdR19bYU5HtqyA2L32ruOqKV1NKTtOmbatQsw2BLKYFSST9uoO_oZRrFTPgXpS-58DeEqQrOg445IGLB0nRsPOjNKWOTduPGsWLX0M17Nbe_iY5eME083qP25CaQK/s1600-h/SQL_DEVELOPER.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVjBUrWJM1WRp1laUfdR19bYU5HtqyA2L32ruOqKV1NKTtOmbatQsw2BLKYFSST9uoO_oZRrFTPgXpS-58DeEqQrOg445IGLB0nRsPOjNKWOTduPGsWLX0M17Nbe_iY5eME083qP25CaQK/s400/SQL_DEVELOPER.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5336695008487960626" style="cursor: pointer; width: 282px; height: 363px; " /></a><br /></div><div> </div><div>4. Creating a PL/SQL procedure for calling the loaded the JAVA method </div><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; font-family: 'Andale Mono', 'Lucida Console', Monaco, fixed, monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%; "><div>SQL> CREATE OR REPLACE FUNCTION helloworld RETURN VARCHAR2 AS </div><div>2 LANGUAGE JAVA NAME 'Hello.world () return java.lang.String'; </div><div>3 / Function created.<br /></div><div><br /></div></pre></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-weight: normal; "><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRuhBANVNpkP4930zWJF16Pu22yW_AOmgytDVDNRcPAalEzexvvp4DI0Ychi4u_ioH4cP5WyeQDUgWgLeLY73Imrx2yTgqaQ87OPMr7pESh1dTH9kCK5Hx4hf0IBqCAefptSwCAuOJYERe/s1600-h/creating_of_func.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgRuhBANVNpkP4930zWJF16Pu22yW_AOmgytDVDNRcPAalEzexvvp4DI0Ychi4u_ioH4cP5WyeQDUgWgLeLY73Imrx2yTgqaQ87OPMr7pESh1dTH9kCK5Hx4hf0IBqCAefptSwCAuOJYERe/s400/creating_of_func.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5336696288736164514" style="cursor: pointer; width: 400px; height: 207px; " /></a></span><br /></span></div><div><span class="Apple-style-span" style="font-weight: bold;"><br /></span></div><div><span class="Apple-style-span" style="font-weight: bold;"><br /></span></div><div><span class="Apple-style-span" style="font-weight: bold;">Note</span></div><div><span class="Apple-style-span" style="font-weight: bold;"> The return type should be JAVA specfic , note this in the above code.</span></div><div><span class="Apple-style-span" style="font-weight: bold;"><br /></span></div><div><span class="Apple-style-span" style=""><br /></span></div><div><span class="Apple-style-span" style="">Now try call the PL/SQL function to call the JAVA method</span></div><div><br /></div><div><div><pre style="border-top-width: 1px; border-right-width: 1px; border-bottom-width: 1px; border-left-width: 1px; border-top-style: dashed; border-right-style: dashed; border-bottom-style: dashed; border-left-style: dashed; border-top-color: rgb(153, 153, 153); border-right-color: rgb(153, 153, 153); border-bottom-color: rgb(153, 153, 153); border-left-color: rgb(153, 153, 153); padding-top: 5px; padding-right: 5px; padding-bottom: 5px; padding-left: 5px; overflow-x: auto; overflow-y: auto; font-family: 'Andale Mono', 'Lucida Console', Monaco, fixed, monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%; "><div>SQL> select helloworld from dual; </div><div>HELLOWORLD </div><div>-------------------------------------------------------------------- </div><div>Hello world</div></pre></div><div><span class="Apple-style-span" style="font-weight: bold;"><br /></span></div></div><div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjS5WmWu3vm3abOFM4P1sSGOitsxctQ_oadBTb50cV5K-l4A4LcaX8M9Ng1uWI7DczfFcr3dHHHXBuGxkyvjeSXZD3wxFfnWJxiemqTlbVvHtoR-t-z-BFQb68cpOkJNAOT1ap0BI_NHUJz/s1600-h/calling_the_java_method.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjS5WmWu3vm3abOFM4P1sSGOitsxctQ_oadBTb50cV5K-l4A4LcaX8M9Ng1uWI7DczfFcr3dHHHXBuGxkyvjeSXZD3wxFfnWJxiemqTlbVvHtoR-t-z-BFQb68cpOkJNAOT1ap0BI_NHUJz/s400/calling_the_java_method.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5336696993406960242" style="cursor: pointer; width: 400px; height: 66px; " /> </a><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgW23WHEHOGg0jDAVxGKnJe9yzvzcEgKVDP22g2Iv7MCiGXpoG9tM1A97-fg1oDr5K0-VdSYk2CuEaan2IyjVcveyPNbNBO-LXGLN65qxrAUs-eYnKO2lXmYZv4VB2AtsQRIx62gAXFuSsz/s1600-h/thinking+dog.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgW23WHEHOGg0jDAVxGKnJe9yzvzcEgKVDP22g2Iv7MCiGXpoG9tM1A97-fg1oDr5K0-VdSYk2CuEaan2IyjVcveyPNbNBO-LXGLN65qxrAUs-eYnKO2lXmYZv4VB2AtsQRIx62gAXFuSsz/s200/thinking+dog.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5336698354062540338" style="cursor: pointer; width: 81px; height: 112px; " /></a></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com2tag:blogger.com,1999:blog-3320668921204955011.post-42359813035684615712009-05-14T12:29:00.000-07:002009-05-14T13:56:34.409-07:00Oracle and SunHi,<div> Atlast Oracle and Sun merges and it would be a great break through.</div><div>Kindly check the Following link for more details</div><div><br /></div><div><a href="http://www.oracle.com/us/corporate/press/018363">*http://www.oracle.com/us/corporate/press/018363*</a><br /></div><div><br /></div><div><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJheR5S-BYolVTovA09j482hK4JL2IfcRlVYk2S1VVjwOw4FrRN8IoxQLZRTv30tKDFPyo3XpokDS9hqXEesqsE7LowrkPqa54qQkZ3hl6Nbus6zECaCM7V1wefppWS-qcIsJgtpaUZtaq/s1600-h/Oracle_buys_sun.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjJheR5S-BYolVTovA09j482hK4JL2IfcRlVYk2S1VVjwOw4FrRN8IoxQLZRTv30tKDFPyo3XpokDS9hqXEesqsE7LowrkPqa54qQkZ3hl6Nbus6zECaCM7V1wefppWS-qcIsJgtpaUZtaq/s400/Oracle_buys_sun.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5335786408931707874" style="cursor: pointer; width: 400px; height: 265px; " /></a><br /></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com0tag:blogger.com,1999:blog-3320668921204955011.post-34440639688268748052009-05-12T20:31:00.000-07:002009-05-12T20:37:20.845-07:00Oracle Forms vs .NETHi,<div><br /><div>There would some dilemmas when selecting the IDE and development tool when </div><div>constructing your windows or web application.</div><div><br /></div><div>I think this is an intresting thread which has hot conversation in favour of both Oracle forms and</div><div>.NET. </div><div><br /></div><div>Kindly click the following link</div><div><span class="Apple-style-span" style="color: rgb(85, 26, 139); text-decoration: underline;"><br /></span></div><div><a href="http://groups.google.com/group/comp.databases.oracle.misc/browse_frm/thread/58064ba7e2db2746?dq=&lr=&prev=/groups%3Fq%3Dcomp.databases.oracle.misc&ie=UTF-8&oe=UTF-8&hl=en">Oracle Forms VS .NET</a> <span class="Apple-style-span" style="font-weight: bold;"> <-----</span></div><div> </div></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com0tag:blogger.com,1999:blog-3320668921204955011.post-57721665250556902292009-05-07T14:35:00.003-07:002009-05-16T13:43:17.936-07:00Oracle Forms connection to MSACCESS<span class="Apple-style-span" style="font-size:small;"><br /></span><span class="Apple-style-span" style=" line-height: 22px; font-family:'Trebuchet MS';"><div><span class="Apple-style-span" style="font-size:small;">Hi,</span></div><div><span class="Apple-style-span" style="font-size:small;"> In This Post we would Look into how to Connect Oracle Forms to MSACESS and combining the feature of Oracle forms and MSACCESS.</span></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size:small;"><br /></span></span></div><div><span class="Apple-style-span" style="font-weight: bold; "><span class="Apple-style-span" style="font-size:small;">Steps</span></span><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><span class="Apple-style-span" style="font-weight: bold; "><span class="Apple-style-span" style="font-size:small;">1. Create a MDB file with a username and password</span></span></div><div><span class="Apple-style-span" style="font-size:small;"> I think everyone knbows to open access and create an MDB file.</span></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><span class="Apple-style-span" style="font-weight: bold; "><span class="Apple-style-span" style="font-size:small;">2. Run the following scripts</span></span></div><div><span class="Apple-style-span" style="font-size:small;"> \Forms_home\OCA60\SQL\ACCESS --> *ACCDMBLD.SQL* (Used to create EMP and DEPT table)</span></div><div><span class="Apple-style-span" style="font-size:small;"> \Forms_home\OCA60\SQL --> *DEMODATA.SQL* (Insert demo data into the tables)</span></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><span class="Apple-style-span" style="font-weight: bold; "><span class="Apple-style-span" style="font-size:small;">3. Create a ODBC data source in the data sources management tab in windows</span></span><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:small;"> 1. Open the Data source managment tab</span></div><div><span class="Apple-style-span" style="font-size:small;"> Control Panel --> Administrative tools ---> Data Sources (ODBC) </span></div><div><span class="Apple-style-span" style="font-size:small;"> </span><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvekLw1_TCmatyJnp35nd5eIAi9gBX3HrXEhMbflWBdtbQtIaFAmKC9IWkS9ZPOpS86ZE9QWxkwD6PPzUPxnUmocYl2b3gOcIs0TSU9RMum6Ch6ugfvnlptruZixmTHx8BRqYEjYy0c4JC/s1600-h/data_source_screen.bmp" style="text-decoration: none; color: rgb(0, 0, 255); "><span class="Apple-style-span" style="font-size:small;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvekLw1_TCmatyJnp35nd5eIAi9gBX3HrXEhMbflWBdtbQtIaFAmKC9IWkS9ZPOpS86ZE9QWxkwD6PPzUPxnUmocYl2b3gOcIs0TSU9RMum6Ch6ugfvnlptruZixmTHx8BRqYEjYy0c4JC/s320/data_source_screen.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333178139289327538" style="cursor: pointer; width: 320px; height: 266px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; " /></span></a></div><div><span class="Apple-style-span" style="font-size:small;"> </span></div><div><span class="Apple-style-span" style="font-size:small;"> 2. Click the Add button,*Create new datasource* screen opens</span></div><div><span class="Apple-style-span" style="font-size:small;"> </span></div><div><span class="Apple-style-span" style="font-size:small;"> </span><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga7G0uQ7OzSoPoSsaUNQDrgUzwxNhZgNftoGZseMn_MLQ0LZFrmlfvjKTKd92R3BJKuxZrpl_-KnI7flQxhOShM1RfAMVoWgdzgK1ZJ-QrH1WffQi1bm1_JZDjhOwlr3P3eYGv6aSWsdVv/s1600-h/Create+_new_ds.bmp" style="text-decoration: none; color: rgb(0, 0, 255); "><span class="Apple-style-span" style="font-size:small;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga7G0uQ7OzSoPoSsaUNQDrgUzwxNhZgNftoGZseMn_MLQ0LZFrmlfvjKTKd92R3BJKuxZrpl_-KnI7flQxhOShM1RfAMVoWgdzgK1ZJ-QrH1WffQi1bm1_JZDjhOwlr3P3eYGv6aSWsdVv/s320/Create+_new_ds.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333178923880573666" style="cursor: pointer; width: 320px; height: 241px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; " /></span></a></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:small;"> 3. Select Microsoft Access Driver(*.mdb) and click Finish.</span></div><div><span class="Apple-style-span" style="font-size:small;"> </span></div><div><span class="Apple-style-span" style="font-size:small;"> 4. ODBC Microsoft access screen opens, give a name for the data source</span></div><div><span class="Apple-style-span" style="font-size:small;"> and click the OK button</span></div><div><span class="Apple-style-span" style="font-size:small;"> </span></div><div><span class="Apple-style-span" style="font-size:small;"> </span><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCOT9aMUpbmDUBlpErqfU4b9bX7hGHJhPckUI0hyqbBVtRd_qwXCdgly0nixfONfxaCmxAGkmoUp_akHcoS0vypSahu_owyuJM9_A0ZW7esIi96kdnhQX-ZCXlkuRHmu_Hdpx-nv2uGeSF/s1600-h/ODBC.bmp" style="text-decoration: none; color: rgb(0, 0, 255); "><span class="Apple-style-span" style="font-size:small;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCOT9aMUpbmDUBlpErqfU4b9bX7hGHJhPckUI0hyqbBVtRd_qwXCdgly0nixfONfxaCmxAGkmoUp_akHcoS0vypSahu_owyuJM9_A0ZW7esIi96kdnhQX-ZCXlkuRHmu_Hdpx-nv2uGeSF/s320/ODBC.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333179912974815762" style="cursor: pointer; width: 320px; height: 217px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; " /></span></a></div><div><span class="Apple-style-span" style="font-size:small;"> </span></div><div><span class="Apple-style-span" style="font-size:small;"> 5. Click the selet button and select the corresponding MDB file</span></div><div><span class="Apple-style-span" style="font-size:small;"> and click the OK button</span></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:small;"> </span><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTaE9j9tL8JtcLXrOYFsktOX0Dzr4Xr4YG1A3hduVU_E0Z4b4khZuDXctXtHWMOYLOlrnMDOBs3KtPDwDY4UaPf_Vs5suBfxEen16xWbGDT16J2Tud_CHk1lGEF-saNVtnOQj3vdSX_26t/s1600-h/SELECT_DATABASE.bmp" style="text-decoration: none; color: rgb(0, 0, 255); "><span class="Apple-style-span" style="font-size:small;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTaE9j9tL8JtcLXrOYFsktOX0Dzr4Xr4YG1A3hduVU_E0Z4b4khZuDXctXtHWMOYLOlrnMDOBs3KtPDwDY4UaPf_Vs5suBfxEen16xWbGDT16J2Tud_CHk1lGEF-saNVtnOQj3vdSX_26t/s320/SELECT_DATABASE.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333185073606957138" style="cursor: pointer; width: 320px; height: 199px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; " /></span></a></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:small;"> 6. The datasource would have been created and check the initial screen</span></div><div><span class="Apple-style-span" style="font-size:small;"> </span></div><div><span class="Apple-style-span" style="font-size:small;"> </span><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE6Nz_e76IaDzAqwj0SMb34hHJ96mH2MTnVnKOZZsV7xdq1axRih3_-kfPuRiTxNlchtTR4ey-BrSXdf5aSCmGMyaI9PSO1xPEiBJBy1zJjJjcgATMZDl86OXzw8XnJDIVR9hf6OvDBRaP/s1600-h/DB_CONNEC_CREATED.bmp" style="text-decoration: none; color: rgb(0, 0, 255); "><span class="Apple-style-span" style="font-size:small;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE6Nz_e76IaDzAqwj0SMb34hHJ96mH2MTnVnKOZZsV7xdq1axRih3_-kfPuRiTxNlchtTR4ey-BrSXdf5aSCmGMyaI9PSO1xPEiBJBy1zJjJjcgATMZDl86OXzw8XnJDIVR9hf6OvDBRaP/s320/DB_CONNEC_CREATED.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333181044773952018" style="cursor: pointer; width: 320px; height: 266px; border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; " /></span></a></div><div><div><span class="Apple-style-span" style="font-size:small;"><span class="Apple-style-span" style="font-weight: bold;">4. Open the Forms Builder and Connect to MSACCESS </span></span></div><div><span class="Apple-style-span" style="font-size:small;"> In connect field type </span><span class="Apple-style-span" style="color: rgb(0, 0, 153);"><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size:small;">username</span></span></span><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size:small;">/</span></span><span class="Apple-style-span" style="color: rgb(0, 51, 0);"><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size:small;">password</span></span></span><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size:small;">@ODBC:</span></span><span class="Apple-style-span" style="color: rgb(255, 0, 0);"><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-size:small;">DATA_SOURCE_NAME</span></span></span></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div><div><span class="Apple-style-span" style="font-size:small;"><span class="Apple-style-span" style="font-weight: bold;">5. Create a Database Block</span> using the database lock wizard and set the primary property of the EMPNO column to 'Yes' before running the form.You would be able to do all the insert, update, Delete , Query Operation from that form.</span></div><div><span class="Apple-style-span" style="font-size:13px;"><br /></span></div><div><span class="Apple-style-span" style="font-size:13px;"> <span class="Apple-style-span" style=" line-height: normal; font-family:Georgia;font-size:16px;"><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhweu9ZfRjRD1gotmLMMb2TguyAN3Lien_AD4fPbfRL7qPtiXGxUi5N1_VoA-HjTBTuB4W1PgQxgMhqDRHmGE6UWm0uZscce_oocCMrXKAarynBCgaR_cSIi6WZVjc9WE1C3t7MK7TQ8llN/s1600-h/LIST_OF_EMPLOYEES.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhweu9ZfRjRD1gotmLMMb2TguyAN3Lien_AD4fPbfRL7qPtiXGxUi5N1_VoA-HjTBTuB4W1PgQxgMhqDRHmGE6UWm0uZscce_oocCMrXKAarynBCgaR_cSIi6WZVjc9WE1C3t7MK7TQ8llN/s400/LIST_OF_EMPLOYEES.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333201583886530498" style="cursor: pointer; width: 400px; height: 317px; " /></a></span></span></div><div><span class="Apple-style-span" style="font-size:small;"><br /></span></div></div></span><span class="Apple-style-span" style=" ;font-family:'Times New Roman';"><div style="border-top-width: 0px; border-right-width: 0px; border-bottom-width: 0px; border-left-width: 0px; border-style: initial; border-color: initial; margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; padding-top: 3px; padding-right: 3px; padding-bottom: 3px; padding-left: 3px; width: auto; font: normal normal normal 100%/normal Georgia, serif; text-align: left; "><span class="Apple-style-span" style="line-height: 22px; font-family:'Trebuchet MS';font-size:13px;"><div class="post-body entry-content"><div><div><span class="Apple-style-span" style=" "><span class="Apple-style-span" style="font-size:small;"><span class="Apple-style-span" style="font-family:arial;"></span></span></span></div></div></div></span></div></span>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com13tag:blogger.com,1999:blog-3320668921204955011.post-75849650934306636202009-05-07T12:58:00.000-07:002009-05-07T13:55:05.572-07:00Connecting Oracle to MSACCESSHi,<div><br /><div>This post summarizes the most important and intresting feature of Oracle which I have tried.</div><div><br /></div><div>Normally for data entry operators, we would enter the data for internal application in small database like MSACCESS,.... </div><div><br /></div><div>but the query feature of Oracle is easy, so if you want to maintain your datasource as MSACCESS and if you want to do any queries using Oracle SQL, this helps you.</div><div><br /></div><div><span class="Apple-style-span" style="font-weight: bold;">Steps</span><br /></div><div><span class="Apple-style-span" style="font-weight: bold;">1. Create a MDB file with a username and password</span></div><div> I think everyone knbows to open access and create an MDB file.</div><div><br /></div><div><span class="Apple-style-span" style="font-weight: bold;">2. Run the following scripts</span></div><div> <span class="Apple-style-span" style="font-size: small;">\Forms_home\OCA60\SQL\ACCESS --> *ACCDMBLD.SQL* (Used to create EMP and DEPT table)</span></div><div><span class="Apple-style-span" style="font-size: small;"> \Forms_home\OCA60\SQL --> *DEMODATA.SQL* (Insert demo data into the tables)</span></div><div><span class="Apple-style-span" style="font-size: 13px;"><br /></span></div><div><span class="Apple-style-span" style="font-weight: bold;">3. Create a ODBC data source in the data sources management tab in windows</span><br /></div><div> 1. Open the Data source managment tab</div><div> Control Panel --> Administrative tools ---> Data Sources (ODBC) </div><div> <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvekLw1_TCmatyJnp35nd5eIAi9gBX3HrXEhMbflWBdtbQtIaFAmKC9IWkS9ZPOpS86ZE9QWxkwD6PPzUPxnUmocYl2b3gOcIs0TSU9RMum6Ch6ugfvnlptruZixmTHx8BRqYEjYy0c4JC/s1600-h/data_source_screen.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvekLw1_TCmatyJnp35nd5eIAi9gBX3HrXEhMbflWBdtbQtIaFAmKC9IWkS9ZPOpS86ZE9QWxkwD6PPzUPxnUmocYl2b3gOcIs0TSU9RMum6Ch6ugfvnlptruZixmTHx8BRqYEjYy0c4JC/s320/data_source_screen.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333178139289327538" style="cursor: pointer; width: 320px; height: 266px; " /></a></div><div> </div><div> 2. Click the Add button,*Create new datasource* screen opens</div><div> </div><div> <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga7G0uQ7OzSoPoSsaUNQDrgUzwxNhZgNftoGZseMn_MLQ0LZFrmlfvjKTKd92R3BJKuxZrpl_-KnI7flQxhOShM1RfAMVoWgdzgK1ZJ-QrH1WffQi1bm1_JZDjhOwlr3P3eYGv6aSWsdVv/s1600-h/Create+_new_ds.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEga7G0uQ7OzSoPoSsaUNQDrgUzwxNhZgNftoGZseMn_MLQ0LZFrmlfvjKTKd92R3BJKuxZrpl_-KnI7flQxhOShM1RfAMVoWgdzgK1ZJ-QrH1WffQi1bm1_JZDjhOwlr3P3eYGv6aSWsdVv/s320/Create+_new_ds.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333178923880573666" style="cursor: pointer; width: 320px; height: 241px; " /></a></div><div><br /></div><div> 3. Select Microsoft Access Driver(*.mdb) and click Finish.</div><div> </div><div> 4. ODBC Microsoft access screen opens, give a name for the data source</div><div> and click the OK button</div><div> </div><div> <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCOT9aMUpbmDUBlpErqfU4b9bX7hGHJhPckUI0hyqbBVtRd_qwXCdgly0nixfONfxaCmxAGkmoUp_akHcoS0vypSahu_owyuJM9_A0ZW7esIi96kdnhQX-ZCXlkuRHmu_Hdpx-nv2uGeSF/s1600-h/ODBC.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCOT9aMUpbmDUBlpErqfU4b9bX7hGHJhPckUI0hyqbBVtRd_qwXCdgly0nixfONfxaCmxAGkmoUp_akHcoS0vypSahu_owyuJM9_A0ZW7esIi96kdnhQX-ZCXlkuRHmu_Hdpx-nv2uGeSF/s320/ODBC.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333179912974815762" style="cursor: pointer; width: 320px; height: 217px; " /></a></div><div> </div><div> 5. Click the selet button and select the corresponding MDB file</div><div> and click the OK button</div><div><br /></div><div> <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTaE9j9tL8JtcLXrOYFsktOX0Dzr4Xr4YG1A3hduVU_E0Z4b4khZuDXctXtHWMOYLOlrnMDOBs3KtPDwDY4UaPf_Vs5suBfxEen16xWbGDT16J2Tud_CHk1lGEF-saNVtnOQj3vdSX_26t/s1600-h/SELECT_DATABASE.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiTaE9j9tL8JtcLXrOYFsktOX0Dzr4Xr4YG1A3hduVU_E0Z4b4khZuDXctXtHWMOYLOlrnMDOBs3KtPDwDY4UaPf_Vs5suBfxEen16xWbGDT16J2Tud_CHk1lGEF-saNVtnOQj3vdSX_26t/s320/SELECT_DATABASE.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333185073606957138" style="cursor: pointer; width: 320px; height: 199px; " /></a></div><div><br /></div><div> 6. The datasource would have been created and check the initial screen</div><div> </div><div> <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE6Nz_e76IaDzAqwj0SMb34hHJ96mH2MTnVnKOZZsV7xdq1axRih3_-kfPuRiTxNlchtTR4ey-BrSXdf5aSCmGMyaI9PSO1xPEiBJBy1zJjJjcgATMZDl86OXzw8XnJDIVR9hf6OvDBRaP/s1600-h/DB_CONNEC_CREATED.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiE6Nz_e76IaDzAqwj0SMb34hHJ96mH2MTnVnKOZZsV7xdq1axRih3_-kfPuRiTxNlchtTR4ey-BrSXdf5aSCmGMyaI9PSO1xPEiBJBy1zJjJjcgATMZDl86OXzw8XnJDIVR9hf6OvDBRaP/s320/DB_CONNEC_CREATED.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333181044773952018" style="cursor: pointer; width: 320px; height: 266px; " /></a></div><div> </div><div><span class="Apple-style-span" style="font-weight: bold;"><br /></span></div><div><span class="Apple-style-span" style="font-weight: bold;">4. Open the SQLPLUS.exe under \Forms_HOME\BIN</span></div><div> </div><div> <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVhlhLXiF4WOlhBt9d2-D21hMCPJM0a92QByKP8ff2GgS-CJz0Flyc9Jx1hndL52Ur5_jON7my4narYuTh-AMRRwWBngMqzApOtucl4gt35Nv0_Uh_JJ8RG_8P2GrOGESIH60ihk1ih2Lo/s1600-h/OPENING_SQLPLUS.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVhlhLXiF4WOlhBt9d2-D21hMCPJM0a92QByKP8ff2GgS-CJz0Flyc9Jx1hndL52Ur5_jON7my4narYuTh-AMRRwWBngMqzApOtucl4gt35Nv0_Uh_JJ8RG_8P2GrOGESIH60ihk1ih2Lo/s320/OPENING_SQLPLUS.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333181934515354370" style="cursor: pointer; width: 320px; height: 162px; " /></a></div><div><span class="Apple-style-span" style="font-weight: bold;"><br /></span></div><div><span class="Apple-style-span" style="font-weight: bold;">5. Connect with the username and password as follows</span></div><div><span class="Apple-style-span" style="font-weight: bold;"> </span><span class="Apple-style-span" style="color: rgb(51, 51, 255);"><span class="Apple-style-span" style="font-weight: bold;">username</span></span><span class="Apple-style-span" style="font-weight: bold;">/</span><span class="Apple-style-span" style="color: rgb(0, 51, 0);"><span class="Apple-style-span" style="font-weight: bold;">password</span></span><span class="Apple-style-span" style="font-weight: bold;">@ODBC:</span><span class="Apple-style-span" style="color: rgb(255, 0, 0);"><span class="Apple-style-span" style="font-weight: bold;">DATA_SOURCE_NAME</span></span></div><div><span class="Apple-style-span" style="color: rgb(255, 0, 0);"> </span></div><div><span class="Apple-style-span" style="color: rgb(255, 0, 0);"> <span class="Apple-style-span" style="color: rgb(0, 0, 0); "><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7lop0py6_JNCPcCkWk4Ne2cOpXWX0pu_8gOo2JK1QWHo8q1Qu-HjdiqVmtW-onH-7S_qBzxiEzAa8wfAV_BbK9xGaQ0czZWZYNqMbXqFalnEYsmH7sLxNzM4Aw5HLpYRW3Fi0OloWPeyF/s1600-h/ENTERING_USER_PASS.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh7lop0py6_JNCPcCkWk4Ne2cOpXWX0pu_8gOo2JK1QWHo8q1Qu-HjdiqVmtW-onH-7S_qBzxiEzAa8wfAV_BbK9xGaQ0czZWZYNqMbXqFalnEYsmH7sLxNzM4Aw5HLpYRW3Fi0OloWPeyF/s400/ENTERING_USER_PASS.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333183719471570946" style="cursor: pointer; width: 400px; height: 82px; " /></a></span></span></div><div> </div><div> </div><div><span class="Apple-style-span" style="font-weight: bold;">6. Select the data from tables in MSACCESS as we do it in Oracle.</span></div><div> </div><div> <span class="Apple-style-span" style="color: rgb(0, 0, 153);">Select </span>* from <span class="Apple-style-span" style="color: rgb(255, 0, 0);">EMP</span>;</div><div><br /></div><div> <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVCBam9uGCES4pRrBsjOUpTkOY5ZGbpBORK46u68laL33kyF-8-cEDldYbOfPlkgUI6ZAYGmm_HIgfDkejzYWEhW3kQg0uOBYtuO5YGBAf_ZYI7SPs-JPPZJLWVeECjFSqw0gjLuVBPeNM/s1600-h/RESULT.bmp"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjVCBam9uGCES4pRrBsjOUpTkOY5ZGbpBORK46u68laL33kyF-8-cEDldYbOfPlkgUI6ZAYGmm_HIgfDkejzYWEhW3kQg0uOBYtuO5YGBAf_ZYI7SPs-JPPZJLWVeECjFSqw0gjLuVBPeNM/s400/RESULT.bmp" border="0" alt="" id="BLOGGER_PHOTO_ID_5333187167814026962" style="cursor: pointer; width: 400px; height: 202px; " /></a></div><div> </div><div> </div><div> </div><div> </div><div><br /></div><div> </div><div><br /></div><div> </div></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com4tag:blogger.com,1999:blog-3320668921204955011.post-82145708732879715052009-05-05T21:00:00.001-07:002009-05-05T22:11:10.995-07:00Reading and Writing CLOB objectsHi,<div> In this post , we can look into how to read and write CLOB objects.</div><div><br /></div><div>Why we need to use CLOB objects?</div><div> The only downside of using VARCHAR2 is that it is limited to 4000 characters, </div><div>so if we are creating large queries or if we you store resume or text files in database, then</div><div>we can use this CLOb, where we can store upto 2GB .</div><div><br /></div><div>Steps</div><div>1. Creating a table containing a CLOB column</div><div> <pre style="border: 1px dashed rgb(153, 153, 153); padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%;">SQL> create table lob_contain<br />2 (id number,<br />3 lob_ptr clob default empty_clob());<br />Table created.<br /></pre></div><div> </div><div>2. Create a procedcure to write a text to the CLOB column</div><div><br /></div><div><br /><pre style="border: 1px dashed rgb(153, 153, 153); padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%;"><br />Declare<br /><br />lob_locator CLOB;<br /><br />text varchar2(200) := 'Resume NAme - G.Subramanian KAushik City - Chennai<br />Location - triplicane Occupation - Software engineer Hobbies - Reading astrology<br />books,nemerology, Playing video games';<br /><br />amount number;<br /><br />offset integer;<br /><br />Begin<br /><br />-- Setting the amount to be written<br />amount := length(text);<br /><br />-- Lock the record in the table lob_contain to get a expicit lock over the record<br />Select lob_ptr into lob_locator from lob_contain where id = 1 FOR UPDATE;<br /><br />--Setting the Offset<br />offset := DBMS_LOB.GETLENGTH(lob_locator)+2;<br /><br />--Write the text to the CLOB table<br />DBMS_LOB.WRITE(lob_locator,amount,offset,text);<br />COMMIT;<br />END; <br /> </pre><div></div></div>3. Select the inserted value as a VARCHAR2 text.<div> <code><br /><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"><br /></span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> SQL> select * from lob_contain;</span><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"><br /></span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> ID LOB_PTR</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);">------- ---------------------------------------------------------</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> 1 Resume NAme - G.Subramanian KAushik City - Chennai </span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> Location - triplicane</span></div><br /> </code></div></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com2tag:blogger.com,1999:blog-3320668921204955011.post-28176287429641033702009-05-01T20:14:00.000-07:002009-05-01T20:17:39.568-07:00Comparison of different SQL implementationsHi,<div> A complete description of the different implementation of the SQL standards, have been clearly explained in the following link.</div><div><br /></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>This would really helps the developers, if they are migrating the application from one database to another.<br /></div><div><br /></div><div><span class="Apple-style-span" style="font-family: Arial; font-size: 13px; white-space: pre; "><a href="http://troels.arvin.dk/db/rdbms/">Comparison of different SQL implementations</a></span><br /></div><div><span class="Apple-style-span" style="font-family: Arial; font-size: 13px; white-space: pre;"><br /></span></div><div><span class="Apple-style-span" style="font-family: Arial; font-size: 13px; white-space: pre;"><br /></span></div><div><br /></div><div><br /></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com0tag:blogger.com,1999:blog-3320668921204955011.post-45981011986284910712009-05-01T19:13:00.000-07:002009-05-01T19:29:47.123-07:00Generating unique identifiersHi,<span class="Apple-tab-span" style="white-space:pre"></span><div> In web applications it is required to use unique id in certain columns in the tables, so that it does not getsrepeated. It is possible to generate unique identifiers in PL/SQL using a built in named SYS_GUID.<span class="Apple-tab-span" style="white-space:pre"></span></div><div><br /><span class="Apple-tab-span" style="white-space:pre"></span></div><div>This builtin generates a 32 chracter length identifier, which can be used as a primary key in a column.</div><div><br /></div><br /><div><span class="Apple-tab-span" style="white-space:pre"> </span></div><br /><pre style="border: 1px dashed rgb(153, 153, 153); padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%;"><br />SQL> begin<br /> 2 dbms_output.put_line('sys_guid');<br /> 3 dbms_output.put_line('--------');<br /> 4 dbms_output.put_line(sys_guid);<br /> 5 end;<br /> 6 /<br />sys_guid<br />--------<br />459C2423AA744A618EACC336A8808284<br /><br />PL/SQL procedure successfully completed<br /></pre>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com0tag:blogger.com,1999:blog-3320668921204955011.post-1272513356229463122009-04-29T21:03:00.000-07:002009-04-30T14:52:29.429-07:00Oracle Web Services<span class="Apple-tab-span" style="white-space: pre;font-family:arial;" ><span class="Apple-style-span" style="font-weight: bold;"> </span></span><span class="Apple-style-span" style="font-weight: bold;font-family:arial;font-size:180%;" ><span class="Apple-style-span" style="text-decoration: underline;">How to call web services in Oracle?<br /></span></span><div style="font-family:arial;"><span class="Apple-style-span" style="font-weight: bold; text-decoration: underline;"><br /></span></div><div style="font-family:arial;"><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-tab-span" style="white-space: pre;"><span class="Apple-style-span" style="font-weight: normal;"> </span></span><span class="Apple-style-span" style="font-weight: normal;">In this Post we would be looking into a basic example for a calling a web service from the database</span> </span></div><div style="font-family: arial;"><br /></div><div style="font-family: arial;"><div>Oracle provides a set of packages to access the web services directly through PL/SQL.</div><div><br /></div><div><span class="Apple-style-span" style="font-weight: bold;"><a href="http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/u_http.htm"><span class="Apple-style-span" style="color: rgb(0, 0, 0);">UTL_HTTP</span></a></span> is a standard package given by Oracle to consume a web service.</div><div><br /></div><div>From Oracle database we can directly call the web services using PL/SQL.</div><div><br /></div><div>Steps</div><div style="font-weight: bold;">1. View the SOAP envelope of the web service to be consumed.</div><div> We have a lot of free web services available over the internet, I have chosen</div><div> a free to web service to validate a given emailid</div><div> </div><div> <a href="http://www.webservicex.net/ValidateEmail.asmx?op=IsValidEmail">Validate email id</a></div><div><br /></div><div> Click the above link to test the web service and to get the full details of the web services.</div><div><br /></div><div style="font-weight: bold;">2. How the input needs to be send to a web service?</div><div> All the communications over the internet is done through XML. So we have to create a XML fragment.</div><div><span class="Apple-tab-span" style="white-space: pre;"> </span><soap:envelope xsi="http://www.w3.org/2001/XMLSchema-instance"><span class="Apple-tab-span" style="white-space: pre;"> </span>xmlns:xsd="http://www.w3.org/2001/XMLSchema" <span class="Apple-tab-span" style="white-space: pre;"> </span>xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> </soap:envelope></div><div> <span class="Apple-style-span" style="color: rgb(255, 0, 0);"><soap:body> </soap:body></span></div><div><span class="Apple-style-span" style="color: rgb(255, 0, 0);"> { All the </span><span class="Apple-tab-span" style="white-space: pre;"><span class="Apple-style-span" style="color: rgb(255, 0, 0);"> </span></span><span class="Apple-style-span" style="color: rgb(255, 0, 0);">request details are sent as an XML imput <parameter 1="">email <span class="Apple-tab-span" style="white-space: pre;"> </span> address </parameter></span></div><div><span class="Apple-style-span" style="color: rgb(255, 0, 0);"> } </span><span class="Apple-tab-span" style="white-space: pre;"><span class="Apple-style-span" style="color: rgb(255, 0, 0);"> </span></span></div><div><span class="Apple-style-span" style="color: rgb(0, 0, 153);"><span class="Apple-style-span" style="color: rgb(255, 0, 0);"> </span> </span></div><div><br /></div><div> </div><div> <span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="text-decoration: underline;">Note</span></span></div><div> To have a complete description of what is SOAP and why it needs to be used kindly click <span class="Apple-tab-span" style="white-space: pre;"> </span> the following link </div><div> <a href="http://www.w3schools.com/soap/default.asp" style="text-decoration: none;"><span class="Apple-style-span" style="text-decoration: underline;">SOAP</span><span class="Apple-tab-span" style="white-space: pre;"> </span></a><img src="http://www.1060research.com/images/xml.png" /></div><div><span style="font-weight: bold;">3. Create a PL/SQL procedure to create the input to be given in the web service.</span><br /></div><div> <pre face="Andale Mono,Lucida Console,Monaco,fixed,monospace" size="12px" style="border: 1px dashed rgb(153, 153, 153); padding: 5px; overflow: auto; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); line-height: 14px; width: 100%;">CREATE OR REPLACE<br />FUNCTION F_VALIDATE_EMAIL<br />(<br />p_email_id VARCHAR2)<br />RETURN VARCHAR2<br />AS<br />input_envelope VARCHAR2(32767);<br />http_req utl_http.req;<br />http_resp utl_http.resp;<br />resp sys.xmltype;<br />in_xml sys.xmltype;<br />WSDL_URL VARCHAR2(2000):='http://www.webservicex.net/ValidateEmail.asmx?WSDL';<br />res VARCHAR2(1000);<br />BEGIN<br />-- Generating the input to be sent to the web service<br />input_envelope := '<?xml version="1.0" encoding="utf-8"?> <br /><soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <br /><soap:Body> <br /><IsValidEmail xmlns="http://www.webservicex.net"> <br /><Email>'||p_email_id||'</Email> <br /></IsValidEmail> <br /></soap:Body> <br /></soap:Envelope>';<br />--Sending the input enevlope to the web service<br />http_req := utl_http.begin_request(WSDL_URL, 'POST','HTTP/1.1');<br />utl_http.set_header(http_req, 'Content-Type', 'text/xml');<br />utl_http.set_header(http_req, 'Content-Length', LENGTH(input_envelope));<br />utl_http.set_header(http_req, 'SOAPAction', 'http://www.webservicex.net/IsValidEmail');<br />utl_http.write_text(http_req, input_envelope);<br />http_resp := utl_http.get_response(http_req);<br />utl_http.read_text(http_resp, input_envelope);<br />res := REPLACE(input_envelope,'<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">','');<br />res := REPLACE(res,'<soap:Body><IsValidEmailResponse xmlns="http://www.webservicex.net"><IsValidEmailResult>','');<br />res := REPLACE(res,'</IsValidEmailResult></IsValidEmailResponse></soap:Body></soap:Envelope>','');<br />dbms_output.put_line(SUBSTR(res,1,250));<br />utl_http.end_response(http_resp);<br />RETURN res;<br />END;<br /></pre><br /><br />Now let us dissect the code<br />1. This portion of the code is used for creating the input to the web service<br /><pre style="border: 1px dashed rgb(153, 153, 153); padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%;"><br />-- Generating the input to be sent to the web service<br />input_envelope := '<?xml version="1.0" encoding="utf-8"?> <br /><soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <br /><soap:Body> <br /><IsValidEmail xmlns="http://www.webservicex.net"> <br /><Email>'||<span style="color: rgb(51, 51, 255); font-weight: bold;">p_email_id</span>||'</Email> --<span style="font-weight: bold;"><span style="color: rgb(51, 51, 255);">p_email_id</span> is sent as a parameter </span> <br /></IsValidEmail> <br /></soap:Body> <br /></pre><br /><br />The format for the SOAP envelope would be aldready given in the service description of the web service.You need to replace only the parameter to be passed.<br /><br />2. Setting all the configurations before calling the web service<br /><pre style="border: 1px dashed rgb(153, 153, 153); padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%;"><br />http_req := utl_http.begin_request(WSDL_URL, 'POST','HTTP/1.1'); -- <span style="font-weight: bold; color: rgb(51, 51, 255);">Starts the request</span><br />utl_http.set_header(http_req, 'Content-Type', 'text/xml'); --Setting the content type<br />utl_http.set_header(http_req, 'Content-Length', LENGTH(input_envelope)); --<span style="color: rgb(51, 51, 255); font-weight: bold;">Setting the length</span><br />utl_http.set_header(http_req, 'SOAPAction', 'http://www.webservicex.net/IsValidEmail'); --<span style="font-weight: bold; color: rgb(51, 51, 255);">Setting the function to be called</span><br /></pre><br />3. Calling the web service and retrieving the value<br /><pre style="border: 1px dashed rgb(153, 153, 153); padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%;"><br /> utl_http.set_header(http_req, 'SOAPAction', 'http://www.webservicex.net/IsValidEmail');<br />utl_http.write_text(http_req, input_envelope);<br />http_resp := utl_http.get_response(http_req); --Getting the response for the given SOAP enevelope<br />utl_http.read_text(http_resp, input_envelope); -- Getting response in a variable<br /></pre><br />Note<br />As the repsone given by the web services is a XML fragment, use a XMLTYPE variable to manipulate the output given by the web service to retrieve the desired value<br />i.e<br />The response given by the web service would be as follows<br /><pre style="border: 1px dashed rgb(153, 153, 153); padding: 5px; overflow: auto; font-family: Andale Mono,Lucida Console,Monaco,fixed,monospace; color: rgb(0, 0, 0); background-color: rgb(238, 238, 238); font-size: 12px; line-height: 14px; width: 100%;"><?xml version="1.0" encoding="utf-8"?><br /><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><br /> <soap:Body><br /> <IsValidEmailResponse xmlns="http://www.webservicex.net"><br /> <IsValidEmailResult><span style="color: rgb(51, 51, 255); font-weight: bold;">true</span></IsValidEmailResult><br /> </IsValidEmailResponse><br /> </soap:Body><br /></soap:Envelope><br /></pre><br /><br /></div></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com3tag:blogger.com,1999:blog-3320668921204955011.post-44247752310239266392009-04-15T19:46:00.001-07:002009-04-25T19:32:24.803-07:00How to Create external tables in Oracle <span class="Apple-style-span" style="font-weight: bold;"> </span><span class="Apple-style-span" style="font-family:arial;"><span class="Apple-style-span" style="font-weight: bold;">ORACLE EXTERNAL TABLES</span></span><br /><span class="Apple-style-span" style="font-family:arial;">This post summarizes the steps to create an external table in Oracle.</span><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="text-decoration: underline;"><span class="Apple-style-span" style="font-family:arial;">Note</span></span></span></div><div><span class="Apple-style-span" style="font-weight: bold; "><span class="Apple-style-span" style="font-weight: normal;"><span class="Apple-style-span" style="font-family:arial;"> The following changes should be done in the database server workstation</span></span><span class="Apple-style-span" style="font-family:arial;"> </span></span><div><span class="Apple-style-span" style="font-family:arial;"><br /></span></div><div><span class="Apple-style-span" style="font-family:arial;"><span class="Apple-style-span" style="font-weight: bold;">Overview</span></span></div><div><span class="Apple-style-span" style="font-family:arial;">1. Create a directory</span></div><div><span class="Apple-style-span" style="font-family:arial;">2. Give apropriate access to the accessing user</span></div><div><span class="Apple-style-span" style="font-family: arial;"><span class="Apple-style-span" style="font-family: Georgia; ">3. <span class="Apple-style-span" style="">Create the csv file which need to be accessed</span> </span><br /></span></div><div>4. <span class="Apple-style-span" style="">Create an external table </span><br /></div><div><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="font-weight: normal;">5. </span><span class="Apple-style-span" style=""><span class="Apple-style-span" style="font-weight: normal;">Try selecting the record using the external table</span></span><br /></span></div><div><span class="Apple-style-span" style="font-family:arial;"> </span></div><div><span class="Apple-style-span" style="font-family:arial;"><span class="Apple-style-span" style="font-weight: bold;"><span class="Apple-style-span" style="text-decoration: underline;">Steps to be followed in creating an external table</span></span></span></div><div><span class="Apple-style-span" style="font-family:arial;">1. <span class="Apple-style-span" style="font-weight: bold;">Create a Directory </span></span></div><div><span class="Apple-style-span" style="font-family:arial;"> 1. Log in to a schema using sql * plus</span></div><div><span class="Apple-style-span" style="font-family:arial;"> 2. You would have placed the csv file in a folder in you local m<span class="Apple-style-span" style=" ;font-family:Georgia;"><span class="Apple-style-span" style=" ;font-family:arial;">achine</span></span></span></div><div><span class="Apple-style-span" style="font-family:arial;"> I have placed the csv file in H:\FORMS folder in my local system.</span></div><div><span class="Apple-tab-span" style="white-space:pre"><span class="Apple-style-span" style="font-family:arial;"> </span></span><span class="Apple-style-span" style="font-family:arial;"> So I would be running the following statement in SQL * PLUS.</span></div><div><span class="Apple-style-span" style="font-family:arial;"><br /></span></div><div><span class="Apple-style-span" style="font-family:arial;"> <span class="Apple-style-span" style="color: rgb(51, 51, 255);">create or replace directory forms as 'H:\Forms'</span>;</span></div><div><span class="Apple-style-span" style="font-family:arial;"><br /></span></div><div><span class="Apple-style-span" style="font-family:arial;"> </span><span class="Apple-style-span" style="font-family:arial;"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiHp_mYGLf-yPeFeKgKNsCIZS7wYnejp6gET5SaXGv5RCbt8DL0oz1HAsbrsk9IzmsCal8NOCBQHhLc4laf6vG6ffzXfTxBAOEQWuLXC_QF42E7Pi3jPfhw3RQRxK88asNgVqcrDYhyaH4y/s320/Directory_creation.JPG" style="cursor:pointer; cursor:hand;width: 320px; height: 76px;" border="0" alt="" id="BLOGGER_PHOTO_ID_5325117378920606226" /></span></div><div><span class="Apple-style-span" style="font-family:arial;">2. <span class="Apple-style-span" style="font-weight: bold;">Give appropriate access to the accessing use</span>r</span></div><div><span class="Apple-style-span" style="font-family:arial;"> We need to give the appropriate acess to all the users who is going </span></div><div><span class="Apple-style-span" style="font-family:arial;"> to access the following <span class="Apple-style-span" style=" ;font-family:Georgia;"><span class="Apple-style-span" style=" ;font-family:arial;"> directory</span></span></span></div><div><span class="Apple-style-span" style="font-family:arial;"><br /></span></div><div><span class="Apple-style-span" style="font-family:arial;"><span class="Apple-style-span" style=" ;font-family:Georgia;"><span class="Apple-style-span" style=" ;font-family:arial;"> <span class="Apple-style-span" style=" ;font-family:Georgia;"><span class="Apple-style-span" style="font-family:arial;"><span class="Apple-style-span" style="color: rgb(51, 51, 255);">grant read, write on directory forms to store;</span></span><span class="Apple-style-span" style=" ;font-family:arial;"><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> </span> </span></span></span></span></span></div><div><span class="Apple-style-span" style="font-family:arial;"><span class="Apple-style-span" style=" white-space: pre; font-family:-webkit-monospace;font-size:13px;"></span></span></div><pre class="code"></pre><div> <img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEglo9vkQlsMqiqgi7AMzdEwltoqbLM0DT0IvkpJuHDGMKCEGQoErg4hu2Y_8xjV06TeZVO8i1Uz663z5-L_8ARVJmPYYkF5uWQs-WFrXHdrdej7ema9Uud0pZandqlgrAxbjAqdTKJNdmQo/s320/access_directories.JPG" style="cursor:pointer; cursor:hand;width: 320px; height: 90px;" border="0" alt="" id="BLOGGER_PHOTO_ID_5325120220007085826" /><br /></div><div>3. <span class="Apple-style-span" style="font-weight: bold;">Create the csv file which need to be accessed</span> </div><div> I have created a file named Family.csv and placed it in the H:\Forms folder in my local.</div><div> It looks similiar to the following file.</div><div> <a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_uIS623OV3-AQ4m7PVzG4oJsyVK7ipgZ8lh3VUe9NAiqbCBft0y5XvKetSrLe5rFeOarxTcG9YKFxnDDL5QVvOFhMBnMc4ZI5ZZX2JFhxhblZuA-whQTiLSMciJkyOW09J1uAlyJe88PB/s1600-h/ext_table_csv.JPG"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_uIS623OV3-AQ4m7PVzG4oJsyVK7ipgZ8lh3VUe9NAiqbCBft0y5XvKetSrLe5rFeOarxTcG9YKFxnDDL5QVvOFhMBnMc4ZI5ZZX2JFhxhblZuA-whQTiLSMciJkyOW09J1uAlyJe88PB/s320/ext_table_csv.JPG" border="0" alt="" id="BLOGGER_PHOTO_ID_5325124162354526994" style="cursor: pointer; width: 320px; height: 246px; " /></a></div><div> 4. <span class="Apple-style-span" style="font-weight: bold;">Create an external table </span></div><div> We need to create a table to access the records available in the CSV file.</div><div> The syntax for creating an external table is as follows.</div><div> <span class="Apple-style-span" style="color: rgb(51, 51, 255);">create TABLE family</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"><span class="Apple-tab-span" style="white-space:pre"> </span> (</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span>ID number, -- Use VARCHAR2 eventhough you are using a NUMBER column</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span>name varchar2(100),</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"><span class="Apple-tab-span" style="white-space:pre"> </span> comments varchar2(1000)</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span> )</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span>organization external (</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span>type oracle_loader</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span>default directory FORMS</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span> access parameters (</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span>records delimited by newline</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span>fields terminated by ','</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span>missing field values are null</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"><span class="Apple-tab-span" style="white-space:pre"> </span> )</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span>location ('Family.csv')</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span> )</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-tab-span" style="white-space:pre"> </span> reject limit unlimited</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> /</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> </span> We would be getting the following output in SQL * PLUS</div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-style-span" style="color: rgb(0, 0, 0); "><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKpGTHPas5sYiOlhk8wLo1DfvFw9H7tDuoaTfey6BC00E3QwsZLXUh1rvNH5bsEVCdXstdKo6u-cxQnaG9mk9R781k90mNpW6M4M_Z9cLShdf_JGC9aBvOYSMWG_EFAMJuvgETdd0JWA4e/s1600-h/external_table.JPG"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhKpGTHPas5sYiOlhk8wLo1DfvFw9H7tDuoaTfey6BC00E3QwsZLXUh1rvNH5bsEVCdXstdKo6u-cxQnaG9mk9R781k90mNpW6M4M_Z9cLShdf_JGC9aBvOYSMWG_EFAMJuvgETdd0JWA4e/s320/external_table.JPG" border="0" alt="" id="BLOGGER_PHOTO_ID_5325126256188793570" style="cursor: pointer; width: 320px; height: 224px; " /></a><span class="Apple-style-span" style="color: rgb(51, 51, 255); "> </span></span></span></div><div> </div><div>5. <span class="Apple-style-span" style="font-weight: bold;">Try selecting the record using the external table</span></div><div> <span class="Apple-style-span" style="color: rgb(51, 51, 255);">Select * from family;</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> </span><span class="Apple-style-span" style="">The output is as follows in SQL * PLUS.</span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> <span class="Apple-style-span" style="color: rgb(0, 0, 0); "><a onblur="try {parent.deselectBloggerImageGracefully();} catch(e) {}" href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiK0z967YGWLIgt_dHb-CIc8MN8h0UwX3jmvGud6xQSKvSu-REOzYXET2cf1SB3LrD6ZSQNVkdxgNoDkKhew5e5YdUaZwX1R0VOEjJoeig8jcYKy8OKPSR2cdS_thqxqJk_pr9So57q8mJc/s1600-h/ext_table_res.JPG"><img src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiK0z967YGWLIgt_dHb-CIc8MN8h0UwX3jmvGud6xQSKvSu-REOzYXET2cf1SB3LrD6ZSQNVkdxgNoDkKhew5e5YdUaZwX1R0VOEjJoeig8jcYKy8OKPSR2cdS_thqxqJk_pr9So57q8mJc/s320/ext_table_res.JPG" border="0" alt="" id="BLOGGER_PHOTO_ID_5325130302355417874" style="cursor: pointer; width: 320px; height: 151px; " /></a></span></span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"> </span></div><div><span class="Apple-style-span" style="color: rgb(51, 51, 255);"><br /></span></div><div><br /></div><div> </div><div> </div></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com0tag:blogger.com,1999:blog-3320668921204955011.post-56239782779361731802009-03-07T18:40:00.000-08:002009-05-23T08:35:12.349-07:00As You think So you BecomeYad Bhavam Tat Bhavathi<div> " As You think So you Become"</div><div> -- Lord Krishna</div><br /><div style="text-align: center;"><br /></div>GShttp://www.blogger.com/profile/02893580182188516939noreply@blogger.com0