Calling Java Application from PL/SQL code

Colleagues,

I am not an expert in PL/SQL neither Java, so I require a help here:

What I am planning to do is to use PL/SQL to call a Java application (pass 3 arguments).

The Java application is used to make call to an API that resides on a Bea Weblogic application/web server (its not used to access database).

I got already the PL/SQL Trigger, but I need to add to it the call to the Java application (the Java application already exists)

Simply, would any one provide an example of how to make that call.

Do I need to load Java classes into the Oracle database or not?

I am using Oracle 9i database, and the Java application is compiled using Java SDK 1.4.2

Many thanksHi,

To call java class from a pl/sql, you need to follow the steps.

1. First create a java file and compile it.

2. Load the java class file to the database using 'loadjava' utility.

Ex: loadjava -u scott/tiger abc.class

3. Create a pl/sql wrapper aroung the java class like

CREATE OR REPLACE PROCEDURE test (a1 VARCHAR2) AS LANGUAGE JAVA name 'abc.main(java.lang.String[])';

4. Then execute the stored procedure

exec test(<input>);|||HI,

Invoking the java stored procedures from any PL/SQL block or stored module works the same way it would for any other PL/SQL stored function.

For example:

SQLPLUS> set serveroutput on size 5000;

SQLPLUS> declare
2> temp varchar2(100);
3> begin
4> temp := ConcatTwo('Hello','World');
5> DBMS_OUTPUT.PUT_LINE(temp);
6> end;
7> /|||Thanks... suneel.kumar and satish_ct

you realy give me some ideas...|||These examples provide the capability to load specific "classes" to the
database and most will work however Oracle will have problems with
classes that import from GUI packages such as AWT or Swing.|||HI,

Did U come across any such error?|||My class is only initiate a request or call to another system API (my class doesn't import from any GUI Packages).

Many thanks to you all|||when I've tried to load the java class, but I got the following error:

ORA-04031: unable to allocate 4032 bytes of shared memory ("shared pool","unknown object","joxs heap init","ioc_allocate_pal")

would some one told me what I should do.

I think that the shared pool is out of memory so should I consult the DBA, system Admin or is it from my PC, where I am making a conection to the server/Database.

calculating sql server 2005 statistics...

Hi every one and greg,

We planning and estimating the sql server expiration or need to upgrade is there any thing in sql server so that i can calculate following things.

Growth of databases hosted on server

Average number of connection that being process in working times/hours

Average hardware consumption (like cups ram hard disk)

We need theses stats for projections.

Is there any thing in sql server 2005?

thanks in advance

I'm not aware of any inbuilt functions that can be used to return historic database growth. This is something that you will have to implement manually.

You could create a SQL Agent job to run every day to execute 'DBCC showfilestats' against each of your databases and log the results into a table. This will allow you to get an idea of your database's growth pattern, but it will take time to build a set of data that can be reliably used to predict database growth.

The number of connections, CPU usage, RAM and disk usage (and a whole lot of other useful counters) can be monitored using 'Perfmon' and the results can be logged into a SQL Server database for future analysis. Bear in mind that for the counter objects that you have specified, Perfmon will monitor all activity on the server rather than just specifically activity relating to SQL Server.

Here are a couple of links to help get you started with Perfmon:

http://www.computerperformance.co.uk/HealthCheck/index.htm

http://support.microsoft.com/kb/296222

Chris

|||

thank you Chris, I have written some procedures that will collects other stats like CPU usage, RAM and disk usage and thinking that execute this procedure after a specified interval and store result in a historical table. What you thinks it will better and if yes then how much time interval should?

Calculating Row Size including blobs

I've seen plenty of posts regarding the estimation of table size,
usually in the processing of planning for server storage needs.

Well, I've got a different problem. I need to know how much data each
of our Customers are using in a Database. (1 SQL DB stores multiple
customers).

Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:
655 MB.

So, how can I ask SQL Server how much data each Row in each table is
taking up? I want to be able to calculate nightly the total size, so I
would take each row in each table that belong to the customer, and add
all the sizes together. I want to take into account blobs that are
storing images and PDF files also.

Thanks in advance,

Jesse Wolgamott[posted and mailed, please reply in news]

Jesse Wolgamott (jesse.wolgamott@.gmail.com) writes:
> I've seen plenty of posts regarding the estimation of table size,
> usually in the processing of planning for server storage needs.
> Well, I've got a different problem. I need to know how much data each
> of our Customers are using in a Database. (1 SQL DB stores multiple
> customers).
> Basically, I want to be able to say: Customer A: 45.5 MB, Customer B:
> 655 MB.
> So, how can I ask SQL Server how much data each Row in each table is
> taking up? I want to be able to calculate nightly the total size, so I
> would take each row in each table that belong to the customer, and add
> all the sizes together. I want to take into account blobs that are
> storing images and PDF files also.

This is not a problem with a clearcut answer, since if a table gets
fragmented, that extra space is not really any particular customer,
but presumably someone should be accounted for it.

Therefore I would for the non-blob data take the number of rows for
each customer and scale that with the number of rows for each customer.
For the blob data I would simply sum the sizes of each field:

SELECT t.CustomerID,
MB = ((1E0 * COUNT(*) * i.reserved / i.rows) * 8192 +
SUM(coalesce(datalength(t.blobcol), 0)) / 1E6
FROM tbl t
CROSS JOIN sysindexes i
WHERE i.id = object_id('tbl')
AND i.indid IN (0, 1)
GROUP BY t.CustomerID

You should run DBCC UPDATEUSAGE on the database prior to running this-

The result will be skewed if you have tables with long non-blob
character/binary columns where different customer have very differing
average length on these columns. Also, if you are using the "text in row",
customers with small blobs may be accounted twice for the same thing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp