Learning The "J"

How to call Java stored procedures/Classes from PL/SQL

There is lot of requirements where we need to use Java like zipping the files stored in database, managing files on server.
Oracle provides the way to access Java stored Procedure and classes from PL/SQL, we can use according to our need
Following steps are required:

  1. Create the Java code elements. You can do this in Netbeans, or in any other Java Integrated Development Environment. Load the Java class(es) into Oracle using the loadjava command-line utility or the CREATE JAVA statement.
  2. Publish the Java class methods inside PL/SQL by writing wrapper programs in PL/SQL around the Java code.
  3. Grant privileges as required on the PL/SQL wrapper programs and the Java class referenced by the PL/SQL wrapper.
  4. Call the PL/SQL programs

Simple Demonstration:

1. Write Java class.

You may write the below code as a simple JAVA program and load it into database. But here we are using the 11g feature, which enables us to write java code directly on sql prompt.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "Hello" AS
public class Hello
{
 public static String world()
 {
  return "Hello world";
 }
};

2. Load java class into database. We can also load a compiled .class file or .jar file using loadjava utility. In this case first step will be ignored.

C:\oracle\bin>loadjava -user scott/tiger -resolve Hello.class

LoadJava utility: The loadjava utility loads Java source and class files into the database. When class files are created in a conventional manner, outside the database; loadjava is used to get them into the database.

3. Write PL/SQL Wrapper Program:

CREATE OR REPLACE
FUNCTION helloworld RETURN VARCHAR2 AS
LANGUAGE JAVA NAME 'Hello.world () return java.lang.String';
/

4. Call PL/SQL Wrapper Program:

DECLARE
my_string VARCHAR2(400 CHAR);
BEGIN
my_string := helloworld();
dbms_output.put_line('The value of the string is ' || my_string);
END;
/

Here is the syntax for loadjava utility:

loadjava {-user | -u} username/password[@database]
[-option_name [-option_name] ...] filename [filename ]...
where option_name stands for the following syntax:
{ {andresolve | a}
| debug
| {definer | d}
| {encoding | e} encoding_scheme_name
| {force | f}
| {grant | g} {username | role_name}[,{username | role_name}]...
| {oci8 | o}
| oracleresolver
| {resolve | r}
| {resolver | R} "resolver_spec"
| {schema | S} schema_name
| {synonym | s}
| {thin | t}
| {verbose | v} }

loadjava requires two database privileges to load java objects into your own schema: CREATE PROCEDURE and CREATE TABLE. To load Java objects into a schema other than the currently connected user, CREATE ANY PROCEDURE and CREATE ANY TABLE privileges are required.

If loading many Java class files at one time, it is advisable to put them in a JAR file and load them into the database at one time, since the loadjava program will also load JAR files.
Oracle has created two new roles to support Java security. For many Java-based operations within the database, you will not have to work with these roles. If, on the other hand, you want to interact with the operating system (to access or modify operating system files, for example), you need to be granted one of the following roles:

JAVASYSPRIV
JAVAUSERPRIV.

You grant these roles as you would any other database role. For example, if I want to allow SCOTT to perform any kind of Java-related operation, I would issue this command from a SYSDBA account:

GRANT JAVASYSPRIV TO SCOTT;
GRANT EXECUTE ON SYS.DBMS_JAVA TO SCOTT

 

Here is an another example that is acting as a TCP/IP client socket and sends data to a TCP/IP server.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED "CounterDisplay" AS

import java.io.*;
import java.io.*;
import java.net.*;

       public class CounterDisplay
       {
              public static String display(String ip,int port,String counternumber,String counter)
              {
                     String errmsg="Working Fine";
                     try{
                            Socket clientSocket = new Socket(ip, port);
                            DataOutputStream outToServer = new DataOutputStream(clientSocket.getOutputStream());
                            String dataToDisplay = counter+counternumber;
                            outToServer.write(dataToDisplay.getBytes());

                            clientSocket.close();
                            outToServer.close();                            
                     }
                     catch(Exception e){
                         errmsg=e.getMessage();            
                         return errmsg;
                     }
                     return errmsg;
              }
       }
  
--******************************************************************************
CREATE OR REPLACE 
FUNCTION COUNTERDISPLAY_FUNC(IP IN VARCHAR2,PORT IN NUMBER,COUNTERNUM IN VARCHAR2,COUNTER IN VARCHAR2) RETURN VARCHAR2 AS
         LANGUAGE JAVA NAME 'CounterDisplay.display(java.lang.String,int,java.lang.String,java.lang.String) return java.lang.String';
--******************************************************************************
DECLARE  on_completion VARCHAR2(400);
    BEGIN
       on_completion :=COUNTERDISPLAY_FUNC('192.192.21.42',5100,'5','503');
       dbms_output.put_line('' || on_completion);
    END;
--******************************************************************************
Advertisements

NetBeans in Cancer Research in Pakistan

What Geertjan wrote about this https://blogs.oracle.com/geertjan/entry/netbeans_in_cancer_research_in

SKM-LAS introduction on Netbeans showcase under “Healthcare” section.
https://platform.netbeans.org/screenshots.html

8-28-2015 7-45-39 PM

This is a laboratory automation integration solution being created at the aforementioned hospital in Pakistan on top of the NetBeans Platform and with NetBeans IDE. It integrates 63 pathology lab machines and analyzers into one coherent application, supporting a range of protocols. The system bi-directionally communicates with true random access analyzers, such as General Chemistry (e.g., Hitachi P-800, COBAS 6000) and Special Chemistry (e.g., Advia, Immulute), where analyzers read the tube barcodes and inquire the system for the test orders and receives them, analyzes samples, and reports back the results to the system. Other features include user/roles/rights management, integrated simulation for quick testing and debugging, and an easily accessible user interface for maintaining the analyzers and other machines, together with their support documents and manuals.

Netbeans Ribbon Bar Integration

This tutorial is based on the following tutorial https://platform.netbeans.org/tutorials/nbm-ribbonbar.html#intro
Download the source code from https://java.net/projects/nbribbonbar/. After configuring it you should see something similar to the following
flprojstructure

 

 

On expanding the Flamingo module you could see following
fljars

It is to be noted that flamingo.jar, forms-1.3.0.jar and trident.jar are added in this module as libraries

Mark all the packages of Flamingo as public

flpublic

 

 

 

Make a new project and add the Flamingo and Flamingo Integration modules to your newly created project

flproj

Expand the Flamingo Integration module and add the resources package in it, in the resources package we can place our icons and images

flint

Ribbon bar

ribbonstruct

Open the LayerRibbonComponentProvider class present in the package com.pinkmatter.modules.flamingo

Add the following to the @Override method public JComponent createRibbon()


JRibbon ribbon = new JRibbon();

JCommandButton LASSetupbutton1 = new JCommandButton("DICOM Viewers", null);
LASSetupbutton1.setIcon(  ImageWrapperResizableIcon.getIcon(LayerRibbonComponentProvider.class.getClassLoader().getResource("/com/pinkmatter/api/flamingo/resources/lasimg.jpg"), new Dimension(100, 100)));

JRibbonBand band1=new JRibbonBand("LAS band1", null);
band1.addCommandButton(LASSetupbutton1, TOP);
band1.setResizePolicies((List) Arrays.asList(new IconRibbonBandResizePolicy(band1.getControlPanel())));

RibbonTask task1 = new RibbonTask("LAS Setup", band1);

band1.setResizePolicies((List) Arrays.asList(new CoreRibbonResizePolicies.None(band1.getControlPanel()),
        new IconRibbonBandResizePolicy(band1.getControlPanel())));

ribbon.addTask(task1);		

LASSetupbutton1.addActionListener(new ActionListener() {
    public void actionPerformed(ActionEvent e) {           
        Action action = FileUtil.getConfigObject("Actions/Window/org-netbeans-modules-options-OptionsWindowAction.instance", Action.class);
        action.actionPerformed(e);
        }
    });
	
return ribbon;

Related links kinks
https://dzone.com/articles/office-laf-netbeans-platform
https://java.net/projects/officelaf/sources
https://java.net/projects/nbribbonbar/
https://blogs.oracle.com/geertjan/entry/decreasing_the_height_of_the1
https://blogs.oracle.com/geertjan/entry/fixing_the_ribbon_bar_integration