CODEWITHSUNDEEP
sqloracle-databaseplsql
user6332149
user6332149

Reputation:

error when execute function pl/sql

I wrote a function where I delete a line number in a table. I did not quite understand the error

DECLARE
a integer ;

CREATE OR REPLACE function f (j sejour.jour%type) return integer is 

n integer 

begin 

select count(*) into n from sejour where jour < j ;

Delete Sejour where jour < j ; 

RETURN n ;

end;
BEGIN
  a:= 5;

  c := f(a);
 dbms_output.put_line(' Nombre est : ' || c);
END;
/

i have error : 

ERROR at line 4:
ORA-06550: line 4, column 1:
PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:
begin function pragma procedure subtype type <an identifier>
<a double-quoted delimited-identifier> current cursor delete
exists prior

Upvotes: 0

Views: 1633

Answers (4)

Avrajit Roy
Avrajit Roy

Reputation: 3303

-- Doing it in PLSQL block i.e no creation of schema/SQL level object --Create or replace only comes for creation of SQL objects not for PLSQL obects.

DECLARE
  a INTEGER ;
  FUNCTION f(
      j sejour.jour%type)
    RETURN INTEGER
  IS
    n INTEGER;
  BEGIN
    SELECT COUNT(*) INTO n FROM sejour WHERE jour < j ;
    DELETE Sejour WHERE jour < j ;
    RETURN n ;
  END;
  BEGIN
    a := 5;
    c := f(a);
    dbms_output.put_line(' Nombre est : ' || c);
  END;
  /

--Second way of doing it by creating a SQL level object function and calling it in PLSQL block a shown below.

CREATE OR REPLACE
FUNCTION f(
    j sejour.jour%type)
  RETURN INTEGER
IS
  n INTEGER;
BEGIN
  SELECT COUNT(*) INTO n FROM sejour WHERE jour < j ;
  DELETE Sejour WHERE jour < j ;
  RETURN n ;
END;
/

--Once created call the function in PLSQL block

DECLARE
  a INTEGER ;
BEGIN
  a := 5;
  c := f(a);
  dbms_output.put_line(' Nombre est : ' || c);
END;
/

Upvotes: 0

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

Actually, there is another option, which leaves things closer to the original effort and does not lead to the creation of a schema-level function. Just remove the "create or replace".

DECLARE
   a   INTEGER;

   FUNCTION f (j sejour.jour%TYPE)
      RETURN INTEGER
   IS
      n   INTEGER;
   BEGIN
      SELECT COUNT (*)
        INTO n
        FROM sejour
       WHERE jour < j;

      DELETE sejour
       WHERE jour < j;

      RETURN n;
   END;
BEGIN
   a := 5;
   c := f (a);
   DBMS_OUTPUT.put_line (' Nombre est : ' || c);
END;

Remember: "create or replace" is not a pat of PL/SQL. That is DDL syntax from SQL itself.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270843

I think you are looking for something like this:

create or replace function f (in_j sejour.jour%type) 
return integer is 
    v_n integer; 
begin 
    select count(*) into v_n from sejour where jour < in_j ;
    delete Sejour where jour < in_j ; 
    return v_n ;
end;
/

declare
  v_a integer ;
  v_c integer;
begin
  v_a := 5;

  v_c := f(v_a);
  dbms_output.put_line('Nombre est : ' || v_c);
end;
/

Notes:

  • You need to declare all variables.
  • Use prefixes, so you can distinguish between variables and columns in tables.
  • If you are modifying the database, I would suggest using a stored procedure rather than a function.

Upvotes: 1

Mr. Bhosale
Mr. Bhosale

Reputation: 3106

DECLARE statements should be after AS keyword.

    CREATE OR REPLACE function f (j sejour.jour%type) return integer is 


        DECLARE
        a integer  ,n integer 

        begin 

        select count(*) into n from sejour where jour < j ;

        Delete Sejour where jour < j ; 

        RETURN n ;

        end;
        BEGIN
          a:= 5;

          c := f(a);
         dbms_output.put_line(' Nombre est : ' || c);
        END;

Upvotes: 0

Related Questions