Dla zwykłego człowieka śmierć jest czymś realnym i może przyjść w każdej chwili…

Dla zwykłego człowieka śmierć jest czymś realnym i może przyjść w każdej chwili, bez ostrzeżenia. Marnowanie swego cennego ludzkiego życia w trakcie banalnych zajęć jest tragedią.

Jedynie praktyka prowadzi do własnego urzeczywistnienia i wyłącznie poprzez swą własną realizację można ostatecznie pomóc innym, przejawiając zdolności prowadzenia ich tak, aby sami osiągnęli taki sam stan. Jakakolwiek pomoc materialna, którą możemy ofiarować, jest zawsze tylko czymś prowizorycznym.

Aby móc pomagać innym, trzeba zacząć od pomagania samemu sobie, bez względu na to, jak paradoksalnie może to brzmieć. Aby doliczyć do miliona, trzeba zacząć od jedynki. Tak samo, aby pomóc społeczeństwu, trzeba zacząć od pracy nad samym sobą. Każda jednostka powinna w pełni ponosić odpowiedzialność za siebie, co można osiągnąć wyłącznie poprzez pracę nad poszerzeniem własnej świadomości, aby stać się w coraz pełniejszym stopniu uważnym, by coraz bardziej być mistrzem samego siebie.

― Kryształ i ścieżka światła – Namkhai Norbu Rinpocze

Hits: 102

SQL owa_util.get_procedure and $$PLSQL_UNIT

In 10g and 11g I use the “owa_util.get_procedure” function. I normally use this in packages as it will also return the name of an internal procedure or function as part of the package name, i.e. (package_name).(procedure name). I use this to provide a generic EXCEPTION template for identifying where an exception occured.

CREATE OR REPLACE procedure some_procedure is
    v_procedure_name varchar2(32);
begin
    v_procedure_name := owa_util.get_procedure;
end;

CREATE OR REPLACE PACKAGE some_package
AS
    FUNCTION v_function_name
    RETURN DATE;
END;
/
CREATE OR REPLACE PACKAGE BODY some_package
AS
    FUNCTION v_function_name
    RETURN DATE
    IS
    BEGIN
        RETURN SYSDATE;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('ERROR IN '||owa_util.get_procedure);
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
END;
/

Source: https://stackoverflow.com/questions/286549/find-out-name-of-pl-sql-procedure#286569

alter session set plscope_settings='identifiers:all'
/
Session altered.

SQL> set serverout on

SQL> create or replace package pkg
as
   procedure p1;

   procedure p2;
end pkg;
/
Package created.

SQL> create or replace package body pkg
as
   procedure get_scope (obj varchar2, line int)
   as
   begin
      for c in (select rpad (lpad (' ', 2 * (level - 1)) || name, 20, '.') || ' ' || rpad (type, 20) || rpad (usage, 20) identifier_usage_contexts
                from user_identifiers t
                where level != 1
                start with line = get_scope.line and object_name = obj
                connect by usage_id = prior usage_context_id)
      loop
         dbms_output.put_line (c.identifier_usage_contexts);
      end loop;
   end get_scope;

   procedure p1
   as
   begin
      null;
   end p1;


   procedure p2
   as
   begin
      get_scope ($$plsql_unit, $$plsql_line);
   end p2;
end pkg;

Return information about witch module call this code.

CREATE OR REPLACE FUNCTION MIBP_OWNER.FN_WHO_AM_I (p_lvl NUMBER DEFAULT 0)
    RETURN VARCHAR2 IS
    /***********************************************************************************************
    FN_WHO_AM_I returns the full ORACLE name of your object including schema and package names
    --
    FN_WHO_AM_I(0) - returns the name of your object
    FN_WHO_AM_I(1) - returns the name of calling object
    FN_WHO_AM_I(2) - returns the name of object, who called calling object
    etc., etc., etc.... Up to to he highest level
    -------------------------------------------------------------------------------------------------
    Copyrigth GARBUYA 2010
    *************************************************************************************************/
    TYPE str_varr_t IS VARRAY (2) OF CHAR (1);

    TYPE str_table_t IS TABLE OF VARCHAR2 (256);

    TYPE num_table_t IS TABLE OF NUMBER;

    v_stack        VARCHAR2 (2048) DEFAULT UPPER (DBMS_UTILITY.format_call_stack);
    v_tmp_1        VARCHAR2 (1024);
    v_tmp_2        VARCHAR2 (1024);
    v_pkg_name     VARCHAR2 (32);
    v_obj_type     VARCHAR2 (32);
    v_owner        VARCHAR2 (32);
    v_idx          NUMBER := 0;
    v_pos1         NUMBER := 0;
    v_pos2         NUMBER := 0;
    v_line_nbr     NUMBER := 0;
    v_blk_cnt      NUMBER := 0;
    v_str_len      NUMBER := 0;
    v_bgn_cnt      NUMBER := 0;
    v_end_cnt      NUMBER := 0;
    it_is_comment  BOOLEAN := FALSE;
    it_is_literal  BOOLEAN := FALSE;
    v_literal_arr  str_varr_t := str_varr_t ('''', '"');
    v_blk_bgn_tbl  str_table_t
                       := str_table_t (' IF '
                                      ,' LOOP '
                                      ,' CASE '
                                      ,' BEGIN ');
    v_tbl          str_table_t := str_table_t ();
    v_blk_bgn_len_tbl num_table_t := num_table_t ();
BEGIN
    v_stack     := SUBSTR (v_stack, INSTR (v_stack, CHR (10), INSTR (v_stack, 'FN_WHO_AM_I')) + 1) || 'ORACLE'; -- skip myself

    FOR v_pos2 IN 1 .. p_lvl LOOP -- advance to the input level
        v_pos1      := INSTR (v_stack, CHR (10));
        v_stack     := SUBSTR (v_stack, INSTR (v_stack, CHR (10)) + 1);
    END LOOP;

    v_pos1      := INSTR (v_stack, CHR (10));

    IF v_pos1 = 0 THEN
        RETURN (v_stack);
    END IF;

    v_stack     := SUBSTR (v_stack, 1, v_pos1 - 1); -- get only current level
    v_stack     := TRIM (SUBSTR (v_stack, INSTR (v_stack, ' '))); -- cut object handle
    v_line_nbr  := TO_NUMBER (SUBSTR (v_stack, 1, INSTR (v_stack, ' ') - 1)); -- get line number
    v_stack     := TRIM (SUBSTR (v_stack, INSTR (v_stack, ' '))); -- cut line number
    v_pos1      := INSTR (v_stack, ' BODY');

    IF v_pos1 = 0 THEN
        RETURN (v_stack);
    END IF;

    v_pos1      := INSTR (v_stack, ' ', v_pos1 + 2); -- find end of object type
    v_obj_type  := SUBSTR (v_stack, 1, v_pos1 - 1); -- get object type
    v_stack     := TRIM (SUBSTR (v_stack, v_pos1 + 1)); -- get package name
    v_pos1      := INSTR (v_stack, '.');
    v_owner     := SUBSTR (v_stack, 1, v_pos1 - 1); -- get owner
    v_pkg_name  := SUBSTR (v_stack, v_pos1 + 1); -- get package name
    v_blk_cnt   := 0;
    it_is_literal := FALSE;

    --
    FOR v_idx IN v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST LOOP
        v_blk_bgn_len_tbl.EXTEND (1);
        v_blk_bgn_len_tbl (v_blk_bgn_len_tbl.LAST) := LENGTH (v_blk_bgn_tbl (v_idx));
    END LOOP;

    --
    FOR src IN (
                   SELECT ' ' || REPLACE (TRANSLATE (UPPER (text), ';(' || CHR (10), '   '), '''''', ' ') || ' ' text
                   FROM all_source
                   WHERE     owner = v_owner
                         AND name = v_pkg_name
                         AND TYPE = v_obj_type
                         AND line < v_line_nbr
                   ORDER BY line
               ) LOOP
        v_stack     := src.text;

        IF it_is_comment THEN
            v_pos1      := INSTR (v_stack, '*/');

            IF v_pos1 > 0 THEN
                v_stack     := SUBSTR (v_stack, v_pos1 + 2);
                it_is_comment := FALSE;
            ELSE
                v_stack     := ' ';
            END IF;
        END IF;

        --
        IF v_stack != ' ' THEN
            --
            v_pos1      := INSTR (v_stack, '/*');

            WHILE v_pos1 > 0 LOOP
                v_tmp_1     := SUBSTR (v_stack, 1, v_pos1 - 1);
                v_pos2      := INSTR (v_stack, '*/');

                IF v_pos2 > 0 THEN
                    v_tmp_2     := SUBSTR (v_stack, v_pos2 + 2);
                    v_stack     := v_tmp_1 || v_tmp_2;
                ELSE
                    v_stack     := v_tmp_1;
                    it_is_comment := TRUE;
                END IF;

                v_pos1      := INSTR (v_stack, '/*');
            END LOOP;

            --
            IF v_stack != ' ' THEN
                v_pos1      := INSTR (v_stack, '--');

                IF v_pos1 > 0 THEN
                    v_stack     := SUBSTR (v_stack, 1, v_pos1 - 1);
                END IF;

                --
                IF v_stack != ' ' THEN
                    FOR v_idx IN v_literal_arr.FIRST .. v_literal_arr.LAST LOOP
                        v_pos1      := INSTR (v_stack, v_literal_arr (v_idx));

                        WHILE v_pos1 > 0 LOOP
                            v_pos2      := INSTR (v_stack, v_literal_arr (v_idx), v_pos1 + 1);

                            IF v_pos2 > 0 THEN
                                v_tmp_1     := SUBSTR (v_stack, 1, v_pos1 - 1);
                                v_tmp_2     := SUBSTR (v_stack, v_pos2 + 1);
                                v_stack     := v_tmp_1 || v_tmp_2;
                            ELSE
                                IF it_is_literal THEN
                                    v_stack     := SUBSTR (v_stack, v_pos1 + 1);
                                    it_is_literal := FALSE;
                                ELSE
                                    v_stack     := SUBSTR (v_stack, 1, v_pos1 - 1);
                                    it_is_literal := TRUE;
                                END IF;
                            END IF;

                            v_pos1      := INSTR (v_stack, v_literal_arr (v_idx));
                        END LOOP;
                    END LOOP;

                    --
                    IF v_stack != ' ' THEN
                        WHILE INSTR (v_stack, '  ') > 0 LOOP
                            v_stack     := REPLACE (v_stack, '  ', ' ');
                        END LOOP;

                        v_stack     := REPLACE (v_stack, ' END IF ', ' END ');
                        v_stack     := REPLACE (v_stack, ' END LOOP ', ' END ');

                        --
                        IF v_stack != ' ' THEN
                            v_stack     := ' ' || v_stack;
                            v_pos1      := INSTR (v_stack, ' FUNCTION ') + INSTR (v_stack, ' PROCEDURE ');

                            IF v_pos1 > 0 THEN
                                v_obj_type  := TRIM (SUBSTR (v_stack, v_pos1 + 1, 9)); -- get object type
                                v_stack     := TRIM (SUBSTR (v_stack, v_pos1 + 10)) || '  '; -- cut object type
                                v_stack     := SUBSTR (v_stack, 1, INSTR (v_stack, ' ') - 1); -- get object name
                                v_tbl.EXTEND (1);
                                v_tbl (v_tbl.LAST) := v_obj_type || ' ' || v_owner || '.' || v_pkg_name || '.' || v_stack;
                            END IF;

                            --
                            v_pos1      := 0;
                            v_pos2      := 0;
                            v_tmp_1     := v_stack;
                            v_tmp_2     := v_stack;

                            FOR v_idx IN v_blk_bgn_tbl.FIRST .. v_blk_bgn_tbl.LAST LOOP
                                v_str_len   := NVL (LENGTH (v_tmp_1), 0);
                                v_tmp_1     := REPLACE (v_tmp_1, v_blk_bgn_tbl (v_idx), NULL);
                                v_bgn_cnt   := NVL (LENGTH (v_tmp_1), 0);
                                v_pos1      := v_pos1 + (v_str_len - v_bgn_cnt) / v_blk_bgn_len_tbl (v_idx);
                                v_str_len   := NVL (LENGTH (v_tmp_2), 0);
                                v_tmp_2     := REPLACE (v_tmp_2, ' END ', NULL);
                                v_end_cnt   := NVL (LENGTH (v_tmp_2), 0);
                                v_pos2      := v_pos2 + (v_str_len - v_end_cnt) / 5; --- 5 is the length(' END ')
                            END LOOP;

                            IF v_pos1 > v_pos2 THEN
                                v_blk_cnt   := v_blk_cnt + 1;
                            ELSIF v_pos1 < v_pos2 THEN
                                v_blk_cnt   := v_blk_cnt - 1;

                                IF     v_blk_cnt = 0
                                   AND v_tbl.COUNT > 0 THEN
                                    v_tbl.DELETE (v_tbl.LAST);
                                END IF;
                            END IF;
                        END IF;
                    END IF;
                END IF;
            END IF;
        END IF;
    END LOOP;

    RETURN CASE v_tbl.COUNT
               WHEN 0 THEN
                   'UNKNOWN'
               ELSE
                   v_tbl (v_tbl.LAST)
           END;
END;
/

Show errors;

Other good example use new features Oracle 10g

DECLARE
	local_exception EXCEPTION;
	FUNCTION nested_local_function
	RETURN BOOLEAN IS
		retval BOOLEAN := FALSE;
BEGIN
	RAISE local_exception;
	RETURN retval;
	END;
BEGIN
IF nested_local_function THEN
	dbms_output.put_line('No raised exception');
END IF;
EXCEPTION
WHEN others THEN
	dbms_output.put_line('DBMS_UTILITY.FORMAT_CALL_STACK');
	dbms_output.put_line('------------------------------');
	dbms_output.put_line(dbms_utility.format_call_stack);
	dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_BACKTRACE');
	dbms_output.put_line('-----------------------------------');
	dbms_output.put_line(dbms_utility.format_error_backtrace);
	dbms_output.put_line('DBMS_UTILITY.FORMAT_ERROR_STACK');
	dbms_output.put_line('-------------------------------');
	dbms_output.put_line(dbms_utility.format_error_stack);
END;
/
This script produces the following output:
DBMS_UTILITY.FORMAT_CALL_STACK
------------------------------
----- PL/SQL Call Stack -----
object line object
handle number
name
20909240 18 anonymous block
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
-----------------------------------
ORA-06512: at line 7
ORA-06512: at line 11
DBMS_UTILITY.FORMAT_ERROR_STACK
-------------------------------
ORA-06510: PL/SQL: unhandled user-defined exception

Hits: 586

Oracle SQL: LAG Oracle 11g is analytic function

Very usefully new features in Oracle 11g is analytic Oracle SQL function LAG.

SQL query LAG

This code find change in address post code in journal table:

  SELECT  *
    FROM  (
              SELECT  t1.SITE_NUMBER
                     ,t1.JN_DATETIME
                     ,t1.ADR_POSTCODE
                     ,LAG (t1.ADR_POSTCODE, 1) OVER (PARTITION BY t1.SITE_NUMBER ORDER BY t1.jn_datetime) AS PREV_ADR_POSTCODE
                FROM  t1 
          )
   WHERE      adr_postcode = '00-001'
          AND PREV_ADR_POSTCODE <> '00-001'
          AND PREV_ADR_POSTCODE <> '00-000'
ORDER BY  SITE_NUMBER;

Test query data

CREATE TABLE T1
(
  SITE_NUMBER   VARCHAR2(16 BYTE),
  JN_DATETIME   DATE,
  ADR_POSTCODE  VARCHAR2(20 BYTE)
)
LOGGING 
NOCOMPRESS 
MONITORING;

SET DEFINE OFF;
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('08/01/2017 15:01:31', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20002', TO_DATE('04/20/2017 11:59:29', 'MM/DD/YYYY HH24:MI:SS'), '00-002');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('06/15/2017 04:02:41', 'MM/DD/YYYY HH24:MI:SS'), '00-003');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20004', TO_DATE('06/20/2017 20:15:13', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('12/10/1929 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('09/25/2091 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-003');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('09/14/1914 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('03/25/1911 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
Insert into KPREISKORN.T1
   (SITE_NUMBER, JN_DATETIME, ADR_POSTCODE)
 Values
   ('20001', TO_DATE('11/22/2029 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), '00-001');
COMMIT;

SQL result

site_number jn_datetime adr_postcode prev_adr_postcode
20001 2017-08-01 15:01:31 00-001 00-003

How it works together

http://sqlfiddle.com/#!4/9a676b/1

Hits: 24

Python rename special characters in filename

Problem

When upload files to One Drive i have problem with files contain special characters. Below you found Python script witch remove any special characters form filenames. Change

lv_path = "D:\\Netbeans Projects"

and enjoy!

Solution

rename spec characters (Python 3+, W7)
#!/usr/bin/env python
#
# wychwytywanie i zmiana nazwy plików
#
import os
import string
import re
import sys

delchars = ''.join(c for c in map(chr, range(256)) if not c.isalnum())

# paths = (os.path.join(root, filename)
#          for root, _, filenames in os.walk('.')
#          for filename in filenames)

# print(paths)
lv_path = "D:\\Netbeans Projects"

paths = (os.path.join(root, filename)
         for root, _, filenames in os.walk(lv_path)
         for filename in filenames)

print ("Search at " + lv_path)

for path in paths:
    newname = path.replace('#', '')
    newname = newname.replace('%', '')
    newname = newname.replace('*', '')
    newname = newname.replace('<', '')
    newname = newname.replace('>', '')
    newname = newname.replace('*', '')
    newname = newname.replace('?', '')
    newname = newname.replace('$', '')
    #newname = newname.replace('!', '')
    #newname = path.replace('\'', '-')
    newname = newname.replace('"', '')
    newname = newname.replace('\'', '')
    newname = path.replace('Å', 'ś')
    newname = path.replace('Å', 'ń')
    #newname = path.translate(str.maketrans("","",delchars))

    #re.sub('[^\w\-_\. ]', '_', newname)

    if newname != path:
        # print(path)
        print(os.path.dirname(path.encode('utf8').decode(sys.stdout.encoding)) + "\t" + os.path.basename(path.encode('utf8').decode(sys.stdout.encoding)) +
              "\t\t\t -> " + os.path.basename(newname.encode('utf8').decode(sys.stdout.encoding)))
        os.rename(path, newname)

Source(s)

Hits: 1095

PL/SQL Errors: ORA-04061: existing state of package has been invalidated

I suspect you’re only reporting the last error in a stack like this:

ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "schema.package" has been invalidated
ORA-04065: not executed, altered or dropped package body "schema.package"
ORA-06508: PL/SQL: could not find program unit being called: "schema.package"

If so, that’s because your package is stateful:

The values of the variables, constants, and cursors that a package declares (in either its specification or body) comprise its package state. If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful; otherwise, it is stateless.

When you recompile the state is lost:

If the body of an instantiated, stateful package is recompiled (either explicitly, with the “ALTER PACKAGE Statement”, or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.

After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it…

You can’t avoid this if your package has state. I think it’s fairly rare to really need a package to be stateful though, so you should revisit anything you have declared in the package, but outside a function or procedure, to see if it’s really needed at that level. Since you’re on 10g though, that includes constants, not just variables and cursors.

But the last paragraph from the quoted documentation means that the next time you reference the package in the same session, you won’t get the error and it will work as normal (until you recompile again).

As of Oracle Database 11g Release 2 (11.2.0.2), Oracle Database treats a package as stateless if its state is constant for the life of a session (or longer). This is the case for a package whose items are all compile-time constants.

A compile-time constant is a constant whose value the PL/SQL compiler can determine at compilation time. A constant whose initial value is a literal is always a compile-time constant. A constant whose initial value is not a literal, but which the optimizer reduces to a literal, is also a compile-time constant. Whether the PL/SQL optimizer can reduce a nonliteral expression to a literal depends on optimization level. Therefore, a package that is stateless when compiled at one optimization level might be stateful when compiled at a different optimization level. For information about the optimizer, see “PL/SQL Optimizer”.

Each session that references a package item has its own instantiation of that package. If the package is stateful, the instantiation includes its state. The package state persists for the life of a session, except in these situations:

  • The package is SERIALLY_REUSABLE.For details, see “SERIALLY_REUSABLE Packages”.
  • The package body is recompiled.If the body of an instantiated, stateful package is recompiled (either explicitly, with the “ALTER PACKAGE Statement”, or implicitly), the next invocation of a subprogram in the package causes Oracle Database to discard the existing package state and raise the exception ORA-04068.After PL/SQL raises the exception, a reference to the package causes Oracle Database to re-instantiate the package, which re-initializes it. Therefore, previous changes to the package state are lost. (For information about initialization, see “Package Instantiation and Initialization”.)
  • Any of the session’s instantiated packages are invalidated and revalidated.All of a session’s package instantiations (including package states) can be lost if any of the session’s instantiated packages are invalidated and revalidated. For information about invalidation and revalidation of schema objects, see Oracle Database Advanced Application Developer’s Guide.

It sounds like what you want is to be able to list all packages that may potentially have state.

What you’re looking for is just packages that have any global variables or constants. For a single package, this is quite simple by inspection. To look across all packages in a schema, however, you could use PL/Scope:

First, log in as the schema owner, turn on PL/Scope in your session:

alter session set plscope_settings='IDENTIFIERS:ALL';

Then, recompile all your package bodies.

Then, run this query to find all the variables and constants declared at the package level:

select object_name AS package,
       type,
       name AS variable_name
from user_identifiers
where object_type IN ('PACKAGE','PACKAGE BODY') and usage = 'DECLARATION' and type in ('VARIABLE','CONSTANT') and usage_context_id in ( select usage_id
  from user_identifiers
  where type = 'PACKAGE' );

I’d suggest the resulting list of packages will be your target.

If you’re on 11gR2, constants no longer cause this problem, so you’d use this query instead:

select object_name AS package,
       type,
       name AS variable_name
from user_identifiers
where object_type IN ('PACKAGE','PACKAGE BODY') and usage = 'DECLARATION' and type = 'VARIABLE' and usage_context_id in ( select usage_id
  from user_identifiers
  where type = 'PACKAGE' );

Source

  1. Annals of Oracle’s Improbable Errors: ORA-04061: existing state of package has been invalidated
  2. https://stackoverflow.com/questions/19376440/ora-06508-pl-sql-could-not-find-program-unit-being-called/19380746

Hits: 1152

PLSQL NOT_DATA_FOUND and CURSORS

Here we are common problem in PLSQL. We try find answer for one common question in programing databases. Is the specyfic record exist in other table or not exist? Some examples below show how to menage this problem:

NOT_DATA_FOUND Example
DECLARE
    VAR_SUPP_NM VARCHAR2(100);
    VAR_SUPP_ID  NUMBER := 1;
    WHILE_VAR CHAR := 'Y';
BEGIN
  SELECT SUPP_NM
    INTO VAR_SUPP_NM
    FROM TEST.SUPPLIER
   WHERE SUPP_ID = VAR_SUPP_ID;

 DBMS_OUTPUT.PUT_LINE('DATA FOUND');

exception
  when no_data_found then 
  	-- here some code when data ont exist
	DBMS_OUTPUT.PUT_LINE('SQL DATA NOT FOUND');

END;

In line 11 and 14 we have place to insert own code. Other solution we found when we use define explicit cursor:

Explicit Cursor Used to Fetch Just One Value
CREATE FUNCTION f_get_name (ip_emp_id IN NUMBER) RETURN VARCHAR2
AS
CURSOR c IS SELECT ename FROM emp WHERE emp_id = f_get_name.ip_emp_id;
lv_ename emp.ename%TYPE;
BEGIN
  OPEN c;
  FETCH c INTO lv_ename;
  IF (SQL%NOTFOUND) THEN
     RAISE NO_DATA_FOUND;
  ENDIF;
  FETCH c INTO lv_ename;
  IF (SQL%FOUND) THEN
    RAISE TOO_MANY_ROWS;
  ENDIF;
  CLOSE c;
  RETURN lv_ename;
END;

Explicit Cursors and Bulk Processing
CREATE OR REPLACE PROCEDURE refresh_store_feed AS
TYPE prod_array IS TABLE OF store_products%ROWTYPE INDEX BY BINARY_INTEGER;
l_prod prod_array;
CURSOR c IS
  SELECT product
    FROM listed_products@some_remote_site;
BEGIN
  OPEN C;
  LOOP
    FETCH C BULK COLLECT INTO l_prod LIMIT 100;
  FOR i IN 1 .. l_csi.COUNT
    LOOP
     /* ... do some procedural code here that cannot be done in SQL to l_csi(i) ... */
    END LOOP;
  FORALL i IN 1 .. l_csi.COUNT
    INSERT INTO store_products (product) VALUES (l_prod(i));
    EXIT WHEN c%NOTFOUND;
  END LOOP;
CLOSE C;
END;
/

REF Cursors in Brief

Though there are two other sections in this chapter that cover REF cursors more fully (“Static REF Cursors” and “Dynamic REF Cursors”), a brief overview regarding these types of cursors warrants mention in this section due to their structure: they must be explicitly opened, fetched from, and closed. If you are returning a result set to a client, a great way to avoid lots of repetitive code is to use a REF cursor. Essentially, the cursor you open usually depends on what input you receive from the requesting client. Listing 10-4 provides a brief example of a REF cursor scenario in PL/SQL:

REF Cursors Work Dynamically, But Are Declared

DECLARE
prod_cursor sys_refcursor;
BEGIN
	IF ( input_param = 'C' )
	THEN
		OPEN prod_cursor FOR
		SELECT * FROM prod_concepts
			WHERE concept_type = 'COLLATERAL'
				AND concept_dt < TO_DATE( '01-JAN-2003', 'DD-MON-YYYY');
	ELSE
		OPEN prod_cursor FOR
		SELECT * FROM prod_concepts
			WHERE concept_category = 'ADVERTISING';
	END IF;
LOOP

A Select … Into Cursor vs. a For … In Cursor
BEGIN
    FOR x IN (SELECT * FROM dual) LOOP ... END LOOP;
	END;
DECLARE
	CURSOR c IS SELECT * FROM dual;
BEGIN
	OPEN c;
	LOOP
		FETCH c INTO …
		EXIT WHEN c%NOTFOUND;
			…
		END LOOP;
		CLOSE c;
	END;
	BEGIN
		SELECT *INTO ...FROM dual;
		END;
	DECLARE
		CURSOR c IS SELECT * FROM dual;
		l_rec dual%ROWTYPE;
	BEGIN
	OPEN c;
	FETCH c INTO l_rec;
	IF (SQL%NOTFOUND)
	THEN
		RAISE NO_DATA_FOUND;
	END IF;
	FETCH c INTO l_rec;
	IF (SQL%FOUND)CHAPTER 
	THEN
		RAISE TOO_MANY_ROWS;
	END IF;
	CLOSE c;
END;

 

Hits: 6

sql – Search for a particular string in Oracle clob column

Example use string in Oracle CLOB column

create table aTable(
      id number,
      aClobColumn  clob
    );

insert into aTable values (1,'value' );
insert into aTable values (1,'values are like this' );

select * from aTable where dbms_lob.substr( aClobColumn , 4000, 1 )='value';

Source: http://sqlfiddle.com/#!4/1878f6/164

select * from my_table where dbms_lob.instr(product_details,'NEW.PRODUCT_NO')>=1

SQL VARCHAR2 Functions and Operators on LOBs

http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_sql_semantics.htm#g1016221

Source: sql – Search for a particular string in Oracle clob column – Stack Overflow

Convert string to CLOB

declare
   c1 clob := to_clob('abc');
   c2 clob;
 begin
      case c1
          when to_clob('abc') then dbms_output.put_line('abc');
          when to_clob('def') then dbms_output.put_line('def');
      end case;

      c2 := case c1
        when to_clob('abc') then 'abc'
         when to_clob('def') then 'def'
     end;
      dbms_output.put_line(c2);
end;

Hits: 531

SQL corner: remove duplicated number

Last time I have problem with duplicated number. Our callback interfaces write to us many error_number. So first i have to find where is and how many this number are.

SELECT l.site_number, l.ADR_STREET_NO, REGEXP_REPLACE (l.ADR_STREET_NO, '([^ ]+)( \1)+', '\1')
FROM mibp_owner.locations l
WHERE REGEXP_LIKE (ADR_STREET_NO, '\d \d'); l
UPDATE mibp_owner.locations l
SET l.ADR_STREET_NO = REGEXP_REPLACE (l.ADR_STREET_NO, '([^ ]+)( \1)+', '\1')
WHERE REGEXP_LIKE (ADR_STREET_NO, '\d \d');

Function to remove double number:

CREATE OR REPLACE FUNCTION MIBP_OWNER.rem_dubble (n in VARCHAR2)
   RETURN VARCHAR2
IS
  ind integer;
  t1 varchar2(100);
  t2 varchar2(100);
BEGIN
  if is_no_dubbled(n)='N' then
    return n;
  end if;

  for i in 1..10 loop
    ind:=instr(n,' ',1,i);
    if ind=0 then
      exit;
    end if;
    t1:=trim(substr(n,1,ind));
    t2:=trim(substr(n,ind));
    if t1=t2 then
      return rem_dubble(t1);
    end if;
  end loop;
  return n;

END;
/

CREATE OR REPLACE FUNCTION MIBP_OWNER.is_no_dubbled (n in VARCHAR2)
   RETURN VARCHAR2
IS
  ind integer;
  t1 varchar2(100);
  t2 varchar2(100);
BEGIN
  if instr(n,' ')=0 then return 'N'; end if;
  for i in 1..10 loop
    ind:=instr(n,' ',1,i);
    if ind=0 then
      exit;
    end if;
    t1:=trim(substr(n,1,ind));
    t2:=trim(substr(n,ind));
    if t1=t2 then
      return 'Y';
    end if;
  end loop;
  return 'N';

END;
/

Show errors;

SQL Fiddle

  1. http://sqlfiddle.com/#!4/75b32/1/1
  2. http://rextester.com/l/oracle_online_compiler
  3. http://dbfiddle.uk/
  4. https://livesql.oracle.com/apex/livesql/file/index.html

Hits: 8

Oracle 10gR2+ conditional PL/SQL Compilation

Examples

Example 1
create or replace procedure p
as
begin
  $IF $$debug_code
  $THEN
    dbms_output.put_line( 'Our debug code' );
    dbms_output.put_line( 'Would go here' );
  $END
  dbms_output.put_line( 'And our real code here' );
end;
/

alter procedure P compile
plsql_ccflags = 'debug_code:true' reuse settings
/

Example 2
create or replace procedure p2
as
begin
 $if $$plsql_debug
 $then
    $error 'This program must be compiled with PLSQL_DEBUG disabled' $end
 $end
 dbms_output.put_line( 'This is where it happens!' );
end;
/
Procedure created
ALTER SESSION SET PLSQL_DEBUG=TRUE
/
alter procedure p2 compile
/
Warning: Procedure altered with compilation errors.
show err
Errors for PROCEDURE P2:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5      PLS-00179: $ERROR: This program must be compiled with PLSQL_DEBUG
         disabled

Source:

  1. http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-conditional-compilation-133587.pdf
  2. Oracle 10gR2 – Conditional PL/SQL Compilation – AMIS Oracle and Java Blog

Hits: 13

subprogram inlining in 11g

Very usefully website 🙂 for PLSQL developers!

subprogram inlining in 11gThe release of Oracle 10g brought with it the first optimising compiler for PL/SQL. As discussed in this oracle-developer.net article, Oracle added two levels of optimisation to provide some impressive performance gains without changing a line of code.Oracle 11g has taken compiler optimisation further, in particular with the concept of subprogram inlining. With subprogram inlining, Oracle will replace a call to a subroutine (such as a function) with the subroutine code itself during compilation. One of the benefits of this is that we can continue to write well-structured, modular code without any performance penalties. For SQL-intensive PL/SQL programs, the gains from inlining might be marginal, but for procedural code, inlining might provide some greater optimisation benefits, as we will see in this article.

Source: subprogram inlining in 11g

Hits: 8

Alternative way monitoring Zabbix via PHP API

Purpose create this project

Display: alarms, warnings from Zabbix on PHP page. In this case I had to show some information about my services on independent web page.

Set enviroment for Zabbix PHP API

What you need to start:

  1. Zabbix API 2+ – doc: https://www.zabbix.com/documentation/, download: https://www.zabbix.com/download
  2. Apache 2.4+
  3. PHP 7+
  4. Bootstap 3+
  5. I have defined some alarms in Zabbix and I want to observe it in “application”. Just use https://github.com/confirm/PhpZabbixApi and https://www.zabbix.com/documentation/2.2/manual/api
  6. Fronted i use Bootstrap http://getbootstrap.com/javascript/#buttons and some additional lib’s https://datatables.net/extensions/index it is good effective way to make it simple an easy.
  7. Git (Btubucket, Github)

Work set

Zabbix PHP API

PhpZabbixApi is an open-source PHP class library to communicate with the Zabbix™ JSON-RPC API. Because PhpZabbixApi is generated directly from the origin Zabbix™ 2.0 PHP front-end source code / files, each real Zabbix™ JSON-RPC API method is implemented (hard-coded) directly as an own PHP method. This means PhpZabbixApi is IDE-friendly, because you’ve a PHP method for each API method, and there are no PHP magic functions or alike.

Bootstrap

Bootstrap is the most popular HTML, CSS, and JS framework for developing responsive, mobile first projects on the web.

Source code on Bitbucket

<?php
/**
 * ZabbixAPI_class.php Copyright 2017 by Karol Preiskorn
 *
 * @version 18.03.2017 21:35:17 Karol Preiskorn Init
 * @version 19.03.2017 16:37:59 KPreiskorn ZabbixAPI_class.php Export to BB
 *
 */
require_once 'PhpZabbixApi-2.4.5/build/ZabbixApi.class.php';
include 'header.php';

use ZabbixApi\ZabbixApi;

/**
 *
 * @todo add LDAP via uptime
 *
 * @var string $uri
 *
 */

$uri = "http://zabixx";
$username = "user";
$password = "pass";

// set passwords and uri
include "passwords.php";

/**
 * poor print_r function
 *
 * @param unknown $val
 *
 */
function print_r2($val)
{
    echo '<pre>';
    print_r($val);
    echo '</pre>';
}

/**
 * Better print_r function
 */
function obsafe_print_r($var, $return = false, $html = true, $level = 0)
{
    $spaces = "";
    $space = $html ? " " : " ";
    $newline = $html ? "<br />" : "\n";
    for ($i = 1; $i <= 6; $i ++) {
        $spaces .= $space;
    }
    $tabs = $spaces;
    for ($i = 1; $i <= $level; $i ++) {
        $tabs .= $spaces;
    }
    if (is_array($var)) {
        $title = "Array";
    } elseif (is_object($var)) {
        $title = get_class($var) . " Object";
    }
    $output = "<b>" . $title . "</b>" . $newline . $newline;
    foreach ($var as $key => $value) {
        if (is_array($value) || is_object($value)) {
            $level ++;
            $value = obsafe_print_r($value, true, $html, $level);
            $level --;
        }
        $output .= $tabs . "[" . $key . "] => [" . $value . "]" . $newline;
    }
    if ($return)
        return $output;
    else
        echo "<pre>" . $output . "</pre>";
}

/**
 *
 * Generate bootstrap table with headers and coun of elements
 *
 * @param string $title
 * @param unknown $count
 * @param unknown $id
 */
function print_table_header($title, $count, $headers, $id)
{
    print('<div class="panel panel-default">' . "\n");
    print('<div class="panel-heading">' . $title . ' <a href="#"> <span class="badge">' . $count . '</span></a></div>' . "\n");
    print("<table id='" . $id . "' class='table table-striped table-bordered'>" . "\n");

    print("<thead><tr>" . "\n");
    foreach ($headers as $header) {
        print("<th>" . $header . "</th>" . "\n");
    }
    print("</tr>\n</thead>\n<tbody>" . "\n");
}

try {
    // connect to Zabbix API
    $api = new ZabbixApi($uri, $username, $password);

    // get all graphs
    $alerts = $api->alertGet();
    // print_r2 ( $graphs [0] );

    arsort($alerts);

    $headers = array(
        "Alert Id",
        "Event Id",
        "Date time",
        "Sent to",
        "Subject",
        "Message"
    );

    print_table_header('Alerts', count($alerts), $headers, 'alarms');

    // print all alerts
    foreach ($alerts as $alert) {
        if ($alert->sendto != "karol.preiskorn@t-mobile.pl") {
            if (strpos($alert->subject, "PROBLEM:") !== false) {
                $alerttype = "class='warning'";
            } else {
                $alerttype = "class='normal'";
            }
            if (preg_match("/Original event ID: ([0-9]{4,12})/", $alert->message, $regs)) {
                $eventid_org = "[link: <a href='#" . $regs[1] . "'>" . $regs[1] . "</a>]";
            } else {
                $eventid_org = "Not found Original event ID";
            }
            printf("<tr id='%s' %s><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td><td>%s</td></tr>" . "\n", $alert->eventid, $alerttype, $alert->alertid, $alert->eventid, date("Y-m-d h:i", $alert->clock), $alert->sendto, $alert->subject, $alert->message . " " . $eventid_org);
        }
    }
    print("</tbody>\n</table>\n</div>\n");

    // get all graphs
    $graphs = $api->graphGet();
    $header_graph = array(
        "Graph Id",
        "Description"
    );
    print_table_header('Graph', count($graphs), $header_graph, 'graph');

    // print all graph IDs
    foreach ($graphs as $graph) {
        printf("<tr><td>%8d</td><td>%s</td></tr>\n", $graph->graphid, $graph->name);
    }
    print("</tbody>\n</table>\n</div>\n");

    print('<div class="panel-footer">

        <p>© T-Mobile Polska Company | OSS development powered by Karol Preiskorn | internal use</p>

      </div>');
    print("</div></body></html>"); // end of container

    // print "<h1>Alerts... agian in raw</h2>";
                                       // obsafe_print_r ( $alerts, FALSE, TRUE );
} catch (Exception $e) {
    // Exception in ZabbixApi catched
    echo $e->getMessage();
}
?>

<?php
/**
 *
 * HTML 5
 *
 */
?>
<!DOCTYPE html>
<html>
<head>
<title>Zabbix</title>

<link rel="stylesheet" type="text/css"
	href="https://cdn.datatables.net/v/bs-3.3.7/jq-2.2.4/dt-1.10.13/af-2.1.3/b-1.2.4/b-colvis-1.2.4/fh-3.1.2/kt-2.2.0/r-2.1.1/rr-1.2.0/sc-1.4.2/se-1.2.0/datatables.min.css" />
<script type="text/javascript"
	src="https://cdn.datatables.net/v/bs-3.3.7/jq-2.2.4/dt-1.10.13/af-2.1.3/b-1.2.4/b-colvis-1.2.4/fh-3.1.2/kt-2.2.0/r-2.1.1/rr-1.2.0/sc-1.4.2/se-1.2.0/datatables.min.js"></script>

<script>
$(document).ready(function() {
    $('#alarms').DataTable( {
        deferRender:    true,
        scrollY:        400,
        scrollCollapse: false,
        scroller:       false
    } );
} );


$(document).ready(function() {
	$('#graph').DataTable({
        deferRender:    true,
        scrollY:        400,
        scrollCollapse: false,
        scroller:       false
    } );
} );
</script>

</head>

<body>
	<div class="container">
		<div class="jumbotron">
			<h1>Zabbix Alarm monitoring</h1>
			<p>Aplikacja monitoruje alarmy z Zabbix.</p>

			<a class="btn btn-primary btn-lg"
				href="http://wrex/zabbix/zabbix.php?action=dashboard.view"
				role="button">Zabbix Dashboard</a> <a class="btn btn-lg"
				href="#alarms" role="button">Alarms</a> <a class="btn btn-lg"
				href="#graph" role="button">Graph</a> <a class="btn btn-lg"
				href="simple-json.php" role="button">Simple JSON</a>


			<button class="btn btn-primary" type="button" data-toggle="collapse"
				data-target="#collapseExample" aria-expanded="false"
				aria-controls="collapseExample">About</button>


			<div class="collapse" id="collapseExample">
				<div class="well">Aplikacja monitoruje alarmy z OSS Zabbix na
					serwerze wrex. Dost�p do API poprzez klas� PHP -> ZabbixApi. Opis
					API
					https://www.zabbix.com/documentation/2.2/manual/api/reference/history/get
				</div>
			</div>

		</div>

<?php

/**
 * Zabbix monitoring in PHP
 *
 * @author Karol Preiskorn
 *
 * @version 2017-04-16 KP init
 * @version 17.03.2017 14:47:16 Karol Preiskorn dodanie informacji na temat Alert�w
 * @version 17.03.2017 18:07:38 Karol Preiskorn - dodanie synchronizacji
 * @version 9 gru 2017 12:47:16 KPreiskorn simple-json.php add composer and manage password
 *
 *
 *
 * phpinfo ();
 *
 */
$uri = "http://zabixx";
$username = "user";
$password = "pass";

// set passwords and uri
include "passwords.php";
/**
 *
 * @param unknown $in
 * @param number $indent
 * @param string $from_array
 * @return string
 */
function json_readable_encode($in, $indent = 0, $from_array = true) {
	$_myself = __FUNCTION__;
	$_escape = function ($str) {
		return preg_replace ( "!([\b\t\n\r\f\"\\'])!", "\\\\\\1", $str );
	};

	$out = '';

	foreach ( $in as $key => $value ) {
		$out .= str_repeat ( "\t", $indent + 1 );
		$out .= "\"" . $_escape ( ( string ) $key ) . "\": ";

		if (is_object ( $value ) || is_array ( $value )) {
			$out .= "\n";
			$out .= $_myself ( $value, $indent + 1 );
		} elseif (is_bool ( $value )) {
			$out .= $value ? 'true' : 'false';
		} elseif (is_null ( $value )) {
			$out .= 'null';
		} elseif (is_string ( $value )) {
			$out .= "\"" . $_escape ( $value ) . "\"";
		} else {
			$out .= $value;
		}

		$out .= ",\n";
	}

	if (! empty ( $out )) {
		$out = substr ( $out, 0, - 2 );
	}

	$out = str_repeat ( "\t", $indent ) . "{\n" . $out;
	$out .= "\n" . str_repeat ( "\t", $indent ) . "}";

	return $out;
}

/**
 *
 * @param unknown $array
 */
function expand_arr($array) {
	foreach ( $array as $key => $value ) {
		if (is_array ( $value )) {
			echo "<i>" . $key . "</i>:<br>";
			expand_arr ( $value );
			echo "<br>\n";
		} else {
			echo "<i>" . $key . "</i>: " . $value . "<br>\n";
		}
	}
}
/**
 *
 * @param unknown $uri
 * @param unknown $data
 * @return mixed
 */
function json_request($uri, $data) {
	$json_data = json_encode ( $data );
	$c = curl_init ();
	curl_setopt ( $c, CURLOPT_URL, $uri );
	curl_setopt ( $c, CURLOPT_CUSTOMREQUEST, "POST" );
	curl_setopt ( $c, CURLOPT_RETURNTRANSFER, true );
	curl_setopt ( $c, CURLOPT_POST, $json_data );
	curl_setopt ( $c, CURLOPT_POSTFIELDS, $json_data );
	curl_setopt ( $c, CURLOPT_HTTPHEADER, array (
			'Content-Type: application/json',
			'Content-Length: ' . strlen ( $json_data )
	) );
	curl_setopt ( $c, CURLOPT_SSL_VERIFYPEER, false );
	$result = curl_exec ( $c );

	echo "<b>JSON Request:</b><br>\n";
	echo $json_data . "<br><br>\n";

	echo "<b>JSON Answer:</b><br>\n";
	echo $result . "<br><br>\n";

	/*
	 * echo "<b>CURL Debug Info:</b><br>\n";
	 * $debug = curl_getinfo ( $c );
	 * echo expand_arr ( $debug ) . "<br><hr>\n";
	 */

	return json_decode ( $result, true );
}
/**
 *
 * @param unknown $uri
 * @param unknown $username
 * @param unknown $password
 * @return unknown
 */
function zabbix_auth($uri, $username, $password) {
	$data = array (
			'jsonrpc' => "2.0",
			'method' => "user.login",
			'params' => array (
					'user' => $username,
					'password' => $password
			),
			'id' => "1"
	);
	$response = json_request ( $uri, $data );
	return $response ['result'];
}

/**
 *
 * @param unknown $uri
 * @param unknown $authtoken
 * @return unknown
 */
function zabbix_get_hostgroups($uri, $authtoken) {
	$data = array (
			'jsonrpc' => "2.0",
			'method' => "hostgroup.get",
			'params' => array (
					'output' => "extend",
					'sortfield' => "name"
			),
			'id' => "2",
			'auth' => $authtoken
	);
	$response = json_request ( $uri, $data );
	return $response ['result'];
}
/**
 *
 * @param unknown $uri
 * @param unknown $authtoken
 */
function zabbix_get_alert($uri, $authtoken) {
	$data = array (
			"jsonrpc" => "2.0",
			"method" => "alert.get",
			"params" => array (
					"output" => "extend",
					"actionids" => "3"
			),
			"auth" => $authtoken,
			"id" => 1
	);
	$response = json_request ( $uri, $data );
	return $response ['result'];
}

/**
 *
 * @var Ambiguous $authtoken
 */
print "<h1>Auth</h1>";
$authtoken = zabbix_auth ( $uri, $username, $password );

print "<h1>HostGrups</h1>";
expand_arr ( zabbix_get_hostgroups ( $uri, $authtoken ) );
print "<h2>json_readable_encode</h2>";
json_readable_encode ( zabbix_get_hostgroups ( $uri, $authtoken ) );
print "<h1>Alerts</h1>";
expand_arr ( zabbix_get_alert ( $uri, $authtoken ) );
?>

Hits: 685

How to make webservices Java, Tomcat, Metro and Eclipse

Abstract

It is about impelemtation webserwices on Tomcat in Java (Metro, Ant)

Creating the Service Provider Environment

Prerequisites

These series of articles require the following software to be installed on your system:

  1. JDK,
  2. Apache Ant 1.6.5 or later, Apache Ant is a Java library and command-line tool whose mission is to drive processes described in build files as targets and extension points dependent upon each other. The main known usage of Ant is the build of Java applications. Ant supplies a number of built-in tasks allowing to compile, assemble, test and run Java applications. Ant can also be used effectively to build non Java applications, for instance C or C++ applications. More generally, Ant can be used to pilot any type of process which can be described in terms of targets and tasks… for example deploy webserwices…
    1. http://ant.apache.org/manual/index.html
    2. http://ant.apache.org/bindownload.cgi
  3. web container: either Glassfish v3.x or Apache Tomcat 7.0+
    1. http://localhost:8080 – console
  1. programming webserwices engine
    1. Metro Standalone Budle 2.x (in case of using Apache Tomcat as a web container)
      1. https://metro.java.net/2.3.1/ Metro
      2. https://metro.java.net/getting-started/building-a-simple-metro-application.html
    2. http://trickyinfo.blogspot.com/2012/08/web-services-development-using-eclipse.html – other way to build webserwices in Eclipse
    3. http://www.eclipse.org/webtools/community/education/web/t320/Implementing_a_Simple_Web_Service.pdf
    4. http://www.ibm.com/developerworks/webservices/tutorials/ws-eclipse-javase1/ws-eclipse-javase1.html
    5. https://www.eclipse.org/webtools/ws/ – Eclipse WS tool
      1. Eclipse Web Services Explorer tool http://www.eclipse.org/webtools/initial-contribution/IBM/evalGuides/WebServicesToolsEval.html
    6. https://netbeans.org/kb/docs/websvc/jax-ws.html

How to prepare webserwis from WSDL

http://www.eclipse.org/webtools/community/education/web/t320/Generating_a_client_from_WSDL.pdf

Set Tomcat

Assuming Java (JDK + JRE) is installed in your system, do the following steps:

  1. Install Tomcat7
  2. Copy ‘tools.jar’ from ‘C:\Program Files (x86)\Java\jdk1.6.0_27\lib’ and paste it under ‘C:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0\lib’.
  3. Setup paths in your Environment Variables as shown below:

C:>echo %path%

C:\Program Files (x86)\Java\jdk1.6.0_27\bin;%CATALINA_HOME%\bin;

C:>echo %classpath%

C:\Program Files (x86)\Java\jdk1.6.0_27\lib\tools.jar;
C:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0\lib\servlet-api.jar;

C:>echo %CATALINA_HOME%

C:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0;

C:>echo %JAVA_HOME%

C:\Program Files (x86)\Java\jdk1.6.0_27;

Now you can test whether Tomcat is setup correctly, by typing the following commands in your command prompt:

C:/>javap javax.servlet.ServletException
C:/>javap javax.servlet.http.HttpServletRequest

It should show a bunch of classes

Now start Tomcat service by double clicking on ‘Tomcat7.exe‘ under ‘C:\Program Files (x86)\Apache Software Foundation\Tomcat 7.0\bin’.

Errors

INFO: The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path:

60down voteaccepted

It means exactly what it says: “The APR based Apache Tomcat Native library which allows optimal performance in production environments was not found on the java.library.path”

The library referred to is bundled into an OS specific dll (tcnative-1.dll) loaded via JNI. It allows tomcat to use OS functionalities not provided in the Java Runtime (such as sendfile, epoll, OpenSSL, system status, etc.). Tomcat will run just fine without it, but for some use cases, it will be faster with the native libraries.

If you really want it, download the tcnative-1.dll (or libtcnative.so for Linux) and put it in the bin folder, and add a system property to the launch configuration of the tomcat server in eclipse.

 -Djava.library.path=c:\dev\tomcat\bin

Set Metro

Again, the top-level directory of the Tomcat installation is passed as an argument to the ant command. To shutdown Tomcat, change directories, invoke ant, and restart Tomcat by executing these commands:

catalina.sh stop
ant -Dtomcat.home=<TOMCAT_INSTALL_DIR> -f <METRO_INSTALL_DIR>/metro-on-tomcat.xml install
catalina.sh start

Other Tutorials

  • http://www.ibm.com/developerworks/learn/web/index.html
  • http://stackoverflow.com/questions/14579661/how-to-set-catalina-home-variable-in-windows-7#17044342

Hits: 0

Java wariacje z ciągiem Fibonacciego

Zadanie

Utwórz rekurencyjna metode obliczajaca ciag Fibonacciego, zdefiniowany dla elementu fibonacci(n) jako sume fibonacci(n-1) + fibonacci(n-2) przy załozeniu, ze fibonacci(1) i fibonacci(2) maja odpowiednio wartosci 1 i 2.

Ciąg Fibonaciego

In mathematics, the Fibonacci numbers are the numbers in the following integer sequence, called the Fibonacci sequence, and characterized by the fact that every number after the first two is the sum of the two preceding ones:[1][2]

1 , 1 , 2 , 3 , 5 , 8 , 13 , 21 , 34 , 55 , 89 , 144 , …

Often, especially in modern usage, the sequence is extended by one more initial term:

0 , 1 , 1 , 2 , 3 , 5 , 8 , 13 , 21 , 34 , 55 , 89 , 144 , …[3]

Tempo Planner for JIRA

The Fibonacci spiral: an approximation of the golden spiral created by drawing circular arcs connecting the opposite corners of squares in the Fibonacci tiling;[4] this one uses squares of sizes 1, 1, 2, 3, 5, 8, 13, 21, and 34.

By definition, the first two numbers in the Fibonacci sequence are either 1 and 1, or 0 and 1, depending on the chosen starting point of the sequence, and each subsequent number is the sum of the previous two.

In mathematical terms, the sequence Fn of Fibonacci numbers is defined by the recurrence relation

Tempo Planner for JIRA

with seed values[1][2]

  Tempo Planner for JIRA

or[5]

Tempo Planner for JIRA

The Fibonacci sequence is named after Italian mathematician Leonardo of Pisa, known as Fibonacci. His 1202 book Liber Abaci introduced the sequence to Western European mathematics,[6] although the sequence had been described earlier as Virahanka numbers in Indian mathematics.[7][8][9] The sequence described in Liber Abaci began with F1 = 1.

Fibonacci numbers are closely related to Lucas numbers  Tempo Planner for JIRA in that they form a complementary pair of Lucas sequencesTempo Planner for JIRA and Tempo Planner for JIRA. They are intimately connected with the golden ratio; for example, the closest rational approximations to the ratio are 2/1, 3/2, 5/3, 8/5, … .

Fibonacci numbers appear unexpectedly often in mathematics, so much so that there is an entire journal dedicated to their study, the Fibonacci Quarterly. Applications of Fibonacci numbers include computer algorithms such as the Fibonacci search technique and the Fibonacci heap data structure, and graphs called Fibonacci cubes used for interconnecting parallel and distributed systems. They also appear in biological settings,[10] such as branching in trees, phyllotaxis (the arrangement of leaves on a stem), the fruit sprouts of a pineapple,[11] the flowering of an artichoke, an uncurling fern and the arrangement of a pine cone‘s bracts.[12]

— Source: https://en.wikipedia.org/wiki/Fibonacci_number

Closed form

\(x_{n}=\frac{(1+5^{1/2})^{n} -(1-5^{1/2})^n}{\sqrt{5}2^n}\)

https://math.stackexchange.com/questions/1145342/find-a-formula-for-the-nth-fibonacci-number?newreg=1b687d38e42346fa97a2fdfe3a94c389

Java program and others interesting links

Sources from Bitbucket

Tempo Planner for JIRA

 

 

/*
 * Example program to find fibonacci numbers
 */
package Fibonacci;

import static java.lang.Math.sqrt;
import java.util.Scanner;

/**
 *
 * @author KPreiskorn
 */
public class Fibonacci {

  /**
   * @param args the command line arguments
   */
  @SuppressWarnings("resource")
  public static void main(String args[]) {

    // input to print Fibonacci series upto how many numbers
    log("Enter number n Fibonacci to print: ");
    int number = new Scanner(System.in).nextInt();

    log("\nUsing Method-1: Using Recursion. Provided Number: " + number);
    // printing Fibonacci series upto number
    for (int i = 1; i <= number; i++) {
      System.out.printf("%3d %6d\n", i, fibonacciRecusion(i));
    }

    log("\nMethod-2: Fibonacci number at location " + number + " is ==> " + (fibonacciLoop(number) + ""));

    System.out.printf("\nMethod #3 (fibonacciClosedForm): Fibonacci number at location %d is %.0f\n\n", number, fibonacciClosedForm(number));

  }

  // Method-1: Java program for Fibonacci number using recursion.
  public static int fibonacciRecusion(int number) {
    if (number == 1 || number == 2) {
      return 1;
    }

    return fibonacciRecusion(number - 1) + fibonacciRecusion(number - 2); // tail recursion
  }

  // Method-2: Java program for Fibonacci number using Loop.
  public static int fibonacciLoop(int number) {
    if (number == 1 || number == 2) {
      return 1;
    }
    int fibo1 = 1, fibo2 = 1, fibonacci = 1;
    for (int i = 3; i <= number; i++) {
      fibonacci = fibo1 + fibo2; // Fibonacci number is sum of previous two Fibonacci number
      fibo1 = fibo2;
      fibo2 = fibonacci;

    }
    return fibonacci; // Fibonacci number
  }

  // metod 3: Closed-form expression
  public static double fibonacciClosedForm(int n) {
    double q = (1 + sqrt(5)) / 2;

    return Math.floor(Math.pow(q, n) / sqrt(5) + 1 / 2); // Fibonacci number
  }

  private static void log(String number) {
    System.out.println(number);

  }

}

Hits: 202

PLSQL corner

PLSQL loop inexed by letters

declare
  k_ascii_a constant pls_integer := ascii('A');
  l_index varchar2(1);
begin
  for i in 1 .. 10 loop -- Change 10 for whatever is your limit
    l_index := chr(k_ascii_A + i - 1);
    dbms_output.put_line('Index is ' || l_index);
  end loop;
end;

But this is not portable across character sets. I think a better solution is to declare up front your index variables like this:

declare
  k_index_char constant varchar2(26) := 'ABCDEF...Z';
  l_index varchar2(1);
begin
  for i in 1 .. 10 loop -- Change 10 for whatever is your limit
    l_index := substr(k_index_char, i, 1);
    dbms_output.put_line('Index is ' || l_index);
  end loop;
end;

That way if you change character sets (e.g. to utf8) you are not reliant on a collating sequence that doesn’t match your char set.

Hits: 7

What are the best programming fonts?

The choice the font in Firefox or Sulbimetext it is always hard decision….

As much as choice of a font is subjective there are important criteria to consider when choosing a programming font. The characters should be clear, easy to read, in cases of clashing ones such as 1, l, i or O, o, 0 easily distinguishable, differences between different kinds of brackets, quotes and parentheses should be clear, punctuation should be emphasized and they shouldn’t break up words or sentences due to uneven character widths. There are two ways fonts are drawn – as bitmaps or as vectors. Vector fonts allow more efficient scaling, but for screens with resolution below 720p it might be worth considering bitmap fonts as they are generally more legible at smaller sizes on low-resolution screens.

Source: What are the best programming fonts? – Slant

Hits: 12

RegEx never endig story….

It is example of use RegEx to prepare SQL data for SELECT/UPDATE operation.

/^.*/gm and substitution: '\0',

Explanation

  1. ^ asserts position at start of a line
  2. .* matches any character (except for line terminators)
  3. * Quantifier — Matches between zero and unlimited times, as many times as possible, giving back as needed (greedy)
  4. Global pattern flags
    1. g modifier: global. All matches (don’t return after first match)
    2. m modifier: multi line. Causes ^ and $ to match the begin/end of each line (not only begin/end of string)

Example

This example simple replace MBS to ,'MBS'. For example when you have many selected values SQL (in one column) and need convert it to running query.

SELECT * FROM TABLE WHERE ID IN(...)

AAA
AAB
AAC

Convert it to:

SELECT * FROM TABLE WHERE ID IN(
'AAA',
'AAB',
'AAC')

Very good tools for testing: https://regex101.com/ and here is this example: https://regex101.com/r/34repv/1

Tempo Planner for JIRA

other good option to test yours regex query is:

  • https://www.regextester.com/
  • https://www.freeformatter.com/regex-tester.html

How its run?

Simple by Sublimetext, Notepad++, Visual Code –> Find/Replace command

Regular Expressions Cheat Sheet

A regular expression specifies a set of strings that matches it. This cheat sheet is based off Python 3’s Regular Expressions (http://docs.python.org/3/library/re.html) but is designed for searches within Sublime Text.

Special Characters

  • \ Escapes special characters or signals a special sequence.
  • . Matches any single character except a newline.
  • ^ Matches the start of the string.
  • $ Matches the end of the string.
  • * Greedily matches 0 or more repetitions of the preceding RE.
  • *? Matches 0 or more repetitions of the preceding RE.
  • + Greedily matches 1 or more repetitions of the preceding RE.
  • +? Matches 1 or more repetitions of the preceding RE.
  • ? Greedily matches 0 or 1 repetitions of the preceding RE.
  • ?? Matches 0 or 1 repetitions of the preceding RE.
  • A|B Matches A, if A is unmatched then matches B, where A and B are arbitrary REs.
  • {m} Matches exactly m many repetitions of the previous RE.
  • {m,n} Greedily matches from m many to n many repetitions of the previous RE.
  • {m,n}? Matches m many to n many repetitions of the previous RE.

[…] Indicates a set of characters to match.

[amk]

Matches ‘a’, ‘m’, or ‘k’.

[a-z]

Matches ‘a’ through ‘z’.

[a-f0-7]

Matches ‘a’ through ‘f’ or ‘0’ through ‘7’.

[a\-z]

Matches ‘a’, ‘-‘, or ‘z’.

[a-]

  • Matches ‘a’ or ‘-‘.
  • [-a] Matches ‘a’ or ‘-‘.
  • [(+*)] Matches ‘(‘, ‘+’, ‘*’, or ‘)’. [] matches special characters literally.
  • [\w] Matches the character class for ‘\w’. See character classes.
  • [^5] Matches anything other than ‘5’. ‘^’ forms the complementary set only as the first character in a set.
  • []()] Matches ‘]’, ‘(‘, and ‘)’. ‘]’ is taken literally only as the first character in a set.
  • [()\]] Matches ‘]’, ‘(‘, and ‘)’.

(…) Matches the RE inside the parenthesis and assigns a new group.
(?P…) The RE matched is accessible by the group indicated by name.

  • (?…) Extension notation which changes a RE’s behavior. These do not assign a new group.
  • (?aiLmsux) Sets the corresponding flag to each letter. Does not work within Sublime Text.
  • (?:…) A non-capturing version of parenthesis. The matched substring cannot be retrieved later.
  • (?P=name) Matches the substring matched by the group named name.
  • (?#…) A comment, the contents are ignored.
  • (?=…) Lookahead assertion, the preceding RE only matches if this matches.
  • (?!…) Negative lookahead assertion, the preceding RE only matches if this doesn’t match.
  • (?<=…) Positive lookbehind assertion, the following RE will only match if preceded with this fixed length RE.
  • (?<!…) Negative lookbehind assertion, the following RE will only match if not preceded with this fixed length RE.
  • (?(id)true|false) If group id exists then uses the true RE, else use the false RE.

Character classes

  • \1 Matches the contents of the group labelled by the same number. Acceptable numbers are 1-99.
  • \A Matches at the start of the current string.
  • \b Matches the empty string at the beginning or end of a word. \b matches the boundary between \w and \W.
  • \B Matches the empty string not at the beginning or end of a word.
  • \d Matches any Unicode decimal digit, including 0-9.
  • \D Matches any Unicode non-decimal digit.
  • \s Matches any Unicode whitespace character, including ‘ ‘, \t, \n, \r, \f and \v.
  • \S Matches any Unicode non-whitespace character.
  • \w Matches any Unicode word character, including a-z, A-Z, and 0-9.
  • \W Matches any Unicode non-word character.
  • \Z Matches at the end of the string.
  • \a Matches the ASCII Bell ().
  • \f Matches the ASCII Formfeed ( ).
  • \n Matches the ASCII Linefeed.
  • \r Matches the ASCII Carriage Return ().
  • \t Matches the ASCII Horizontal Tab.
  • \v Matches the ASCII Vertical Tab ( ).

Hits: 20

Tempo Planner for JIRA

Learn how to do sprint planning with Tempo Planner for JIRA. Make sure you’re fully utilizing your Tempo Planner instance.

Best way to communication in JIRA projects

Log in to HipChat with your work email address. New to HipChat? Want to create a new team? Sign up for free at hipchat.com.

Source: HipChat

This recording covers all necessary steps to configure Tempo and JIRA to start planning. This tutorial is based on Tempo Planner Version 3.4.3.1

https://youtu.be/n-hEOWd1j7M

Hits: 18

Proudly powered by WordPress | Theme: Baskerville 2 by Anders Noren.

Up ↑