1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Execute a Package from a Procedure

Discussion in 'SQL PL/SQL' started by philseery, Oct 13, 2010.

  1. philseery

    philseery Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hello All,

    I am looking to execute a package from a procedure and I keep running into issues.

    I have a simple package which writes a value into a table, this can be executed in SQL+ fine.

    Code (SQL):


    PACKAGE SCHEMA.PACK1    AS
           PROCEDURE PROC1 (param VARCHAR2);
           FUNCTION FUN1 RETURN VARCHAR2;
    END PACK1;

    PACKAGE BODY SCHEMA.PACK1   AS
           PROCEDURE PROC1 (param IN VARCHAR2) IS
                     BEGIN
                          INSERT INTO SCHEMA.TABLE2 VALUES (param);
                          COMMIT;
                     END PROC1;
                     FUNCTION FUN1 RETURN VARCHAR2 IS
                      BEGIN
                        RETURN ('Hello from Fun1');
                      END FUN1;
                 END PACK1;
    However I need to be able to call this packaged procedure from a regular procedure, so i tried the following:

    Code (SQL):
    PROCEDURE SCHEMA.PROCEDURE3   (param1 IN VARCHAR)
    IS

           q1 VARCHAR(50) := 'PACK1';
           q2 VARCHAR(50) := '.';
           q3 VARCHAR(50) := 'PROC1(''';
           q4 VARCHAR(50) := ''');';
           p1 VARCHAR(200);
           p2 VARCHAR(200);
           p3 VARCHAR(200);
           fq VARCHAR(200);
    BEGIN

         p1 := CONCAT(q1,q2);
         p2 := CONCAT(q3, param1);
         p3 := CONCAT(p2,q4);
         fq := CONCAT(p1,p3);

         dbms_output.put_line(p1);
         dbms_output.put_line(p2);
         dbms_output.put_line(p3);
         dbms_output.put_line(fq);

         EXECUTE IMMEDIATE fq;

    END;
    However trying to execute this procedure gives the following error messages:

    ORA-00900: invalid SQL statement
    ORA-06512: at "SCHEMA.PROCEDURE3", line 24
    ORA-06512: at line 1

    If i copy the output of fq and use that in an EXECUTE command then it works perfectly, so i would assume my syntax is incorrect somewhere.

    Any assistance would be greatly appreciated.
     
  2. SBH

    SBH Forum Expert

    Messages:
    99
    Likes Received:
    6
    Trophy Points:
    160
    Location:
    Bangalore
    Any procedure call from EXECUTE IMMEDIATE must be done within BEGIN...END section.

    You are missing BEGIN...END section
    check the below code

    Code (SQL):

    PROCEDURE SCHEMA.PROCEDURE3   (param1 IN VARCHAR)
    IS
     
           q1 VARCHAR(50) := 'PACK1';
           q2 VARCHAR(50) := '.';
           q3 VARCHAR(50) := 'PROC1(''';
           q4 VARCHAR(50) := ''');';
           p1 VARCHAR(200);
           p2 VARCHAR(200);
           p3 VARCHAR(200);
           fq VARCHAR(200);
    BEGIN
     
         p1 := CONCAT(q1,q2);
         p2 := CONCAT(q3, param1);
         p3 := CONCAT(p2,q4);
         fq := CONCAT(p1,p3);
     
         dbms_output.put_line(p1);
         dbms_output.put_line(p2);
         dbms_output.put_line(p3);
         dbms_output.put_line(fq);
     
         EXECUTE IMMEDIATE 'BEGIN '||fq ||' END;'; -------Added BEGIN END
     
    END;
    EXECUTE IMMEDIATE processes only SQL or PL/SQL calls, but here you are trying to put a dangling call.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No, the problem here is EXECUTE IMMEDIATE is for SQL statements, NOT PL/SQL calls. Its use is completely unnecessary:

    Code (SQL):
     
    SQL> CREATE OR REPLACE PROCEDURE PROCEDURE3      (param1 IN VARCHAR)
      2  IS
      3  BEGIN
      4
      5       pack1.proc1(param1);
      6
      7  END;
      8  /
    PROCEDURE created.
    SQL>
    SQL> EXEC procedure3('SMABORP')
    PL/SQL PROCEDURE successfully completed.
    SQL>
    SQL> SELECT * FROM table2;
    SNORM
    --------------------
    SMABORP
    SMABORP
    SQL>

     
     
    Sadik likes this.
  4. philseery

    philseery Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hello,

    That was exactly what i was looking for. Thank you for all your help, it's greatly appreciated.