-
Hi,
I am new at making stored procedures and Functions.
here is a function. It reports an error that it was created but had compile errors ..
I d appreciate if you could find some syntactical error for me.
thanks
Function number 1.
CREATE OR REPLACE FUNCTION FIND_DATA(FORM_RECORD Form_Index%ROWTYPE,
PDF_RECORD Form_Data%ROWTYPE,
subindex Number,
FldUID number,
FldValue varchar2) RETURN number AS
intCL number; -- CLUID
intT number; -- TUID
intRT number; -- RTUID
intFA number; -- FAUID
intRTTP number; -- RTTPUID
intFATP number; -- FATPUID
strOk varchar2;
strNo varchar2;
BEGIN
strOk = 'ok';
strNo = 'no';
intCL := FORM_RECORD.CLUID * PDF_RECORD.CLUID;
intRT := FORM_RECORD.RTUID * PDF_RECORD.RTUID;
intT := FORM_RECORD.TUID * PDF_RECORD.TUID;
intFA := Form_Record.FAUID * PDF_RECORD.FAUID;
intRTTP := FORM_RECORD.RTTPUID * PDF_RECORD.RTTPUID;
intFATP := FORM_RECORD.FATPUID * PDF_RECORD.FATPUID;
SELECT FieldValue INTO FldValue FROM Form_Data
WHERE
CLUID = intCL AND
TUID = intT AND
RTUID = intRT AND
FAUID = intFA AND
RTTPUID = intRTTP AND
FATPUID = intFATP AND
SubIndexVal = subIndex AND
FieldUID = FldUID;
IF fldValue IS NOT NULL THEN
RETURN strok;
ELSE
RETURN strNo;
END IF;
--EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- fldValue := NULL;
-- RETURN strNo;
END FIND_DATA;
FUnction # 2:
---------------------------------------------------------------------
Create Or Replace Function saveTaxFormInDB(FormUID Number,
CLUID number,
PDFdata varchar2) RETURN varchar2 AS
strDatTmp varchar2(32767);
strFldName varchar2(50);
strOldFldValue varchar2(255);
strFldValue varchar2(255);
strDate CONSTANT VARCHAR2(8):='00000000';
strDated varchar2(8);
strTmpName varchar2(50);
strValid varChar2(2);
-- intOldFldVAl NUmber;
intStart number;
intBar number;
intEnd number;
intDot number;
intSubIndex Number;
intFldUID Number;
i number;
Form_Record Form_Fields%ROWTYPE;
PDF_Record Form_Data%ROwTYPE;
Begin
i := 0;
-- gets the Data from the Form_Index Table
SELECT FORMUID, CLUID, TUID, RTUID, FAUID, RTTPUID, FATPUID, Variation, Status
INTO Form_Record
FROM Form_Index
WHERE FormUID = FormUID AND CLUID = CLUID;
-- Parse string. Begin Loop.
WHILE NOT i > LENGTH(PDFData) LOOP
-- Look for the delimiters
intStart := INSTR(PDFData,'<',1,1) + 1;
intBar := INSTR(PDFData,'|',1,1);
intEnd := INSTR(PDFData,'>',1,1) - 1;
IF (intStart = intBar) OR (intBar = intEnd) THEN
goTo LoopNext;
END IF;
strFldName := SUBSTR(PDFData, intStart, (intBar - 1) - intStart + 1);
strFldValue := SUBSTR(PDFData, intStart, intBar + 1, intEnd - (intBar + 1) + 1);
strDated := strDate;
intDot := Instr(strFldName,".",1,1);
-- finding the FieldName and index.
IF NOT intDOT = 0 THEN
strTmpName := strFldName;
strFldName := SUBSTR(strTmpName, intDot - 1, -(intDot-1)); -- getting the Left side of the String
intSubIndex := SUBSTR(strTMpName, intDot+1, Length(strTmpName) - intDot); -- getting the right part
ELSE
intSubIndex := 0;
END IF;
-- get the FieldUID from Form_Fields.
SELECT FieldUID INTO intFldUID FROM Form_Fields WHERE FieldName = strFldName;
-- setting the old DataValue.
strOldFldValue := '';
-- getting data from Form_Fields into PDF_Record.
IF NOT intFldUID = 0 THEN
SELECT FIELDUID, CLUID, RTUID, FAUID, RTTPUID, FATPUID, SUBINDEXED, FIELDNAME
INTO PDF_Record
FROM Form_Fields
WHERE FieldUID := intFldUID;
-- get the relevant data from Form_data.
strValid := FIND_DATA(Form_Record, PDF_Record, intSubIndex, intFldUID, strOldFldValue);
-- IF strValid THEN.
IF strValid = 'ok'THEN
-- IF strFieldValue <> '' THEN.
IF strFldValue <> strOldFldValue THEN
UPDATE_DATA(Form_Record, PDF_Record, intFieldUID,intSubIndex,strFldValue);
END IF
ELSE
ADD_DATA(Form_Record,PDF_Record,intFldUID,intSubIndex,strDated,strFldValue);
END IF
-- End Of Loop
LoopNext:
i := intEnd + 2;
END LOOP;
-- Exception handler.
EXCEPTION
WHEN NO_DATA_FOUND THEN
GOTO LoopNext
End savetaxformInDB;
-
Hi
You have some syntax errors like the following
strOk = 'ok';
strNo = 'no';
This is not allowed it should have been
strOk := 'ok';
strNo := 'no';
The colon was missing
and some temporary variable definition has to be done
when ever you get some error at the sql prompt type the following command
SQL> Show errors
the above command will show errors
Hope this helped if you still have problem continue posting Iam sure we will be able to help
Regards
Santosh
-
Aaargghh
Thanks man santhosh,
That was great. I think i need a break.. that was silly.
anyway .. i am glad i could make the other htings work. i started a coupla hours ago.
thanks.. but beware
more coming.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|