CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Adrián Jaramillo
Adrián Jaramillo

Reputation: 299

I cannot execute this procedure I'm creating in oracle with sql developer, maybe a syntax error? Or can updates be used inside procedures?

What I have to do is...
"Write a procedure to modify the location of a department. The procedure is going to get as IN params the

IMPORTANT NOTE (IM WORKING IN THE HR DEFAULT SCHEMA OF ORACLE)

I have this Pl/SQL code

SET SERVEROUTPUT ON
    BEGIN
        MOD_LOCALI(&n_dep, &nueva_local);
    END;

And I'm trying to create this stored procedure


    create or replace PROCEDURE MOD_LOCALI 
(
  N_DEP IN NUMBER 
, NUEVA_LOCAL IN VARCHAR2 
) AS 
BEGIN
    UPDATE departments d
    SET d.location_id = (SELECT d.location_id
                            FROM departments d
                            INNER JOIN locations l
                            ON d.location_id = l.location_id
                            WHERE l.city = NUEVA_LOCAL;
                        )
    WHERE d.department_id = N_DEP;

    DBMS_OUTPUT.PUT_LINE( 'El departmento con número ' || N_DEP || ', se ha mudado a ' || NUEVA_LOCAL);

END MOD_LOCALI;

But when I execute the procedure to save it (I press the compile button in oracle sql developer), I get these errors

enter image description here

That's all, thanks in advance

Upvotes: 0

Views: 680

Answers (3)

Adrián Jaramillo
Adrián Jaramillo

Reputation: 299

The final working pieces of code are:
CODE FOR CALLING THE PROCEDURE

SET SERVEROUTPUT ON
BEGIN
    MOD_LOCALI(&n_dep, '&nueva_local');
END;

CODE OF THE PROCEDURE

create or replace PROCEDURE MOD_LOCALI 
(
    N_DEP IN NUMBER, 
    NUEVA_LOCAL IN VARCHAR2
) AS 
BEGIN
    UPDATE departments d
    SET d.location_id = (SELECT l.location_id
                            FROM locations l
                            WHERE l.city = NUEVA_LOCAL
                        )
    WHERE d.department_id = N_DEP;

    DBMS_OUTPUT.PUT_LINE( 'El departmento con número ' || N_DEP || ', se ha mudado a ' || NUEVA_LOCAL);

END MOD_LOCALI;

The errors I was having were

  • Semicolon after NUEVA_LOCAL
  • When calling the procedure, I needed to add single quotes to '&nueva_local'
  • The subquery was wrong, I was doing needlessly a join where It had literally no sense at all

PEOPLE WHO HELPED ME REACH THE CORRECT SOLUTION

  • RobertPaulsen

  • Littlefoot

  • Anurag

Upvotes: 1

Anurag
Anurag

Reputation: 33

remove semicolon from sub query and add "/" after "end mod_locali;"

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=b6b8371c5ed33b733681c5a436f44982

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142788

That would be

SQL> create or replace procedure mod_locali
  2    (n_dep       in number ,
  3     nueva_local in varchar2
  4    )
  5  as
  6  begin
  7    update departments d
  8      set d.location_id = (select d.location_id
  9                             from departments d inner join locations l
 10                                  on d.location_id = l.location_id
 11                            where l.city = nueva_local
 12                          )
 13      where d.department_id = n_dep;
 14
 15      dbms_output.put_line( 'El departmento con número ' || n_dep || ', se ha mudado a ' || nueva_local);
 16  end mod_locali;
 17  /

Procedure created.

SQL> set serveroutput on
SQL> set ver off
SQL>
SQL> begin
  2    mod_locali(&n_dep, '&nueva_local');
  3  end;
  4  /
Enter value for n_dep: 10
Enter value for nueva_local: Croatia
El departmento con número 10, se ha mudado a Croatia

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions