Please help solve the error / questions on Stored Procs/fns
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Dec 2001
    Posts
    26
    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;


  2. #2
    Join Date
    Mar 2001
    Posts
    635
    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

  3. #3
    Join Date
    Dec 2001
    Posts
    26

    Talking 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
  •  



Click Here to Expand Forum to Full Width