Oracle SQL Developer – How to Update Procedure via .sql Script

oracleoracle-sql-developer

I need to update a Oracle procedure via a .sql script. I know the procedure works because it is currently running on our server, but I need to update the procedure on a client's box.

I need to be able to hand them a .sql script which they will open and execute with Oracle SQL Developer, which will update the procedure, but when I try to create the .sql file and run the .sql file it fails, because it's trying to run the procedure.

I don't want the procedure to be run, but rather be created or replaced with the following updated procedure:

    CREATE OR REPLACE PROCEDURE updateDBUSER(
         p_userid IN DBUSER.USER_ID%TYPE,
         p_username IN DBUSER.USERNAME%TYPE)
    IS
    BEGIN

      UPDATE DBUSER SET USERNAME = p_username where USER_ID = p_userid;

      COMMIT;

    END;

Best Answer

Just save the ddl for the procedure. This can be done using sql-developer 'save to file'. Tell the client to start sql-developer, connect to the schema where the code should be written and have them run the script using 'start' or the equivalent for start '@' script_name The sql script should be located in the directory defined in the preferences for Database>Worksheet>'select default path to look for scripts'

@your_script

start your_script

It works from the worksheet, very similar to sql*plus.

Related Solutions

SQL Developer: how to script output to query results grid

if you remove first two lines (var ... and exec), you can run this in sql_developer and get your results in a grid.

Now, if you must have variable declaration, you could replace the : to a & in "job.contract_year in (2013,:v_yr)" so it will prompt you to enter a value at run time.

If you must convert this to a procedure, then you need to go the ref_cursor route.

How to Run Procedure in Oracle SQL Developer

First, if you are creating a procedure in a package, the package name will need to be included when you call the procedure.

begin
  hotel.fill_city(10000);
end;
/

should correctly invoke your procedure.

Second, you have issues with the naming of your local variables. Normally, you would not create local variables like city and postal_number that are the same as the names of columns in tables in your database. That makes it far too easy to introduce errors in your code where you intend to refer to the local variable but scope resolution rules mean that you are really referring to the column name. For example, if you write the perfectly valid function

CREATE OR REPLACE FUNCTION get_dname( deptno IN NUMBER )
  RETURN VARCHAR2
IS
  dname VARCHAR2(30);
BEGIN
  SELECT dname
    INTO dname
    FROM dept
   WHERE deptno = deptno;
  RETURN dname;
END;

in your WHERE clause, both references to deptno will resolve to the column in the dept table, not to the parameter deptno. That means that no matter what value you pass in to the function, the SELECT statement will return every row from the table and, thus, throw a too_many_rows error. Normally, you would come up with a convention on how to name variables and parameters that would not conflict with your table naming conventions. Prefixing parameters with p_ and local variables with l_ is one common convention. That turns our function into something like this

CREATE OR REPLACE FUNCTION get_dname( p_deptno IN NUMBER )
  RETURN VARCHAR2
IS
  l_dname VARCHAR2(30);
BEGIN
  SELECT dname
    INTO l_dname
    FROM dept
   WHERE deptno = p_deptno;
  RETURN l_dname;
END;

The other option would be to use explicit scoping prefixes when referring to local variables (i.e. get_dname.dname and get_dname.deptno) rather than altering the names of your local variables.

Best Answer

Related Solutions

SQL Developer: how to script output to query results grid

How to Run Procedure in Oracle SQL Developer

Related Question