How to build a Java app and load it into an Oracle DB

Create the Java class

[java]public class Hello2 {

public static String hello() {
return “Hello World.”;
}

public static String hello(String name) {
return “Hello ” + name + “.”;
}

public static void main(String[] args) {
System.out.println(Hello2.hello());
System.out.println(Hello2.hello(“Java”));
}
}[/java]

Test the output (console):

[bash]java Hello[/bash]
Output:
[text]Hello World.
Hello Java.[/text]
After compilation, run the following (via puntty on DB server) to load it into the Database (console)

[bash]loadjava -r -f -o -user dbuser/dbpass HelloWorld2.class
# run dropjava to unload any loaded java app/class[/bash]
Calling it from JDeveloper:
[sql]–Create a db connection in JDev as SYS
–R-click on this new connection and select “SQL Worksheet”
–Enter the following in command window:
call dbms_java.LOADJAVA(‘/home/oracle/myapp.jar -verbose -force -grant SCOTT -resolve’);
–to Drop it:
call dbms_java.DROPJAVA(‘/home/oracle/myapp.jar -verbose’);[/sql]

Using a Package (see for Function further down):

[sql]–Create PL/SQL wrapper package bind to a Java class file
CREATE OR REPLACE PACKAGE hello2 AS
–Null argument function header
FUNCTION hello RETURN VARCHAR2;
–One argument function header
FUNCTION hello(who VARCHAR2) RETURN VARCHAR2;
END hello2;
/
–Package body
CREATE OR REPLACE PACKAGE BODY hello2 AS
–Null argument function body
FUNCTION hello RETURN VARCHAR2 IS
LANGUAGE JAVA NAME ‘Hello2.hello() return String’;
–One argument function body
FUNCTION hello(who VARCHAR2) RETURN VARCHAR2 IS
LANGUAGE JAVA NAME ‘Hello2.hello(java.lang.String) return String’;
END hello2;
/[/sql]

Now check that the objects are present (sql):

[sql]SELECT object_name, object_type, status
FROM user_objects
WHERE object_name IN (‘Hello2’, ‘HELLO2’);[/sql]

The output should be something like:

[text]OBJECT_NAME  OBJECT_TYPE   STATUS
———–  ————  ——
HELLO2 PACKAGE VALID
HELLO2 PACKAGE BODY VALID
Hello2 JAVA CLASS VALID[/text]

Finally, test the package by calling:

[sql]SELECT  hello2.hello(‘Mr Java’) FROM dual;[/sql]

Output:

[text]HELLO2.HELLO(‘MRJAVA’)
———————————–
Hello Mr Java.[/text]

Using a PL/SQL Function rather than a Package

[sql]create or replace function hello2 return varchar2 as
language java name ‘Hello2.hello() return java.lang.String’;[/sql]

To call the function, from sqlplus set a variable and dump the string in there:
[sql]variable tmp varchar2(20);
call hello2.hello() into :tmp;[/sql]
Output:
[text]tmp
———
Hello World.[/text]

how to create a cursor to read through table rows

[sql]declare
  counter1 number := 0;
  counter2 number := 0;
  cursor cur_t is
    SELECT * from emp where e_name = 'Ptere'; --set cursor, a select statement
  rec_t emp%rowtype; --defines this is a row from the database

begin
  counter1 := 0;
  counter2 := 0; --some counters to log the records being changed
  open cur_t; --open cursor

  loop
    --begin loop
  
    fetch cur_t
      into rec_t; --fetches cursor row into the rec_t variable
  
    exit when cur_t%notfound;
    UPDATE emp SET e_name = 'Peter' where e_name = 'Ptere';
    counter1 := counter1 + 1; --increment updates
    insert into update_log
      (record_id, table_name, description) --useful for ref. purposes
    values
      (rec_t.emp_id, 'emp', 'Peter');
    counter2 := counter2 + 1; --increment inserts
    commit;
  end loop;
  --row_count := sql%rowcount; --sql counter for update statements
  dbms_output.put_line('EMP: ' || 'name changed, updated: ' || counter1);
  dbms_output.put_line('insert into UPDATE_LOG: ' || counter2);
  close cur_t; --close the cursor
end;
[/sql]

Note for DBMS output we need to set the command prompt:[sql]set serveroutput on size 1000000 [/sql]format wrapped

how to find specific text in oracle schema using a procedure and save to table

The following function will find any text matching the variable set within the procedure.
(the output will display on screen -dbms_output)

To execute: <SQL> [sql]execute update_found_records_proc;[/sql]

We could also add a parameter on calling the procedure as:
CREATE OR REPLACE PROCEDURE FIND_IN_RECORDS_PROC(val IN varchar2) IS…
note: ‘val’ is set once and used anywhere later in the code

Credit:: Rene Nyffenneger on Oracle

PL/SQL code: (more…)

outer joins

Say we have two tables:

tab1  tab2
1          2
2         3
4         4
5

[sql]select t1.col1, t2.col2
from tab1 t1, tab2 t2
where t1.col1 = t2.col2(+) –pl/sql

–Informix SQL: …from tab1 t1, outer tab2 t2[/sql]

Result:
t1 t2
1   –
2   2
4   4
5   –

–this will take primary table tab1 rows and match with tab2, including tab2’s nulls

 

useful pl/sql code snippets

Create a “New Line” within a PL/SQL formula, use the following:
[sql]/*
Function to calculate the retirement date of a person
based on person’s date of birth

Parameter: p_birthdate, date
Returns: ret_date, date
*/
CREATE OR REPLACE FUNCTION get_retire_date(p_birthdate IN DATE)
RETURN date IS
ret_date date;
BEGIN
— Calculates with months: 756 = 63 * 12
ret_date := ADD_MONTHS (p_birthdate, 757);
RETURN ret_date;
END get_retire_date;[/sql]

Create a “New Line” within a PL/SQL formula, use the following:
[sql]CHR(10)[/sql]

Use ‘decode()’ with ‘nvl()’ where you can’t use an if statement:
[sql]decode(field_name, test1, test2, else)
decode(pr_grade, nvl(pr_grade, ‘ ‘), ‘,’, ‘ ‘) as grade[/sql]

Update all records in column with specific character(s):
-the following will find char 12 and turn it into a “5”, only if it is a match to “4”
[sql]– records text: “//10.10.10.4/test/myfile34.rtf”
update mytable t
set t.column_a = substr(t.column_a, 1, 11) || ‘5’ || substr(t.column_a, 13, 50)
where substr(t.column_a, 12, 1) = ‘4’;
— result AFTER update: “//10.10.10.5/test/myotherfile.rtf” [/sql]

Search and Replace function:
-the following with search for a string of text and replace
[sql]– records text: “The sky is blue”
update mytable t
set t.column_a = replace(t.column_a, ‘blue’, ‘green’);
— result: “The sky is green”[/sql]

another replace method:
[sql]– records text: “\\10.10.10.5\test\some_doc.doc”
select replace(t.column_a, ‘\\10.10.10.5\test’, ‘k:’) from mytable t
— result: “k:\some_doc.doc”[/sql]