Please help solve the error / questions on Stored Procs/fns

[TO_raptor]

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;

[santoshym]

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

[TO_raptor]

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.