title: PLSQL insert and returning id form trigger on table
body: <p>I am trying to execute insert statement within PLSQL insert and returning out the id generated from trigger on table that use sequences but 
  I am getting below error dose anyone has any idea about that ?</p>
  
  <blockquote>
    <p>ORA-00905: missing keyword</p>
    
    <p>ORA-06512: at "ADMIN.F_INSERT_ORDER_DATA", line 22</p>
    
    <p>ORA-06512: at line 5</p>
  </blockquote>
  
  <pre><code>CREATE OR REPLACE FUNCTION F_INSERT_ORDER_DATA (P_CUSTOMER_ID   IN NUMBER,
                                                                                                        P_NOTES                IN VARCHAR2)
         RETURN NUMBER
      IS
         vCreated_ON   DATE := SYSDATE;
         vORDER_ID     NUMBER;
         vSQL          VARCHAR2 (1024);
      BEGIN
         vSQL := 'INSERT INTO orders'
                                            ||'(ORDER_ID,'
                                            || 'CUSTOMER_ID,'
                                            || 'NOTES,'
                                            || 'CREATED_BY,'
                                            || 'CREATED_ON)'
                                            || 'VALUES (NULL,'                                    --ORDER_ID Filled by trigger
                                            || P_CUSTOMER_ID|| ','                           --CUSTOMER_ID
                                            || ''''|| P_NOTES|| ''','                             --NOTES
                                            || '''SYSTEM'','                                        --CREATED_BY
                                            || ''''|| vCREATED_ON|| ''')'                   --CREATED_ON
                 || 'RETURNING ORDER_ID INTO vORDER_ID';
  
         EXECUTE IMMEDIATE vSQL;
         DBMS_OUTPUT.PUT_LINE(vORDER_ID);
  
         RETURN (vORDER_ID);
      END F_INSERT_ORDER_DATA;
      /
  </code></pre>
  
  <p>Calling the function as below</p>
  
  <pre><code>declare
     result varchar2(1024);
  begin
     -- Call the function
     result := F_INSERT_ORDER_DATA (1000,'----');
  
  end;
  </code></pre>
  
  <p><strong>Code Update</strong></p>
  
  <pre><code>CREATE OR REPLACE FUNCTION F_INSERT_ORDER_DATA (P_CUSTOMER_ID   IN NUMBER,
                                                  P_NOTES         IN VARCHAR2)
     RETURN NUMBER
  IS
     vORDER_ID     NUMBER;
     vCreated_by   VARCHAR2 (128) := 'SYSTEM';
     vCreated_ON   DATE := SYSDATE;
     sql_stmt      VARCHAR2 (1024);
  BEGIN
     sql_stmt :=
        'INSERT INTO orders (ORDER_ID,
                                                           CUSTOMER_ID,
                                                           NOTES,
                                                          CREATED_BY,
                                                          CREATED_ON)
                                          VALUES (NULL,                                 --ORDER_ID Filled by trigger
                                                         :PCUSTOMER_ID,               --CUSTOMER_ID
                                                         :PNOTES,                            --NOTES
                                                         :PCREATED_BY,                  --CREATED_BY
                                                         :PCREATED_ON,                 --CREATED_ON
                       RETURNING ORDER_ID INTO :vORDER_ID';
  
     EXECUTE IMMEDIATE sql_stmt
        USING P_CUSTOMER_ID,
              P_NOTES,
              VCreated_by,
              vCreated_ON
        RETURNING INTO vORDER_ID;
  
     DBMS_OUTPUT.PUT_LINE (vORDER_ID);
  
     RETURN (vORDER_ID);
  END F_INSERT_ORDER_DATA;
  /
  </code></pre>
  
  <p>calling function</p>
  
  <pre><code>DECLARE
     vSQL    VARCHAR2 (4000);
     var     NUMBER;
  BEGIN
     vSQL := 'F_INSERT_ORDER_DATA(:P_CUSTOMER_ID,:P_NOTES);';
  
     EXECUTE IMMEDIATE vSQL USING 1000, 'TEST' RETURNING INTO var;
  
     DBMS_OUTPUT.PUT_LINE (var);
  END;
  </code></pre>