PL SQL Developer Conditional Breakpoint

Making Conditional Breakpoint

When you make a breakpoint conditional, the debugger pauses when a certain condition is met.
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.

The Conditions tab in the Breakpoint 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 true, then the breakpoint pauses the program. If the condition evaluates to false , then the debugger does not stop at that breakpoint location.

To set a breakpoint condition:

1. Set a breakpoint on a line of code by clicking to the left of the line in the Code Editor.
2. Open the Breakpoints window by choosing V iew | Debug Windows | Breakpoints.
3. In the Breakpoints window, right-click the breakpoint you just set and choose Edit Breakpoint.
4. In the Edit Breakpoints dialog, click Conditions .
5. Enter an expression in the Condition field, for example:
1. field = 20
2. field > 10
3. field != 30
6. Click OK. 

Developer 4.0.13 breakpoint example:

put your mouse cursor over breakpoint and Box will appear

Enter some conditional breakpont value example:
   name_of_field > 31000
   if is in a cursor lets say ;
      for c1 in (select * from table)
    you would need to enter c1.name_of_field = value_to_break

and just run debugger

When condition is met popup will be shown

Read More

PLSQL Locked package

PL SQL locked package

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

SELECT
   vss.SID,
   vss.SERIAL#,
   vss.osuser,
   vss.machine,
   vs.sql_text,
   dd.*
FROM
   v$session vss,
   DBA_DDL_LOCKS dd,
   v$sql vs
WHERE
   name LIKE 'NAME_OF_YOUR_PACKAGE' and  --ime paketa
   vss.sid=dd.session_id   and
   vs.sql_id = vss.sql_id  and
   rownum < 2;

Let's say  NAME_OF_YOUR_PACKAGE is TEST_TURKI so if you run it on Oracle 11g database you will see if someone is using it...

by data that is showing we can see that user under SID 4527 and SERIAL 145 is using it.
We now can perform kill session command to get back access to that specific package.

So if you have database administrator privileges you can type 

execute kill_session(SID,SERIAL#);

in this particular case command would be;

execute kill_session(4527,145);

and you will get message that is killed or it is rolling back.

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)

One more trick to kill session is that in ORACLE SQL developer you can choose Tools.>Monitor sessions
and find user that is holding you're package

When it's opened you can find it under SID and SERIAL and right klick him with Kill session option

That's it...

Read More

Leap year function PLSQL

PL SQL Leap year function

Hi all, I didn't have time to write cause I had no time on my schedule :)
so I decided to refresh my blog with leap year calculator....

Some exceptions to this rule are required since the duration of a solar year is slightly less than 365.25 days. Over a period of four centuries, the accumulated error of adding a leap day every 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.
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.

Why do we need Leap Years?

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.

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!
Which
The year is evenly divisible by 4;
If the year can be evenly divided by 100, it is NOT a leap year, unless;
The year is also evenly divisible by 400. Then it is a leap year.

Years are Leap Years?

In the Gregorian calendar 3 criteria must be taken into account to identify leap years:

This means that 2000 and 2400 are leap years, while 1800,1900,2100,2200,2300 and 2500 are NOT leap years.

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

Why should I care about Leap Years?

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.
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....

Little function for leaping year

 -- function returns 1 for leaping year
 

FUNCTION f_leap_year (i_year VARCHAR2) RETURN NUMBER IS
v_remainder1 number(5,2);
v_remainder2 number(5,2);
v_remainder3 number(5,2);
o_value pls_integer;
begin
   v_remainder1 := mod(i_year,4);
   v_remainder2 := mod(i_year,100);
   v_remainder3 := mod(i_year,400);
   if ((v_remainder1 = 0 and v_remainder2 <> 0 ) or v_remainder3 = 0) then
      o_value := 1;
      return o_value;  
   else
      o_value := 0;
      return o_value;  
   end if;
end f_leap_year;

 enjoy
....................................................

Read More

Backup of Source Code (effective versioning without SVN)

PL SQL versioning tutorial without SVN

Have been busy lately with MSAccess coding and had no time to write something useful....
But now I manage to take some time and write ....

I had some bad experience with "loosing source code"  day after I wrote it...
Scenario: You write the code test it and Compile package
Someone else has that package opened and hit compile on it....
Next day you open your source and you see that there are no changes that you made day before...

So go with coding all over again.... :)

I decide to make some table and Trigger that will automatically execute and save my package before someone compiles it....

So let's make table where to store our changes...

CREATE TABLE "MYBASE"."BACKUP_SOURCE"
   (    "CHANGE_DATE" TIMESTAMP (6),
    "OWNER_OF" VARCHAR2(4000 BYTE),
    "PKG_NAME" VARCHAR2(4000 BYTE),
    "PKG_TYPE" VARCHAR2(4000 BYTE),
    "CONTENT" CLOB,
    "CHANGED_BY" VARCHAR2(100 BYTE)
     );

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...

Let's write Trigger that will do some BACKING-UP....
We will make AFTER CREATE trigger...

If you cannot create trigger read this (or just curious).... otherwise skip on trigger creation...

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.
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.
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.
In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.
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.
What is after trigger....

AFTER
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.
CREATE 
Specify CREATE to fire the trigger whenever a CREATE statement adds a new database object to the data dictionary.
Trigger CODE

create or replace trigger mybase.trg_backup_source
AFTER CREATE ON mybase.SCHEMA
declare
l_owner varchar2(1000) := ' ';
l_name varchar2(1000) := ' ';
l_type varchar2(1000) := ' ';
l_clob clob;
l_user varchar2(100) :=' ';

BEGIN
IF ORA_DICT_OBJ_TYPE in ('PROCEDURE', 'FUNCTION',
       'PACKAGE', 'PACKAGE BODY',
        'TYPE', 'TYPE BODY')
THEN
for c1 in
    (SELECT sysdate, all_source.* FROM ALL_SOURCE
                    where type = ORA_DICT_OBJ_TYPE
                    and name = ORA_DICT_OBJ_NAME
    )
 loop
        l_clob := l_clob||' '||c1.text;

end loop;

select
    all_source.owner,
    all_source.name,
    all_source.type
into
    l_owner,
    l_name,
    l_type
from
    all_source
where type = ORA_DICT_OBJ_TYPE
    and name = ora_dict_obj_name
    and rownum <2;
-- get username
begin
    select
       osuser
    into
       l_user 
   from
      v$session
   where
      audsid = userenv('sessionid');

exception
    when no_data_found then
         l_user := 'Unknown';
    when others then
         null;
end;

insert into backup_source values
     (systimestamp,
       l_owner,
       l_name,
       l_type,
       l_clob,
       l_user);

end if;
exception
    when no_data_found then
         null;
when others then
      raise_application_error(-20000,' ERROR IN                TRG_BACKUP_SOURCE_mybase sqlerr:'|| sqlerrm||' NUM:'||SQLcode);
end;

After that just Compile and Enable trigger and you're ready to go ....
do some compiling of some package and then select from table

SELECT * FROM BACKUP_SOURCE ORDER BY CHANGE_DATE DESC;

Every time you hit Compile it will make backup...


Enjoy
P.S.

will add how to delete source every 10 days into code when I get time.....


Happy coding

Read More

PLSQL Operators

PL SQL Operators

Relational operators compare two expressions or values and return a Boolean result. PL/SQL supports operators like

=, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN

Operator	Meaning
=	equal to
<>, !=, ~=, ^=	not equal to
<	less than
>	greater than
<=	less than or equal to
>=	greater than or equal to

Is NULL operator checks if
Example code:

declare
   a number ( 2 ) := 50 ;
   b number ( 2 ) := 15 ;
begin
   if ( a = b ) then
      dbms_output.put_line ( '1-a is equal to b' );
   else
      dbms_output.put_line ( '1-a is not equal to b' );
   end if;
   if ( a < b ) then
      dbms_output.put_line ( '2-a is less than b' );
   else
      dbms_output.put_line ( '2-a is not less than b' );
   end if ;
   if ( a > b ) then
      dbms_output.put_line ( '3-a is greater than b' );
   else
      dbms_output.put_line ( '3-a is not greater than b' );
   end if ;
   -- Lets change value  a and b
   a      := 40 ;
   b      := 90 ;
   if ( a <= b ) then
      dbms_output.put_line ( '4-a is either equal or less than b' );
   end if ;
   if ( b >= a ) then
      dbms_output.put_line ( '5-b is either equal or greater than a' );
   end if ;
   if ( a <> b ) then
      dbms_output.put_line ( '6-a is not equal to b' );
   else
      dbms_output.put_line ( '6-a is equal to b' );
   end if ;
end;

Output:



IS NULL Operator

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


LIKE Operator

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.

The patterns matched by LIKE can include two special-purpose characters underscore _ matches one character and percent sign % matches zero or more characters
example:
select var_name from table_names where var_name LIKE 'JOHN%';

you will get all names starting with JOHN like JOHN, JOHNSON,JOHNATHON etc...

BETWEEN Operator

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."
Example:
If you want to select some record between some input date and system date

Select * from SOME_TABLE where to_char(beginning_date,'yyyymmdd') between to_char(i_first_date,'yyyymmdd') and to_char(sysdate,'yyyymmdd')

IN Operator

The IN operator tests set membership. It means "equal to any member of
 select * from table names where var_name IN ('JOHN','ERIC');
You will get all records containing names of JOHN and ERIC...

Read More

PLSQL Modulo 11 check...

What is Modulo 11 check?

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. 

A typical MSI bar code is

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.

COMPUTING THE CHECKSUM DIGIT MSI 

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).

The most common methods of calculating MSI check digits are: Modulo 10 and Modulo 11


Modulo 11 is what we want which is developed by IBM company based on Lhun algorithm...

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.
Steps to calculate the MOD11 check digit for a number such as an id #:

• 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.
• Each digit in the id # is multiplied by its weight
• The results of the multiplication are added together
• This product is divided by the modulus number 11
• The remainder is subtracted from the modulus number 11 giving the check digit

Example: find the check digit for the number 036532

0	3	6	5	3	2
x7	x6	x5	x4	x3	x2
0	18	30	20	9	4

0 + 18 + 30 + 20 + 9 + 4 = 81
81/11 = 7 remainder 4
11 - 4 = 7
7 is therefore the check digit.
PROBLEMS: 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.
Steps to verify if the check digit is included as part of the number:

• The entire number is multiplied by the same weights that were used to calculate and the check digit itself is multiplied by 1.
• The results of the multiplication are added together.
• The sum is divided by 11 and if the remainder is 0, the number is correct.

PROBLEM: Note that if the check digit is X then 10 is used in the multiplication. Code for this occurrence must be included.
Example of verifying the number 0365327 where 7 is the calculated MOD11 check digit:

0	3	6	5	3	2	7
x7	x6	x5	x4	x3	x2	x1
0	18	30	20	9	4	7

0 + 18 + 30 + 20 + 9 + 4 + 7 = 88
88/11 is 8 remainder 0
Since the remainder from this calculation is 0, the check digit 7 is valid.

So without any other complications here is the PL code...

procedure modulo_11 (l_input in varchar2) as
l_cntrl_num_sum number(1):= 0;
l_pom varchar2(30):=' ';
l_length number(30):= 0;
l_length_pom number(30):= 0;
l_sum number(30):= 0;
l_error number(2) := 0;
----
l_counter number(30);
l_sum1 number(30):=0;
l_sum_uk number(30):=0;
l_reminder number(30) := 0;
l_control_number number(30) := 0;
l_counter1 number(2) := 0;
l_errmsg varchar2(200) := ' ';
e_exit exception;
begin

-- test input

--l_input  := '102305789016';
   -- determine length of input num
   l_length := length(l_input);
   -- take last digit for control
   l_cntrl_num_sum := substr(l_input,l_length,1);
   -- set length -1 sign
   -- take length in some local variable
   l_length_pom := l_length;
   -- set variable counters to zero
   l_counter := 0;
   l_reminder := 0;
   l_control_number := 0;
   l_counter :=0;
   -- counter to 1
   l_counter1 := 1;
   l_pom := null;
   -- reversing string input
   select reverse(l_input) into l_pom from dual;
   -- taking second place till last in local var which is used for calculating
   l_pom := substr(l_pom,2,l_length_pom - 1);
   l_length := length(l_pom);
   if l_length is null then
      l_error := 2;
      raise e_exit;
   end if;
   -- starting algorithm
   for k in 1..l_length
      loop
         l_sum := 0;
         l_sum1 :=0;
         l_counter := l_counter + 1;
         l_counter1 :=  l_counter1 + 1;
         l_sum := l_counter1 * substr(l_pom,l_counter,1);
         l_sum1 := l_sum;        
         l_sum_uk := l_sum_uk + l_sum1;
         l_length_pom := l_length_pom - 1;
      end loop;
   l_length := length(l_input) - 1;
   l_reminder := mod(l_sum_uk,11);
   l_control_number := 11 - l_reminder;
   if l_reminder in (0, 1) then
      l_control_number := 0;
   end if;
   if l_control_number != l_cntrl_num_sum then
      l_error := 2;
      l_errmsg := ('error in MOD_11_INI :');
      raise e_exit;
   end if;
exception
   when e_exit then
      raise_application_error(-20010,l_errmsg||' '||sqlcode);
  END modulo_11;

enjoy.....

text sources: 

http://www.pgrocer.net/Cis51/mod11.html

http://en.wikipedia.org/wiki/Luhn_algorithm

http://en.wikipedia.org/wiki/MSI_Barcode

Read More

PLSQL backup time...

PL SQL package backup tutorial

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...
Second i wrote it because there is Bug in SQL DEVELOPER that causes lines to be messed up  when you have large code...
Example:

You have large code and export it with right click on package name and select save specs and body

Real code is written like this...

1 line - BEGIN
2 LINE - SELECT RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))
3 line - INTO L_INPUTNAME
4 line - FROM V$INSTANCE ;
5 line - EXCEPTION WHEN OTHERS THEN
6 LINE - NULL;
7 line - END;

but sometimes export does the weird thing and saves it messed up
switching the line order....

1 line - BEGIN
2 line - SELECT RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))
4 line - FROM V$INSTANCE ;
3 line - INTO L_INPUTNAME
5 line - EXCEPTION WHEN OTHERS THEN
6 line - NULL;
7 line - END;

I googled this program somewhere on the web two years ago and manage to rewrite it for my needs...
So without any chit chat let's see the code

CREATE OR REPLACE
PROCEDURE BACKUP_OF_PL_FILES
    (I_TIPE_OF_OBJECT    VARCHAR2
    ,I_NAME_OF_OBJECT    VARCHAR2 DEFAULT '%')
IS
    PL_OUTPUT    UTL_FILE.FILE_TYPE;
    L_READ UTL_FILE.FILE_TYPE;
    L_BUFFER    VARCHAR2(4000);
    G_SESSONID    VARCHAR2(80);
    L_LINE        VARCHAR2(80);
    L_COUNTER        NUMBER(30) :=1 ;
    L_SAVE VARCHAR2(32767);
    L_INPUTNAME    VARCHAR2(20);
    L_ERRNUM    NUMBER := 0;
    L_DIRNAME VARCHAR2(100) := ' ';
BEGIN
  
   G_SESSONID := USERENV('sessionid');
   L_DIRNAME := 'BACKUP_PL/'||I_NAME_OF_OBJECT||'/'||TO_CHAR(TO_DATE(SYSDATE,'dd.mm.yy'),'yyyymmdd')||'/';
   -----------------------------------------------------------------------------
  
    BEGIN
        SELECT    RTRIM(SUBSTR(INSTANCE_NAME,1,8))||'-'||RTRIM(SUBSTR(HOST_NAME,1,15))
        INTO    L_INPUTNAME
        FROM    V$INSTANCE ;
    EXCEPTION WHEN OTHERS THEN
        NULL;
    END;

    IF I_NAME_OF_OBJECT='%' THEN
        G_SESSONID    := 'BAK_'||INITCAP(I_TIPE_OF_OBJECT)||'_'||L_INPUTNAME||'_'||TO_CHAR(SYSDATE, 'DDMMYY_HH24MI') || '.pls';
    ELSE
        G_SESSONID    := 'BAK_'||INITCAP(I_NAME_OF_OBJECT)||'_'||L_INPUTNAME||'_'||TO_CHAR(SYSDATE, 'DDMMYY_HH24MI') || '.pls';
    END IF;   
  
   PL_OUTPUT := UTL_FILE.FOPEN('c:\BACKUP', G_SESSONID,'A',4000);      
   UTL_FILE.PUT_LINE(PL_OUTPUT, 'c:\BACKUP' || L_DIRNAME || G_SESSONID);

    FOR RS_TYPE IN (SELECT    DISTINCT TYPE, NAME
            FROM    USER_SOURCE
            WHERE    TYPE LIKE UPPER(I_TIPE_OF_OBJECT)||'%'
            AND    NAME LIKE UPPER(I_NAME_OF_OBJECT)
            ORDER BY 2,1 )
    LOOP
        FOR RS_NAME IN (SELECT    TEXT
                FROM    USER_SOURCE
                WHERE    TYPE = RS_TYPE.TYPE
                AND    NAME = RS_TYPE.NAME )
        LOOP
            BEGIN
                IF (L_COUNTER = 1 OR UPPER(RS_NAME.TEXT) LIKE '%PACKAGE BODY%') THEN
                    L_BUFFER := 'CREATE OR REPLACE '||RS_NAME.TEXT ;
                ELSE
                    L_BUFFER := RTRIM(RS_NAME.TEXT,' ');
                END IF;
                IF SUBSTR(L_BUFFER,-1,1) = ';' THEN
                    UTL_FILE.PUT_LINE(PL_OUTPUT,RTRIM(L_BUFFER));
                ELSE
                    UTL_FILE.PUT_LINE(PL_OUTPUT,RTRIM(L_BUFFER,SUBSTR(L_BUFFER,-1,1)));
                END IF;
                L_COUNTER := L_COUNTER + 1 ;
           
            EXCEPTION WHEN OTHERS THEN
                L_ERRNUM :=1 ;
                DBMS_OUTPUT.PUT_LINE('error 2nd loop...'||SQLERRM);
            END ;
        END LOOP;
        L_COUNTER := 1;
        UTL_FILE.PUT_LINE(PL_OUTPUT,'/');              
        IF RS_TYPE.TYPE NOT LIKE '%PACKAGE%' OR RS_TYPE.TYPE LIKE '%PACKAGE BODY%' THEN
          UTL_FILE.PUT_LINE(PL_OUTPUT,L_LINE);   
        END IF;
    END LOOP;
    UTL_FILE.FCLOSE(PL_OUTPUT);
   L_READ := UTL_FILE.FOPEN('PL_OUT', G_SESSONID,'A',4000);
   ----------------------
EXCEPTION WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error... '||SQLCODE);
    UTL_FILE.FCLOSE(PL_OUTPUT);
END BACKUP_OF_PL_FILES;

 
Basically this procedure is searching database same as export and write package in your local directory c:\BACKUP

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

BEGIN
   BACKUP_OF_PL_FILES('package','MY_PACKAGE_NAME_PKG');
   BACKUP_OF_PL_FILES('package','MY_PACKAGE_NAME_PKG');
   BACKUP_OF_PL_FILES('procedure','MY_PROCEDURE_NAME);
   BACKUP_OF_PL_FILES('function','MY_FUNCTION_NAME');
END;

Read More