Development

ODTUG Kaleidoscope 2010, June 27 - July 1st Washington DC is a great conference for Oracle developers and architects, offering the best content by renowned experts. We will have the privilege of delivering two Optimizer sessions this year, 'Explaining the Explain plan' and 'Finally Plan Stability during Database Upgrade with SQL Plan Management'. In the Explain the Explain plan session we will discuss each aspect of an execution plan (from selectivity to parallel execution), explain what information you should be getting from the plan, and how it will affects the execution. While in the SQL Plan Management session, we will will give detailed instructions on how to capture your existing execution plans before you upgrade to 11g, as well as an in-depth discussion on what to expect from the Optimizer after you upgrade to 11g.
ODTUG is a great conference where you can learn lots in a fun and casual atmosphere. Looking forward to seeing some of you there!

Interesting article in the Chicago Tribune today: "Toyota rebuts claims of SIU professor: In webcast, consulting firm re-creates electronic glitch in other vehicle brands".

First, David Gilbert demonstrates to Congress a way to cause sudden acceleration in Toyota vehicles.

Then Toyota countered his demonstration as follows: "Using similar methodology, engineers at Toyota's facility on Monday duplicated the results on camera in seven other vehicles, including a Chevrolet Malibu, Honda Accord and Ford Fusion. Officials said the results, though not maligning other manufacturers, show that the methodology was essentially flawed."

Gee, I don't know. It seems like you could just as legitimately conclude that the problems experienced by Toyota owners could well be showing up in other vehicles.

I must admit that as a programmer, it scares me to think that my safety (and the safety of my family members) when driving is so dependent on software.

Won the iPhone 2g in the bid for 209$ and Internatianl Shipping 28$

USPS Tracking Link
http://trkcnfrm1.smi.usps.com/PTSInternetWeb/InterLabelInquiry.do?origTrackNum=CQ003851850US
Shipped from USA:
Label/Receipt Number: CQ00 3851 850U S
Class: Priority Mail International Parcels
Service(s): International Parcels
Status: Out of Foreign Customs
    -Out of Foreign Customs, February 13, 2010, 4:21 pm, INDIA
    -Arrived Abroad, February 13, 2010, 2:49 pm, INDIA
    -International Dispatch, February 09, 2010, 3:52 pm, FOREIGN CENTERJERSEY CITY NJ 099
    -Arrival
    -Electronic Shipping Info Received, February 01, 2010

Indian Post Tracking Link ( No use as it wont show any info)
http://services.ptcmysore.gov.in/speednettracking/Track3.aspx?articlenumber=CQ003851850US

Trying to figure out the Post office number
http://services.ptcmysore.gov.in/Spcdetails/Managers.aspx?code=9
Bangalore    560001    22861435,22861390    22861994    spc.bangalore@indiapost.gov.in    T.R.SHANKAR

I called
GPO Post office Bangalore-- Bangalore 080 2286 7302
They asked me to call Parcel section  080 2558 9993

When I called they said it is pending in Custom screening at Bangalore
and asked me to call this number: Bangalore Postal Customs: 2558 9992
and they provided me with the new India tracking number #1357

AS soon as Indian Post recieves the parcel they code it with their own number in this case mine was 1357
So no info in the Indian Post office tracking website or USPS website

Called the above number they said it got cleared and sent it back to Post, couldnt get the date when they cleared and send it back.
Figured out manually visiting GPO that it got sent to Local Post office on 19th Feb

Checked in the local Post office for my address and I found the package with custom duty as it wasnt marked as GIFT :(
The Customs duty was:
Total Value: 209.99$
Postage and Fees: 25.17$

Indian Custom duty: 1758/-
Indian Postal Fees:  100/-
-------------------------
So, total fine 1858/- :(
===========================

So, next time if you ordering something make sure you ask the vendor to mention/mark it as GIFT
Because I got one more iPhone2G without any fine:
Label/Receipt Number: LC28 9608 926U S
Class: Priority Mail International Parcels
Service(s): International Letter
Status: Acceptance

Only difference between these two is one was marked as GIFT + Service Type: International Letter
The one where custom duty charged was NOT marked as GIFT + Service(s): International Parcels

Since my last Oracle APEX 4.0 blog coverage – Part 2 posting, new articles have been posted about our early adopter release 2. Have fun reading them! APEX 4.0 EA2 – Websheets by Dimitri Gielis [Oracle APEX 4.0] Text Field with autocomplete by Whitehorses APEX 4.0 EA2: Improved debugging, my new favorite feature by Mark Lancaster [Oracle APEX 4.0] [...]

It took a little bit longer as expected, but the new feature list of Oracle APEX 4.0 Early Adopter 2 is now online (link is at the end of the posting). The list contains the EA1 features as well. Hopefully we didn’t miss any new feature, there are to many to count. One of the [...]

In APEX 4.0 EA2 the Websheet functionality is enabled. Websheets allow you to share information with others in a very quick, user friendly and secure way.

Let's have a look at an example. If you go to Application Builder in APEX 4.0 there is a new type there called "Websheet Applications".


We create a new Websheet Application and give it a name and possibly some content that will appear on the home page. You can type the content with the WYSIWYG editor (CK Editor) by clicking on the arrow button.


Before it creates the Websheets, the wizard gives a summary page and that is it.
Websheets are even simpler to create than a normal APEX (database) application.

You can compare Websheets with a preconfigured APEX application. A whole Framework is build for you with a lot of features out-of-the-box. You can just use all that.

When you run the Websheet from the App Builder you come into "Websheet-land". As a default Authentication mechanism it uses the Application Express Account. But you can change that by going into the Websheet Properties to Public Access, Single Sign On, LDAP or Custom.


When logged in, you come into the Websheet Framework. As we have Websheet Development Access, we can change the Websheet. In the top menu and on the right hand side you see what you can do. E.g. you can add pages, sections, data grids etc. There's a lot to explore there!


In the different sections of the page you also have Edit links, which allow you to easily change the text, all with a (WYSIWYG) html editor.


I see two big parts of Websheets; sharing information and data. The information is done through using Pages, sections, tags, files etc. just like you do in a Wiki/CMS. But where it outperforms all the typical wiki or content management systems, is with the data part!

One of the killer features of Websheets are the Data Grids. If you go to Data > Data Grids and create a new Data Grid, you have the choice to start from scratch or from Excel/Text with copy/paste. That allows you to create "a table" on the fly which holds your data (actually it's a record in the Websheet repository). This Data Grid look very similar to an Interactive Report (see Actions button), but it allows to do inline editing and a lot more (see Manage button)!

Websheets are too big to discuss in one blog post. There are so many areas which could be covered. There's the wiki part, the data part, the administration (security, dashboards), ...

Maybe one last thing... look at the url of a Websheet (login with end/end)... it doesn't start with f?p... it starts with ws?p ;-)

The call for papers for both Oracle Open World and Oracle Develop is now open here. This is your chance to share your experience with JDeveloper and ADF with your peers, and get a free pass to the conference. From... shay.shmeltzer http://blogs.oracle.com/shay

Pizza Hut is advertising (hard) its new recession-proof pricing: "Any piza. Any size. Any crust. Any toppings. Just $10." See? They say so right here:


 But wait! Is that fine print at the bottom? Yes, it is! And what does it say? "ADDITIONAL CHARGE FOR STUFFED CRUST PIZZA"
Now, I must admit: $10 for a pizza that is loaded up with a bunch of stuff is a good deal (unless you simply won't buy food at a place like Pizza Hut because it will also be loaded up with high fructose corn syrup and other "food products"). That's not what bothers me.
What really bothers me is the blatant manipulation and assumption that we are all rather dumb. They come up with a marketing line that is very powerful - but a lie. It's not "any pizza, any crust".
Why oh why can't everyone be as logical and straightforward and honest as a software programmer?

One of the new features of Early Adopter 2 are Cascading LOVs/Select Lists. I’m pretty sure that almost every APEX developer had already the requirement to refresh a child select list when a parent select list was changed. For example you pick a department in the first select list and the second should just [...]

A few hours after my previous post, APEX 4.0 EA2 hit the air.

The url to this new version of APEX 4.0 is still the same: http://tryapexnow.com/apex/


I'll start some proper testing next week, but if you already want to read what is changed, you find more information on Joel Kallman's post.

Here are links to some new JDeveloper and ADF Bloggers that I came across since my last bloggers list update: Christian Straub Geoff Gabrielle Crawford Pino Jang Vijay Singh Neeraj Sehgal Vijay Mohan Gert Leenders Bioinformatics Consulting Maiko Rocha Rodrigo... shay.shmeltzer http://blogs.oracle.com/shay

Tryapexnow.com has just been updated to our new Oracle APEX 4.0 Early Adopter Release 2. Have a look, there are many new features, like WebSheets for example. A detailed list will be made available on Monday. I have also updated my plug-in example application with new plug-ins. Tutorials will follow soon. You can check it [...]

The APEX Development likes to hint and build up our expectations ;-)


It looks like APEX 4.0 Early Adopter 2 is almost there... a couple of hours? a couple of days?
Fact is that Joel is doing some tests on the EA2 build and reading Patrick's tweets says enough!


I really look forward to that release as it should have Websheets (and the DataGrids?) in and a ton of other new features! Keep watching the blogs...

I just released ruby-plsql version 0.4.2 which mainly adds support for more PL/SQL procedure parameter types. See change history file for more detailed list of changes.

Object types and object methods

Now you can use ruby-plsql to construct PL/SQL objects and call methods on these object. For example, if you have the following type defined:

CREATE OR REPLACE TYPE t_address AS OBJECT (
  street    VARCHAR2(50),
  city      VARCHAR2(50),
  country   VARCHAR2(50),
  CONSTRUCTOR FUNCTION t_address(p_full_address VARCHAR2)
    RETURN SELF AS RESULT,
  MEMBER FUNCTION display_address(p_separator VARCHAR2 DEFAULT ',') RETURN VARCHAR2,
  MEMBER PROCEDURE set_country(p_country VARCHAR2),
  STATIC FUNCTION create_address(p_full_address VARCHAR2) RETURN t_address
);

Then you can construct PL/SQL objects and call methods on them:

# call default constructor with named parameters
address = plsql.t_address(:street => 'Street', :city => 'City', :country => 'Country')
# call default constructor with sequential parameters
address = plsql.t_address('Street', 'City', 'Country')
# call custom constructor
address = plsql.t_address('Street, City, Country')
address = plsql.t_address(:p_full_address => 'Street, City, Country')

# returned PL/SQL object is Hash object in Ruby
address == {:street => 'Street', :city => 'City', :country => 'Country'}

# but in addition you can call PL/SQL methods on it
address.display_address == 'Street, City, Country'
address.set_country('Other') == {:street => 'Street', :city => 'City', :country => 'Other'}

# or you can call object member methods also with explicit self parameter
plsql.t_address.display_address(:self => {:street => 'Street', :city => 'City', :country => 'Other'}, :p_separator => ',') == 'Street, City, Country'

# or you can call static methods of type
plsql.t_address.create_address('Street, City, Country') == {:street => 'Street', :city => 'City', :country => 'Country'}

Record types and table of record types inside packages

Now you can call Pl/SQL procedures with parameters which have record or table of record type that is defined inside PL/SQL package. For example if you have the following package:

CREATE OR REPLACE PACKAGE test_records IS
  TYPE t_employee IS RECORD(
    employee_id   NUMBER(15),
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    hire_date     DATE
  );
  TYPE t_employees IS TABLE OF t_employee;
  TYPE t_employees2 IS TABLE OF t_employee
    INDEX BY BINARY_INTEGER;
  FUNCTION test_employee (p_employee IN t_employee)
    RETURN t_employee;
  FUNCTION test_employees (p_employees IN t_employees)
    RETURN t_employees;
  FUNCTION test_employees2 (p_employees IN t_employees2)
    RETURN t_employees2;
END;

Then you can call these package functions from Ruby:

employee = {
  :employee_id => 1,
  :first_name => 'First',
  :last_name => 'Last',
  :hire_date => Time.local(2010,2,26)
}
# PL/SQL record corresponds to Ruby Hash
plsql.test_records.test_employee(employee) == employee
# PL/SQL table corresponds to Ruby Array
plsql.test_records.test_employees([employee, employee]) == [employee, employee]
# PL/SQL index-by table corresponds to Ruby Hash
plsql.test_records.test_employees({1 => employee, 2 => employee}) == {1 => employee, 2 => employee}

If you will use table types defined inside PL/SQL packages then ruby-plsql will dynamically create session specific temporary tables which will be used to pass and get table parameter values. To ensure that these session specific temporary tables will be dropped you need to explicitly call plsql.logoff to close connection. For example, if you use ruby-plsql-spec for PL/SQL unit testing then in spec_helper.rb include

at_exit do
  plsql.logoff
end

to ensure that connection will be closed with plsql.logoff before Ruby script will exit. But in case of some script failure if this was not executed and you notice that there are temporary tables with RUBY_ prefix in your schema then you can call plsql.connection.drop_all_ruby_temporary_tables to drop all temporary tables.

Establish new connection

Now there is simpler connect! method how to establish new ruby-plsql connection when you need a new connection just for ruby-plsql needs. You can do it in several ways:

plsql.connect! username, password, database_tns_alias
plsql.connect! username, password, :host => host, :port => port, :database => database
plsql.connect! :username => username, :password => password, :database => database_tns_alias
plsql.connect! :username => username, :password => password, :host => host, :port => port, :database => database

And the good thing is that this method will work both with MRI 1.8 or 1.9 or with JRuby – you do not need to change the way how you are establishing connection to database.

Savepoints

Now there is simpler way how to define savepoints and how to rollback to savepoint:

plsql.savepoint "before_something"
plsql.rollback_to "before_something"

Check validity of database objects

Now ruby-plsql will check if referenced database object is valid before trying to call it. And if it will not be valid then corresponding compilation error will be displayed. For example, if you have invalid database object:

CREATE OR REPLACE FUNCTION test_invalid_function(p_dummy VARCHAR2) RETURN VARCHAR2 IS
  l_dummy invalid_table.invalid_column%TYPE;
BEGIN
  RETURN p_dummy;
END;

then when trying to call it

plsql.test_invalid_function('dummy')

you will get the following error message:

ArgumentError: Database object 'HR.TEST_INVALID_FUNCTION' is not in valid status
Error on line    2:   l_dummy invalid_table.invalid_column%TYPE;
     position   11: PLS-00201: identifier 'INVALID_TABLE.INVALID_COLUMN' must be declared
     position   11: PL/SQL: Item ignored

Other improvements

See History.txt file for other new features and improvements and see RSpec tests in spec directory for more usage examples.

I get kind of sad sometimes, watching people disappear into their mobile distraction devices. iPhones and their like certainly offer new opportunities for community and entertainment, but mostly it seems to me that they lead to isolation in one's physical context (aka, the real world) and lack of introspection.

So here is my idea for a truly useful iPhone app: Basta! (which means "Enough!" in Spanish)

Basta would allow you to request disabling of specific features of your iPhone (or all of it, just turn it into a black slate) for a given period of time.

Basta makes it easy for you to answer questions like: "Can I go 10 seconds without reading/writing texts?"

But the really exciting, Web 2.0 feature of Basta is that your friends (via Facebook, etc.) can challenge you to specific Basta restrictions: "I bet Steven can't go more than an hour without sending a tweet." and so on. All dares - proposed, accepted, denied and the results - are posted to your Facebook page and can be seen by others.

So you can build reputation and status around your ability, willingness, discipline to step away from devices and instead interact more with real live, breathing humans.

Or just watch more TV.

Anyway, that's my idea of a really great app!

Setting up Webutil for Oracle Forms

What is Webutil

While migrating Oracle Forms applications from 6i to 10g, it's possible that you'll face problems on parts of the form that won't work anymore. Oracle Forms 6i are Client based, this means that every form has to be on each client computer in order to work. The functionality that was written in the forms will be executed on the client machine. Oracle Forms 10g is Client - Server, this means that there are no forms needed on the client computer in order to work. This means that when you don't change your code, the code will be ran on the server instead of the client computer. Webutil is a utility which provides much of the client side functionality taken for granted with a client/server application by providing a set of pre-written Java Beans and an API to perform client side functions such as Host, OLE integration and Text_IO running on the client browser machine. With Webutil you'll be able to change all this and use the application as it was designed for.

 

Downloads

You can download the Webutil Software files (Version 1.0.6) from the oracle server: Click here

You also need the JACOB files (jar and dll) you can download them from Local or from their site

If you want to read the entire documentation file about Webutil: Click here

 

Features Text_IO File transfer Read and write text files on the client machine. Move from between the client, application server and database. Tool_Env File Manipulation Read client side variables Manipulate client side files. C API on the client Client machine information Interface with client side C. Read information from the client machine Host READ/WRITE_IMAGE_FILE Run Host commands on the client machine Read and write client side images OLE2 Get_File_Name Integrate with client side OLE (e.g. Word and Excel) Use a file selection dialog on the client machine Enhanced Host commands D2KWUtil? features Host command can now call back into Forms! Client side interface into the D2KWUtil? package. Browser functions Integrate with the browser.

 

Configuration WebUtil

This installation can only be used when you have allready a working Forms 10g environment up and running!

1) Make sure that the JDK executables are in the windows path so the can be called from everywhere:
Go to control panel -> system-> environmentvariables->add c:\devsuitehome\jdk\bin at the beginning of PATH variable

2) Copy Jacob.jar and dll from the links above to C:\DevSuiteHome\forms\java and C:\DevSuiteHome\forms\webutil

3) frmwebutil.jar en Jacob.jar have to be signed:
Open cmd prompt
Go to C:\DevSuiteHome\forms\webutil
Execute the following commands: sign_webutil C:\DevSuiteHome\forms\java\frmwebutil.jar
and sign_webutil C:\DevSuiteHome\forms\java\jacob.jar

4) Add the following path C:\DevSuiteHome\forms\java\jacob.jar to this register key [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_DevSuiteHome] “FORMS_BUILDER_CLASSPATH” using regedit

5) Change the configuration file formsweb.cfg so webutiljpi.htm will be used as htm template for the forms:
baseHTML=webutiljpi.htm
baseHTMLjpi=webutiljpi.htm

6) Also add the following part in the formsweb.cfg file after allownewconnections
#################################################
WebUtilArchive=frmwebutil.jar,jacob.jar
WebUtilLogging=off
WebUtilLoggingDetail=normal
WebUtilErrorMode=Alert
WebUtilDispatchMonitorInterval=5
WebUtilTrustInternal=true
WebUtilMaxTransferSize=16384
archive=frmall.jar, CaffoJavaClient.jar, frmwebutil.jar, jacob.jar
#################################################

7) Add the following to the CLASSPATH in the default.env file: C:\DevSuiteHome\forms\java\frmall.jar and C:\DevSuiteHome\forms\java\jacob.jar

 

 

Setting up Webutil for Oracle Forms

What is Webutil

While migrating Oracle Forms applications from 6i to 10g, it's possible that you'll face problems on parts of the form that won't work anymore. Oracle Forms 6i are Client based, this means that every form has to be on each client computer in order to work. The functionality that was written in the forms will be executed on the client machine. Oracle Forms 10g is Client - Server, this means that there are no forms needed on the client computer in order to work. This means that when you don't change your code, the code will be ran on the server instead of the client computer. Webutil is a utility which provides much of the client side functionality taken for granted with a client/server application by providing a set of pre-written Java Beans and an API to perform client side functions such as Host, OLE integration and Text_IO running on the client browser machine. With Webutil you'll be able to change all this and use the application as it was designed for.

 

Downloads

You can download the Webutil Software files (Version 1.0.6) from the oracle server: Click here

You also need the JACOB files (jar and dll) you can download them from Local or from their site

If you want to read the entire documentation file about Webutil: Click here

 

Features Text_IO File transfer Read and write text files on the client machine. Move from between the client, application server and database. Tool_Env File Manipulation Read client side variables Manipulate client side files. C API on the client Client machine information Interface with client side C. Read information from the client machine Host READ/WRITE_IMAGE_FILE Run Host commands on the client machine Read and write client side images OLE2 Get_File_Name Integrate with client side OLE (e.g. Word and Excel) Use a file selection dialog on the client machine Enhanced Host commands D2KWUtil? features Host command can now call back into Forms! Client side interface into the D2KWUtil? package. Browser functions Integrate with the browser.

 

Configuration WebUtil

This installation can only be used when you have allready a working Forms 10g environment up and running!

1) Make sure that the JDK executables are in the windows path so the can be called from everywhere:
Go to control panel -> system-> environmentvariables->add c:\devsuitehome\jdk\bin at the beginning of PATH variable

2) Copy Jacob.jar and dll from the links above to C:\DevSuiteHome\forms\java and C:\DevSuiteHome\forms\webutil

3) frmwebutil.jar en Jacob.jar have to be signed:
Open cmd prompt
Go to C:\DevSuiteHome\forms\webutil
Execute the following commands: sign_webutil C:\DevSuiteHome\forms\java\frmwebutil.jar
and sign_webutil C:\DevSuiteHome\forms\java\jacob.jar

4) Add the following path C:\DevSuiteHome\forms\java\jacob.jar to this register key [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_DevSuiteHome] “FORMS_BUILDER_CLASSPATH” using regedit

5) Change the configuration file formsweb.cfg so webutiljpi.htm will be used as htm template for the forms:
baseHTML=webutiljpi.htm
baseHTMLjpi=webutiljpi.htm

6) Also add the following part in the formsweb.cfg file after allownewconnections
#################################################
WebUtilArchive=frmwebutil.jar,jacob.jar
WebUtilLogging=off
WebUtilLoggingDetail=normal
WebUtilErrorMode=Alert
WebUtilDispatchMonitorInterval=5
WebUtilTrustInternal=true
WebUtilMaxTransferSize=16384
archive=frmall.jar, CaffoJavaClient.jar, frmwebutil.jar, jacob.jar
#################################################

7) Add the following to the CLASSPATH in the default.env file: C:\DevSuiteHome\forms\java\frmall.jar and C:\DevSuiteHome\forms\java\jacob.jar

 

 

Kris Rice blogged about the new version of the APEX Listener here and here. The APEX Listener will become the preferred way of connecting to APEX (before Apache and the Embedded PL/SQL Gateway). If you are not familiar with the APEX Listener, you find more information here.

To install the APEX Listener EA2 (0.10.56.22.08) on my Oracle Linux VM I followed these steps:

• Download Java SE JDK (before I used the JRE, but Kris told me the debugger doesn't work with that as you need the JDK for that part of the code)
  As a side note; now that Oracle and Sun are together, that website already has the "Oracle look". I wasn't expecting that so fast, but it's nice. The theme looks very similar to the APEX 4.0 Theme 1.
  

• Become root: su
• Go to the path you want java to install: cd /usr/java
• Copy the file you downloaded in that directory
• Change the permission of the file you downloaded to be executable: chmod a+x jdk-6u18-linux-i586-rpm.bin
• Start the installation process: ./jdk-6u18-linux-i586-rpm.bin
  If that is done you should see a screen like this and a directory jdk1.6.0_18 should exist:
  
  
• You find a complete guide of installing Java here.
• Next you need to download and install the APEX Listener
• The APEX Listener needs a webserver, depending the webserver you need to follow different steps. You find a complete guide how to install the webserver and the APEX Listener here.
• I already had Apache Tomcat installed on my VM, so I went with that and just followed the installation notes.
• Basically copy a war file to the webserver and change the config file
• I was interested to test the new Excel upload feature, to get to work I added following line to the config file (apache-tomcat/temp/apex/apex-config.xml)
  true
• Finally run APEX via the port of the Listener and create a new page with a File Browse item, a button and a report that queries the collection.
• That's it!
  
  

With the latest release of the APEX Listener you query the collection like this:
select *
from apex_collections
where collection_name = 'P1_EXCEL_FILE'P1_EXCEL_FILE is the item name of the File Browse Item. The other thing to remember is to call your button XLS2COLLECTION or have that as the request value.

Calling a Java class in PL/SQL

 

• Create your java class. Make sure the method you want to call from PL/SQL is declared static.
• From Oracle 8 and higher, you can use the commandline utility loadjava to load your Java class

loadjava -user username/password@SID -force -resolve MyPath\MyFile.java

• Login as SYS to grant privileges to the user

Call dbms_java.grant_permission('<FUNCTION_OWNER>','java.io.FilePermission','writeFileDescriptor',null);
Call dbms_java.grant_permission('<FUNCTION_OWNER>',',java.io.FilePermission','readFileDescriptor',null);

• Still logged in as SYS, give the user all roles starting with JAVA
• Log in as the user, and create a function that will call the Java class

create or replace FUNCTION MyFunction RETURN VARCHAR2 as language java name 'myPackage.MyClass.myMethod() return String';

• Execute the PL/SQL Function

 

links

• Oracle® Database Java Developer's Guide, 11g Release 1 (11.1), Part Number B31225-04 : The loadjava Tool

Calling a Java class in PL/SQL

 

• Create your java class. Make sure the method you want to call from PL/SQL is declared static.
• From Oracle 8 and higher, you can use the commandline utility loadjava to load your Java class

loadjava -user username/password@SID -force -resolve MyPath\MyFile.java

• Login as SYS to grant privileges to the user

Call dbms_java.grant_permission('<FUNCTION_OWNER>','java.io.FilePermission','writeFileDescriptor',null);
Call dbms_java.grant_permission('<FUNCTION_OWNER>',',java.io.FilePermission','readFileDescriptor',null);

• Still logged in as SYS, give the user all roles starting with JAVA
• Log in as the user, and create a function that will call the Java class

create or replace FUNCTION MyFunction RETURN VARCHAR2 as language java name 'myPackage.MyClass.myMethod() return String';

• Execute the PL/SQL Function

 

links

• Oracle® Database Java Developer's Guide, 11g Release 1 (11.1), Part Number B31225-04 : The loadjava Tool