tag:blogger.com,1999:blog-16832999429815365432024-02-20T12:12:09.781+01:00PLSQL showroomPLSQL tips, tricks and codes...Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.comBlogger12125tag:blogger.com,1999:blog-1683299942981536543.post-84021438464864409862013-11-22T11:20:00.000+01:002013-11-22T11:20:37.868+01:00PL SQL Developer Conditional Breakpoint<h2>
Making Conditional Breakpoint </h2>
When you make a breakpoint conditional, the debugger pauses when a
certain condition is met.<br />
When breakpoint is first set, the debugger
pauses the program execution each time the breakpoint is encountered.
However, using the Edit Breakpoints dialog, you can customize
breakpoints so that they are activated only in certain conditions.
<br />
<br />
The <span class="GUITag">Conditions</span> tab in the <span class="GUITag">
Breakpoint </span>dialog is where you enter an expression that is evaluated
each time the debugger encounters the breakpoint while executing the
program. If the expression evaluates to <span class="PropertyName"><code>
true</code></span>, then the breakpoint pauses the program. If the
condition evaluates to <span class="PropertyName"><code>false</code></span>
, then the debugger does not stop at that breakpoint location.
<br />
<br />
<h3>
To set a breakpoint condition:
</h3>
<ol>
<li>
Set a breakpoint on a line of code by clicking to the left of the line
in the Code Editor.
</li>
<li>
Open the Breakpoints window by choosing <span class="GUITag"><u>V</u>
iew | Debu<u>g</u> Windows | <u>B</u>reakpoints</span>.
</li>
<li>
In the Breakpoints window, right-click the breakpoint you just set and
choose <b class="GUITag">Edit Breakpoint</b>.
</li>
<li>
In the Edit Breakpoints dialog, click <span class="GUITag">Conditions</span>
.
</li>
<li>
Enter an expression in the <span class="GUITag"><u>C</u>ondition</span>
field, for example:</li>
<ol>
<li>field = 20</li>
<li>field > 10</li>
<li>field != 30</li>
</ol>
<li>Click <b class="GUITag">OK</b>. </li>
</ol>
<h4>
Developer 4.0.13 breakpoint example:</h4>
put your mouse cursor over breakpoint and Box will appear<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4IVFPhJSWWWl1m5AwvpLsuGIPj24Tw8sL-AGfE7Rrz0oJBSGS725x5plBc3tpynKg06LEzgH87Titl2fDL3NECzX7dXYnY9n2AxWLmxMaUaPlijKlmBa7Ho_htGh_RFB70D09q3V626U/s1600/slika1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4IVFPhJSWWWl1m5AwvpLsuGIPj24Tw8sL-AGfE7Rrz0oJBSGS725x5plBc3tpynKg06LEzgH87Titl2fDL3NECzX7dXYnY9n2AxWLmxMaUaPlijKlmBa7Ho_htGh_RFB70D09q3V626U/s1600/slika1.png" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
Enter some conditional breakpont value example:<br />
name_of_field > 31000<br />
if is in a cursor lets say ;<br />
for c1 in (select * from table)<br />
you would need to enter c1.name_of_field = value_to_break <br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhG-QT96-5jGY6KSysAcuyzYdIpGv4jhT7CPNr4M5ZrqegwCU08DZJNXO3cFQjcFenhrCXG48T-Eo6e4bsSvPtjIviXWB7mqQGttGKyzkt9674iXSzhFCf5i87l2tDeghdUvXWxzSzprvY/s1600/slika2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFY0hcMrQPEzuD9Csb32fX3LHl0bj-iBD2eTeWFXpIj-sYcKC0_2l-5Tra-6R2faZ-sgk2LZ_CRh9IAq6va8VxnimTfC3XMoL5j2CQjDaxvkqTGInMKD35JORHt4KGKwQYLDUHXTyn5kE/s1600/slika2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgFY0hcMrQPEzuD9Csb32fX3LHl0bj-iBD2eTeWFXpIj-sYcKC0_2l-5Tra-6R2faZ-sgk2LZ_CRh9IAq6va8VxnimTfC3XMoL5j2CQjDaxvkqTGInMKD35JORHt4KGKwQYLDUHXTyn5kE/s1600/slika2.png" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjezQaYC_xUYSiEpnAeaKZnvEcWObal3cjupLV9Rb_khBNIb1r-9nCVSRLXlkD0Gxo-TRTpqNcvg2PUZsfCNsCn7sSa3LUdLMJyjNGMlJdG2u0NqeKvgYd_66EOWaSNjQsltMdnXP4WjhE/s1600/slika2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><br /></a></div>
<br />
and just run debugger<br />
<br />
When condition is met popup will be shown<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJ8c15PTn5T4lNknOp0rXTZf2LTImwzO8QA30P-h6LsnoU4p1dll6o9DCqjwm4h0Npome0Z9KBHNvd-idDjpCxuiRb5w8d_lIEmzChWpx_lMaCa1MbhC385MG2EoJaWAGBO8i3hLSB2ZE/s1600/slika3.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiJ8c15PTn5T4lNknOp0rXTZf2LTImwzO8QA30P-h6LsnoU4p1dll6o9DCqjwm4h0Npome0Z9KBHNvd-idDjpCxuiRb5w8d_lIEmzChWpx_lMaCa1MbhC385MG2EoJaWAGBO8i3hLSB2ZE/s1600/slika3.png" /></a></div>
<br />
<br />Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com0tag:blogger.com,1999:blog-1683299942981536543.post-27045572118324145742013-10-02T11:41:00.000+02:002013-10-09T13:24:14.295+02:00PLSQL Locked package<h2>
PL SQL locked package </h2>
When you work in a large company that has many programmers, you can often find yourself in situation of locked package.
Locked package?
Yes, that is right. Locked package is the thing that happens when somebody else compile that package or is debugging it and you are also working on that package, but with no team coding option.
If person A is debugging package, and person B let's say wants to compile package he will end up in a "wait session" until person A stops debugging.
Personally I had situation that Person A debugged package and left package in debug mode. So i waited and waited but I couldn't test program so this code came in handy....
So in you're Sql developer (Oracle developer) enter this code<br />
<br />
<blockquote class="tr_bq">
<i><span style="font-size: x-small;"><span style="font-family: Arial,Helvetica,sans-serif;">SELECT<br /> vss.SID, <br /> vss.SERIAL#, <br /> vss.osuser, <br /> vss.machine, <br /> vs.sql_text,<br /> dd.*<br />FROM <br /> v$session vss, <br /> DBA_DDL_LOCKS dd,<br /> v$sql vs<br />WHERE <br /> name LIKE 'NAME_OF_YOUR_PACKAGE' and --ime paketa<br /> vss.sid=dd.session_id and<br /> vs.sql_id = vss.sql_id and<br /> rownum < 2;</span></span></i></blockquote>
<br />
<span style="font-size: x-small;"><span style="font-family: Arial,Helvetica,sans-serif;">Let's say </span></span><i><span style="font-size: x-small;"><span style="font-family: Arial,Helvetica,sans-serif;"> NAME_OF_YOUR_PACKAGE</span></span></i> is TEST_TURKI so if you run it on Oracle 11g database you will see if someone is using it...<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_7pxc4yzKSlRDjP_1qCq37RXLGnqpADaGm_j5QfhyphenhyphenOMFDvIY1J3gykczJrSnCBLViriJKesymqz1Z1Kq8uFk03EetuPPKXUTI6gKbHqP1dsq5s1oqCfSR_jVJCmohPkwG8Yoe92WIa9g/s1600/Lock.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh_7pxc4yzKSlRDjP_1qCq37RXLGnqpADaGm_j5QfhyphenhyphenOMFDvIY1J3gykczJrSnCBLViriJKesymqz1Z1Kq8uFk03EetuPPKXUTI6gKbHqP1dsq5s1oqCfSR_jVJCmohPkwG8Yoe92WIa9g/s1600/Lock.JPG" height="260" width="640" /></a></div>
<br />
by data that is showing we can see that user under SID 4527 and SERIAL 145 is using it.<br />
We now can perform kill session command to get back access to that specific package.<br />
<br />
So if you have database administrator privileges you can type <br />
<br />
<blockquote class="tr_bq">
execute kill_session(SID,SERIAL#);</blockquote>
in this particular case command would be;<br />
<br />
<blockquote class="tr_bq">
execute kill_session(4527,145);</blockquote>
<br />
and you will get message that is killed or it is rolling back.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoo9wAwMBIly8tJclZBZ2ARvs5m3jD6CGNXiRKHf4Gd4EO9MbgE4cqmU7miDZOjNl126yMFc58a_hfM3aLLuAhb17aTJpSW_a4_NsKcHumoXt_i47Th2RGWpmrg8jyspKxOORZ3UC4Jio/s1600/kill.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhoo9wAwMBIly8tJclZBZ2ARvs5m3jD6CGNXiRKHf4Gd4EO9MbgE4cqmU7miDZOjNl126yMFc58a_hfM3aLLuAhb17aTJpSW_a4_NsKcHumoXt_i47Th2RGWpmrg8jyspKxOORZ3UC4Jio/s1600/kill.JPG" height="158" width="640" /></a></div>
<br />
so that's it you will gain access to your package in a few seconds or minutes (depending how large is package or what that user is doing)<br />
<br />
One more trick to kill session is that in ORACLE SQL developer you can choose Tools.>Monitor sessions<br />
and find user that is holding you're package<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhk3GyWNm5Jctw0udrDcbQvmHejFpvQInATWc-UUSI5e2eu41petde43WjHwJSVZzpBn59rzJEauD-oHksn_yplot-l75rMGo2iUeWeoodyv7k1lijoeX7rIaUmFyNIIdCsr3526rn8zz4/s1600/session.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhk3GyWNm5Jctw0udrDcbQvmHejFpvQInATWc-UUSI5e2eu41petde43WjHwJSVZzpBn59rzJEauD-oHksn_yplot-l75rMGo2iUeWeoodyv7k1lijoeX7rIaUmFyNIIdCsr3526rn8zz4/s1600/session.jpg" height="320" width="299" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
When it's opened you can find it under SID and SERIAL and right klick him with Kill session option</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiylLx6uRi1QqPOawXe9kEOaBV6tRKQePDQaNfYA_MVENL_QK71Es_7IlnmWZvxsIqPA3l5Ssd-T0SmbBIMYMv0c_lLJb2mE8XfKtfwj8JfpYvvGV6D2nRvJHhD9Tg_Zq1HYFTRlCozKtA/s1600/Capture.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiylLx6uRi1QqPOawXe9kEOaBV6tRKQePDQaNfYA_MVENL_QK71Es_7IlnmWZvxsIqPA3l5Ssd-T0SmbBIMYMv0c_lLJb2mE8XfKtfwj8JfpYvvGV6D2nRvJHhD9Tg_Zq1HYFTRlCozKtA/s1600/Capture.JPG" height="237" width="320" /></a></div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
<div class="" style="clear: both; text-align: left;">
That's it...</div>
<div class="" style="clear: both; text-align: left;">
<br /></div>
Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com0tag:blogger.com,1999:blog-1683299942981536543.post-89621979953128228182013-06-07T13:16:00.001+02:002013-10-09T13:23:41.610+02:00Leap year function PLSQL<h2>
PL SQL Leap year function</h2>
<br />
Hi all, I didn't have time to write cause I had no time on my schedule :)<br />
so I decided to refresh my blog with leap year calculator....<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGFLHOttJ_WTxo-uyf7q3t5gwXTuO8RlEi_a-HS_nYHqko24Jn4rrdHmcP-JFpnLxum85ZmYFFClLlgxG0AhaWrj0o6Mr4WWd6C0NlL5KbndWSxBPKaWWyOVxaClBGtvvkdYg1A8tdb_o/s1600/leap.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhGFLHOttJ_WTxo-uyf7q3t5gwXTuO8RlEi_a-HS_nYHqko24Jn4rrdHmcP-JFpnLxum85ZmYFFClLlgxG0AhaWrj0o6Mr4WWd6C0NlL5KbndWSxBPKaWWyOVxaClBGtvvkdYg1A8tdb_o/s1600/leap.JPG" height="239" width="320" /></a></div>
Some exceptions to this rule are required since the duration of a solar year is slightly <i>less</i> than 365.25 days. Over a period of four centuries, the accumulated error of adding a leap day <i>every</i>
four years amounts to about three extra days. The Gregorian Calendar
therefore omits 3 leap days every 400 years, omitting February 29 in the
3 century years (integer multiples of 100) that are not also integer
multiples of 400.<br />
For example, 1600 was a leap year, but 1700, 1800 and 1900 were not.
Similarly, 2000 was a leap year, but 2100, 2200, and 2300 will not be.
By this rule, the average number of days per year is 365 + 1/4 − 1/100 +
1/400 = 365.2425.<a href="http://en.wikipedia.org/wiki/Leap_year#cite_note-5"></a><br />
<br />
<h3>
Why do we need Leap Years?</h3>
<br />
Leap Years are needed to keep our calendar in alignment with the Earth's revolutions around the sun. It takes the Earth approximately 365.242199 days – or 365 days, 5 hours, 48 minutes, and 46 seconds (a tropical year) – to circle once around the Sun.<br />
<br />
However, the Gregorian calendar has only 365 days in a year, so if we didn't add a day on February 29 nearly every 4 years, we would lose almost six hours off our calendar every year. After only 100 years, our calendar would be off by approximately 24 days!<br />
Which<br />
The year is evenly divisible by 4;<br />
If the year can be evenly divided by 100, it is NOT a leap year, unless;<br />
The year is also evenly divisible by 400. Then it is a leap year.<br />
<h3>
Years are Leap Years?</h3>
<br />
In the Gregorian calendar 3 criteria must be taken into account to identify leap years:<br />
<br />
This means that 2000 and 2400 are leap years, while 1800,1900,2100,2200,2300<a href="http://www.timeanddate.com/calendar/?year=2300"></a> and 2500<a href="http://www.timeanddate.com/calendar/?year=2500"></a> are NOT leap years.<br />
<br />
The year 2000 was somewhat special as it was the first instance when the third criterion was used in most parts of the world since the transition from the Julian to the Gregorian Calendar<a href="https://www.blogger.com/null"> </a><br />
<h2>
</h2>
<h3>
Why should I care about Leap Years?</h3>
<br />
The 29th day added to February (leap day ) brings a smidgen of a
financial boost for some consumers and slight adjustments for businesses
that rely on daily sales and workers who are paid by the hour.<br />
So if February is leaping then you must add +1 day to the monthly salary if you are accountant or to calculate extra profit for February....<br />
<br />
<h3>
Little function for leaping year</h3>
-- function returns 1 for leaping year <br />
<br />
<blockquote class="tr_bq">
FUNCTION f_leap_year (i_year VARCHAR2) RETURN NUMBER IS<br />
v_remainder1 number(5,2); <br />
v_remainder2 number(5,2); <br />
v_remainder3 number(5,2); <br />
o_value pls_integer;<br />
begin <br />
v_remainder1 := mod(i_year,4); <br />
v_remainder2 := mod(i_year,100);<br />
v_remainder3 := mod(i_year,400);<br />
if ((v_remainder1 = 0 and v_remainder2 <> 0 ) or v_remainder3 = 0) then <br />
o_value := 1;<br />
return o_value; <br />
else <br />
o_value := 0;<br />
return o_value; <br />
end if; <br />
end f_leap_year;</blockquote>
enjoy<br />
.................................................... Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com1tag:blogger.com,1999:blog-1683299942981536543.post-63074386829009336492013-04-24T16:28:00.001+02:002013-10-09T13:23:12.598+02:00Backup of Source Code (effective versioning without SVN)<h2>
PL SQL versioning tutorial without SVN</h2>
<br />
Have been busy lately with MSAccess coding and had no time to write something useful....<br />
But now I manage to take some time and write ....<br />
<br />
I had some bad experience with "loosing source code" day after I wrote it...<br />
Scenario: You write the code test it and Compile package<br />
Someone else has that package opened and hit compile on it....<br />
Next day you open your source and you see that there are no changes that you made day before...<br />
<br />
So go with coding all over again.... :)<br />
<br />
I decide to make some table and Trigger that will automatically execute and save my package before someone compiles it....<br />
<br />
So let's make table where to store our changes... <br />
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;"><i>CREATE TABLE "MYBASE"."BACKUP_SOURCE" </i></span><br />
<span style="font-size: x-small;"><i> ( "CHANGE_DATE" TIMESTAMP (6), </i></span><br />
<span style="font-size: x-small;"><i> "OWNER_OF" VARCHAR2(4000 BYTE), </i></span><br />
<span style="font-size: x-small;"><i> "PKG_NAME" VARCHAR2(4000 BYTE), </i></span><br />
<span style="font-size: x-small;"><i> "PKG_TYPE" VARCHAR2(4000 BYTE), </i></span><br />
<span style="font-size: x-small;"><i> "CONTENT" CLOB, </i></span><br />
<span style="font-size: x-small;"><i> "CHANGED_BY" VARCHAR2(100 BYTE)</i></span><br />
<span style="font-size: x-small;"><i> );</i></span></blockquote>
<br />
In this table we have change date, name of shema (owner of package), name of package, package type, full content backup and username of person who compiled last...<br />
<br />
Let's write Trigger that will do some BACKING-UP....<br />
We will make AFTER CREATE trigger...<br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">If you cannot create trigger read this (or just curious).... otherwise skip on trigger creation... </span><br />
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Before a trigger can be created, the user SYS must run a SQL script commonly called DBMSSTDX.SQL. The exact name and location of this script depend on your operating system. <br />To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER system privilege. <br />To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER system privilege.<br />In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege. <br />If the trigger issues SQL statements or calls procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner rather than acquired through roles.</span><br />
What is after trigger....<br />
<br />
<i><u>AFTER</u> </i><br />
Specify AFTER to cause the database to fire the trigger after executing the triggering event. For row triggers, the trigger is fired after each affected row is changed. Restrictions on AFTER Triggers AFTER triggers are subject to the following restrictions: You cannot specify an AFTER trigger on a view or an object view. You cannot write either the :OLD or the :NEW value.<br />
<u><i><span class="subhead3">CREATE </span></i> </u><br />
<span style="font-family: inherit;">Specify CREATE to fire the trigger whenever a CREATE statement adds a new database object to the data dictionary.</span><br />
<b><i><u>Trigger CODE</u></i></b><br />
<blockquote class="tr_bq">
<blockquote class="tr_bq">
<span style="font-size: x-small;"><i>create or replace trigger mybase.trg_backup_source</i></span><br />
<span style="font-size: x-small;"><i>AFTER CREATE ON mybase.SCHEMA </i></span><br />
<span style="font-size: x-small;"><i>declare</i></span><br />
<span style="font-size: x-small;"><i>l_owner varchar2(1000) := ' ';</i></span><br />
<span style="font-size: x-small;"><i>l_name varchar2(1000) := ' ';</i></span><br />
<span style="font-size: x-small;"><i>l_type varchar2(1000) := ' ';</i></span><br />
<span style="font-size: x-small;"><i>l_clob clob;</i></span><br />
<span style="font-size: x-small;"><i>l_user varchar2(100) :=' ';</i></span><br />
<span style="font-size: x-small;"><i><br /></i></span>
<span style="font-size: x-small;"><i>BEGIN</i></span><br />
<span style="font-size: x-small;"><i>IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',</i></span><br />
<span style="font-size: x-small;"><i> 'PACKAGE', 'PACKAGE BODY',</i></span><br />
<span style="font-size: x-small;"><i> 'TYPE', 'TYPE BODY')</i></span><br />
<span style="font-size: x-small;"><i>THEN</i></span><br />
<span style="font-size: x-small;"><i>for c1 in </i></span><br />
<span style="font-size: x-small;"><i> (SELECT sysdate, all_source.* FROM ALL_SOURCE</i></span><br />
<span style="font-size: x-small;"><i> where type = ORA_DICT_OBJ_TYPE </i></span><br />
<span style="font-size: x-small;"><i> and name = ORA_DICT_OBJ_NAME </i></span><br />
<span style="font-size: x-small;"><i> )</i></span><br />
<span style="font-size: x-small;"><i> loop</i></span><br />
<span style="font-size: x-small;"><i> l_clob := l_clob||' '||c1.text; </i></span></blockquote>
</blockquote>
<blockquote class="tr_bq">
<blockquote class="tr_bq">
<span style="font-size: x-small;"><i>end loop; </i></span></blockquote>
</blockquote>
<blockquote class="tr_bq">
<blockquote class="tr_bq">
<span style="font-size: x-small;"><i>select </i></span><br />
<span style="font-size: x-small;"><i> all_source.owner,</i></span><br />
<span style="font-size: x-small;"><i> all_source.name,</i></span><br />
<span style="font-size: x-small;"><i> all_source.type </i></span><br />
<span style="font-size: x-small;"><i>into</i></span><br />
<span style="font-size: x-small;"><i> l_owner,</i></span><br />
<span style="font-size: x-small;"><i> l_name,</i></span><br />
<span style="font-size: x-small;"><i> l_type</i></span><br />
<span style="font-size: x-small;"><i>from </i></span><br />
<span style="font-size: x-small;"><i> all_source</i></span><br />
<span style="font-size: x-small;"><i>where type = ORA_DICT_OBJ_TYPE </i></span><br />
<span style="font-size: x-small;"><i> and name = ora_dict_obj_name </i></span><br />
<span style="font-size: x-small;"><i> and rownum <2;</i></span><br />
<span style="font-size: x-small;"><i>-- get username</i></span><br />
<span style="font-size: x-small;"><i>begin</i></span><br />
<span style="font-size: x-small;"><i> select</i></span><br />
<span style="font-size: x-small;"><i> osuser</i></span><br />
<span style="font-size: x-small;"><i> into</i></span><br />
<span style="font-size: x-small;"><i> l_user </i></span><br />
<span style="font-size: x-small;"><i> from</i></span><br />
<span style="font-size: x-small;"><i> v$session</i></span><br />
<span style="font-size: x-small;"><i> where</i></span><br />
<span style="font-size: x-small;"><i> audsid = userenv('sessionid'); </i></span></blockquote>
</blockquote>
<blockquote class="tr_bq">
<blockquote class="tr_bq">
<span style="font-size: x-small;"><i>exception</i></span><br />
<span style="font-size: x-small;"><i> when no_data_found then</i></span><br />
<span style="font-size: x-small;"><i> l_user := 'Unknown';</i></span><br />
<span style="font-size: x-small;"><i> when others then</i></span><br />
<span style="font-size: x-small;"><i> null;</i></span><br />
<span style="font-size: x-small;"><i>end;</i></span><br />
<span style="font-size: x-small;"><i><br /></i></span>
<span style="font-size: x-small;"><i>insert into backup_source values </i></span><br />
<span style="font-size: x-small;"><i> (systimestamp,</i></span><br />
<span style="font-size: x-small;"><i> l_owner,</i></span><br />
<span style="font-size: x-small;"><i> l_name,</i></span><br />
<span style="font-size: x-small;"><i> l_type,</i></span><br />
<span style="font-size: x-small;"><i> l_clob,</i></span><br />
<span style="font-size: x-small;"><i> l_user);</i></span><br />
<span style="font-size: x-small;"><i><br /></i></span>
<span style="font-size: x-small;"><i>end if;</i></span><br />
<span style="font-size: x-small;"><i>exception</i></span><br />
<span style="font-size: x-small;"><i> when no_data_found then</i></span><br />
<span style="font-size: x-small;"><i> null;</i></span><br />
<span style="font-size: x-small;"><i>when others then</i></span><br />
<span style="font-size: x-small;"><i> raise_application_error(-20000,' ERROR IN TRG_BACKUP_SOURCE_mybase sqlerr:'|| sqlerrm||' NUM:'||SQLcode);</i></span><br />
<span style="font-size: x-small;"><i>end;</i></span></blockquote>
</blockquote>
<br />
After that just Compile and Enable trigger and you're ready to go ....<br />
do some compiling of some package and then select from table<br />
<br />
SELECT * FROM BACKUP_SOURCE ORDER BY CHANGE_DATE DESC;<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtj65xmsqd4jj3Kd6wZiOy9TzdXvo7iUYRwXYxE-rH86eCDHj2AS_pC_H4IgazQaGyZgPQTlJGuPkGsJzQkdpDXhDVZ828q28RuraBFdW9k9zLwpX0eWw6HDP0Fb-p9ccRFL6gU2xibmo/s1600/BackupTable.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtj65xmsqd4jj3Kd6wZiOy9TzdXvo7iUYRwXYxE-rH86eCDHj2AS_pC_H4IgazQaGyZgPQTlJGuPkGsJzQkdpDXhDVZ828q28RuraBFdW9k9zLwpX0eWw6HDP0Fb-p9ccRFL6gU2xibmo/s1600/BackupTable.png" height="185" width="640" /></a></div>
<br />
Every time you hit Compile it will make backup...<br />
<br />
<br />
Enjoy<br />
P.S.<br />
<br />
will add how to delete source every 10 days into code when I get time.....<br />
<br />
<br />
Happy codingAnonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com0Bjelovar, Croatia45.8987972 16.84230930000001145.8104057 16.680947800000009 45.9871887 17.003670800000013tag:blogger.com,1999:blog-1683299942981536543.post-6189186954057919572013-03-18T18:00:00.000+01:002013-10-09T13:22:42.123+02:00PLSQL Operators<h2>
PL SQL Operators</h2>
<br />
Relational operators compare two expressions or values and return a Boolean result. PL/SQL supports operators like<br />
<br />
=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN<a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="36096"></a>
<br />
<br />
<br />
<table border="1" cellpadding="3" cellspacing="0" class="Informal" dir="ltr" frame="HSIDES" rules="GROUPS" style="width: 100%px;" summary="" title=""><thead>
<tr class="Informal"><th align="left" class="Informal" scope="col" valign="bottom"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><b>Operator</b></span></span></th>
<th align="left" class="Informal" scope="col" valign="bottom"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="19619"></a> <b>Meaning</b></span></span></th>
</tr>
</thead>
<tbody>
<tr align="left" class="Informal" valign="top">
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="36439"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><code>=</code></span></span></div>
</td>
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="36441"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;">equal to</span></span></div>
</td>
</tr>
<tr align="left" class="Informal" valign="top">
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="36443"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><code><></code>, <code>!=</code>, <code>~=</code>, <code>^=</code></span></span></div>
</td>
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="36445"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;">not equal to</span></span></div>
</td>
</tr>
<tr align="left" class="Informal" valign="top">
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="36447"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><code><</code></span></span></div>
</td>
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="36449"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;">less than</span></span></div>
</td>
</tr>
<tr align="left" class="Informal" valign="top">
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="36451"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><code>></code></span></span></div>
</td>
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="36453"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;">greater than</span></span></div>
</td>
</tr>
<tr align="left" class="Informal" valign="top">
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="36455"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><code><=</code></span></span></div>
</td>
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="36457"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;">less than or equal to</span></span></div>
</td>
</tr>
<tr align="left" class="Informal" valign="top">
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="32047"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><code>>=</code></span></span></div>
</td>
<td class="Informal"><span style="font-family: Verdana,sans-serif;"><span style="font-size: small;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="32052"></a></span></span>
<br />
<div class="TB">
<span style="font-family: Verdana,sans-serif;"><span style="font-size: small;">greater than or equal to</span></span></div>
</td></tr>
</tbody></table>
<br />
Is NULL operator checks if <br />
Example code:<br />
<br />
<blockquote class="tr_bq">
declare<br />
a number ( 2 ) := 50 ;<br />
b number ( 2 ) := 15 ;<br />
begin<br />
if ( a = b ) then<br />
dbms_output.put_line ( '1-a is equal to b' );<br />
else<br />
dbms_output.put_line ( '1-a is not equal to b' );<br />
end if;<br />
if ( a < b ) then<br />
dbms_output.put_line ( '2-a is less than b' );<br />
else<br />
dbms_output.put_line ( '2-a is not less than b' );<br />
end if ;<br />
if ( a > b ) then<br />
dbms_output.put_line ( '3-a is greater than b' );<br />
else<br />
dbms_output.put_line ( '3-a is not greater than b' );<br />
end if ;<br />
-- Lets change value a and b<br />
a := 40 ;<br />
b := 90 ;<br />
if ( a <= b ) then<br />
dbms_output.put_line ( '4-a is either equal or less than b' );<br />
end if ;<br />
if ( b >= a ) then<br />
dbms_output.put_line ( '5-b is either equal or greater than a' );<br />
end if ;<br />
if ( a <> b ) then<br />
dbms_output.put_line ( '6-a is not equal to b' );<br />
else<br />
dbms_output.put_line ( '6-a is equal to b' );<br />
end if ;<br />
end;</blockquote>
Output:<br />
<img alt="" src="data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAARgAAAB8CAIAAAA9/jgjAAAIMklEQVR4nO2b3dKjOAxE8/4vnb3YmhliS3LLCGOScy62+IgstX/aMIX39QaA07zuFgDwDWAkgAIwEkAB32mk1+v1etldC36azrmM6zTs0LtH87VjFyyL6RWzw1ITNUxI3aF3z+Xf2L3+cPzzbe1VSuQx5tXx7qbNy9kLaPK4HXPE96X76l6hpjtBdU+n2c1mQIKRF8X3oz3sppkZdD6WSHPdr6GSyOa/caRy0+6YJWn4p1hdcVEqp+elOfGmwmGPMNIZop3+/Pr2luC0kY46Bx3zV1XWSNnSQXOvkDIg3p/9xJkCzEh9Y4KYGs/ERvLuzOWMbwYylD+HRhrWFQUXGkkUoEuCCa410rt7Z5gw0twOWmgkU1JQeiLnpUYK/I+RqrBf7Zp3gP59wPuz+entvJU1+c2Gf2/2F31k2yurhHLfTGKKiQWkcvaq5sTHtbJpIcXlA8fcTMCgPY4LJ4x9bg4G7YkwWwAFYCSAAjASQAEYCaAAjARQAEYCKAAjARTQfuGuL/AD30MWfPbhy9LmSAe39ud25aKAMzpv7yMEzBhJ/PTuhSknHv7+mj1CNqeqKXcM8Gr1aeO6gU5dea8HNiFtpGYFpOJTzZvVHDefeCCYyeNCQcUJAcOcfaSpBHZgcEQ/3kEnjHRsrjcsNJL5zEkVMvWvMVK2HCxj9RNJL3eRkc4Uuv2JlC0Hy9j61a6/VoyUte7J971h0bgWRvoOPpaL/tKlBHvvUUqVY4yXIWgiilduvjsvNcGpoTN1pgZErAKLYUo+YI3CHKybf7DfwzQsGoACMBJAARgJoACMBFAARgIoACMBFICRAArIfV+fKbDZZ5m7vhRdNLa7De/PMj5F9ghSmtd00DzEtKAK3MLgEKfdJnmqzbsfZDDPuXlpJ067KVLNnGIhU5LYo6DvXl94NN1OetfMPrv6RSM275dg0Dy1jBRJ+k2xyjvTIzPSqyJGwqVE/y+At9OLu7KZ89hcb3i1keJuTog3JQXim7Sp7UaMhEtJT8PJJ5L4k1fo6ieSrnPuidRfn3z0YaRNuHzmglV7kZGyS3zok4n1bQZjpC/m46Vcf+lSgs0wscoxxssQNMkqF3XqQxT3IqjVlIjLed2B9TD6AAVgJIACMBJAARgJoACMBFAARgIoACMBFICRAArASAAFDM5oFhT4vS/uFw3jD47kg5g5RfZoruhdn3NNFdiHyXPNygbphenHw5ojZ+ZFUKu/6R1O8+70hYJuHiO9tn2YWCXICTtgTPOgwfLT394Sj3N6hfqiQaS4ZM2cvRJPpxlgVhEjYT3RE2m4f08Y6dhcbzh0gq4z1TxWGOf0rptCEztLShusIf1ql53OIGauULxqq5oP5cWR3sNHvDmsgpF24/LpbGLOFyo00uvPvzoU5SmpGOnXGPx73W7jvAspYUpDM0NcenjTvD9RaCg1uO7TBpHKYMI+PGNKWDqwOVsvUDZgeAqsUYACMBJAARgJoACMBFAARgIoACMBFICRAAqIji3XFLjgK9Aw57O+Pi1Te9ewXFF3tyk2DsJtpU+k1/ysXixTu8yxC+puNcUzRhIPHHhh+pmxPsxsaJ5DOx5gU3KawWKnmgyNjKH+t7wmToo3Cw2HNL4ZiJyYjuxciJELGJxHNhrcffrbbOitj+ZXL+fEKjSTx4VSPRKr6+KHdafFD6u8M9PhDaNZRYxcwLhL0ztTn7lpPmzoFRKNlOrRsCNe89Sy03tkVh9ep5K8hSk2g7NVYvHKeA6rpAbhCuwVFsjKqhdTnWyoG2lCSRymGykYuhuNlIqfblUySkrOu0gP00kjzQ3T8Kc+7SZGEvfarYx0cn2bwT9nJOXxrT+7+jCloZlhWPp407vO5lQkxTePF2atuepm6bi5FxkPSNxHUap33asKIsXu3MX9Cr6JHWYUboGJL2Of3RHWw6wDFICRAArASAAFYCSAAjASQAEYCaAAjARQQO7T9cR3kiu+qwxzPut7zrPUrucR45M+rbRnl3pVe+r0eJbaI2s2yv3HZ9JIyibhPd/0I1J9mNnQPHbl6fRynulUk6GRMdT/zgz+efHxxbCbZtiwua7TzOm19UZbGQ2vm3OM59is/fd6XKDi9HeQJLjT6/Ry6uPeX/ezOIxUeiRWnxDvLfG4UFC9VqeXU5xNfX3qS1HBaN9oMvcGvbw3KKJjzWDRSP21l1OfYHFA4sWk9MisPrwuaR6MkjJ0JwfZy+ldn1mf3nRMEBnJqy1GDmP0QsOfdCNNKInDdCMFQ7ebkcqb6zqHOacHWck5zeXlgxVzkZH+v97ESOJm+R1G6kd+mFaU+hgjvbU3rtcng9RWsN7cDA4EHG9619mciqT45vHCrDVX3Sw9Id5rPrxp3p8opPTUu+7TBpHDAVFURanONIaGk5OxjKfofBAMaBklG9ul7K/wuTCmAAVgJIACMBJAARgJoACMBFAARgIoACMBFOAeDigr8ANfLRZ8nOH7z+ZUHje6kdvFiwLO6Ly9jxAwOM1pt9EeXF6YcrqpOTEVp02dmDLD+gNar8/TXEqn4rqBTl15rwc2IX2WtFkB4wInTn8fV3PcXFxbZhPTM3FkX3FCwDBnH2kqgR0YrJt4B50w0rG53rDQSL34bCFT/xojZcvBMsZ7bdvg3BNJ/MkrVPhEmih0+xMpWw6WsdpIJwspRspaNy40fHYNi8a1MNJ3kP4n7zEyjvfeo5RCxxgvQ9BEFK/cfHdeaoLF0Qh0pgZErAKLYUo+YI3CHKybf7DfwzQsGoACMBJAARgJoACMBFAARgIoACMBFICRAArASAAF/Ad6UhStHapkgAAAAABJRU5ErkJggg==" /><br />
<br />
<br />
IS NULL Operator <br />
<br />
The IS NULL operator returns the Boolean value TRUE if its operand is null or FALSE if it is not null. test for the state of being null IF variable IS NULL THEN <br />
<a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="3981"></a>
<br />
<br />
LIKE Operator <br />
<br />
You use the LIKE operator to compare a character, string, or CLOB value to a pattern. Case is significant. LIKE returns the Boolean value TRUE if the patterns match or FALSE if they do not match. <br />
<br />
The patterns matched by LIKE can include two special-purpose characters underscore _ matches one character and percent sign % matches zero or more characters<br />
example: <br />
select var_name from table_names where var_name LIKE 'JOHN%';<br />
<br />
you will get all names starting with JOHN like JOHN, JOHNSON,JOHNATHON etc...<br />
<br />
BETWEEN Operator <br />
<br />
The BETWEEN operator tests whether a value lies in a specified range. It means "greater than or equal to low value and less than or equal to high value." <br />
Example:<br />
If you want to select some record between some input date and system date <br />
<br />
Select * from SOME_TABLE where to_char(beginning_date,'yyyymmdd') between to_char(i_first_date,'yyyymmdd') and to_char(sysdate,'yyyymmdd')<br />
<br />
IN Operator <br />
<br />
The IN operator tests set membership. It means "equal to any member of<br />
select * from table names where var_name IN ('JOHN','ERIC');
<br />
You will get all records containing names of JOHN and ERIC... <br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com0tag:blogger.com,1999:blog-1683299942981536543.post-5646049227353279082013-03-12T00:00:00.000+01:002013-10-09T13:22:14.106+02:00PLSQL Modulo 11 check...<div style="text-align: left;">
<h2>
<span style="font-size: small;">What is Modulo 11 check?</span></h2>
<span style="font-size: small;">MSI was developed by the MSI Data Corporation, based on the original Plessey Code. MSI, also known as Modified Plessey, is used primarily to mark retail shelves for inventory control. MSI is a continuous, non-self-checking symbology. While the length of an MSI bar code can be of any length, a given application usually implements a fixed-length code.</span><span style="font-size: small;"> </span></div>
<div style="text-align: left;">
</div>
<div style="text-align: left;">
<span style="font-size: small;">A typical MSI bar code is</span><span style="font-size: small;"></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.barcodeisland.com/msi.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://www.barcodeisland.com/msi.gif" /></a></div>
<div style="text-align: left;">
<br />
<br />
<span style="font-size: small;"> MSI, and other symbologies based on Pulse-Width Modulation, offer no significant benefit over more modern symbologies. While it is not a bad idea to support MSI for legacy bar codes, most new applications do not choose MSI as their symbology of choice.</span><br />
<br />
<br />
<h3>
<span style="font-size: small;">COMPUTING THE CHECKSUM DIGIT MSI </span></h3>
</div>
<div style="text-align: left;">
</div>
<div style="text-align: left;">
<span style="font-size: small;">uses one or two check digits, which may be calculated in a number of different ways. As such, it is really up to the software application to implement and check the check digit(s).</span><br />
<br />
<span style="font-size: small;"> The most common methods of calculating MSI check digits are: Modulo 10 and Modulo 11</span><br />
<br />
<br />
<span style="font-size: small;">Modulo 11 is what we want which is developed by IBM company based on <a href="http://en.wikipedia.org/wiki/Luhn_algorithm">Lhun algorithm</a>...</span><br />
<br />
A check digit is a number that is used to validate a series of
numbers whose accuracy you want to insure. Frequently the last digit of
a number string such as identification number is a check digit. Lets
say the identification number starts out at 6 digits. A calculation is
done using the six digits and a seventh digit is produced as a result of
the calculation. This number is the check digit. There are many
calculations that can be used - this example illustrates the logic of
the MOD11 check digit.<br />
<b>Steps to calculate the MOD11 check digit for a number such as an id #:<br />
</b>
<br />
<ul><ul>
<li>Assign weights to each digit of the id #. The weights in MOD11 are
from 2 through a maximum of 10 beginning with the low order position in
the field.</li>
<li>Each digit in the id # is multiplied by its weight</li>
<li>The results of the multiplication are added together</li>
<li>This product is divided by the modulus number 11</li>
<li>The remainder is subtracted from the modulus number 11 giving the check digit</li>
</ul>
</ul>
Example: find the check digit for the number <b>036532</b><br />
<b>
</b>
<br />
<table border="" cellpadding="7" cellspacing="1" style="width: 361px;">
<tbody>
<tr><td valign="TOP" width="15%"><div align="RIGHT">
0</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
3</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
6</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
5</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
3</div>
</td>
<td valign="TOP" width="18%"><div align="RIGHT">
2</div>
</td>
</tr>
<tr><td valign="TOP" width="15%"><div align="RIGHT">
x7</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
x6</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
x5</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
x4</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
x3</div>
</td>
<td valign="TOP" width="18%"><div align="RIGHT">
x2</div>
</td>
</tr>
<tr><td valign="TOP" width="15%"><div align="RIGHT">
0</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
18</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
30</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
20</div>
</td>
<td valign="TOP" width="17%"><div align="RIGHT">
9</div>
</td>
<td valign="TOP" width="18%"><div align="RIGHT">
4</div>
</td>
</tr>
</tbody></table>
<span style="font-family: Courier New;">
0 + 18 + 30 + 20 + 9 + 4 = 81<br />
81/11 = 7 remainder 4<br />
11 - 4 = 7 <br />
</span><b>7 is therefore the check digit</b>.<br />
<b>PROBLEMS:</b> If the remainder from the division is 0 or
1, then the subtraction will yield a two digit number of either 10 or
11. This won't work, so if the check digit is 10, then X is frequently
used as the check digit and if the check digit is 11 then 0 is used as
the check digit. If X is used, then the field for the check digit has
to be defined as character (PIC X) or there will be a numeric problem.<br />
<b>Steps to verify if the check digit is included as part of the number:<br />
</b>
<br />
<ul><ul>
<li>The entire number is multiplied by the same weights that were used to calculate and the check digit itself is multiplied by 1.</li>
<li>The results of the multiplication are added together.</li>
<li>The sum is divided by 11 and if the remainder is 0, the number is correct.</li>
</ul>
</ul>
<b>PROBLEM:</b> Note that if the check digit is X then 10 is used in the multiplication. Code for this occurrence must be included.<br />
Example of verifying the number <b>0365327</b> where 7 is the calculated MOD11 check digit:<br />
<table border="" cellpadding="7" cellspacing="1" style="width: 590px;">
<tbody>
<tr><td valign="TOP" width="14%"><div align="RIGHT">
0</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
3</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
6</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
5</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
3</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
2</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
7</div>
</td>
</tr>
<tr><td valign="TOP" width="14%"><div align="RIGHT">
x7</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
x6</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
x5</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
x4</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
x3</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
x2</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
x1</div>
</td>
</tr>
<tr><td valign="TOP" width="14%"><div align="RIGHT">
0</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
18</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
30</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
20</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
9</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
4</div>
</td>
<td valign="TOP" width="14%"><div align="RIGHT">
7</div>
</td>
</tr>
</tbody></table>
<span style="font-family: Courier New;">
0 + 18 + 30 + 20 + 9 + 4 + 7 = 88<br />
88/11 is 8 remainder 0<br />
</span>Since the remainder from this calculation is 0, the check digit 7 is valid.</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
So without any other complications here is the PL code...</div>
<div style="text-align: left;">
<span style="font-size: x-small;"><br /></span></div>
<blockquote class="tr_bq">
<div style="text-align: left;">
<span style="font-size: x-small;">procedure modulo_11 (l_input in varchar2) as<br />l_cntrl_num_sum number(1):= 0;<br />l_pom varchar2(30):=' ';<br />l_length number(30):= 0;<br />l_length_pom number(30):= 0;<br />l_sum number(30):= 0;<br />l_error number(2) := 0;<br />----<br />l_counter number(30);<br />l_sum1 number(30):=0;<br />l_sum_uk number(30):=0;<br />l_reminder number(30) := 0;<br />l_control_number number(30) := 0;<br />l_counter1 number(2) := 0;<br />l_errmsg varchar2(200) := ' ';<br />e_exit exception;<br />begin</span></div>
<div style="text-align: left;">
<span style="font-size: x-small;">-- test input </span></div>
<div style="text-align: left;">
<span style="font-size: x-small;">--l_input := '102305789016';<br /> -- determine length of input num<br /> l_length := length(l_input);<br /> -- take last digit for control<br /> l_cntrl_num_sum := substr(l_input,l_length,1);<br /> -- set length -1 sign<br /> -- take length in some local variable<br /> l_length_pom := l_length;<br /> -- set variable counters to zero<br /> l_counter := 0;<br /> l_reminder := 0;<br /> l_control_number := 0;<br /> l_counter :=0;<br /> -- counter to 1<br /> l_counter1 := 1;<br /> l_pom := null;<br /> -- reversing string input<br /> select reverse(l_input) into l_pom from dual; <br /> -- taking second place till last in local var which is used for calculating<br /> l_pom := substr(l_pom,2,l_length_pom - 1);<br /> l_length := length(l_pom); <br /> if l_length is null then<br /> l_error := 2;<br /> raise e_exit;<br /> end if;<br /> -- starting algorithm<br /> for k in 1..l_length<br /> loop<br /> l_sum := 0;<br /> l_sum1 :=0;<br /> l_counter := l_counter + 1;<br /> l_counter1 := l_counter1 + 1;<br /> l_sum := l_counter1 * substr(l_pom,l_counter,1);<br /> l_sum1 := l_sum; <br /> l_sum_uk := l_sum_uk + l_sum1;<br /> l_length_pom := l_length_pom - 1;<br /> end loop;<br /> l_length := length(l_input) - 1;<br /> l_reminder := mod(l_sum_uk,11); <br /> l_control_number := 11 - l_reminder;<br /> if l_reminder in (0, 1) then<br /> l_control_number := 0;<br /> end if;<br /> if l_control_number != l_cntrl_num_sum then<br /> l_error := 2;<br /> l_errmsg := ('error in MOD_11_INI :');<br /> raise e_exit;<br /> end if;<br />exception <br /> when e_exit then<br /> raise_application_error(-20010,l_errmsg||' '||sqlcode);<br /> END modulo_11;</span></div>
</blockquote>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
enjoy.....</div>
<div style="text-align: left;">
</div>
<div style="text-align: left;">
</div>
<div style="text-align: left;">
text sources: </div>
<div style="text-align: left;">
<span style="font-size: xx-small;">http://www.pgrocer.net/Cis51/mod11.html</span></div>
<div style="text-align: left;">
<span style="font-size: xx-small;">http://en.wikipedia.org/wiki/Luhn_algorithm</span></div>
<div style="text-align: left;">
<span style="font-size: xx-small;">http://en.wikipedia.org/wiki/MSI_Barcode </span></div>
<span style="font-family: verdana; font-size: x-small; line-height: 22px;"><a href="https://www.blogger.com/blogger.g?blogID=1683299942981536543" name="Check Digit"></a></span>Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com0tag:blogger.com,1999:blog-1683299942981536543.post-66670397060929319912013-03-11T00:00:00.000+01:002013-10-09T13:21:25.374+02:00PLSQL backup time...<h2>
PL SQL package backup tutorial</h2>
I started this project because i needed program that will easily back up my files from developer without clicking on package and exporting it to save my work...<br />
Second i wrote it because there is Bug in SQL DEVELOPER that causes lines to be messed up when you have large code...<br />
Example:<br />
<br />
You have large code and export it with right click on package name and select save specs and body<br />
<br />
Real code is written like this...<br />
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">1 line - BEGIN</span><br />
<span style="font-size: x-small;">2 LINE - SELECT RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))</span><br />
<span style="color: red;"><span style="font-size: x-small;">3 line - INTO L_INPUTNAME</span></span><br />
<span style="color: #990000;"><span style="font-size: x-small;">4 line - FROM V$INSTANCE ;</span></span><br />
<span style="font-size: x-small;">5 line - EXCEPTION WHEN OTHERS THEN</span><br />
<span style="font-size: x-small;">6 LINE - NULL;</span><br />
<span style="font-size: x-small;">7 line - END;</span></blockquote>
<br />
<span style="font-size: x-small;"><span style="font-size: small;">but sometimes export does the weird thing and saves it messed up</span></span><br />
switching the line order....<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">1 line - BEGIN<br />2 line - SELECT RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))<br /><span style="color: #990000;">4 line - FROM V$INSTANCE ;</span><br /><span style="color: red;">3 line - INTO L_INPUTNAME</span><br />5 line - EXCEPTION WHEN OTHERS THEN<br />6 line - NULL;<br />7 line - END;</span></blockquote>
<br />
I googled this program somewhere on the web two years ago and manage to rewrite it for my needs...<br />
So without any chit chat let's see the code<br />
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">CREATE OR REPLACE<br />PROCEDURE BACKUP_OF_PL_FILES <br /> (I_TIPE_OF_OBJECT VARCHAR2<br /> ,I_NAME_OF_OBJECT VARCHAR2 DEFAULT '%')<br />IS<br /> PL_OUTPUT UTL_FILE.FILE_TYPE;<br /> L_READ UTL_FILE.FILE_TYPE;<br /> L_BUFFER VARCHAR2(4000);<br /> G_SESSONID VARCHAR2(80);<br /> L_LINE VARCHAR2(80);<br /> L_COUNTER NUMBER(30) :=1 ;<br /> L_SAVE VARCHAR2(32767);<br /> L_INPUTNAME VARCHAR2(20);<br /> L_ERRNUM NUMBER := 0;<br /> L_DIRNAME VARCHAR2(100) := ' ';<br />BEGIN<br /> <br /> G_SESSONID := USERENV('sessionid');<br /> L_DIRNAME := 'BACKUP_PL/'||I_NAME_OF_OBJECT||'/'||TO_CHAR(TO_DATE(SYSDATE,'dd.mm.yy'),'yyyymmdd')||'/';<br /> -----------------------------------------------------------------------------<br /> <br /> BEGIN<br /> SELECT RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))<br /> INTO L_INPUTNAME<br /> FROM V$INSTANCE ;<br /> EXCEPTION WHEN OTHERS THEN<br /> NULL;<br /> END;<br /><br /> IF I_NAME_OF_OBJECT='%' THEN<br /> G_SESSONID := 'BAK_'||INITCAP(I_TIPE_OF_OBJECT)||'_'||L_INPUTNAME||'_'||TO_CHAR(SYSDATE, 'DDMMYY_HH24MI') || '.pls';<br /> ELSE<br /> G_SESSONID := 'BAK_'||INITCAP(I_NAME_OF_OBJECT)||'_'||L_INPUTNAME||'_'||TO_CHAR(SYSDATE, 'DDMMYY_HH24MI') || '.pls';<br /> END IF; <br /> <br /> PL_OUTPUT := UTL_FILE.FOPEN('c:\BACKUP', G_SESSONID,'A',4000); <br /> UTL_FILE.PUT_LINE(PL_OUTPUT, 'c:\BACKUP' || L_DIRNAME || G_SESSONID);<br /><br /> FOR RS_TYPE IN (SELECT DISTINCT TYPE, NAME <br /> FROM USER_SOURCE<br /> WHERE TYPE LIKE UPPER(I_TIPE_OF_OBJECT)||'%' <br /> AND NAME LIKE UPPER(I_NAME_OF_OBJECT) <br /> ORDER BY 2,1 )<br /> LOOP<br /> FOR RS_NAME IN (SELECT TEXT <br /> FROM USER_SOURCE<br /> WHERE TYPE = RS_TYPE.TYPE <br /> AND NAME = RS_TYPE.NAME )<br /> LOOP<br /> BEGIN<br /> IF (L_COUNTER = 1 OR UPPER(RS_NAME.TEXT) LIKE '%PACKAGE BODY%') THEN<br /> L_BUFFER := 'CREATE OR REPLACE '||RS_NAME.TEXT ;<br /> ELSE<br /> L_BUFFER := RTRIM(RS_NAME.TEXT,' ');<br /> END IF;<br /> IF SUBSTR(L_BUFFER,-1,1) = ';' THEN<br /> UTL_FILE.PUT_LINE(PL_OUTPUT,RTRIM(L_BUFFER));<br /> ELSE<br /> UTL_FILE.PUT_LINE(PL_OUTPUT,RTRIM(L_BUFFER,SUBSTR(L_BUFFER,-1,1)));<br /> END IF;<br /> L_COUNTER := L_COUNTER + 1 ;<br /> <br /> EXCEPTION WHEN OTHERS THEN<br /> L_ERRNUM :=1 ;<br /> DBMS_OUTPUT.PUT_LINE('error 2nd loop...'||SQLERRM);<br /> END ;<br /> END LOOP;<br /> L_COUNTER := 1;<br /> UTL_FILE.PUT_LINE(PL_OUTPUT,'/'); <br /> IF RS_TYPE.TYPE NOT LIKE '%PACKAGE%' OR RS_TYPE.TYPE LIKE '%PACKAGE BODY%' THEN<br /> UTL_FILE.PUT_LINE(PL_OUTPUT,L_LINE); <br /> END IF;<br /> END LOOP;<br /> UTL_FILE.FCLOSE(PL_OUTPUT);<br /> L_READ := UTL_FILE.FOPEN('PL_OUT', G_SESSONID,'A',4000); <br /> ----------------------<br />EXCEPTION WHEN OTHERS THEN<br /> DBMS_OUTPUT.PUT_LINE('Error... '||SQLCODE);<br /> UTL_FILE.FCLOSE(PL_OUTPUT);<br />END BACKUP_OF_PL_FILES; </span></blockquote>
<br />
<br />
Basically this procedure is searching database same as export and write package in your local directory c:\BACKUP<br />
<br />
Calling is easy. All you have to do is call it from anonymous block with specification(package,procedure,function...) and name of the Package/Procedure... like this<br />
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">BEGIN</span><br />
<span style="font-size: x-small;"> BACKUP_OF_PL_FILES('package','MY_PACKAGE_NAME_PKG');</span><br />
<span style="font-size: x-small;"> BACKUP_OF_PL_FILES('package','MY_PACKAGE_NAME_PKG');</span><br />
<span style="font-size: x-small;"> BACKUP_OF_PL_FILES('procedure','MY_PROCEDURE_NAME);</span><br />
<span style="font-size: x-small;"> BACKUP_OF_PL_FILES('function','MY_FUNCTION_NAME');</span><br />
<span style="font-size: x-small;">END; </span></blockquote>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhiuUCz7-Rn49cjmN8Zjn-3603_JR_09aIcesCR8unzFMNWSZG8oDG8lyxl61KnWooYXiomRKU2-zFcW-F76LTdXp6tSYYO-9YcQJgKkPN2thpL1_IxIc9QW1qZfOz8jI6vTKj7W010cBU/s1600/backup.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhiuUCz7-Rn49cjmN8Zjn-3603_JR_09aIcesCR8unzFMNWSZG8oDG8lyxl61KnWooYXiomRKU2-zFcW-F76LTdXp6tSYYO-9YcQJgKkPN2thpL1_IxIc9QW1qZfOz8jI6vTKj7W010cBU/s1600/backup.jpg" height="137" width="640" /></a></div>
<br />
<br />
<br />Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com0tag:blogger.com,1999:blog-1683299942981536543.post-21750593356466619222013-03-10T00:00:00.000+01:002013-10-09T13:20:07.384+02:00PLSQL - Decoding time<h2>
PL SQL Decode Function</h2>
Some of my friends told me to write something about famous decode :)<br />
So here it goes ....<br />
<br />
<h3>
What is decode?</h3>
Decode is implemented function that has the functionality of an IF THEN ELSE statement.<br />
But there is one major difference between implementing these two functionality. DECODE can only work in select statement.You need to have in mind that DECODE is supported only in <acronym title="Structured Query Language">SQL</acronym> Context so you don't really working with PL code. More flexible than decode is case statement....<br />
<div style="text-align: left;">
<blockquote class="tr_bq">
<span style="font-size: x-small;">The syntax for the decode function is: decode( expression , search , result [, search , result]... [, default] )</span></blockquote>
<i>expression</i> is the value to compare. <br />
<br />
<i>search</i> is the value that is compared against expression. <br />
<br />
<i>result</i> is the value returned, if expression is equal to search. <br />
<br />
<i>default</i> is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).</div>
<br />
<br />
So something to decode now....<br />
let's create some table<br />
<span style="font-size: x-small;"><br /></span>
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">CREATE<br /> TABLE company<br /> (<br /> id NUMBER(32) NOT NULL,<br /> company_id NUMBER(8),<br /> owner_name VARCHAR2(20 byte),<br /> street_adress VARCHAR2(20 byte)<br /> );</span></blockquote>
<br />
insert some data in it<br />
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;"> insert into company values (1,10001,'Nerad Kovacevic','Neradna BB');<br /> insert into company values (2,10003,'Vedran Rudjic','Shvalerska 22');<br /> insert into company values (3,10006,'Ljuban Draga','Vlak u snijegu bb'); </span></blockquote>
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">select * from company;</span></blockquote>
<br />
you will get <br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPckvEuhNRRWOG2LZJpS-kReFpcpywAFMmLfqPBDopbplI1RkUz5dQ5KzPtNaCphh5Liac-FZF73OslOdEgjcMtkkCtlnxons94YjZfmxBskHA9QfcrxFyF6kNqs2PgcWJUkEI9ji50cY/s1600/decode1.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjPckvEuhNRRWOG2LZJpS-kReFpcpywAFMmLfqPBDopbplI1RkUz5dQ5KzPtNaCphh5Liac-FZF73OslOdEgjcMtkkCtlnxons94YjZfmxBskHA9QfcrxFyF6kNqs2PgcWJUkEI9ji50cY/s1600/decode1.JPG" height="99" width="320" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
let's say 10001 is IBM company's id, 10003 is Microsoft and 10006 is TrainStation Co :)<br />
<br />
and now we want to display some writings instead of company id's so we decode it<br />
Pseudo-code; if i found id by the number of 10001 i will write 'IBM' if that number is 10003 i will write 'Microsoft' if that number is 10006 i will write 'TrainStation Co' if i don't find (ELSE) number that i have in my decode function i will write 'Nameless' <br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">select decode(company_id,10001,'IBM',10003,'Microsoft',10006,'Trainstation Co','Nameless') as company_name , owner_name, street_adress from company;</span></blockquote>
<br />
after running statement we get;<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY_80YHaeehAFFPZr0vknAInAt65m4uYctE6xVniZZosVbjZaNoKf-331DCnYC9Ox-6ROkomb6-RSWP3EFLbhldE84GCwsaFuxg3M9k3vvfCL4SC9rku7NzFddggs5F40TXHl_AnJZGpc/s1600/decode.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY_80YHaeehAFFPZr0vknAInAt65m4uYctE6xVniZZosVbjZaNoKf-331DCnYC9Ox-6ROkomb6-RSWP3EFLbhldE84GCwsaFuxg3M9k3vvfCL4SC9rku7NzFddggs5F40TXHl_AnJZGpc/s1600/decode.JPG" height="89" width="320" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
Lets insert one more row...<br />
<span style="font-size: x-small;"><br /></span>
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">insert into company values (3,10009,'Unknown','UNKNOWN bb'); </span></blockquote>
<br />
and run our statement again and for every undefined id we will get Nameless in company name column... <br />
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">select
decode(company_id,10001,'IBM',10003,'Microsoft',10006,'Trainstation
Co','Nameless') as company_name , owner_name, street_adress from
company; </span></blockquote>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQa1_f94NxqjpCvotQzUn6ArYXj3fmjPNT7sb4dY_ioln-gCyAJQmsCgUi9JhdWaYursV0FzMvruYxzWIrsEs38mKilYVdgY_YnZOKsOrrNs-BRTNisHgigjLovZfSvhuDYQmLmVfASyI/s1600/decode2.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjQa1_f94NxqjpCvotQzUn6ArYXj3fmjPNT7sb4dY_ioln-gCyAJQmsCgUi9JhdWaYursV0FzMvruYxzWIrsEs38mKilYVdgY_YnZOKsOrrNs-BRTNisHgigjLovZfSvhuDYQmLmVfASyI/s1600/decode2.JPG" height="112" width="320" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
So that's about it...<br />
<br />
Simple decode function....<br />
<br />
Anyway you can find it useful in many occasions and it's use is quite simple and in many situations very grateful...<br />
<br />
so one last example <br />
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;"> select decode((select id from company where id = 1 ),1,'IBM',2,'Microsoft',3,'Trainstation Co','Nameless') as company_name , owner_name, street_adress from company where rownum < 2;</span></blockquote>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOxWUmIqfhvqBHUlxu3jl6_t1wefpxq8sKETjM6SBRHPRGV4QSvBcr5jH8pICl4GKuuczROD2nRt_TANRhf3grv0TM_7ZnhP7DVLXphle6hf16G6BHJ5GXnDx7d4gxGpQAeMqMVUQUgCM/s1600/decode3.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOxWUmIqfhvqBHUlxu3jl6_t1wefpxq8sKETjM6SBRHPRGV4QSvBcr5jH8pICl4GKuuczROD2nRt_TANRhf3grv0TM_7ZnhP7DVLXphle6hf16G6BHJ5GXnDx7d4gxGpQAeMqMVUQUgCM/s1600/decode3.JPG" height="80" width="320" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
Happy decoding :D Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com0Bjelovar, Croatia45.8987972 16.84230930000001145.8104057 16.680947800000009 45.9871887 17.003670800000013tag:blogger.com,1999:blog-1683299942981536543.post-5483657553305795862013-03-08T00:00:00.001+01:002013-10-09T13:18:32.787+02:00PLSQL check my email adress...<h2>
PL SQL Code for checking mail adress</h2>
So you want to put some security check on you're mail input field...<br />
As you know mail can consist letters, numbers and dots....<br />
It must contain only one @ sign...<br />
How would you check you're email?<br />
<br />
<br />
I do check on two different ways<br />
No1.<br />
<br />
<h3>
Simple way of checking allowed signs in mail</h3>
<blockquote class="tr_bq">
<span style="font-size: x-small;">function f_emailok (i_email in varchar2) return boolean is<br /> l_dot NUMBER;<br /> l_at NUMBER;<br /> l_length NUMBER;<br />BEGIN<br /> l_dot := instr(i_email ,'.');<br /> l_at := instr(i_email,'@');<br /> l_length := length(i_email);<br /> IF ((l_dot = 0) OR (l_at = 0) OR (l_dot = l_at + 1) OR<br /> (l_at = 1) OR (l_at = l_length) OR (l_dot = l_length))<br /> then<br /> RETURN (false);<br /> end if;<br /> IF instr(substr(i_email,l_at),'.') = 0 then<br /> RETURN (false);<br /> end if;<br /> return (true);<br />END f_emailok;</span></blockquote>
<br />
Let's explain code....<br />
<br />
<span style="font-size: small;">With instr function we are searching for sign in our string. In this case we search for . (dot), @ (at) and we are measuring length of our string input.</span><br />
<span style="font-size: small;">with </span><br />
<span style="font-size: small;"> l_dot := instr(i_email ,'.');</span><br />
<span style="font-size: small;">we get some numeric value in our l_dot variable.</span><br />
<span style="font-size: small;">let's say i_email is filled with value some.mail@gmail.com</span><br />
<span style="font-size: small;">l_dot will pick up value 5 from i_email input cause dot sign is on the fifth place in our string.</span><br />
<span style="font-size: x-small;"><span style="font-size: small;">after we pick up @ value on 11th sign in string and length of our string we can do some logic.</span></span><br />
<span style="font-size: x-small;"><span style="font-size: small;">We say if value of l_dot is 0 or l_at = 0 or there is @ sign behind dot or @ sign is on the first place or there is all string filled with @ or with dots return false.</span></span><br />
<br />
<span style="font-size: x-small;"><span style="font-size: small;">We still need one more check. Is there <span style="font-size: small;">anything behind @ sign </span></span></span><br />
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-size: x-small;"><span style="font-size: x-small;"> <span style="font-size: small;">instr(substr(i_email,l_at),'.') = 0</span></span></span></span></span><br />
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-size: x-small;"><span style="font-size: x-small;"><span style="font-size: small;">if value is zero return false...</span></span></span></span></span><br />
<br />
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-size: x-small;"><span style="font-size: x-small;"><span style="font-size: small;">If everything goes correctly we return true value.</span></span></span></span></span><br />
<br />
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-size: x-small;"><span style="font-size: x-small;"><span style="font-size: small;">But there is one bug in code :) you can pass email in incorrect format...</span></span></span></span></span><br />
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-size: x-small;"><span style="font-size: x-small;"><span style="font-size: small;">Figure out which and how to solve it...</span></span></span></span></span><br />
<br />
<br />
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-size: x-small;"><span style="font-size: x-small;"><span style="font-size: small;">No2.</span></span></span></span></span><br />
<br />
<span style="font-size: x-small;"><span style="font-size: small;"><span style="font-size: x-small;"><span style="font-size: x-small;"><span style="font-size: small;">Now we check email format with OWA pattern integrated function </span></span> </span></span></span><br />
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">function f_emailok (i_email in varchar2) return boolean is</span></blockquote>
<blockquote>
<span style="font-size: x-small;">begin<br /> if owa_pattern.match(i_email,'\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}'||<br /> '\@\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}\.\w{1,}[.,0-9,a-z,A-Z,_]\w{1,}$') then<br /> return(true);<br /> else<br /> return(false);<br /> end if;<br /> exception<br /> when others then<br /> return(false);<br />END f_emailok;</span></blockquote>
<br />
<span style="font-size: small;">So what does this code do?</span><br />
<span style="font-size: small;">It searches for allowed signs in our code....</span><br />
<span style="font-size: small;">we pass some string and in order with our owa patterns we search if it consists of illegal characters...</span><br />
<span style="font-size: small;">here is list of pattern </span> <br />
<span style="font-size: x-small;"><br /></span>
<br />
<h2>
<span style="font-size: small;"> PLSQL list of pattern</span>
</h2>
<h3>
<span style="font-size: small;">Assertions:</span></h3>
<ul>
<li><span style="font-size: small;">
^ Matches the beginning of a line (or string)</span></li>
<li><span style="font-size: small;">
$ Matches the end of a line (or string)</span></li>
<li><span style="font-size: small;">Quantifiers:</span></li>
<li><span style="font-size: small;">
{n,m} Must match at least n times, but not more than m times</span></li>
<li><span style="font-size: small;">
{n,} Must match at least n times</span></li>
<li><span style="font-size: small;">
{n} Must match exactly n times.</span></li>
<li><span style="font-size: small;">
* 0 or more occurances</span></li>
<li><span style="font-size: small;">
+ 1 or more occurances</span></li>
<li><span style="font-size: small;">
? 0 or 1 occurance(s)</span></li>
</ul>
<h3>
<span style="font-size: small;">Legal atoms:</span></h3>
<ul>
<li><span style="font-size: small;">
. matches any character except \n</span></li>
<li><span style="font-size: small;"> A list of characters in square brackets [] is a class of characters,</span></li>
<li><span style="font-size: small;">
for example [0-9] indicates match any character from 0 to 9.</span></li>
<li><span style="font-size: small;"> \n matches newlines</span></li>
<li><span style="font-size: small;">
\t matches tabs</span></li>
<li><span style="font-size: small;">
\d matches digits [0-9]</span></li>
<li><span style="font-size: small;">
\D matches non-digits [^0-9]</span></li>
<li><span style="font-size: small;">
\w matches word characters (alphanumeric) [0-9a-z_A-Z]</span></li>
<li><span style="font-size: small;">
\W matches non-word characters [^0-9a-z_A-Z]</span></li>
<li><span style="font-size: small;">
\s matches whitespace characters [ \t\n]</span></li>
<li><span style="font-size: small;">
\S matches non-whitespace characters [^ \t\n]</span></li>
<li><span style="font-size: small;">
\b matches on “word” boundaries (between \w and \W)</span></li>
<li><span style="font-size: small;"> A backslashed x followed by two hexadecimal digits, such as \x7f,</span>matches the character having that hexadecimal value.</li>
</ul>
<span style="font-size: small;"></span><br />
<ul>
<li><span style="font-size: small;"> A backslashed 2 or 3 digit octal number such as \033 matches the </span>character with the specified value.</li>
</ul>
<span style="font-size: small;"></span><br />
<ul>
<li><span style="font-size: small;"> Any other “backslashed” character matches itself.</span></li>
</ul>
Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com1Bjelovar, Croatia45.8987972 16.84230930000001145.8104057 16.680947800000009 45.9871887 17.003670800000013tag:blogger.com,1999:blog-1683299942981536543.post-36606355336038755922013-03-08T00:00:00.000+01:002013-10-09T13:16:55.037+02:00PLSQL iso7064 MOD 97,10 check<h2>
What is iso7064?</h2>
Check <a href="http://en.wikipedia.org/wiki/ISO_7064">wikipedia</a><br />
It is standard for checking accounting number on Credit cards, Resident Identity Card etc....<br />
<br />
<h3>
Rules for checking Mod97,10 (iso7061) are:</h3>
<br />
This scheme produces two digits as a check. And as a result, it catches just about every possible error. If you can afford the extra digit, this system is superior to the dihedral check. <br />
It also has an especially compact formula. The check digits are given by <tt>mod(98 - mod(data * 100, 97), 97)</tt> and the verification is just <tt>mod(data_check,97) == 1</tt>. In practice an alternate algorithm (based on Horner's Rule) is used to avoid overflow issues. <br />
Usage: banking<br />
<br />
<span style="font-size: small;"> So PLSQL code should look something like this ....</span><br />
<blockquote class="tr_bq">
<br />
<span style="font-size: x-small;">function f_control_iso7064(i_input in varchar2) as<br /> l_input varchar2(32767) := '123456789';<br /> e_exception exception;<br /> l_control_num number(10) := 0;<br />begin<br /> -- as L_input not I<br /> l_input := trim(i_input);<br /> l_control_num := mod(98 - mod(l_input * 100, 97), 97);</span><br />
<span style="font-size: x-small;"> l_control_num:= mod(l_control_num,97); <br /> if l_control_num != 1 then</span> <span style="font-size: x-small;">return (false);<br /> else<br /> return (true); <br /> end if;<br />end f_control_iso7064; </span></blockquote>
<br />
Updated post: Had some issues with the code so changed line for check.... now it's good ;)Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com1Bjelovar, Croatia45.8987972 16.84230930000001145.8104057 16.680947800000009 45.9871887 17.003670800000013tag:blogger.com,1999:blog-1683299942981536543.post-41292270531915012502013-03-07T00:00:00.000+01:002013-10-09T13:16:22.945+02:00PLSQL what day is it?<h2>
PLSQL what day is it</h2>
Say that for some reason you need name of the day for certain reason...<br />
You can create function that will return name of the day of specified date....<br />
<br />
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">function f_name_of_the day (i_date) return varchar2 as<br />l_day varchar2(50):=null;<br />begin<br /> SELECT TO_CHAR( trunc(to_date( to_char(to_date(i_date,'YYYYMMDD')) )),'day') into l_day FROM DUAL;<br /> return(day);<br />end f_name_of_the day ;</span></blockquote>
<br />
<h3>
So what does this function do?</h3>
You enter number in date format like 20130306 which is 2013 year 03 month 06 day and function should return Wednesday value....<br />
You can manipulate with desired input value by changing format of date <br />
<span style="font-size: small;"> (to_date(i_date,'YYYYMMDD')) into lets say (to_date(i_date,'YYYYDDMM')) so that input now must be in year day month shape. </span><br />
<br />
<h2>
CODE in SQL developer</h2>
Here is code for simple developer test that will display name of the day <br />
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">declare<br />l_day varchar2(50):='20130306';<br />begin<br /> select to_char( trunc(to_date( to_char(to_date(l_day,'YYYYMMDD')) )),'day') into l_day from dual;<br /> dbms_output.put_line(l_day);<br />end;</span></blockquote>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOM682cOM2ssdYE1NCuTkTXVqwH4bal6MAoCG_m7x5Mb9OYIqrU9z92GxwcyWhaFLkZHCPsoSPb8R7opVauWkgMmB4Jj-7rJ-WLRFaKN5KE0PV-3Lp0m5wYa1GQFs6rtQ61jnuQnVj0eY/s1600/wednesday.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOM682cOM2ssdYE1NCuTkTXVqwH4bal6MAoCG_m7x5Mb9OYIqrU9z92GxwcyWhaFLkZHCPsoSPb8R7opVauWkgMmB4Jj-7rJ-WLRFaKN5KE0PV-3Lp0m5wYa1GQFs6rtQ61jnuQnVj0eY/s640/wednesday.jpg" height="345" width="640" /></a></div>
Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com3tag:blogger.com,1999:blog-1683299942981536543.post-27529624211717912132013-03-06T09:00:00.000+01:002013-10-09T13:14:28.981+02:00PLSQL numeric check<br />
<h3>
PL SQL numeric check</h3>
<br />
Assuming that you need some function to check input from the front-end and you want to assure that input is numerical data, you can do it by simple function that has one input parameter and is returning false or true...<br />
So here it goes...<br />
<br />
<blockquote class="tr_bq">
<span style="font-size: x-small;">Function F_numeric_val (i_value IN VARCHAR2) return boolean is</span><br />
<span style="font-size: x-small;">l_num NUMBER;</span><br />
<span style="font-size: x-small;">BEGIN</span><br />
<span style="font-size: x-small;"> l_num := to_number(i_value);</span><br />
<span style="font-size: x-small;"> return(true);</span><br />
<span style="font-size: x-small;">exception</span><br />
<span style="font-size: x-small;"> when others then</span><br />
<span style="font-size: x-small;"> return(false);</span><br />
<span style="font-size: x-small;">end;</span></blockquote>
<br />
So basically what this function do?<br />
You have some input on front end, let's say it is 123456A and you want to check it's number consistency.<br />
So when front-end passes you that input you call Function F_numeric_val and check its content.<br />
In this case function will return false value to input 123456A<br />
example;<br />
<br />
I have program that needs to check are there any letters from third place of input till it's end.<br />
If there are any letters I don't want to continue my code.<br />
<blockquote class="tr_bq">
<br />
<span style="font-size: x-small;">declare</span><br />
<span style="font-size: x-small;"> l_input varchar2(32767) := 'BJ1234567890 '; -- setting inputs default value</span><br />
<span style="font-size: x-small;"> l_errmsg varchar2(32767) := null;</span><br />
<span style="font-size: x-small;">begin</span><br />
<span style="font-size: x-small;">/* substr command means that you're checking some range of chars from desired sign. let's say we have input from l_input parameter. We string it with substr('BJ1234567890 ',3,32767) and we get '1234567890 ' values from third sign to end of string . If we add trim before input, it will crop spaces before and after string, but not inside the string. Substr(trim('BJ1234567890 '),3,32767) this expression would return 1234567890 value but we just want to see is there any alpha characters after third sign and we don't mind if there are any spaces so we trim them */</span></blockquote>
<blockquote class="tr_bq">
<span style="font-size: x-small;">if not f_numeric_val(substr(trim(l_input),3,32767)) then</span><br />
<span style="font-size: x-small;"> l_errmsg :=( 'You don''t have number in you''re data on right places');</span><br />
<span style="font-size: x-small;"> return;</span><br />
<span style="font-size: x-small;"> else</span><br />
<span style="font-size: x-small;"> -- do some code here :)</span><br />
<span style="font-size: x-small;"> l_errmsg := 'all ok';</span><br />
<span style="font-size: x-small;"> end if;</span><br />
<span style="font-size: x-small;">exception</span><br />
<span style="font-size: x-small;"> when others then</span><br />
<span style="font-size: x-small;"> raise_application_error(-20001,'Program stopped '||sqlcode);</span><br />
<span style="font-size: x-small;">end;</span></blockquote>
This code will return all ok message....<br />
Simple :)<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU3F9n6uK6ppTn9uybv1muDp0GLdEpXgCQXqqqZT7ga1NbwKGIpXrGQKnsFJQIz0KxoTQ0IlY0MSc-B9g1ScGI7RN_OguSk6T08gKgqHWExmq6eqy4rVOznuN9b-OnWyQp7yRIzBwpvo4/s1600/oracle.JPG" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiU3F9n6uK6ppTn9uybv1muDp0GLdEpXgCQXqqqZT7ga1NbwKGIpXrGQKnsFJQIz0KxoTQ0IlY0MSc-B9g1ScGI7RN_OguSk6T08gKgqHWExmq6eqy4rVOznuN9b-OnWyQp7yRIzBwpvo4/s320/oracle.JPG" height="58" width="320" /></a></div>
Anonymoushttp://www.blogger.com/profile/14232176430890830765noreply@blogger.com0Bjelovar, Croatia45.8987972 16.84230930000001145.8104057 16.680947800000009 45.9871887 17.003670800000013