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;
/

Answer 1

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!!

Answer 2

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