Executing a Function or Procedure from ouside Oracle
I have verified the code below as a valid Function and it executes from TOAD. It does not work if called from:
SELECT F_T_TOTALE_TO_T_SCRAP_SC(V_TIRE_ID_EXTRACT)
FROM T_TOTALE
The Error is 14551. I have researched this error and it states that a function cannot be executed from a SELECT statement if INSERT or various othe statements are in the code. However this is exactly what I want for the function to do. Please educate me on maybe a different way to do the same thing.
CREATE OR REPLACE FUNCTION F_T_TOTALE_TO_T_SCRAP_SC
--this function copies the RECORD from T_TOTALE to T_SCRAP_SC
--input: Tire_ID
--output: vPassFail (Pass=1, Fail=0)
(Tire_IDIn NUMBER)
RETURN NUMBER
IS
vPassFail NUMBER;
vTTotaleIDCOP NUMBER;
vTScrapSCIDCOP NUMBER;
--get TIRE ID from T_TOTALE
CURSOR tt
IS
SELECT IDCOP
FROM MPA.T_TOTALE
WHERE IDCOP = Tire_IDIn;
--get TIRE ID from T_SCCRAP_SC
CURSOR sc
IS
SELECT IDCOP
FROM MPA.T_SCRAP_SC
WHERE IDCOP = Tire_IDIn;
-- Get Tire Number from T_Totale for comparison
BEGIN
--INSERT INTO MPA.T_SCRAP_SC
INSERT INTO MPA.T_SCRAP_SC
SELECT *
FROM MPA.T_TOTALE
WHERE MPA.T_TOTALE.IDCOP=Tire_IDIn;
--get TIRE ID from T_TOTALE
OPEN tt;
FETCH tt INTO vTScrapSCIDCOP;
--get TIRE ID from T_SCRAP_SC
OPEN sc;
FETCH sc INTO vTTotaleIDCOP;
IF (vTTotaleIDCOP)=(vTScrapSCIDCOP) THEN
vPassFail:= 1;
ELSE
vPassFail:= 0;
END IF;
RETURN vPassFail;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
-- Consider logging the error and then re-raise
RAISE;
END F_T_TOTALE_TO_T_SCRAP_SC;
/
Software/Hardware used:
Software/Hardware used:
ASKED:
November 14, 2006 9:57 AM
UPDATED:
November 16, 2006 9:34 AM
Answer Wiki:
consider the following:
declare
....
v_passfail interger ;
TIRE_ID_EXTRACT integer ;
begin
....
SELECT V_TIRE_ID_EXTRACT into TIRE_ID_EXTRACT
FROM T_TOTALE where rownum = 1 ;
v_passfail := F_T_TOTALE_TO_T_SCRAP_SC(TIRE_ID_EXTRACT) ;
....
To see all answers submitted to the Answer Wiki: View Answer History.
How about an autonomous function?
Research this: PRAGMA AUTONOMOUS_TRANSACTION
http://www.psoug.org/reference/autonomous_tx.html
http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1522
Kojak
I modified the code as follows and it works from the external “SELECT” (maybe too good). When I tested Oracle returned Error – 00001 Primary Key Violated. I did some debugging by disabling the Constraints and found that the code continuously executes.
I want it to only execute once and return the ?vPassFail? value. Maybe someone has run into this problem before?
CREATE OR REPLACE FUNCTION F_T_TOTALE_TO_T_SCRAP_SC
–this function copies the RECORD from T_TOTALE to T_SCRAP_SC
–input: Tire_ID
–output: vPassFail (Pass=1, Fail=0)
(Tire_IDIn NUMBER)
RETURN NUMBER
IS
vPassFail NUMBER;
vTTotaleIDCOP NUMBER;
vTScrapSCIDCOP NUMBER;
–get TIRE ID from T_TOTALE
CURSOR tt
IS
SELECT IDCOP
FROM MPA.T_TOTALE
WHERE IDCOP = Tire_IDIn;
–get TIRE ID from T_SCCRAP_SC
CURSOR sc
IS
SELECT IDCOP
FROM MPA.T_SCRAP_SC
WHERE IDCOP = Tire_IDIn;
PRAGMA AUTONOMOUS_TRANSACTION
– Get Tire Number from T_Totale for comparison
BEGIN
–INSERT INTO MPA.T_SCRAP_SC
INSERT INTO MPA.T_SCRAP_SC
SELECT *
FROM MPA.T_TOTALE
WHERE MPA.T_TOTALE.IDCOP=Tire_IDIn;
COMMIT;
–get TIRE ID from T_TOTALE
OPEN tt;
FETCH tt INTO vTScrapSCIDCOP;
–get TIRE ID from T_SCRAP_SC
OPEN sc;
FETCH sc INTO vTTotaleIDCOP;
IF (vTTotaleIDCOP)=(vTScrapSCIDCOP) THEN
vPassFail:= 1;
ELSE
vPassFail:= 0;
END IF;
RETURN vPassFail;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
WHEN OTHERS
THEN
– Consider logging the error and then re-raise
RAISE;
END F_T_TOTALE_TO_T_SCRAP_SC;
/