1 2 3 Previous Next 41 Replies Latest reply: Oct 10, 2013 4:43 AM by jihuyao RSS

    calling a fucntion inside a fucntion to insert into a table and recieveing error --ORA-14551: cannot perform a DML operation inside a query .

    978485

      Hi all ,

      your help is greatly appreciated ...

      Am calling B.fucntion inside a A.function to insert data into a table ...


      Here when am calling the B.fucntion to insert data into the table ..am recieveing an error

      Failed in B -SQL Error : ORA-14551: cannot perform a DML operation inside a query..


      Can anyone please help me to clear this ..

       


      FUNCTION A(varUPD_TYPE  IN VARCHAR2) RETURN VARCHAR2 IS
      varRETURN_VALUE       VARCHAR2(25):= NULL;

      numALLOWED_COUNT           PROD.PROCESS_COUNTER.ALLOWED_COUNT%TYPE;
      numLAST_COUNT_ADDED        PROD.PROCESS_COUNTER.LAST_COUNT_ADDED%TYPE;  
      dtCHANGE_DATE              DATE := NULL;
      varSTMT                    VARCHAR2(2000);
      bln_Allowed                BOOLEAN ;
      myVar                      VARCHAR2(32767);


      BEGIN

      IF varUPD_TYPE ='A' THEN

                BEGIN
                    SELECT CH_DATE,LAST_COUNT_ADDED, ALLOWED_COUNT
                     INTO dtCH_DATE,numLAST_COUNT_ADDED, numALLOWED_COUNT
                    FROM PROD.PROCESS_COUNTER
                    WHERE PROCESS_NAME = 'DAILY'
                    AND COUNTER_IND = 'D'

                    IF dtCH_DATE<= TRUNC(SYSDATE)  THEN
                        numLAST_COUNT_ADDED := 0;
                    END IF;

               EXCEPTION
                    WHEN OTHERS THEN
                        numLAST_COUNT_ADDED := 0;
                        numALLOWED_COUNT := 1;
               END;
                    IF  numALLOWED_COUNT >= numLAST_COUNT_ADDED+1  THEN
                      
                       bln_Allowed :=True;
                                      
                        varSTMT := 'UPDATE PROD.TMS_PROCESS_COUNTER ';
                        varSTMT := varSTMT ||' SET last_count_added = ' || (numLAST_COUNT_ADDED + 1);
                        varSTMT := varSTMT ||' WHERE process_name = ''DAILY''';
                        varSTMT := varSTMT ||' AND COUNTER_IND = ''D''';

                    
                    IF varSTMT IS NOT NULL   
                       THEN
                            myvar := B(96,varSTMT);
                          PROC_LOG ('CALLING B '||myvar);
                          IF myvar >0 THEN
                            NULL;
                           END IF;
                     END IF;
                   Else
                       
                   End if;

      END IF;  
               
      EXCEPTION WHEN OTHERS THEN
          PROC_LOG('Failed in  A ');
          PROC_LOG('SQL Error : ' || SUBSTR(SQLERRM,1,1000));
          RETURN NULL;
      END A;

       

      FUNCTION B(numTABLE_ID IN NUMBER, varSQL_STATEMENT IN VARCHAR2) RETURN NUMBER IS
      varINSERT_BATCH_STMT  VARCHAR2(32767)     := NULL;
      varADD_REC_TYPE       BATCH_TABLES.ADD_REC_TYPE%TYPE;

      BEGIN
        
          INSERT INTO BATCH_STATEMENT(ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
          VALUES (numID,numTABLE_ID,varSQL_STATEMENT,1);

      EXCEPTION WHEN OTHERS THEN
          PROC_LOG('Failed in B');
          PROC_LOG('SQL Error : ' || SUBSTR(SQLERRM,1,1000));
          RETURN -1;
      END B;


      Batch_statement table structure:


      ID              number(15) not null
      table_id        number(2)  not null
      statement        varchar2(4000) not null
      Queue_sequence_id number(5)  not null

       


        1 2 3 Previous Next