CODEWITHSUNDEEP
oracle-databasestored-proceduresdbeaver
C.Dumange
C.Dumange

Reputation: 128

Oracle Stored Procedure posing a prob

[EDIT]Editing the code to reflect changes coming from comments

I have a problem with one of the stored procedures I'm trying to create in an Oracle database.

The goal is to update every table which has an indiv column.

CREATE OR REPLACE PROCEDURE sp_majUserOnAllK (lastU IN VARCHAR2, newU IN VARCHAR2)
AS 
BEGIN
  FOR item IN (
        select table_name , owner 
        from all_tab_columns 
        where column_name = 'INDIV' AND OWNER ='K'
    )
    LOOP                
        EXECUTE IMMEDIATE 'UPDATE K.' ||  item.table_name || ' SET indiv = :newValue WHERE indiv = :oldValue' USING newU, lastU;
    END LOOP;
END sp_majUserOnAllK;

exec sp_majUserOnAllK( 'hum','hum');

Problem is, when I try to execute the stored procedure, I got an error message with no detail at all ('non valid SQL').

I tried taking the code out of the stored procedure. And there, it works. Only the beginning is changing to :

DECLARE
    newU NVARCHAR2(50);
    lastU NVARCHAR2(50);
    req VARCHAR2(100);
    CURSOR ctable IS
        select table_name , owner from all_tab_columns where column_name = 'INDIV' AND OWNER ='KEXPLOIT';

    BEGIN

        newU := 'hum';
        lastU := 'hum';
        FOR item IN ctable
            ....

Like that, it works perfectly and does exactly what it is supposed to do.

As the only difference is the assignation of the variable, I think I may have a problem with my procedure declaration but I can't find a solution. The compilation is ok.

Any idea ?

Upvotes: 0

Views: 6445

Answers (2)

user5683823
user5683823

Reputation:

A five-second Google search on "dbeaver exec command" brought this up among the first few hits:

https://github.com/dbeaver/dbeaver/issues/749

In it, we learn that EXEC is not supported by dbeaver.

EXEC is an SQL*Plus command. It is not Oracle SQL, and it is not PL/SQL. SQL*Plus is a shell program of sorts for interacting with Oracle databases; it has its own language, distinct from SQL and PL/SQL.

SQL Developer and Toad (and perhaps other similar programs) support (most of) SQL*Plus, but apparently dbeaver (with which I am not familiar) does not.

The link I copied above suggests using the CALL command instead. See the link for examples.

As an aside, when we use EXEC in SQL*Plus and SQL Developer, there is no semicolon at the end of the procedure call. Adding an unnecessary semicolon, however, does not throw an error (SQL*Plus is, apparently, smart enough to simply ignore it).

Upvotes: 2

kfinity
kfinity

Reputation: 9091

Your procedure's syntax is not correct. Try this.

CREATE OR REPLACE PROCEDURE sp_majUserOnAllK (lastU IN VARCHAR2, newU IN VARCHAR2)
IS 
  req VARCHAR2(100);
BEGIN
  FOR item IN (select table_name , owner from all_tab_columns where column_name = 'INDIV' AND OWNER ='K')
    LOOP                
        req := 'UPDATE K.' ||  item.table_name || ' SET indiv = :newValue WHERE indiv = :oldValue';
        EXECUTE IMMEDIATE req USING newU, lastU;
    END LOOP;
  -- return 1; -- note: procedures do not return values
END;
/

Upvotes: 3

Related Questions