Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: oracle procedure

    --DROP TABLE MIG_LIMIT_ID_GEN;
    /*CREATE TABLE MIG_LIMIT_ID_GEN(CustomerNo VARCHAR(20),
    AccountNo VARCHAR(20),
    LimitProduct VARCHAR(20),
    LimitReferenceId VARCHAR(30),
    ParentLimitRefId VARCHAR(30),
    HeadParentLimitRefId VARCHAR(30));



    */
    CREATE OR REPLACE PROCEDURE CREATE_LIMIT
    IS
    --BEGIN
    CURSOR CREATE_LIMIT_ID IS
    SELECT LM.ACC_NO,LM.ACC_NO,
    CASE
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3701' THEN '1151'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3702' THEN '1174'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3703' THEN '1181'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3801' THEN '1141'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3802' THEN '1142'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3803' THEN '1143'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3804' THEN '1144'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3901' THEN '3611'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3902' THEN '3621'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3903' THEN '3681'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3904' THEN '3651'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3907' THEN '3641'
    END AS LimitProduct,

    CASE
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3701' THEN '1150'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3702' THEN '1170'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3703' THEN '1180'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3801' THEN '1140'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3802' THEN '1140'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3803' THEN '1140'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3804' THEN '1140'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3901' THEN '3610'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3902' THEN '3620'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3903' THEN '3680'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3904' THEN '3650'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3907' THEN '3640'
    END AS ParentLimitProduct,

    CASE
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3701' THEN '1100'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3702' THEN '1100'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3703' THEN '1100'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3801' THEN '1100'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3802' THEN '1100'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3803' THEN '1100'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3804' THEN '1100'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3901' THEN '3600'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3902' THEN '3600'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3903' THEN '3600'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3904' THEN '3600'
    WHEN SUBSTR(LM.ACC_TYPE,1,4) = '3907' THEN '3600'
    END AS HeadParentLimitProduct
    FROM LA_MASTERI1 LM
    WHERE SUBSTR(LM.ACC_TYPE,1,4) IN('3701','3702','3703','3801','3802','3803','3804 ','3901','3902','3903','3904','3907')
    AND LM.CLOSED='F'
    ORDER BY LM.ACC_NO,LM.ACC_TYPE;

    ACC_NO VARCHAR2 (14 Byte);
    LimitProduct VARCHAR2 (14 Byte);
    ParentLimitProduct VARCHAR2 (14 Byte);
    HeadParentLimitProduct VARCHAR2 (14 Byte);
    ACC_TYPE VARCHAR2 (14 Byte);

    customerNo VARCHAR2(20 Byte);
    prevCustomerNo VARCHAR2(20 Byte);
    limitRef VARCHAR2(20 Byte);
    parentLimitRef VARCHAR2(20 Byte);
    HeadParentLimitRef VARCHAR2(20 Byte);
    prevLimitRef VARCHAR2(20 Byte);
    /*accountNo VARCHAR(20);
    limitRef VARCHAR(20);
    parentLimitRef VARCHAR(20);
    HeadParentLimitRef VARCHAR(20);
    counter INT;
    headParentCounter INT;
    parentCounter INT;
    limitId VARCHAR (30);
    parentLimitId VARCHAR (30);
    headParentLimitId VARCHAR (30);

    prevLimitRef VARCHAR(20);
    prevParentLimitRef VARCHAR(20);
    prevHeadParentLimitRef VARCHAR(20);*/

    --customerNo,accountNo,limitRef,parentLimitRef,HeadP arentLimitRef;
    limitId VARCHAR2 (14 Byte);
    parentLimitId VARCHAR2 (14 Byte);
    headParentLimitId VARCHAR2 (14 Byte);
    counter int;
    parentCounter int;
    headParentCounter int;



    BEGIN
    OPEN CREATE_LIMIT_ID;
    LOOP
    FETCH CREATE_LIMIT_ID INTO ACC_NO,LimitProduct,ParentLimitProduct,HeadParentL imitProduct,ACC_TYPE;
    EXIT WHEN CREATE_LIMIT_ID%NOTFOUND;



    limitId := '' ;
    parentLimitId := '';
    headParentLimitId := '';

    IF customerNo != prevCustomerNo THEN
    BEGIN
    counter := 1;
    parentCounter := 1;
    headParentCounter := 1;

    limitId := customerNo||'.000'||limitRef ||'.01';
    parentLimitId := customerNo||'.000'||parentLimitRef ||'.01';
    headParentLimitId := customerNo||'.000'||HeadParentLimitRef ||'.01';

    counter := counter + 1;
    parentCounter := parentCounter + 1;
    headParentCounter := headParentCounter + 1;

    END;
    ELSE
    BEGIN
    IF limitRef != prevLimitRef THEN
    BEGIN
    counter := 1;
    limitId := customerNo||'.000'||limitRef ||'.01';
    counter := counter + 1;
    END;
    ELSE
    BEGIN
    IF counter<=9 THEN
    BEGIN
    limitId := customerNo||'.000'||limitRef||'.0'||CONVERT(VARCHA R2(8),counter,112);
    END;
    ELSE
    BEGIN
    limitId := customerNo||'.000'||limitRef||'.'||CONVERT(VARCHAR 2(8),counter,112);
    END;
    counter := counter + 1;
    END IF;
    END;
    END IF;
    END;
    END IF;

    IF parentLimitRef != prevParentLimitRef THEN

    BEGIN
    parentCounter := 1;
    parentLimitId := customerNo||'.000'||parentLimitRef ||'.01';
    parentCounter := parentCounter + 1;
    END;
    ELSE
    BEGIN
    IF parentCounter<=9 THEN
    BEGIN
    parentLimitId := customerNo||'.000'||parentLimitRef||'.0'||CONVERT( VARCHAR2(8),parentCounter,112);
    END;
    ELSE
    BEGIN
    parentLimitId := customerNo||'.000'||parentLimitRef||'.'||CONVERT(V ARCHAR2(8),parentCounter,112);
    END;
    parentCounter := parentCounter + 1;
    END IF;
    END;
    END IF;

    IF HeadParentLimitRef != prevHeadParentLimitRef THEN

    BEGIN
    headParentCounter := 1;
    headParentLimitId := customerNo||'.000'||HeadParentLimitRef ||'.01';
    headParentCounter := headParentCounter + 1;
    END;
    ELSE
    BEGIN
    IF headParentCounter<=9 THEN
    BEGIN
    headParentLimitId := customerNo||'.000'||HeadParentLimitRef||'.0'||CONV ERT(VARCHAR2(8),headParentCounter,112);
    END;
    ELSE
    BEGIN
    headParentLimitId := customerNo||'.000'||HeadParentLimitRef||'.'||CONVE RT(VARCHAR2(8),headParentCounter,112);
    END;
    headParentCounter := headParentCounter + 1;
    END IF;


    END;
    END IF ;
    --END IF;

    INSERT INTO MIG_LIMIT_ID_GEN VALUES(customerNo,accountNo,limitRef,limitId,paren tLimitId,headParentLimitId);

    prevCustomerNo := customerNo;
    prevLimitRef := limitRef;
    prevParentLimitRef := parentLimitRef;
    prevHeadParentLimitRef := HeadParentLimitRef;

    FETCH CREATE_LIMIT_ID INTO customerNo,accountNo,limitRef,parentLimitRef,HeadP arentLimitRef;
    --END IF;
    --END;
    CLOSE CREATE_LIMIT_ID;
    --END
    END LOOP;
    END;


    i can't run this procedure!!!!!!!why??????

  2. #2
    Join Date
    Nov 2006
    Posts
    82
    What do you mean you can't? Did you get any error message, if so what is the error message?
    BTW use code tags while putting code to forum.

  3. #3
    Join Date
    Feb 2004
    Location
    Chennai
    Posts
    53
    were u able to compile it
    -Mathan
    For a quick pocket reference of oracle refer http://pocketoracle.blogspot.com/

  4. #4
    Join Date
    Jun 2011
    Posts
    2

    oracle procedure

    if i execute this procedure it show some error message!!!!!!!!!


    128/100 PLS-00222: no function with name 'VARCHAR2' exists in this scope
    128/45 PL/SQL: Statement ignored
    132/99 PLS-00222: no function with name 'VARCHAR2' exists in this scope

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Shortly: what a mess! You should really properly format your code, enclose it within the CODE tags in order to make it readable. Also, omit unnecessary comments.

    What does this do?
    Code:
    CONVERT( VARCHAR2(8),parentCounter,112);
    There is the CONVERT function, but it converts a string from one character set to another. Your code doesn't suggest that it is used as it is supposed to.

    Did you study PL/SQL User's Guide and Reference, as well as Application Developer's Guide - Fundamentals?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •