Hi i have a very basic knowledge

[sucram]

Hi

i have a very basic knowledge of Oracle and Stored procedures. Would someone be able to write a small stored procedure as an example for me.

SELECT *
FROM report

I want to execute the above query and retrieve the result set.
I think it is done using CURSORS but I really have no clue.

Thanks,
Marcus

 

[Naith]

Marcus,

It isn't really clear what you want to do here. There is no real relation between cursors and your query, based on the information that you've given.

Your SELECT statement is fine as a simple select. If you were to integrate it into a stored procedure, it'd look like:

CREATE OR REPLACE PROCEDURE MyStoredProcedure

BEGIN

SELECT *
FROM report

End MyStoredProcedure;

All this will do is return all the records held in the report object. When you mention cursors, I imagine that you want to apply conditions to the recordset that you bring back. If so, explain how what you want, and how you want it differs from what and how the data is stored in the report object.

Naith

 

[sucram]

Thanks Naith,

I want to be able to call the stored procedure from Java and caputure the return values in a ResultSet object.

Is it as simple as you have shown or does there have to be some returning mechanism.

I heard that you Oracle only allows the return of multiple rows when you use a cursor.

Exactly what i want to do is, execute an SQL statement and capture the results.

Thanks,
Marcus

 

[vrudenko]

Hi MARCUS,

I don't know how to catch cursor from Java but I can give you an example doing it in SQLPlus.

First the cursor variable should be declared in a package to be a global one.

CREATE OR REPLACE PACKAGE REF_CURSOR_TYPE
AS
TYPE RCT IS REF CURSOR;
END REF_CURSOR_TYPE;

CREATE OR REPLACE PROCEDURE MyStoredProcedure
(cv IN OUT REF_CURSOR_TYPE.RCT)
AS
BEGIN

OPEN cv FOR
SELECT * FROM report;

END MyStoredProcedure;

Then in SQLPlus:

SQL> var C REFCURSOR
SQL> EXEC MyStoredProcedureC)

Good luck!

 

[Naith]

I don't want you to make life harder for yourself than it needs to be. You don't necessarily need to employ a cursor just to enable you to capture a multi-row resultset.

If you look at the simple SQL in your first post, this would most likely return multiple rows, which could all be ported over to another environment after the complete recordset had been returned. However, if you're talking about representing and handling each row before taking it over to another environment one at a time, then you would do well to consider cursors.

Naith