Problem with Execute Procedure in Pl/SQL Developer

Problem with execute procedure in PL/SQL Developer

Calling stored procedures using execute as above is specific to SQL*Plus. In fact, SQL*Plus converts execute some_proc() into BEGIN some_proc(); END;, You can see this for yourself by attempting to call a procedure that doesn't exist:

SQL> execute some_proc()
BEGIN some_proc(); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'SOME_PROC' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Problem with stored procedure in SQL Developer - no refreshing the changes made in the block

I finally could solve this problem.

The problem was data-related; the database itself has too many tables and it's complex, so, I had to generate a full test of the program - after changing the text sample in the stored procedure MY_STORED_PROC to:

'Testing in LBASPOC - DATE: ' || TO_CHAR(SYSDATE, 'dd/MM/yyyy HH:mi:ss')

The result was as follows:

Testing in LBASPOC - DATE: 09/11/2020 10:11:30

When I generated more testings, new records were added in T_DEBUG table - and the DATE value did change in each test results:

Samples:

Testing in LBASPOC - DATE: 09/11/2020 10:12:29
Testing in LBASPOC - DATE: 09/11/2020 10:21:55
Testing in LBASPOC - DATE: 09/11/2020 10:37:41

I then discovered that - in other parts of the program (i.e the database) - another records were added (in a table called "T_OBSERV") - those records were based on records from table "T_DEGUB".

The solution was: modify the SP "MY_STORED_PROC" for "before do any action" - delete the duplicated records in "T_OBSERV".

I was very sure the problem were in the SP itself, but, the problem was really in the duplicated data.

Executing procedure in plsql developer

In PL/SQL Developer, you execute PL/SQL blocks in the Test Window.

File > New > Test Window will provide a template block something like this:

declare 
begin

end;

You just need to add your procedure name (and remove the unneeded declare section as you have no variables), so it's:

begin
    employee;
end;

Alternatively, right-click on the procedure name and select 'Test' from the pop-up menu, and it will generate the above block for you.

If the expected dbms_output text is not displayed in the 'Output' tab, the first thing to check is that the 'Enabled' checkbox is checked.

To diagnose dbms_output, the simplest test case would be just:

begin
    dbms_output.put_line('Hello');
end;

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

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

Oracle PLSQL problems creating a procedure

Have a cursor as an OUT parameter and use DEFAULT in the signature of the procedure:

CREATE PROCEDURE LAST_EMPLOYEE_HISTORY( 
  i_employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE,
  i_rws         IN PLS_INTEGER DEFAULT 20,
  o_cursor      OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN o_cursor FOR
    SELECT e.employee_id, 
           e.first_name,
           e.last_name,
           e.card_num,
           l.location_id,
           l.location_name,
           a.access_date
    FROM   employees    e
           INNER JOIN access_history a
           ON a.employee_id = e.employee_id
           INNER JOIN locations l
           ON l.location_id = a.location_id
    WHERE  e.employee_id = i_employee_id
    ORDER BY access_date DESC
    FETCH FIRST i_rws ROWS ONLY;
END;
/

Then in SQL/Plus or SQL Developer:

VARIABLE cur REFCURSOR;
EXECUTE LAST_EMPLOYEE_HISTORY(1, 50, :cur);
PRINT cur;

db<>fiddle here

Note: From Oracle 12, you can the use FETCH FIRST n ROWS ONLY syntax.

SQLDeveloper execute procedure with parameters

As this is a procedure with an OUT parameter, you'll have to use another PL/SQL block which has DECLARE section (so that you'd have "something" to accept what your procedure returns).

declare
  l_rc sys_refcursor;
begin
  procedure_example(var1   => 1,
                    var2   => 2,
                    var3   => 3,
                    result => l_rc);
end;
/

(You'd pass meaningful values to IN parameters, of course.)

---

Another option is to declare a variable, use it while executing the procedure and print its contents. For example (based on Scott's sample schema):

---

Related Topics