01. marts 2004 - 13:58Der 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.
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?
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 :-)
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
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
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?
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:
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;
I'm going crazy over this so I end this thread and opens a new, of course you get your points.
Best regards Fredrik
Synes godt om
Ny brugerNybegynder
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.