Need help to create a storedprocedure

what was wrong with using the function...? it does the same thing.

proceduren
--------------------------------------------------------------

CREATE OR REPLACE PROCEDURE (p_cursor OUT SYS_REFCURSOR)
AS
BEGIN
    OPEN p_cursor FOR SELECT ID, FILNAMN FROM bild;
END;
----------------------------------------------------------


java koden
---------------------------------------------------------------------        CallableStatement stmt = forb.prepareCall("{ call testing(?) }");
stmt.registerOutParameter(1, OracleTypes.CURSOR);
stmt.execute();
       
ResultSet rset = (ResultSet) stmt.getObject(1);
    while(rset.next())
        {
  System.out.println(rset.getString("Navn"));
        }
--------------------------------------------------


if your not using oracle 9i you have to create a cursor yourself
just tell me and i will come up with the code.

sorry forgot the name ;)

CREATE OR REPLACE PROCEDURE testing (p_cursor OUT SYS_REFCURSOR)

Hello Loadet!

Thanks alot mate! This worked perfect!

But please tell me if I am wrong, if I do the same in SQL Server från Microsoft I guess that I can write the storedprocedure like below and it will return all posts without any use of cursers?

CREATE OR REPLACE PROCEDURE getImages ()
AS
BEGIN
    SELECT ID, FILNAMN FROM bild;
END;

Or doesn't this work in that database either?

Best regards
Fredrik

im sorry, i don't have any experience at all with mssql ;)

Thanks anyway mate!

Best regards
Fredrik

fredand> Problemet i Oracle er, at en procedure ikke kan returnere et resultset direkte, man må lave diverse underligheder (men det er nu ikke nødvendigt at lave det i java - man kan sagtens lave det i PL/SQL uden direkte at definere en cursor).

I SQL Server er der intet problem med at lade en procedure returnere et eller flere recordset direkte. Du kan fx lave en

CREATE PROCEDURE getData
AS
BEGIN
    SELECT ID, FILNAMN FROM bild
    SELECT forfatter, titel FROM books
    SELECT fornavn, efternavn, telefon FROM personer
END
GO

Og din klient vil med et kald modtage 3 forskellige recordset i stedet for blot et enkelt.

Desværre kan SQL Servers syntaks ikke håndtere CREATE OR REPLACE - det er enten CREATE PROCEDURE eller UPDATE PROCEDURE.

trer du siger man kan lave det i PL/SQL uden at definere en curser, kan du forklare mig hvordan?

eller evt et link til noget læsning.

Det var et eksempel jeg fandt på nettet - mener via metalinks. Har lige forsøgt at finde det, men uden held :-)

ok :/

Jeg mener at jeg sendte en mail til et par kollegaer med den - forsøger nu at finde ud af om nogen af dem har gemt den.

loadet> er nu tilbage på arbejde - og har fundet den ref. jeg havde.  Håber du kan have glæde af den:

SNIP:
Getting a Result Set Without Using OracleTypes.CURSOR
I tried to retrieve a refcursor from a stored procedure, using OracleTypes. CURSOR, and it works. But to do this I have to load the oracle.jdbc.driver, which is not agreeable. I need to use java.sql and retrieve multiple rows, using a stored procedure. Is there a way to retrieve multiple records by calling a stored procedure from a Java program without using the OracleTypes. CURSOR and not loading the oracle.jdbc.driver?
Basically you need a method to get a result set in JDBC without using any Oracle extensions. Instead of calling a stored procedure to get a query result, use a query to call a stored procedure, and that procedure then becomes the result set.
Here is a small example. You must create an object type in SQL to represent your result set type. For example, if I wanted to be able to return a result set with X and Y of type number, I would do the following:
ops$tkyte@DEV8I.WORLD> create or replace
type myScalarType
2 as object ( x int, y int )
3 /
Type created.
ops$tkyte@DEV8I.WORLD> create or replace
type myTableType
2 as table of myScalarType;
3 /
Type created.
Now I can create a function that returns that type.
ops$tkyte@DEV8I.WORLD> create or replace
2 function demo_proc2( p_rows_to_make_up in number )
3 return myTableType
4 as
5 l_data myTableType := myTableType();
6 begin
7 for i in 1 .. p_rows_to_make_up
8 loop
9 l_data.extend;
10 l_data(i) := myScalarType( i, i );
11 end loop;
12 return l_data;
13 end;
14 /
Function created.
Next, I select * from the stored procedure:
ops$tkyte@DEV8I.WORLD> select *
2 from the ( select cast( demo_proc2(5)
as mytableType )
3 from dual );
X Y
------ ------
1 1
2 2
3 3
4 4
5 5
ops$tkyte@DEV8I.WORLD>
In this fashion, Java never calls a stored procedure but instead invokes the above Select statement, which calls the stored procedure and gives you a result set. This "trick" of selecting from a stored procedure has many other useful applications as well. For another use of this feature, see "In List Question with Bind Variables" (http://osi.oracle.com/wa /ask/owa/ask_tom_pkg.display?p_dispid=139812348065).

takker :)