Re: Stored Proc Fails

Looks like SQL server coding style, yes? Have to learn how to code properly for ORALCE PL/SQL. Does not look like you need a function here. Just use a procedure. There are quite a few requirements and restictions on calling PL/SQL functions from SQL. One of them is that you are not allowed to change the database (insert, update or delete on tables). Read the ORACLE DOC on these more advanced topics. Though for beginners, learn the basics first...

• Jusung Yang

dgnatek_at_flash.net (David) wrote in message news:<9b514443.0306040527.772304fc_at_posting.google.com>...
> Being new to Oracle stored procs I am having a little trouble here. I
> have the following stored proc which always fails:
>
> CREATE OR REPLACE PACKAGE my_package AS
> FUNCTION my_function RETURN NUMBER;
> END my_package;
> /
>
> CREATE OR REPLACE PACKAGE BODY my_package AS
> FUNCTION my_function RETURN NUMBER IS
> BEGIN
> DELETE FROM headersfile;
> RETURN -1;
>
> EXCEPTION
> WHEN OTHERS THEN
> RETURN 0;
> END my_function;
> END my_package;
>
> When I run the delete manually in SQLPlus it works. I then create a
> package (successfully) and run it my typing "SELECT
> my_package.my_function FROM dual;" However, it always returns 0. Is
> this a privelege thing? If so, how do I resolve it? BTW, I have
> noticed I can perform Selects from the table (in the stored proc) and
> all works well. Thks for any input.
>
> David.
Received on Wed Jun 04 2003 - 21:25:44 CEST