Tag Archives: PL/SQL

Using DUMP, CHR, and REPLACE character functions

Posted on by
Reply

task#7 The set of sentences that will be analysed consist of character strings with the following pattern:

Number#1 Word#2 Number#2 Word#3 Number#3 Word#4 Number#4 Word#5 Number#5

Substrings Number#1, Number#2, Number#3, Number#4, Number#5 need to be extracted from the text and saved into separate table columns as numbers in order to be used as parameters in SQL aggregate functions for further analysis.

The length of each sentence is unknown as well as the format of Number# and Word# substrings.

step#1 – Start the SQL Developer

Start SQL Developer

Start SQL Developer

step#2 – The collected set of sentences is saved into the table DEMO.TEXTS. Each sentence is represented as a string of VARCHAR2(256) in the column WORD_SET.

As the table TEXTS is too long , I selected 6 rows that represent a typical characters sets for this task.

The Set Of Strings

The Set Of Strings

What is strange with this set of strings is that they appear to be of the similar sizes, but their exact lengths are different. The shortest string is 29 characters long, and the longest one is 166 characters long. Obviously, the longer strings contain some other ASCII non-printable characters. How to see them?

step#3 – Apply SQL DUMP function to reveal the content of strings.

Apply the DUMP Function

Apply the DUMP Function

The Result Set shows that our strings contain ASCII characters:

CHR(10) or Line feed, ‘\n’

CHR(13) or Carriage return, ‘\r’

CHR(32) or Space

step#4 – With the simple update statement and REPLACE function, characters chr(10), chr(13), and chr(32) will be removed from all strings:

The UPDATE Statement with REPLACE Functions

The UPDATE Statement with REPLACE Functions

step#5 – All strings are clean and contain only “0-9”, “a-z”, and “A-Z” characters.

The Clean Strings

The Clean Strings

step#6 – The typical string looks like

5,219Spring28.5KSummer44.1KAutumn2,557Winter6

The following procedure called proc_extract will extract substrings with numbers and save them in separate columns as NUMBER data type.

Those columns are table TEXTS columns named

TOTAL for Number#1,

SPRING for Number#2,

SUMMER for Number#3,

AUTUMN for Number#4, and

WINTER for Number#5 substrings.

create or replace procedure proc_extract

authid current_user

is

  type type_id is table of DEMO.texts.word_id%TYPE;

  l_my_id type_id := type_id();

  type t_stats is table of DEMO.texts.word_set%TYPE;

  l_my_list t_stats := t_stats();

  l_last_id number := 0;

  l_len1 number;

  l_len2 number;

  l_my_str varchar2(256);

  l_my_str2 varchar2(256);

  l_my_i DEMO.texts.word_id%TYPE;

begin

  select word_id, word_set bulk collect into l_my_id, l_my_list

  from texts

  where word_set <> 'X';

  dbms_output.put_line(l_my_list.COUNT);

  for i in l_my_id.FIRST..l_my_id.COUNT

  loop

    l_my_i := l_my_id(i);

    l_my_str := l_my_list(i);

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Winter', 1, 1) <> 0 ) then

      l_len1 := instr(l_my_str,'Winter', 1, 1);

      l_my_str2 := substr(l_my_str, l_len1, l_len2 - l_len1 + 1);

      l_my_str2 := funct_format_num(replace(l_my_str2, 'Winter') );

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set winter = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Autumn',1,1) <> 0 ) then

      l_len1 := instr(l_my_str, 'Autumn', 1, 1);

      l_my_str2:= substr(l_my_str, l_len1, l_len2 - l_len1 +1);

      l_my_str2 := funct_format_num(replace(l_my_str2,'Autumn'));

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set autumn = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Summer',1,1) <> 0 ) then

      l_len1 := instr(l_my_str,'Summer',1,1);

      l_my_str2:= substr(l_my_str, l_len1 , l_len2 - l_len1 + 1);

      l_my_str2 := funct_format_num(replace(l_my_str2,'Summer'));

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set summer = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if (instr(l_my_str,'Spring',1,1) <> 0 ) then

      l_len1 := instr(l_my_str,'Spring',1,1);

      l_my_str2:= substr(l_my_str, l_len1 , l_len2 - l_len1 + 1);

      l_my_str2 := funct_format_num(replace(l_my_str2,'Spring'));

      l_my_str := substr(l_my_str, 1, l_len1 - 1);

      dbms_output.put_line(l_my_str2);

      dbms_output.put_line(l_my_str);

      update texts set spring = to_number(l_my_str2) where word_id = l_my_i;

    end if;

    l_len2 := length (l_my_str);

    if l_len2 > 0 then

      l_my_str := funct_format_num(l_my_str);

      dbms_output.put_line(l_my_str);

      update texts set total = to_number(l_my_str) where word_id = l_my_i;

    end if;
 
  end loop;

  commit;

exception

when others then

  dbms_output.put_line(SQLERRM);

end;

The function funct_format_num will transform the existing number format “9,999”, “99.9K”, “999K”, and “99.9M” into simple “9999999” format that is easy to use.

create or replace function funct_format_num (p_in_str varchar2)

return varchar2

authid current_user

as

  l_s varchar2(256);

begin

  l_s := p_in_str;

  if (instr(p_in_str,'.') <> 0 AND instr(p_in_str,'K') <> 0 ) then

    l_s := replace(replace(l_s,'.'),'K');

    l_s := l_s || '00';

  elsif instr(p_in_str,'K') <> 0 then

    l_s := replace(l_s,'K');

    l_s := l_s || '000';

  elsif ( instr(p_in_str,'.') <> 0 AND instr(p_in_str,'M')<> 0 ) then

    l_s:= replace(replace(l_s,'.'),'M');

    l_s:= l_s || '00000';

  elsif instr(p_in_str,',') <> 0 then

    l_s := replace(l_s,',');

  end if;

  return l_s;

exception

when others then

  raise;

end;

step#7 – After execution of the procedure proc_extract, the table is populated with correct values.

The Table DEMO.TEXTS

The Table DEMO.TEXTS

step#8 – Now, it is easy to execute any SQL aggregate function on TEXTS columns

Apply Aggregate Functions

Apply Aggregate Functions

Summary

Sometimes, we do not need to write long procedures with FOR loops and analyze each table column that contains character strings. Instead, the rule “Use SQL statement first.” is the best choice. Simple UPDATE statement combined with CHR and REPLACE functions will save the day and our time and the helpful DUMP function gives us the evidence what non-printable characters are inside our character strings.

The other story is when the once clean strings that we have, should be divided to several substrings. Then, SQL functions SUBSTR, INSTR, and LENGTH should be used and appropriate subprograms should be written as well to finish the task.

Reminder

DUMP (expr, return_fmt, start_position, length)

returns a VARCHAR2 value containing the data type code, length in bytes, and internal representation of expr. The returned result is always in the database character set. [ref2 ]

CHR (n USING NCHAR_CS)

returns the character having the binary equivalent to n as a VARCHAR2 value in either the database character set or, if you specify USING NCHAR_CS, the national character set. [ref2 ]

REPLACE (char, search_string, replacement_string)

returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then char is returned. [ref2 ]

Reference

[ref2] Oracle® Database SQL Language Reference 12c Release 1 (12.1) E41329-09

Upgrade to Oracle Database 12c

Posted on by
Reply

task#1 Check the version of your Oracle database and if it is not 12c, notify that it should be upgraded to the Oracle Database 12c.

step#1 – Connect to the Oracle Database as SYSDBA

c:TEMP>sqlplus / as sysdba
SYS@orcl > spool task#1.ext append

step#2 – Check the database version

SYS@orcl > select value from v$parameter where name='optimizer_features_enable';
VALUE
-----------------------------------------------------------
12.1.0.2
SYS@orcl > select value from v$parameter where name='db_unique_name';
VALUE
---------
orcl

The other way to know the Oracle Database version is the following:

--Oracle Database 10.2 
SQL> begin 
2 dbms_output.put_line(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE); 
3 end; 
4 / 
10.2
--Oracle Database 12.1
SYS@orcl > begin
  2  dbms_output.put_line(DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE);
  3  end;
  4  /
12.1


step#3 – If the database is not Oracle Database 12c, show the following message ” current_version should be updated to Oracle database 12c. Go to the www.oracle.com ” , and go to step#8.

SYS@orcl > select value current_version,
case substr(value,1,2) 
  when '12' then 'The current version is ' || value
  else value || ' should be updated to Oracle Database 12c. Go to www.oracle.com'
end as status
from v$parameter
where name='optimizer_features_enable';
/
VERSION       STATUS
------------- -----------------------------------------------
12.1.0.2      The current version is 12.1.0.2


step#4 – Else, if it is Oracle Database 12c, show CDB/PDBs details

SYS@orcl > select con_id, name, open_mode, restricted from v$containers;
CON_ID     NAME         OPEN_MODE  RES
------     -----------  --------  ---
1          CDB$ROOT     READ WRITE NO
2          PDB$SEED     READ ONLY  NO
3          PDBORCL      MOUNTED    NO

step#5 – If any PDB is not opened, set its mode to READ ONLY or READ WRITE accordingly

SYS@orcl > select con_id, name
from v$containers
where open_mode not IN ('READ WRITE', 'READ ONLY');
CON_ID     NAME
---------- ------------------------------
3          PDBORCL

SYS@orcl > alter pluggable database pdborcl open read write;
Pluggable database altered.

SYS@orcl > select con_id, name, open_mode, restricted 
from v$containers 
where name='PDBORCL';
/
CON_ID     NAME                           OPEN_MODE  RES
---------- ------------------------------ ---------- ---
3          PDBORCL                        READ WRITE NO


step#6 – Connect to the last PDB that is opened in READ WRITE mode.

SYS@orcl > alter session set container=pdborcl;
Session altered.

step#7 – Show the container ID of the PDB

SYS@orcl > sho con_id
CON_ID
------------------------------
3
SYS@orcl > sho parameter plsql
NAME                   TYPE        VALUE
-------------------    ----------- ------------------------------
plsql_ccflags          string
plsql_code_type        string      INTERPRETED
plsql_debug            boolean     FALSE
plsql_optimize_level   integer     2
plsql_v2_compatibility boolean     FALSE
plsql_warnings         string      DISABLE:ALL

step#8 – Disconnect from the database.

SYS@orcl > exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
c:TEMP>type task#1.ext | more

task#2 Automate task#1. Create a standalone procedure that will return the same result as task#1.

create or replace function funct_db_version
  return number
  authid current_user
is
  l_intval number;
  l_strval varchar2(2000);
  l_type number;
begin
  l_type := dbms_utility.get_parameter_value ('optimizer_features_enable', l_intval, l_strval);
  return to_number(substr(l_strval,1,2));
exception
  when others then
    dbms_output.put_line(SQLERRM);
end;
/

--Oracle Database 10.2
select funct_db_version from dual;

FUNCT_DB_VERSION
----------------
              10

--Oracle Database 12.1
SYS@orcl > select funct_db_version from dual;

FUNCT_DB_VERSION
----------------
              12

create or replace procedure proc_upgrade_to_12c
  authid current_user
is
  l_stmt varchar2(500);
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  CURSOR cur_id_name
  is 
  select con_id, name from v$containers where open_mode not IN ('READ WRITE', 'READ ONLY');
  TYPE t_id is table of v$containers.con_id%TYPE;
  TYPE t_name is table of v$containers.name%TYPE;
  l_list_id t_id;
  l_list_name t_name;
  l_con_id varchar2(256);
  l_con_name varchar2(256);
$END
begin
  if not funct_db_version = 12 then
    dbms_output.put_line(' The database ' ||DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || ' should be updated to Oracle Database 12c. Go to www.oracle.com' );
    return;
  end if;
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if not cur_id_name%ISOPEN then
    open cur_id_name;
  end if; 
  fetch cur_id_name BULK COLLECT into l_list_id, l_list_name;
  if l_list_id.COUNT > 0 then
    for i in 1..l_list_id.COUNT
    loop
      l_stmt := 'alter pluggable database ' ||lower( l_list_name(i) )|| ' open read write';
      execute immediate l_stmt;
      dbms_output.put_line('Container ' || lower( l_list_name(i) )|| ' open READ WRITE');
    end loop;
  end if; 
  select sys_context('USERENV','CON_ID') into l_con_id from dual;
  select sys_context('USERENV','CON_NAME') into l_con_name from dual;
  dbms_output.put_line('Current container ' || l_con_name || ' has ID ' || l_con_id);
  dbms_output.put_line('$$PLSQL_CODE_TYPE = ' || $$PLSQL_CODE_TYPE);
  dbms_output.put_line('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
  dbms_output.put_line('$$PLSCOPE_SETTINGS = ' || $$PLSCOPE_SETTINGS);
  if cur_id_name%ISOPEN then
    close cur_id_name;
  end if;
$END
exception
  when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if cur_id_name%ISOPEN then
    close cur_id_name;
  end if;
$END
  dbms_output.put_line(SQLERRM);
end proc_upgrade_to_12c;
/

--Oracle Database 10.2
SQL>exec proc_upgrade_to_12c;

The database 10.2 should be updated to Oracle Database 12c. Go to www.oracle.com

PL/SQL procedure successfully completed.

--Oracle Database 12.1
SYS@orcl > exec proc_upgrade_to_12c;
Current container CDB$ROOT has ID 1
$$PLSQL_CODE_TYPE = INTERPRETED
$$PLSQL_OPTIMIZE_LEVEL = 2
$$PLSCOPE_SETTINGS = IDENTIFIERS:NONE

PL/SQL procedure successfully completed.

task#3 Create a standalone procedure that shows details of CDB/PDBs such as container id, name, and open mode.


create or replace procedure proc_show_details_12c
  authid current_user
  is
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  CURSOR cur_id_name_mode 
  is 
  select con_id, name, open_mode from v$containers;
  TYPE t_id is table of v$containers.con_id%TYPE;
  TYPE t_name is table of v$containers.name%TYPE;
  TYPE t_mode is table of v$containers.open_mode%TYPE;
  l_list_id t_id;
  l_list_name t_name;
  l_list_mode t_mode;
  l_con_id varchar2(256);
  l_con_name varchar2(256);
  l_stmt varchar2(500);
$END
begin
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if funct_db_version = 12 then
    if not cur_id_name_mode%ISOPEN then
      open cur_id_name_mode;
    end if; 
    fetch cur_id_name_mode BULK COLLECT into l_list_id, l_list_name, l_list_mode;
    if l_list_id.COUNT > 0 then
      for i in 1..l_list_id.COUNT
      loop
        if l_list_name(i) = 'CDB$ROOT' then
          dbms_output.put_line('Root ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        else
          dbms_output.put_line('Pluggable DB ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        end if;
      end loop; 
    end if;
    if cur_id_name_mode%ISOPEN then
      close cur_id_name_mode;
    end if; 
  end if;
$ELSE
  dbms_output.put_line('Version '|| DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || 
  ' does not support this code. Upgrade to 12c.');
$END
exception
  when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
  if cur_id_name_mode%ISOPEN then
    close cur_id_name_mode;
  end if;
$END
  dbms_output.put_line(SQLERRM);
end proc_show_details_12c;
/

--Oracle Database 10.2
SQL> exec proc_show_details_12c;
Version 10.2 does not support this code. Upgrade to 12c.

PL/SQL procedure successfully completed.

--Oracle Database 12.1
SYS@orcl > exec proc_show_details_12c;
Root 1, CDB$ROOT open as READ WRITE
Pluggable DB 2, PDB$SEED open as READ ONLY
Pluggable DB 3, PDBORCL open as READ WRITE

PL/SQL procedure successfully completed.

Or, you can see all the above actions in a SQL*Plus Command Line Window:

Alter The Mode Of The Pluggable Database

Alter The Mode Of The Pluggable Database

task#4 Create a package with procedures that will return the same result as task#2 and task#3.


create or replace package pkg_checker
is
  function funct_db_version return number;
  procedure proc_show_details_12c;
  procedure proc_upgrade_to_12c;
end;
/



create or replace package body pkg_checker
is
  function funct_db_version
  return number
  is
    l_intval number;
    l_strval varchar2(2000);
    l_type number;
  begin
    l_type := dbms_utility.get_parameter_value ('optimizer_features_enable', l_intval, l_strval);
    return to_number(substr(l_strval,1,2));
  exception
    when others then
      dbms_output.put_line(SQLERRM);
  end;

  procedure proc_show_details_12c
  is
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    CURSOR cur_id_name_mode
    is 
    select con_id, name, open_mode from v$containers;
    TYPE t_id is table of v$containers.con_id%TYPE;
    TYPE t_name is table of v$containers.name%TYPE;
    TYPE t_mode is table of v$containers.open_mode%TYPE;
    l_list_id t_id;
    l_list_name t_name;
    l_list_mode t_mode;
    l_con_id varchar2(256);
    l_con_name varchar2(256);
    l_stmt varchar2(500);
$END
  begin
    if not funct_db_version = 12 then
      dbms_output.put_line( 'The database ' || DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || ' should be updated to Oracle Database 12c. Go to www.oracle.com' );
      return;
    end if;
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if not cur_id_name_mode%ISOPEN then
      open cur_id_name_mode;
    end if; 
    fetch cur_id_name_mode BULK COLLECT into l_list_id, l_list_name, l_list_mode;
    if l_list_id.COUNT > 0 then
      for i in 1..l_list_id.COUNT
      loop
        if l_list_name(i) = 'CDB$ROOT' then
          dbms_output.put_line('Root ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        else
          dbms_output.put_line('Pluggable DB ' || l_list_id(i) || ', ' || l_list_name(i) || ' open as ' || l_list_mode(i) );
        end if;
      end loop; 
    end if;
    if cur_id_name_mode%ISOPEN then
      close cur_id_name_mode;
    end if; 
$END
  exception
    when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if cur_id_name_mode%ISOPEN then
      close cur_id_name_mode;
    end if;
$END
    dbms_output.put_line(SQLERRM);
  end;

  procedure proc_upgrade_to_12c
  is
    l_stmt varchar2(500);
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    CURSOR cur_id_name
    is 
    select con_id, name from v$containers where open_mode not IN ('READ WRITE', 'READ ONLY');
    TYPE t_id is table of v$containers.con_id%TYPE;
    TYPE t_name is table of v$containers.name%TYPE;
    l_list_id t_id;
    l_list_name t_name;
    l_con_id varchar2(256);
    l_con_name varchar2(256);
$END
  begin
    if not funct_db_version = 12 then
      dbms_output.put_line('The database ' || DBMS_DB_VERSION.VERSION || '.' || DBMS_DB_VERSION.RELEASE || ' should be updated to Oracle Database 12c. Go to www.oracle.com' );
      return;
    end if;
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if not cur_id_name%ISOPEN then
      open cur_id_name;
    end if; 
    fetch cur_id_name BULK COLLECT into l_list_id, l_list_name;
    if l_list_id.COUNT > 0 then
      for i in 1..l_list_id.COUNT
      loop
        l_stmt := 'alter pluggable database ' ||lower( l_list_name(i) )|| ' open read write';
        execute immediate l_stmt;
        dbms_output.put_line('Container ' || lower( l_list_name(i) )|| ' open READ WRITE' ) ;
      end loop;
    end if;
    select sys_context('USERENV','CON_ID') into l_con_id from dual;
    select sys_context('USERENV','CON_NAME') into l_con_name from dual;
    dbms_output.put_line('Container ' || l_con_name || ' has ID ' || l_con_id);
    dbms_output.put_line('$$PLSQL_CODE_TYPE = ' || $$PLSQL_CODE_TYPE);
    dbms_output.put_line('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
    dbms_output.put_line('$$PLSCOPE_SETTINGS = ' || $$PLSCOPE_SETTINGS);
    if cur_id_name%ISOPEN then
      close cur_id_name;
    end if;
$END
  exception
    when others then
$IF DBMS_DB_VERSION.VERSION >= 12 $THEN
    if cur_id_name%ISOPEN then
      close cur_id_name;
    end if;
$END
    dbms_output.put_line(SQLERRM);
  end;
end;
/

--Oracle Database 10.2
SQL> exec pkg_checker.proc_show_details_12c;
The database 10.2 should be updated to Oracle Database 12c. Go to www.oracle.com

PL/SQL procedure successfully completed.

--Oracle Database 12.1
SYS@orcl > exec pkg_checker.proc_show_details_12c;
Root 1, CDB$ROOT open as READ WRITE
Pluggable DB 2, PDB$SEED open as READ ONLY
Pluggable DB 3, PDBORCL open as READ WRITE

PL/SQL procedure successfully completed.

--Oracle Database 10.2
SQL> exec pkg_checker.proc_upgrade_to_12c;
The database 10.2 should be updated to Oracle Database 12c. Go to www.oracle.com

PL/SQL procedure successfully completed.

--Oracle Database 12.1
SYS@orcl > exec pkg_checker.proc_upgrade_to_12c;
Container CDB$ROOT has ID 1
$$PLSQL_CODE_TYPE = INTERPRETED
$$PLSQL_OPTIMIZE_LEVEL = 2
$$PLSCOPE_SETTINGS = IDENTIFIERS:NONE

PL/SQL procedure successfully completed.

Now, you can repeat the process over and over again, see the SQL*Plus Command Line Window:

Repeat the Action Over and Over Again

Repeat the Action Over and Over Again

So far, the following schema objects were created:

--Oracle Database 10.2
SYS$SQL> 
select substr(object_name,1,20), substr(object_type,1,20)
from user_objects  
where created > sysdate - 1;

SUBSTR(OBJECT_NAME,1 SUBSTR(OBJECT_TYPE,
-------------------- -------------------
PROC_UPGRADE_TO_12C  PROCEDURE
PROC_SHOW_DETAILS_12 PROCEDURE
PKG_CHECKER          PACKAGE
PKG_CHECKER          PACKAGE BODY
FUNCT_DB_VERSION     FUNCTION

--Oracle Database 12.1
SYS@orcl > select lpad(substr(object_type,1,20),20) type, substr(object_name,1,20) name, created --12.1
  2  from user_objects
  3  where object_name like 'FUNCT%'
  4  or object_name like 'PROC%'
  5  or object_name like 'PKG%';

TYPE                 NAME                 CREATED
-------------------- -------------------- ---------
             PACKAGE PKG_CHECKER          21-AUG-15
        PACKAGE BODY PKG_CHECKER          21-AUG-15
           PROCEDURE PROC_SHOW_DETAILS_12 21-AUG-15
           PROCEDURE PROC_UPGRADE_TO_12C  21-AUG-15
            FUNCTION FUNCT_DB_VERSION     21-AUG-15

10 rows selected.

Final Task# Drop all objects from the database that were created in the above examples!

SYS@orcl > drop function funct_db_version;
SYS@orcl > drop procedure proc_upgrade_to_12c;
SYS@orcl > drop procedure proc_show_details_12c;
SYS@orcl > drop package pkg_checker;

Instead of Summary

The purpose of the task#1 is to show how many solutions you can create for the one task, in this case it was to find out the version and the release of the Oracle Database. This can also be done easily with this select statement:

c:TEMP>sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 10 18:14:23 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SYS@orcl > COL PRODUCT FORMAT A40
SYS@orcl > COL VERSION FORMAT A15
SYS@orcl > COL STATUS FORMAT A15
SYS@orcl > SELECT * FROM PRODUCT_COMPONENT_VERSION;

PRODUCT                               VERSION    STATUS
------------------------------------ ----------  ------------
NLSRTL                               12.1.0.2.0  Production
OracleDatabase12c Enterprise Edition 12.1.0.2.0  64bit Production
PL/SQL                               12.1.0.2.0  Production
TNS for 64-bit Windows:              12.1.0.2.0  Production

SYS@orcl >

Dictionary
sqlplus = “SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database installation. It has a command-line user interface.”, [ref2]
spool = SQL*Plus command that stores query results in a file, or optionally sends the file to a printer, [ref2]
.ext = the extension of the file where command spool stores the query results, [ref2]
append = The option that adds the contents of the buffer to the end of the file you specify in the spool command, [ref2]
CDB = a multitenant container database,
PDB = a pluggable database,
v$parameter = The dynamic performance view that displays information about the initialization parameters that are currently in effect for the session, [ref3]
v$containers = The dynamic performance view that displays information about PDBs and the root associated with the current instance, [ref3]
select = SQL statement that retrieves data from one or more tables, object tables, views, object views, or materialized views, [ref1]
CDB$ROOT = Oracle Database 12c root container, [ref4]
PDB$SEED = Oracle Database 12c seed PDB, [ref4]
PDBORCL = Oracle Database 12c pluggable database, a user-created entity that contains the data and code required for a specific set of features, [ref4]
READ ONLY = A database that is available for queries only and cannot be modified, [ref4]
READ WRITE = A database that is available for queries and that can be modified, [ref4]
MOUNTED = An database instance that is started and has the database control file open, [ref4]
alter pluggable database = SQL statement that modifies a pluggable database (PDB), [ref1]
alter session = SQL statement that sets or modifies any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database, [ref1]
show user = SQL*Plus command that shows the username you are currently using to access SQL*Plus, [ref2]
show con_id = SQL*Plus command that displays the id of the Container to which you are connected when connected to a Consolidated Database. If issued when connected to a non-Consolidated Database, this command returns 0, [ref2]
exit = SQL*Plus command that let you exit SQL*Plus, [ref2]
case = SQL expressions that let you use IF … THEN … ELSE logic in SQL statements without having to invoke procedures, [ref1]

Further reading
If you want to be familiar with Oracle SQL, please see [ref1].
If you want to know what kind of the CLI (Command Line Interface) SQL*Plus is, please see [ref2].
if you want to know details of the Oracle Database meta data, please see [ref3].
If you want to understand the Oracle Database architecture, please see [ref4].
If you want to know details of PL/SQL (Oracle extension of SQL), please see [ref5].

References
[ref1] SQL*Plus® User’s Guide and Reference Release 12.1 E18404-12
[ref2] Oracle® Database SQL Language Reference 12c Release 1 (12.1) E41329-09
[ref3] Oracle® Database Reference 12c Release 1 (12.1) E41527-15
[ref4] Oracle® Database Concepts 12c Release 1 (12.1) E41396-12
[ref5] Oracle® Database PL/SQL Language Reference 12c Release 1 (12.1) E50727-04
[ref6] Oracle® Database PL/SQL Packages and Types Reference 12c Release 1 (12.1)
E41829-05

Set up the test environment for database-centric applications

Posted on by
Reply

The free version of the Oracle Database 11g2 express edition could be downloaded from the

http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html

xe11g_downloads

The documentation about the installation of the database and the application development could be found on
http://www.oracle.com/technetwork/database/database-technologies/express-edition/documentation/index.html

xe11g2_documents

The list of documents on the next picture shows that there are a lot of to be read, learn, and practice.
The first to be read is the License document, then the Installation Guide, the Getting Started Guide, etc.

xe11g2DocumentList

After the successful download of the database 11g2 on a Windows XP 32-bit computer,
here are the steps of the installation process that would take a couple of minutes:
step#1 Click twice on the DISK/setup.exe file to start the installation.

xe11g_install

step#2 Then, accept the terms in the Oracle license agreement.

xe11g_install_step2

step#3 Next, choose destination location for the database files.

xe11g_install_step3

step#4 Next, decide about the password for the database accounts SYS and SYSTEM.

xe11g_install_step4

step#5 Review all settings such as destination folders and ports.

xe11g_install_step5

step#6 After a few minutes, the successful installation would finish with this message.

xe11g_install_step6

step#7 Start the Oracle 11g2 database by clicking on
AllPrograms=> Oracle Database 11g Express Edition => Get Started.

Start11g2

step#8 The Database would start in the Google Chrome Browser on the “127.0.0.1:8080/apex/”.

InGoogleChrome

step#9 Click on Storage and login as the SYS user.

LoginAsSYS

step#10 The Storage page would show the current tablespaces: SYSAUX, SYSTEM, UNDO, TEMP, and USER, and their free and used spaces in MB.

xe11g2_tablespaces

step#11 Logout from the database.
step#12 Open the Microsoft Command Prompt window and connect to the database as sysdba by using SQL*Plus command user interface.

SQL> sqlplus / as sysdba

sys@XE> select instance_name || version from v$instance;

INSTANCE_NAME||VERSION
---------------------------------
xe11.2.0.2.0

step#13 Unlock user “HR” account, change its password, and test it to be sure that all is set up correctly

sys@XE> ALTER USER hr ACCOUNT UNLOCK;

User altered.

sys@XE> ALTER USER hr IDENTIFIED BY hr;

User altered.

sys@XE> connect hr/hr
Connected.

hr@XE> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
COUNTRIES TABLE
DEPARTMENTS TABLE
EMPLOYEES TABLE
EMP_DETAILS_VIEW VIEW
JOBS TABLE
JOB_HISTORY TABLE
LOCATIONS TABLE
REGIONS TABLE

8 rows selected.

hr@XE> connect / as sysdba
Connected.

step#14 Create a new schema

sys@XE> create user biljana identified by davidovic;

User created.

sys@XE> grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
> CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
> CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, -
> CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
> to biljana;

Grant succeeded.

sys@XE> connect biljana/davidovic
Connected.

biljana@XE> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
APEX$_ACL TABLE
APEX$_WS_FILES TABLE
APEX$_WS_HISTORY TABLE
APEX$_WS_LINKS TABLE
APEX$_WS_NOTES TABLE
APEX$_WS_ROWS TABLE
APEX$_WS_TAGS TABLE
APEX$_WS_WEBPG_SECTIONS TABLE
APEX$_WS_WEBPG_SECTION_HISTORY TABLE
DEMO_CUSTOMERS TABLE
DEMO_ORDERS TABLE
DEMO_ORDER_ITEMS TABLE
DEMO_PRODUCT_INFO TABLE
DEMO_STATES TABLE
DEMO_USERS TABLE
DEPT TABLE
EMP TABLE

17 rows selected.

step#15 Create a new table

biljana@XE> CREATE TABLE "BILJANA"."HOWWELEARN"
( "ID" NUMBER,
"HOW" VARCHAR2(20),
"VALUE" NUMBER(3,0);

Table created.

biljana@XE> alter table howwelearn add constraint test_pk primary key (id);
Table altered.

biljana@XE> CREATE SEQUENCE learn_seq START WITH 1 INCREMENT BY 1 NOCYCLE NOCACHE;
Sequence created.

biljana@XE> create trigger LEARN_TR_INSERT
before
insert on howwelearn
for each row
declare
var_id number;
begin
select learn_seq.nextval into var_id from dual;
:new.ID := var_id;
end;

Trigger created.

biljana@XE> insert into howwelearn(how, value) values( 'Lecture',5 );
1 row created.

step#16 Create the function funct_get_table_ddl and get DDL statement about the created table ‘HowWeLearn’

biljana@XE> CREATE OR REPLACE FUNCTION funct_get_table_ddl
RETURN CLOB
AUTHID CURRENT_USER
IS

-- Define local variables.

l_h NUMBER; --handle returned by OPEN
l_th NUMBER; -- handle returned by ADD_TRANSFORM
l_doc CLOB; --returned sql ddl statement

BEGIN

-- Specify the object type.

l_h := DBMS_METADATA.OPEN('TABLE');

-- Use filters to specify the particular object desired.

DBMS_METADATA.SET_FILTER(l_h,'SCHEMA','BILJANA');
DBMS_METADATA.SET_FILTER(l_h,'NAME','HOWWELEARN');

-- Request that the metadata be transformed into creation DDL.

l_th := DBMS_METADATA.ADD_TRANSFORM(l_h,'DDL');

-- Fetch the object.

l_doc := DBMS_METADATA.FETCH_CLOB(l_h);

-- Release resources.

DBMS_METADATA.CLOSE(l_h);

-- Return the result

RETURN l_doc;

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

biljana@XE> SET PAGESIZE 0
biljana@XE> SET LONG 1000000
biljana@XE> SELECT funct_get_table_ddl FROM dual;
biljana@XE> set timing on
biljana@XE> SELECT funct_get_table_ddl FROM dual;

CREATE TABLE "BILJANA"."HOWWELEARN"
( "ID" NUMBER,
"HOW" VARCHAR2(20),
"VALUE" NUMBER(3,0),
CONSTRAINT "LEARN_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"
Elapsed: 00:00:01.12

step#17 The data inserted into the table “HOWWELEARN” has been taken from my favorite YouTube channel GoogleTechTalks.

Here is the section from its ABSTRACT:

Explore the brain’s amazing ability to change throughout a person’s life. This phenomenon—called neuroplasticity—is the science behind brain fitness, and it has been called one of the most extraordinary scientific discoveries of the 20th century.

PBS had recently aired this special, The Brain Fitness Program, which explains the brain’s complexities in a way that both scientists and people with no scientific background can appreciate.

This is opportunity to learn more about how our minds work—and to find out more about the latest in cutting-edge brain research, from the founder of Posit Science and creator of the Brain Fitness Program software, Dr. Michael Merzenich.
The video “Think faster focus better and remember more – Rewiring our brain to stay younger” by Dr. Michael Merzenich can be watched on YouTube on


And, here is the important message learned:

biljana@XE> select 'After ' ||
rpad(how,15) ||
' We Learn ONLY ' ||
nvl(value,0) || '%'
from howwelearn;

After Lecture We Learn ONLY 5%
After Reading We Learn ONLY 10%
After Audio We Learn ONLY 20%
After Visual We Learn ONLY 20%
After Demonstration We Learn ONLY 30%
After Discussion We Learn ONLY 50%
After Practice by doing We Learn ONLY 75%
After Teach others We Learn ONLY 90%
After Nothing We Learn ONLY 0%

9 rows selected.

Elapsed: 00:00:00.00
biljana@XE>

As we can see from the result set, we remember things mostly only as we start teaching others about it.

Summary

In this post the test environment for the applications that are database-centric has been set up using the free express edition of the Oracle database 11g2.
I hope this will be helpful for anyone who would like to learn more about Oracle Database.

Thank you for visiting my blog!

Have a nice day!

P.S. Keep climbing Mount Olympus ( I mean keep learning and make things happen ) !