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