Home » SQL & PL/SQL » SQL & PL/SQL » calling a function in another function to insert a dml record is failing
calling a function in another function to insert a dml record is failing [message #597842] Tue, 08 October 2013 12:29 Go to next message
912345
Messages: 7
Registered: October 2013
Junior Member
Hi all ,

Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error

SQL Error : ORA-14551: cannot perform a DML operation inside a query

Please help me....

Below Is the code:
FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS

BEGIN

    INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
    VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);

    RETURN 1;

EXCEPTION WHEN OTHERS THEN
    PROC_LOG('Failed in BATCH');
    PROC_LOG('SQL Error : ' || SUBSTR(SQLERRM,1,1000));
    RETURN -1;
END BATCH;
-------------------------------------------------------------------------------
FUNCTION DOWNLOAD_FUNC(numMERCHANT_NUMBER Number) RETURN VARCHAR2 IS
varRETURN_VALUE       VARCHAR2(25):= NULL;

numALLOWED_COUNT           PROD.COUNTER.ALLOWED_COUNT%TYPE;
numLAST_COUNT_ADDED        PROD.COUNTER.LAST_COUNT_ADDED%TYPE;
dtCHANGE_DATE              DATE := NULL;
myVar                      VARCHAR2(32767);
varSTMT                    VARCHAR2(32767);
Yes_SAS_Mer          VARCHAR2(1);
blnSAS_Allowed             BOOLEAN ;

BEGIN
            
            BEGIN 
                SELECT  'Y' into Yes_SAS_Mer
                FROM  PROD.SAS_ASSIGNED_MERCHANTS  sam
                   WHERE sam.MERCHANT_NUMBER = numMERCHANT_NUMBER;
                   dbms_output.put_line('GDS_MERCHANT_NUMBER FOUND : ' ||  numMERCHANT_NUMBER);
             EXCEPTION WHEN OTHERS
                THEN
                  dbms_output.put_line('ERROR IN SAM SELECT : ' || SUBSTR (SQLERRM, 1, 1000));
             END;

            BEGIN
               SELECT CHANGE_DATE, LAST_COUNT_ADDED, ALLOWED_COUNT
               INTO dtCHANGE_DATE, numLAST_COUNT_ADDED, numALLOWED_COUNT
               FROM PROD.COUNTER
               WHERE PROCESS_NAME = 'DAILY' AND COUNTER_IND = 'ABCD'  AND PROCESS_FLAG  = 'Y';
                 IF dtCHANGE_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
             blnSAS_Allowed :=True;
               varSTMT := 'UPDATE PROD.COUNTER ';
              varSTMT := varSTMT ||'SET last_count_added = ' || (numLAST_COUNT_ADDED + 1);
              varSTMT:= varSTMT||' WHERE process_name = ''DAILY''';
              varSTMT := varSTMT ||' AND COUNTER_IND = ''ABCD'''; 
              dbms_output.put_line(' COUNTER STATEMENT FOR UPDATING IS :-' ||varSTMT);


                 IF BATCH(98,varSTMT)>0 THEN
                   NULL;
                 END IF;
          ELSE
           dbms_output.put_line('REACHED MAX ALLOWED COUNT  SO ENDING  :'||SYSDATE);
          END IF;
                  IF (Yes_SAS_Mer='Y') or (Not blnSAS_Allowed) THEN
                      varRETURN_VALUE :='GDS'  ;
                  END IF;

RETURN varRETURN_VALUE ;

EXCEPTION WHEN OTHERS THEN
   dbms_output.put_line('FAILED in  DOWNLOAD_FUNC ');
   dbms_output.put_line('SQL Error : ' || SUBSTR(SQLERRM,1,1000));
    RETURN NULL;
END DOWNLOAD_FUNC;

Desc Batch_Statement

QUEUE_ID Number(15) not null,
TABLE_ID Number(2)not null ,
STATEMENT varchar2(4000 byte) not null,
QUEUE_SEQUENCE_ID number(5) not null ;

*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Tue, 08 October 2013 13:22] by Moderator

Report message to a moderator

icon13.gif  Re: calling a function in another function to insert a dml record is failing [message #597843 is a reply to message #597842] Tue, 08 October 2013 12:32 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

ORA-14551: cannot perform a DML operation inside a query
 *Cause:  DML operation like insert, update, delete or select-for-update
          cannot be performed inside a query or under a PDML slave.
 *Action: Ensure that the offending DML operation is not performed or
          use an autonomous transaction to perform the DML operation within
          the query or PDML slave.

And remove all WHEN OTHERS useless parts (like the one that just call dbms_output); read the link.

Re: calling a function in another function to insert a dml record is failing [message #597848 is a reply to message #597842] Tue, 08 October 2013 13:15 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2193
Registered: May 2013
Location: World Wide on the Web
Senior Member
What is the purpose of al those BEGIN END blocks when your exception block will just hide all the errors?
Re: calling a function in another function to insert a dml record is failing [message #597886 is a reply to message #597848] Wed, 09 October 2013 02:21 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
Lalit, those blocks allow an error to occur without stopping the execution of the outer block:
begin
  begin
    -- process
    exception
       --if an exception occurs exit this block gracefully
  end;
  begin
    --this block will execute even if the previous block raised an error
     --because the previous block still exited gracefully
 --etc etc
end;
/


Don't get me wrong, the actual implementation isn't great here (i.e. using when others rather than trapping explicit errors) but the premise is sound.
Re: calling a function in another function to insert a dml record is failing [message #597900 is a reply to message #597886] Wed, 09 October 2013 03:26 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2193
Registered: May 2013
Location: World Wide on the Web
Senior Member
pablolee wrote on Wed, 09 October 2013 12:51
those blocks allow an error to occur without stopping the execution of the outer block:


I should have better said "Exception handling is vague". My poor statement is questioning about BEGIN-END in disguise. My bad, should have been more clear.

Regards,
Lalit
Re: calling a function in another function to insert a dml record is failing [message #597902 is a reply to message #597900] Wed, 09 October 2013 03:31 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
Quote:
questioning about BEGIN-END in disguise.
What does this mean?
Re: calling a function in another function to insert a dml record is failing [message #597903 is a reply to message #597842] Wed, 09 October 2013 04:00 Go to previous messageGo to next message
tigsav
Messages: 38
Registered: April 2012
Member
Hi ,

Remove the exception handling to check where exactly the error is coming from.
I did the following snippet and it worked for me.
SQL> set serveroutput on;
SQL> 
SQL> DROP TABLE TESTB;
 
Table dropped
SQL> CREATE TABLE TESTB
  2  (
  3  NUM NUMBER,
  4  sql_stmt VARCHAR2(4000)
  5  );
 
Table created
SQL> CREATE OR REPLACE FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS
  2  
  3  BEGIN
  4  
  5      INSERT INTO TESTB
  6      VALUES (numTABLE_ID,varSTMT);
  7  
  8      RETURN 1;
  9  
 10  EXCEPTION WHEN OTHERS THEN
 11      RETURN -1;
 12  END BATCH;
 13  /
 
Function created
SQL> DECLARE
  2  v_sqlstmt VARCHAR2(4000) := 'UPDATE T SET COL1 = 3;';
  3  
  4  BEGIN
  5  
  6      IF BATCH('1',v_sqlstmt) > 0 THEN
  7         DBMS_OUTPUT.PUT_LINE('Done');
  8      ELSE
  9         DBMS_OUTPUT.PUT_LINE('Failed');
 10      END IF;
 11  END;
 12  /
 
Done
 
PL/SQL procedure successfully completed
Re: calling a function in another function to insert a dml record is failing [message #597904 is a reply to message #597903] Wed, 09 October 2013 04:05 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
You've missed the point mate. The op is trying to call that function from a SQL statement.
Re: calling a function in another function to insert a dml record is failing [message #597908 is a reply to message #597904] Wed, 09 October 2013 04:23 Go to previous messageGo to next message
tigsav
Messages: 38
Registered: April 2012
Member
My apologies .
Re: calling a function in another function to insert a dml record is failing [message #597909 is a reply to message #597902] Wed, 09 October 2013 04:39 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2193
Registered: May 2013
Location: World Wide on the Web
Senior Member
pablolee wrote on Wed, 09 October 2013 14:01
Quote:
questioning about BEGIN-END in disguise.
What does this mean?


I mean, my intention was to tell wbout the exception handling, however, my statement is not clear. It sounds as if I was asking OP about why the BEGIN-END blocks have been used. But my intention was to point to the unuseful exception block.

Thanks to you for making things clear.
Re: calling a function in another function to insert a dml record is failing [message #598141 is a reply to message #597909] Thu, 10 October 2013 11:34 Go to previous messageGo to next message
912345
Messages: 7
Registered: October 2013
Junior Member
Hi pablolee ,Michel Cadot,lalit

Thank you all for taking time and look into it...I have tried using Pragma Autonomous_transaction for DOWNLOAD_FUNC .

This Time ..1)It Inserted the Dml into the Batch_statment table .
3)Updated the prod.counter.last_count_added column .
4)Batch function returned with 1.

So my concern is ,can i use the autonomous transaction here to avoid the error or not... Please suggest or is there any way to fix this issue..


FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS

BEGIN

INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);

RETURN 1;

EXCEPTION WHEN OTHERS THEN
PROC_LOG('Failed in BATCH');
PROC_LOG('SQL Error : ' || SUBSTR(SQLERRM,1,1000));
RETURN -1;
END BATCH;
-------------------------------------------------------------------------------
FUNCTION DOWNLOAD_FUNC(numMERCHANT_NUMBER Number) RETURN VARCHAR2 IS
varRETURN_VALUE VARCHAR2(25):= NULL;

PRAGMA AUTONOMOUS_TRANSACTION ;

numALLOWED_COUNT PROD.COUNTER.ALLOWED_COUNT%TYPE;
numLAST_COUNT_ADDED PROD.COUNTER.LAST_COUNT_ADDED%TYPE;
dtCHANGE_DATE DATE := NULL;
myVar VARCHAR2(32767);
varSTMT VARCHAR2(32767);
Yes_SAS_Mer VARCHAR2(1);
blnSAS_Allowed BOOLEAN ;

BEGIN

BEGIN
SELECT 'Y' into Yes_SAS_Mer
FROM PROD.SAS_ASSIGNED_MERCHANTS sam
WHERE sam.MERCHANT_NUMBER = numMERCHANT_NUMBER;
dbms_output.put_line('GDS_MERCHANT_NUMBER FOUND : ' || numMERCHANT_NUMBER);
EXCEPTION WHEN OTHERS
THEN
dbms_output.put_line('ERROR IN SAM SELECT : ' || SUBSTR (SQLERRM, 1, 1000));
END;

BEGIN
SELECT CHANGE_DATE, LAST_COUNT_ADDED, ALLOWED_COUNT
INTO dtCHANGE_DATE, numLAST_COUNT_ADDED, numALLOWED_COUNT
FROM PROD.COUNTER
WHERE PROCESS_NAME = 'DAILY' AND COUNTER_IND = 'ABCD' AND PROCESS_FLAG = 'Y';
IF dtCHANGE_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
blnSAS_Allowed :=True;
varSTMT := 'UPDATE PROD.COUNTER ';
varSTMT := varSTMT ||'SET last_count_added = ' || (numLAST_COUNT_ADDED + 1);
varSTMT:= varSTMT||' WHERE process_name = ''DAILY''';
varSTMT := varSTMT ||' AND COUNTER_IND = ''ABCD''';
dbms_output.put_line(' COUNTER STATEMENT FOR UPDATING IS :-' ||varSTMT);


BEGIN
myvar :=INSERT_BATCH(96,varSTMT) ;
Commit;
IF myvar >0 THEN
NULL;
END IF;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

ELSE
dbms_output.put_line('REACHED MAX ALLOWED COUNT SO ENDING :'||SYSDATE);
END IF;
IF (Yes_SAS_Mer='Y') or (Not blnSAS_Allowed) THEN
varRETURN_VALUE :='GDS' ;
END IF;

RETURN varRETURN_VALUE ;

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('FAILED in DOWNLOAD_FUNC ');
dbms_output.put_line('SQL Error : ' || SUBSTR(SQLERRM,1,1000));
RETURN NULL;
END DOWNLOAD_FUNC;
Re: calling a function in another function to insert a dml record is failing [message #598142 is a reply to message #598141] Thu, 10 October 2013 11:42 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
why do you blatantly ignore what has been directed to you?

*BlackSwan added {code} tags. Please do so yourself in the future.
And remove all WHEN OTHERS useless parts (like the one that just call dbms_output); read the link.
Re: calling a function in another function to insert a dml record is failing [message #598148 is a reply to message #598142] Thu, 10 October 2013 12:09 Go to previous messageGo to next message
912345
Messages: 7
Registered: October 2013
Junior Member
Hi pablolee ,Michel Cadot,lalit

Thank you all for taking time and look into it...I have tried using Pragma Autonomous_transaction for DOWNLOAD_FUNC .

This Time ..1)It Inserted the Dml into the Batch_statment table .
3)Updated the prod.counter.last_count_added column .
4)Batch function returned with 1.

So my concern is ,can i use the autonomous transaction here to avoid the error or not... Please suggest or is there any way to fix this issue..
FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS

BEGIN

INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);

RETURN 1;

EXCEPTION WHEN OTHERS THEN
RETURN -1;
END BATCH;
-------------------------------------------------------------------------------
FUNCTION DOWNLOAD_FUNC(numMERCHANT_NUMBER Number) RETURN VARCHAR2 IS
varRETURN_VALUE VARCHAR2(25):= NULL;

PRAGMA AUTONOMOUS_TRANSACTION ;

numALLOWED_COUNT PROD.COUNTER.ALLOWED_COUNT%TYPE;
numLAST_COUNT_ADDED PROD.COUNTER.LAST_COUNT_ADDED%TYPE;
dtCHANGE_DATE DATE := NULL;
myVar VARCHAR2(32767);
varSTMT VARCHAR2(32767);
Yes_SAS_Mer VARCHAR2(1);
blnSAS_Allowed BOOLEAN ;

BEGIN

BEGIN 
SELECT 'Y' into Yes_SAS_Mer
FROM PROD.SAS_ASSIGNED_MERCHANTS sam
WHERE sam.MERCHANT_NUMBER = numMERCHANT_NUMBER;
EXCEPTION WHEN OTHERS
THEN
 NULL;
END;

BEGIN
SELECT CHANGE_DATE, LAST_COUNT_ADDED, ALLOWED_COUNT
INTO dtCHANGE_DATE, numLAST_COUNT_ADDED, numALLOWED_COUNT
FROM PROD.COUNTER
WHERE PROCESS_NAME = 'DAILY' AND COUNTER_IND = 'ABCD' AND PROCESS_FLAG = 'Y';
IF dtCHANGE_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
blnSAS_Allowed :=True;
varSTMT := 'UPDATE PROD.COUNTER ';
varSTMT := varSTMT ||'SET last_count_added = ' || (numLAST_COUNT_ADDED + 1);
varSTMT:= varSTMT||' WHERE process_name = ''DAILY''';
varSTMT := varSTMT ||' AND COUNTER_IND = ''ABCD'''; 


BEGIN
myvar :=INSERT_BATCH(96,varSTMT) ;
Commit;
IF myvar >0 THEN
NULL;
END IF;

EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

ELSE

END IF;
IF (Yes_SAS_Mer='Y') or (Not blnSAS_Allowed) THEN
varRETURN_VALUE :='GDS' ;
END IF;

RETURN varRETURN_VALUE ;

EXCEPTION WHEN OTHERS THEN

RETURN NULL;
END DOWNLOAD_FUNC;
icon8.gif  Re: calling a function in another function to insert a dml record is failing [message #598150 is a reply to message #598148] Thu, 10 October 2013 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58938
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

A code that is not indented is not readable.

[Updated on: Thu, 10 October 2013 12:25]

Report message to a moderator

Re: calling a function in another function to insert a dml record is failing [message #598153 is a reply to message #598148] Thu, 10 October 2013 13:10 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
Having a when others without a raise is generally considered to be a bug, and is a very, very poor programming practice. As BS said
Please remove all of these bugs, trap expected exceptions, allow unexpected exceptions to propagate, then post your code again, this time, please format it so that it is more readable (indents particularly help with this as Michel said.
Can you also tell us what exactly it is that you;re trying to do? Why o you need the batch function to be a function? Why do you need to have that insert occur?
Re: calling a function in another function to insert a dml record is failing [message #598154 is a reply to message #598150] Thu, 10 October 2013 13:14 Go to previous messageGo to next message
912345
Messages: 7
Registered: October 2013
Junior Member
Sorry ..here you go
FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS

BEGIN

    INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
    VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);

    RETURN 1;

EXCEPTION WHEN OTHERS 
   THEN   
    RETURN -1;
END BATCH;
-------------------------------------------------------------------------------
FUNCTION DOWNLOAD_FUNC(numMERCHANT_NUMBER Number) RETURN VARCHAR2 IS
varRETURN_VALUE       VARCHAR2(25):= NULL;

numALLOWED_COUNT           PROD.COUNTER.ALLOWED_COUNT%TYPE;
numLAST_COUNT_ADDED        PROD.COUNTER.LAST_COUNT_ADDED%TYPE;
dtCHANGE_DATE              DATE := NULL;
myVar                      VARCHAR2(32767);
varSTMT                    VARCHAR2(32767);
Yes_SAS_Mer          VARCHAR2(1);
blnSAS_Allowed             BOOLEAN ;

BEGIN
            
            BEGIN 
                SELECT  'Y' into Yes_SAS_Mer
                FROM  PROD.SAS_ASSIGNED_MERCHANTS  sam
                   WHERE sam.MERCHANT_NUMBER = numMERCHANT_NUMBER;                  
             EXCEPTION WHEN OTHERS
                THEN
                   null ;
             END;

            BEGIN
               SELECT CHANGE_DATE, LAST_COUNT_ADDED, ALLOWED_COUNT
               INTO dtCHANGE_DATE, numLAST_COUNT_ADDED, numALLOWED_COUNT
               FROM PROD.COUNTER
               WHERE PROCESS_NAME = 'DAILY' AND COUNTER_IND = 'ABCD'  AND PROCESS_FLAG  = 'Y';
                 IF dtCHANGE_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
             blnSAS_Allowed :=True;
               varSTMT := 'UPDATE PROD.COUNTER ';
              varSTMT := varSTMT ||'SET last_count_added = ' || (numLAST_COUNT_ADDED + 1);
              varSTMT:= varSTMT||' WHERE process_name = ''DAILY''';
              varSTMT := varSTMT ||' AND COUNTER_IND = ''ABCD'''; 
                  BEGIN
                     myvar :=INSERT_BATCH(96,varSTMT) ;
                      Commit;
                        IF myvar >0 THEN
                          NULL;
                 END IF;

          ELSE
            blnSAS_Allowed :=flase;
          END IF;
                  IF (Yes_SAS_Mer='Y') or (Not blnSAS_Allowed) THEN
                      varRETURN_VALUE :='GDS'  ;
                  END IF;

RETURN varRETURN_VALUE ;

EXCEPTION WHEN OTHERS 
   THEN

    RETURN NULL;
END DOWNLOAD_FUNC;
Re: calling a function in another function to insert a dml record is failing [message #598172 is a reply to message #598154] Fri, 11 October 2013 00:19 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2193
Registered: May 2013
Location: World Wide on the Web
Senior Member
912345 wrote on Thu, 10 October 2013 23:44
                
             EXCEPTION WHEN OTHERS
                THEN
                   null ;
             END;

            EXCEPTION WHEN OTHERS
                THEN
                   numLAST_COUNT_ADDED := 0;
                   numALLOWED_COUNT := 1;
            END;


You have not cosidered what has been suggested to you multiple times.
Re: calling a function in another function to insert a dml record is failing [message #598173 is a reply to message #598172] Fri, 11 October 2013 00:25 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
you can lead some folks to knowledge, but you can not make them think

[Updated on: Fri, 11 October 2013 00:26]

Report message to a moderator

Re: calling a function in another function to insert a dml record is failing [message #598183 is a reply to message #598148] Fri, 11 October 2013 01:39 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
912345 wrote on Thu, 10 October 2013 19:09
1)It Inserted the Dml into the Batch_statment table .
3)Updated the prod.counter.last_count_added column .
4)Batch function returned with 1.

No UPDATE statement is called in BATCH function (by the way, in DOWNLOAD_FUNC you are calling INSERT_BATCH, which is totally different function).
If you refer to the content of VARSTMT, it is only stored into BATCH_STATEMENT.STATEMENT column of the newly inserted row.
912345 wrote on Thu, 10 October 2013 19:09
So my concern is ,can i use the autonomous transaction here to avoid the error or not...

Wrong concern. Autonomous transaction should not be uset to avoid any error. They should be used to make changes independently on the main transaction. The correct question is: after rolling back the calling transaction, do you want to keep the rows in BATCH_STATEMENT table or should those INSERTs be rolled back too??? It depends on exact requirements on that function, so only you (or the one who designed it) is able to answer it.
Quote:
Please suggest or is there any way to fix this issue..

Do not call it (most probably DOWNLOAD_FUNC) in a query, call it in PL/SQL assignment (as you did with INSERT_BATCH).
As you did not post its call here, there is nothing more to suggest.
Re: calling a function in another function to insert a dml record is failing [message #598233 is a reply to message #598183] Fri, 11 October 2013 11:16 Go to previous messageGo to next message
912345
Messages: 7
Registered: October 2013
Junior Member
No UPDATE statement is called in BATCH function (by the way, in DOWNLOAD_FUNC you are calling INSERT_BATCH, which is totally different function).


--Batch is a typo , the fuicntion name is Insert_Batch .

If you refer to the content of VARSTMT, it is only stored into BATCH_STATEMENT.STATEMENT column of the newly inserted row.


Yes.the actual process is to store the record in the bath_satement table and then the record will be executed by a fucntion alled execute_batch_statement as below..(here the dml will happen).

am just pasting few lines as below :

FUNCTION execute_batch_statement return varchar2 is 
varBATCH_STATEMENT           VARCHAR2(32767):= NULL;

CURSOR BATCH_STATEMENT_CUR IS
    SELECT TABLE_ID, STATEMENT
    FROM   BATCH_STATEMENT
    WHERE  QUEUE_ID = numQUEUE_ID  --(this is a sequence_id)
    ORDER BY TABLE_ID,QUEUE_SEQUENCE_ID;

---------------------------------------------
 varBATCH_STATEMENT := BATCH_STATEMENT_REC.STATEMENT;
-----------
 varBATCH_STATEMENT := REPLACE(varBATCH_STATEMENT,'''SYSDATE''','TO_DATE('''||varSYS_DATE_STRING||''',''YYYYMMDDHH24MISS'')');

------------
   EXECUTE IMMEDIATE varBATCH_STATEMENT;

My question was not about the auditing purpose of the records into Batch_statement table.
Here when the records are stored into the batch_statement table ,and are called by the same pacakge.fucntion to excute the dml statements.

So is it the correct way to use the autonomous transaction in a funciton to call another function with a dml statement ?
Re: calling a function in another function to insert a dml record is failing [message #598236 is a reply to message #598233] Fri, 11 October 2013 12:03 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2193
Registered: May 2013
Location: World Wide on the Web
Senior Member
912345 wrote on Fri, 11 October 2013 21:46

So is it the correct way to use the autonomous transaction in a funciton to call another function with a dml statement ?


No.
Re: calling a function in another function to insert a dml record is failing [message #598242 is a reply to message #598236] Fri, 11 October 2013 12:43 Go to previous messageGo to next message
912345
Messages: 7
Registered: October 2013
Junior Member
Lailt ..

Please suggest other than thought what would be the solution ...??

because when i used the autonomous transaction ,everything worked as expected for me ....
Re: calling a function in another function to insert a dml record is failing [message #598248 is a reply to message #598233] Fri, 11 October 2013 13:41 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
912345 wrote on Fri, 11 October 2013 18:16
--Batch is a typo , the fuicntion name is Insert_Batch .

Unfortunately, Oracle does not "self-correct" typos (e.g "fuicntion" in the above sentence, if you would enter it anywhere). How can anyone in the forum distinguish between wrong statements you ran and typos you made when posting?
912345 wrote on Fri, 11 October 2013 18:16
So is it the correct way to use the autonomous transaction in a funciton to call another function with a dml statement ?

It depends on the answer on my question: flyboy wrote on Fri, 11 October 2013 08:39
after rolling back the calling transaction, do you want to keep the rows in BATCH_STATEMENT table or should those INSERTs be rolled back too???

When using AUTONOMOUS TRANSACTION, do not be surprised that BATCH_STATEMENT will contain the newly added rows even after the main transaction fails anywhere after the call to DOWNLOAD_FUNC.
912345 wrote on Fri, 11 October 2013 19:43
Please suggest other than thought what would be the solution ...??

Again, you should read my post to the end, as the answer is there: flyboy wrote on Fri, 11 October 2013 08:39
Do not call it (most probably DOWNLOAD_FUNC) in a query, call it in PL/SQL assignment (as you did with INSERT_BATCH).
As you did not post its call here, there is nothing more to suggest.
Re: calling a function in another function to insert a dml record is failing [message #598259 is a reply to message #598242] Fri, 11 October 2013 16:02 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2193
Registered: May 2013
Location: World Wide on the Web
Senior Member
912345 wrote on Fri, 11 October 2013 23:13
Lailt ..

Please suggest other than thought what would be the solution ...??

because when i used the autonomous transaction ,everything worked as expected for me ....


Firstly, Autonomous transactions are commonly used by error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction.
Re: calling a function in another function to insert a dml record is failing [message #598332 is a reply to message #598259] Sun, 13 October 2013 21:11 Go to previous messageGo to next message
912345
Messages: 7
Registered: October 2013
Junior Member
@flyboy :


The DOWNLOAD_FUNC is calling by a procedure FINAL based on the record data in the Fields table ....


PROCEDURE FINAL(varUPDATE_TYPE VARCHAR2,varTABLE_NAME VARCHAR2 ) IS

CURSOR IM_FIELDS IS (SELECT  F.COLUMN_NAME,F.SELECT_FUNC,F.EXE_FUNC,F.IM_FIELD
              FROM FIELDS F , CHANGE C
              WHERE F.IMP_FIELD IS NOT NULL
              AND F.IM_FIELD = C.FIELD_ID
              AND F.FIELD_ID NOT IN(SELECT C.FIELD_ID FROM CHANGE C, FIELDS F WHERE C.FIELD_ID = F.FIELD_ID AND F.IM_FIELD <>C.FIELD_ID AND C.QUEUE_ID = numQUEUE_ID)
              AND C.QUEUE_ID = numQUEUE_ID --(this Queue_id is a sequence # assigned globaly.)
              AND F.TABLE_NAME = varTABLE_NAME);
              
varDEFAULT              VARCHAR2(255):=NULL;

BEGIN 
  FOR IM_FIELDS_REC IN IM_FIELDS LOOP

        IF varUPDATE_TYPE = 'A' THEN

            varFUNC_RESULT := NULL;
            varFUNC_STATEMENT := NULL;
            IF IMP_FIELDS_REC.EXE_FUNC IS NULL THEN  -- (this column has null value )

                varFUNC_STATEMENT := 'SELECT BATCH.' || IMP_FIELDS_REC.SELECT_FUNC || '(''' || varUPDATE_TYPE || ''', ''' || IM_FIELDS_REC.COLUMN_NAME || ''', NULL,'''||  ''', ''' ||  IM_FIELDS_REC.IM_FIELD || ''') FROM DUAL'; --DOWNLOAd_func is called from here.
            
                BEGIN
               
                     EXECUTE IMMEDIATE  varFUNC_STATEMENT INTO varFUNC_RESULT ;
                END;
            END IF;
        END IF;
END LOOP;
END final;
This is the table structure and record values for FIELDs for DOWNLOAD_FUNC 

DAta setup in the Fields table for the Download_func
 
COLUMN_NAME   SELECT_FUNC     EXE_FUNC    IM_FIELD   TABLE_NAME 
OWN_SYS       DOWNLOAD_FUNC                22222      MINAL 




Purpose of Dowload_func is to set the MINAL.OWN_SYS
Re: calling a function in another function to insert a dml record is failing [message #598333 is a reply to message #598332] Sun, 13 October 2013 21:23 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
> EXECUTE IMMEDIATE varFUNC_STATEMENT INTO varFUNC_RESULT ;
where is varFUNC_RESULTS defined/established?

posted code should be valid & not contain syntax error(s)
Re: calling a function in another function to insert a dml record is failing [message #598334 is a reply to message #597842] Sun, 13 October 2013 23:33 Go to previous messageGo to next message
flyboy
Messages: 1770
Registered: November 2006
Senior Member
@BlackSwan: I am sure that were just small "typos" like call of different function in previous posts; I have no idea for its reason, but I would bet on laziness.

@912345: Just meditate over the difference between the present call (by the way, using SELECT FROM DUAL for variable assignment is bad practice in PL/SQL)
declare
  l_dynamic_func varchar2(100) := 'regexp_instr';
  l_source_str  varchar2(100) := 'Am I understanding SQL?';
  l_regexp_mask varchar2(100) := 'Sql';
  l_match_param varchar2(1) := 'i';
  varfunc_result integer;
begin
  EXECUTE IMMEDIATE 'SELECT '||l_dynamic_func||'( ''' || l_source_str || ''', ''' || l_regexp_mask || ''', 1, 1, 0, ''' || l_match_param || ''') FROM DUAL'
    INTO varfunc_result;
  --dbms_output.put_line( 'result: '||to_char(varfunc_result) );
end;
and simple variable assignment from a dynamic statement
declare
  l_dynamic_func varchar2(100) := 'regexp_instr';
  l_source_str  varchar2(100) := 'Am I understanding SQL?';
  l_regexp_mask varchar2(100) := 'Sql';
  l_match_param varchar2(1) := 'i';
  varfunc_result integer;
begin
  EXECUTE IMMEDIATE 'begin :res := '||l_dynamic_func||'( :l_source_str, :l_regexp_mask, 1, 1, 0, :l_match_param ); end;'
    USING OUT varfunc_result, l_source_str, l_regexp_mask, l_match_param;
  --dbms_output.put_line( 'result: '||to_char(varfunc_result) );
end;

And the very important thing to mention: do not forget to bind the literals that change as I did in the second block of demonstration code (as you can see, it is also much more transparent than concatenating string variables).
http://www.akadia.com/services/ora_bind_variables.html
Re: calling a function in another function to insert a dml record is failing [message #598335 is a reply to message #598334] Sun, 13 October 2013 23:45 Go to previous message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
> but I would bet on laziness.
code does not morph into new text without purposeful assistance
Previous Topic: dynamic cursor
Next Topic: Procedure Calling Methods
Goto Forum:
  


Current Time: Sat Aug 30 15:15:39 CDT 2014

Total time taken to generate the page: 0.10154 seconds