CODEWITHSUNDEEP
oracle-databasepackageexists
Suwaid Akbar
Suwaid Akbar

Reputation: 139

Check Package exist or not before create it in Oracle

Need help

How do I check for a package if it exists or not and skip the creation if it already exists.

I have done the following and I got an error

DECLARE
    l_cnt INTEGER;
    own   VARCHAR(200);
BEGIN
    SELECT sys_context( 'userenv', 'current_schema' ) INTO own FROM dual;
    SELECT count(*)
    INTO l_cnt
    FROM ALL_OBJECTS
    WHERE object_type = 'PACKAGE' 
    and object_name = 'JUSTICE_LEAGUE'
      AND OWNER        = own;
    IF( l_cnt       <= 0) THEN

EXECUTE IMMEDIATE 
'create or replace PACKAGE "JUSTICE_LEAGUE" AS

FUNCTION BATMAN(argPSN INT)
RETURN INT;

FUNCTION SUPERMAN(argSN int)
RETURN Info.infovalue%Type;

PROCEDURE AQUAMAN(argASN INT,argAssignedUser folderProcess.assignedUser%Type DEFAULT 'None');

END JUSTICE_LEAGUE';

DBMS_OUTPUT.PUT_LINE('Package created successfully');
    ELSE
DBMS_OUTPUT.PUT_LINE('Package exists already');

END IF;
END;
/

Error report -
ORA-06550: line 23, column 70:
PLS-00103: Encountered the symbol "ALL" when expecting one of the following:

Is it a right way to put the create command for package within EXECUTE IMMEDIATE ?

Upvotes: 1

Views: 8370

Answers (2)

Littlefoot
Littlefoot

Reputation: 142968

First of all - no, it is wrong way to do that. The fact that dynamic SQL exists doesn't mean that you should use it, especially not for creating packages (or any other objects). There are really rare situations you'd want to do that.

PL/SQL procedures (functions, packages, triggers) offer create OR REPLACE option so - it is safe to run that statement as is (not as dynamic SQL). It means that:

  • if it doesn't exist, it'll be created
  • if it exists, it'll be overwritten by code you've just ran

If you insist on dynamic SQL, check its (package's) existence by querying user_objects:

SQL> select count(*)
  2  from user_objects
  3  where object_name = 'JUSTICE_LEAGUE'
  4    and object_type = 'PACKAGE';

  COUNT(*)
----------
         0

SQL>

Depending on result, run execute immediate (or not).

Upvotes: 4

Popeye
Popeye

Reputation: 35920

There is only one issue with your code.

You have not handled the dynamic query properly. single-quote in the dynamic query must be escaped.

Two single quotes('') in the string are considered as one single quote(') in the final string. Or you can use the quoted-string (q'{<your_string>}')

replace -- DEFAULT 'None'); with DEFAULT ''None'');

Upvotes: 1

Related Questions