PL-SQL: Execute trigger inside procedure

Question

I want to execute the trigger inside a procedure. Is there any way to execute PL/SQL (with apices) inside a procedure? I try with this code receiving the following error.

 LINE/COL ERROR
 -------- -----------------------------------------------------------------
 6/22     PLS-00103: Encountered the symbol "
          CREATE OR REPLACE TRIGGER managed_service_start
          AFTER STARTUP ON DATABASE
          DECLARE
          pdb_role VARCHAR2(64);
          pdb_name " when expecting one of the following:
          ( - + case mod new not null <an identifier>
          <a double-quoted delimited-identifier> <a bind variable>
          continue avg count current exists max min prior sql stddev
          sum variance execute forall merge time timestamp interval
          date <a string literal with character set specification>

 LINE/COL ERROR
 -------- -----------------------------------------------------------------
          <a number> <a s

How should I do? My goal is to run this procedure just one time and avoid executing the trigger inside each pdb.

CREATE OR REPLACE PROCEDURE start_services AS
BEGIN
    FOR i IN (SELECT name FROM v$pdbs p WHERE p.name <> 'PDB$SEED')
    LOOP
        EXECUTE IMMEDIATE 'ALTER SESSION SET CONTAINER= ' || i.name;
        EXECUTE IMMEDIATE  '
        CREATE OR REPLACE TRIGGER managed_service_start
            AFTER STARTUP ON DATABASE
        DECLARE
            pdb_role VARCHAR2(64);
            pdb_name VARCHAR2(64);
            host VARCHAR2(64);
        BEGIN
            SELECT database_role INTO pdb_role FROM v$database;
            SELECT Upper(sys_context ("userenv", "con_name")) INTO pdb_name FROM dual;
            SELECT host_name INTO host FROM v$instance;

            IF pdb_role = "PRIMARY" THEN
                DBMS_SERVICE.START_SERVICE(pdb_name || "_RW");
            ELSE
                -- IF host IN ("host_name1","host_name2") THEN
                IF host LIKE "%de%" THEN 
                    DBMS_SERVICE.START_SERVICE(pdb_name || "_RO");
                END IF;
            END IF;
        END;
        /
        ';
    END LOOP;
END;
/

What is the reason to create the database trigger dynamically? I don't see any variable in it, so just create the trigger. If you try this, you should also see the syntax errors. Double quotes " must be replaced by single quotes '. Inside a string just double it, i.e. IF pdb_role = ''PRIMARY'' THEN — Wernfried Domscheit
– Wernfried Domscheit, Commented Jun 17, 2019 at 8:08
The reason is that this database architecture has one container and many pluggable database (pdb) inside the container. If I can do this way I avoid creating trigger N times (one for each pdb) and recreating after every plug/unplug. I try replacing " with '' but same error remains. — gipsy
– gipsy, Commented Jun 17, 2019 at 8:19

Stew Ashton · Accepted Answer · 2019-06-17 08:14:32Z

1

In your text, it looks like you are putting double quotes around all your literals. They need to be single quotes.

Since those single quotes are inside of a literal string, you need to put two single quotes in a row.

begin
execute immediate '
CREATE OR REPLACE TRIGGER managed_service_start
AFTER STARTUP ON DATABASE
DECLARE
  pdb_role VARCHAR2(64);
  pdb_name VARCHAR2(64);
  host VARCHAR2(64);
BEGIN
  SELECT database_role INTO pdb_role FROM v$database;
  SELECT Upper(sys_context (''userenv'', ''con_name'')) INTO pdb_name FROM dual;
  SELECT host_name INTO host FROM v$instance;

  IF pdb_role = ''PRIMARY'' THEN
    DBMS_SERVICE.START_SERVICE(pdb_name || ''_RW'');
  ELSE
    -- IF host IN (''host_name1'',''host_name2'') THEN
    IF host LIKE ''%de%'' THEN 
      DBMS_SERVICE.START_SERVICE(pdb_name || ''_RO'');
    END IF;
  END IF;
END;
/';
end;
/

Best regards, Stew Ashton

answered Jun 17, 2019 at 8:14

Stew Ashton

1,5199 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gipsy Over a year ago

With this it compiles but when executing gives error: ORA-44303: service name exists

Popeye · Accepted Answer · 2019-06-17 11:25:11Z

0

Simply, Replace all the double quotes with single quotes and Wrap the DDL of the trigger with Quoted string (q'<delimeter><Your code><delimeter>') as follows:

CREATE OR REPLACE PROCEDURE start_services AS
BEGIN
    FOR i IN (SELECT name FROM v$pdbs p WHERE p.name <> 'PDB$SEED')
    LOOP
        EXECUTE IMMEDIATE 'ALTER SESSION SET CONTAINER= ' || i.name;
        EXECUTE IMMEDIATE  q'#
        CREATE OR REPLACE TRIGGER managed_service_start
            AFTER STARTUP ON DATABASE
        DECLARE
            pdb_role VARCHAR2(64);
            pdb_name VARCHAR2(64);
            host VARCHAR2(64);
        BEGIN
            SELECT database_role INTO pdb_role FROM v$database;
            SELECT Upper(sys_context ('userenv', 'con_name')) INTO pdb_name FROM dual;
            SELECT host_name INTO host FROM v$instance;

            IF pdb_role = 'PRIMARY' THEN
                DBMS_SERVICE.START_SERVICE(pdb_name || '_RW');
            ELSE
                -- IF host IN ('host_name1','host_name2') THEN
                IF host LIKE '%de%' THEN 
                    DBMS_SERVICE.START_SERVICE(pdb_name || '_RO');
                END IF;
            END IF;
        END;
        #';
    END LOOP;
END;
/

Cheers!!

edited Jun 17, 2019 at 11:25

answered Jun 17, 2019 at 8:40

Popeye

36k4 gold badges12 silver badges31 bronze badges

1 Comment

Popeye Over a year ago

You need to remove '/' just before ending String. I have edited the answer. Please check.

Collectives™ on Stack Overflow

PL-SQL: Execute trigger inside procedure

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related