Avatar billede fredand Forsker
01. marts 2004 - 13:58 Der er 14 kommentarer og
4 løsninger

Can't create a storedprocedure in Oracle

Hello!

I try to create a simple storedprocedure in Oracle, but I guess that my syntax is wrong cause I get some errors all the time.

My code for the procedure, run from SQL+ is:
CREATE OR REPLACE PROCEDURE getImages()
    AS
BEGIN
    SELECT * FROM bild
END;

My Errors is:

Errors for PROCEDURE GETIMAGES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/21    PLS-00103: Encountered the symbol ")" when expecting one of the
        following:
        <an identifier> <a double-quoted delimited-identifier>
        current

5/1      PLS-00103: Encountered the symbol "END" when expecting one of the
        following:
        . , @ ; for <an identifier>
        <a double-quoted delimited-identifier> group having intersect
        minus order partition start subpartition union where connect
        SAMPLE_
        The symbol ";" was substituted for "END" to continue.

So if any one could help me create this simple storedprocedure it would be great!

Please answer in english since my danish is poor.

BTW I need to do it like this cause a Java-app will create the storedprocedure it later, and an other Java-app will launch it after that. So I do not whant to create it from the Enterprise Manager.

Best regards
Fredrik
Avatar billede umfugo Nybegynder
01. marts 2004 - 14:05 #1
Fjern () efter procedurenavnet samt sæt et ; bag din sql-statement.
Avatar billede umfugo Nybegynder
01. marts 2004 - 14:06 #2
Remove () from procedurename and add ";" behind "select * from bild"
Avatar billede fredand Forsker
01. marts 2004 - 14:28 #3
Hello Umfugo!

I wrote it like this:
CREATE OR REPLACE PROCEDURE getImages
    AS
BEGIN
    SELECT * FROM bild;
END;


But still I get:
Warning: Procedure created with compilation errors.

And the errors is:

Errors for PROCEDURE GETIMAGES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/5      PLS-00428: an INTO clause is expected in this SELECT statement


Perhaps there is something else, or what do you think?

Best regards
Fredrik
Avatar billede arne_v Ekspert
01. marts 2004 - 14:33 #4
Læs det link som loadet selv angiver her:
  http://www.eksperten.dk/spm/463097
Avatar billede fredand Forsker
01. marts 2004 - 14:48 #5
Hello Mates!

I looked At loadets link and the result now is:
CREATE OR REPLACE PROCEDURE getImages AS
  sp_id number;
  sp_filnamn varchar2(50);
BEGIN
  SELECT id, filnamn INTO sp_id, sp_filnamn FROM bild;
END;

And I get no errors, but how do I call it ?

I tried:
call getImages;
But got:

Procedure created.

call getImages
    *
ERROR at line 1:
ORA-06576: not a valid function or procedure name

And by the way please give "svar" not "Komentar" so I could give you your points :-)

Best regards
Frerik
Avatar billede fredand Forsker
01. marts 2004 - 14:52 #6
I alos tried this:
call getImages()

But get got this:
call getImages()
    *
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "PHOTO.GETIMAGES", line 5

????
Avatar billede arne_v Ekspert
01. marts 2004 - 15:00 #7
You select into simple variables and your query returns more than one row.
Avatar billede arne_v Ekspert
01. marts 2004 - 15:00 #8
answer
Avatar billede umfugo Nybegynder
01. marts 2004 - 15:02 #9
You have to declare a varibel where your recordset/column should put into.

CREATE OR REPLACE PROCEDURE getImages
AS
BEGIN
Declare
  sname Varchar2(10);
BEGIN
  select <column>
  into sname
  from <table>;
END;
END;
Avatar billede fredand Forsker
01. marts 2004 - 15:13 #10
Hello Amigos!

I guess that you mean like this:
CREATE OR REPLACE PROCEDURE getImages AS
BEGIN
DECLARE
  sp_id number;
  sp_filnamn varchar2(50);
  BEGIN
    SELECT id, filnamn INTO sp_id, sp_filnamn FROM bild;
  END;
END;

But after the call I still get:

call getImages()
    *
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "PHOTO.GETIMAGES", line 7

Thanks for taking the time

Fredrik
Avatar billede arne_v Ekspert
01. marts 2004 - 15:17 #11
Yes - see my 15:00:13 comment
Avatar billede fredand Forsker
01. marts 2004 - 15:22 #12
Hello Arne!

Perhaps you could give me some code for that, I must admit that I do not understand. You talk about simple variables but what kind of viarables should I use then?

Best Regrads
Fredrik
Avatar billede arne_v Ekspert
01. marts 2004 - 15:29 #13
Where do you want the data to go ?

Back to the caller or to another table ?
Avatar billede arne_v Ekspert
01. marts 2004 - 15:33 #14
Again - read that EE link - it also mentions your error and describes
how to use a cursor to read all records.
Avatar billede fredand Forsker
01. marts 2004 - 15:39 #15
Hello!

The storedprocedure should be in a database used by a webbapp written in Java. So for eg I would like to be able to call the storedprocedure like this:

CallableStatement callableStatement = connection.prepareCall("{call getImages()}");
ResultSet resultSet = callableStatement.executeQuery();

And from the ResultSet I need to get the id and the file name.

Hope that was what you needed!

So if you could give me som code for the storedprocedure it would be great, cause I can't get it right!

/Fredrik
Avatar billede simonvalter Praktikant
01. marts 2004 - 15:39 #16
perhaps you can use this example. use a function...

CREATE OR REPLACE FUNCTION TESTEN (p_user_like_in IN VARCHAR2) RETURN SYS_REFCURSOR
AS
    l_return_cursor SYS_REFCURSOR;
BEGIN
    OPEN l_return_cursor FOR SELECT Navn from KUNDER WHERE Navn = p_user_like_in;
    RETURN l_return_cursor;
END;



        CallableStatement stmt = forb.prepareCall("{ ? = call HELLO(?) }");
            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.setString(2, "Simon");
            stmt.execute();

            ResultSet rset = (ResultSet) stmt.getObject(1);
            while(rset.next()){
                System.out.println(rset.getString("Navn"));
            }
Avatar billede simonvalter Praktikant
01. marts 2004 - 16:01 #17
if you dont want to send any parameters with the function you just do like this

CREATE OR REPLACE FUNCTION TESTEN RETURN SYS_REFCURSOR
...
and

CallableStatement stmt = forb.prepareCall("{ ? = call TESTEN }");
...

im sorry to say i never got it to work with the procedure and more than 1 row in the resultset... but a function is working just fine.

a stored java procedure is much easier but i dont know if its possible to create that from outside the enterprise manager.
Avatar billede fredand Forsker
01. marts 2004 - 16:22 #18
Hello Amigos!

I'm going crazy over this so I end this thread and opens a new, of course you get your points.

Best regards
Fredrik
Avatar billede Ny bruger Nybegynder

Din løsning...

Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.

Loading billede Opret Preview
Kategori
Computerworld tilbyder specialiserede kurser i database-management

Log ind eller opret profil

Hov!

For at kunne deltage på Computerworld Eksperten skal du være logget ind.

Det er heldigvis nemt at oprette en bruger: Det tager to minutter og du kan vælge at bruge enten e-mail, Facebook eller Google som login.

Du kan også logge ind via nedenstående tjenester