Executing a Function or Procedure from ouside Oracle

Tags:
Microsoft Windows
Oracle
Oracle 9i
Oracle development
SQL
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; /

Answer Wiki

Thanks. We'll let you know when a new response is added.

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) ;

….

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kojakhu
    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
    0 pointsBadges:
    report
  • Moonmullins
    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; /
    0 pointsBadges:
    report
  • Moonmullins
    [...] Address: http://itknowledgeexchange.techtarget.com/itanswers/executing-a-function-or-procedure-from-ouside-or... (0) Comments Read [...]
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following