Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137

    Unanswered: Weird procedure size limit in PLSQL?

    Has anyone else come across this weird bug when the sizeof a procedure exceeds a certain amount the compile fails with ora-00604 and an 0ra-06502 plsql: numeric or value error.
    So if I remove a bit of code or even a string contant (doesnt matter which one) it compiles. Also whats weird is commenting out code doesnt affect it I have to phyically remove the code before it works.

    The database is 9.2.0.1 and the characterset is UTF8 on win2k. I can also duplicate this through TOAD, SQL nav or sqlplus. Cant find anything on metalink about it either.

    I even managed to create a dummy procedure which just assigns a long string contant repeatedly into a variable and after about a 100 lines it throws the same error.

    Alan

    i.e.
    <pre>
    CREATE OR REPLACE PROCEDURE map_submitted_one (
    this_netid IN NUMBER,
    this_period IN NUMBER,
    this_nsc IN NUMBER,
    orig_schema IN VARCHAR2,
    orig_brand IN VARCHAR2
    )
    IS
    this_dpid NUMBER;
    this_finyrend NUMBER;
    newtot NUMBER:= 0;
    origtot NUMBER:= 0;
    cond1val NUMBER:= 0;
    cond2val NUMBER:= 0;
    num_forms NUMBER:= 0;
    condvalue NUMBER;
    is_it_there NUMBER;
    stot NUMBER;
    clean_first BOOLEAN;
    same_items BOOLEAN;
    do_map BOOLEAN;
    sql_stmt VARCHAR2(900);
    dest_item_id NUMBER;
    must_clean_for_same_items EXCEPTION;
    BEGIN
    clean_first := TRUE;
    same_items := TRUE;

    -- get dp_id for this net_id and period
    SELECT dp_id
    INTO this_dpid
    FROM dataprofile
    WHERE networkentity_id = this_netid
    AND period = this_period
    AND drv_or_subm = 'S'
    AND act_or_bud = 'A'
    AND itemset_id = 1;

    -- get finyrend for this net_id
    SELECT fin_yrend
    INTO this_finyrend
    FROM dealerprofile
    WHERE networkentity_id = this_netid;

    IF (clean_first)
    THEN
    DELETE FROM gmemb.submitteddata WHERE dp_id = this_dpid;
    IF same_items THEN
    EXECUTE IMMEDIATE sql_stmt USING this_dpid;
    END IF;
    ELSE
    IF (same_items)
    THEN
    sql_stmt := 'insert into gmemb.submitteddata select * from '|| orig_schema ||
    '.submitteddata where dp_id=:1 and value is not null and item_id<2833)';

    RAISE must_clean_for_same_items;
    END IF;
    END IF;

    newtot := 0;

    <<first_item_loop>>
    FOR crec2 IN (SELECT sequence_id, destination_item_id, condition_id
    FROM stg$map_to_gmemb s, gmemb.item i
    WHERE nsc_id IN (0,this_nsc)
    AND i.item_id = s.destination_item_id
    GROUP BY sequence_id, destination_item_id, condition_id
    ORDER BY sequence_id ASC)
    LOOP
    IF crec2.condition_id IS NOT NULL THEN
    sql_stmt :=
    'select sum(value) from '|| orig_schema ||
    '.submitteddata s, stg$map_to_gmemb_condition@nxcdev3 rmc where rmc.condition_id=:P0 and s.item_id=to_number(rmc.condition) and s.dp_id='|| to_char(this_dpid);

    EXECUTE IMMEDIATE sql_stmt
    INTO condvalue
    USING crec2.condition_id, this_dpid;
    IF (condvalue <> 0) THEN
    do_map := TRUE;

    IF (crec2.destination_item_id BETWEEN 2619 AND 2621) THEN
    do_map := FALSE;
    END IF;
    ELSE
    do_map := FALSE;

    IF (crec2.destination_item_id BETWEEN 2619 AND 2621) THEN
    do_map := TRUE;
    END IF;
    END IF;

    ELSE
    do_map := TRUE;
    END IF;

    IF (do_map)
    THEN

    EXECUTE IMMEDIATE sql_stmt
    INTO stot
    USING this_dpid,
    crec2.destination_item_id,
    crec2.sequence_id;
    BEGIN
    SELECT dp_id
    INTO is_it_there
    FROM gmemb.submitteddata
    WHERE item_id = crec2.destination_item_id
    AND dp_id = this_dpid;

    UPDATE gmemb.submitteddata
    SET VALUE = stot
    WHERE item_id = crec2.destination_item_id
    AND dp_id = this_dpid;
    EXCEPTION
    WHEN NO_DATA_FOUND
    THEN
    INSERT INTO gmemb.submitteddata VALUES (this_dpid, crec2.destination_item_id, stot);
    END;

    stot := 0;
    END IF;
    END LOOP first_item_loop;

    map_submitted_two ( this_netid, this_period, this_nsc, orig_schema, orig_brand, this_dpid);
    END map_submitted_one;
    /
    </pre>

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    you start with
    this_dpid NUMBER;

    then you do this:
    SELECT dp_id
    INTO this_dpid
    FROM dataprofile


    but later you convert that to:
    s.dp_id='|| to_char(this_dpid);


    my questions is: is "this_dpid" a number or a character?
    It looks like a number so could the "to_char" line be causing a problem somehow?

    I think you are going wrong somewhere below since it looks like you are
    setting up two variables differently in your sql_stmt line:
    PHP Code:
    IF crec2.condition_id IS NOT NULL THEN
    sql_stmt 
    :=
    'select sum(value) from '|| orig_schema ||
    '.submitteddata s, stg$map_to_gmemb_condition@nxcdev3 rmc where rmc.condition_id=:P0 and s.item_id=to_number(rmc.condition) and s.dp_id='|| to_char(this_dpid);

    EXECUTE IMMEDIATE sql_stmt
    INTO condvalue
    USING crec2
    .condition_idthis_dpid;
    IF (
    condvalue <> 0THEN
    do_map 
    := TRUE
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    No I tried with and without the to_char and it still errors. The weird thing is that when the code size is above a certain point the code automatically errors on line 8 with a 6502 regardless of what the code actually is.

    Alan

    This also has the same problem. Remove a couple of lines and it will compile

    create or replace PROCEDURE map_subm_one
    IS
    this_sql VARCHAR2 (1000);
    BEGIN
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    this_sql := 'this is a long piece of text';
    END map_subm_one;
    /

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    patch to 9.2.0.4 (or 9205 is the latest)

    your code compiles on my db (9205)
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What version of the database are you using. There is an upper limit (total # of characters) on the size of a procedure. An easy work around is to break your code into multiple procedures and call it in order. For example.

    declare

    procedure my_proc1....
    end;

    procedure my_proc2....
    end;
    procedure my_proc3....
    end;

    begin
    my_proc1;
    my_proc2;
    my_proc3;
    end;

    Where each inline procedure is under the limit.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by beilstwh
    What version of the database are you using. There is an upper limit (total # of characters) on the size of a procedure.
    he stated he is 9.2.0.1
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Then thats not the problem. The limit (around 8k) disappeared in Oracle 8I.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  8. #8
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Compiles fine on 8i as well. Just curious, is this "failing" on the DB or your front-end (lets say Forms/Reports) ?

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Sorted the problem. I had a ddl trigger to audit all ddl and this recorded the calling sql. So when the ddl exceeded the 4k limit on the column it raised an exception but not in the trigger but the calling sql.

    Alan

Posting Permissions

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