1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Procedure - IdeConnections displaying 'in record' for each iteration

Discussion in 'SQL PL/SQL' started by oracle_lm, Oct 21, 2013.

  1. oracle_lm

    oracle_lm Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi,

    I am having an issue with running a particular PL/SQL procedure whereby the results from running an iteration keep displaying a list of 'in record' outputs.
    I thought it might be something cached in the db so I've ran the following:

    alter system flush shared_pool;

    ALTER SYSTEM FLUSH BUFFER_CACHE;

    execute dbms_result_cache.flush;

    but still does not resolve my problem. I'm now beginning to think it is holding on to indexes (index by binary_integer) used from the same procedure run previously.

    This is some of the output as an example:

    User:1
    .047761
    .000379
    Test is: AUTO_AGENTPERANALYSISICDSKTBL
    User:2
    '01803' in record
    '01804' in record
    '01805' in record
    '01806' in record
    '01807' in record
    '01808' in record
    '01809' in record
    '01810' in record
    '01812' in record
    '01813' in record
    '01814' in record
    '01815' in record
    '01816' in record
    '01817' in record
    '01818' in record
    '01819' in record
    '01820' in record
    '01821' in record
    '01822' in record
    '01823' in record
    '01824' in record
    '01825' in record
    '01826' in record
    '01827' in record
    '01828' in record
    '01829' in record
    '01830' in record
    '01831' in record

    Has anyone ever had this problem before? I can send on the procedure if needed.

    Thanks
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You will need to post the procedure before anyone can tell you what may be wrong.
     
  3. oracle_lm

    oracle_lm Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    This is the procedure...

    create or replace
    PROCEDURE "CONCURRENT_TESTS"
    (
    test_detail out varchar2,
    time_taken out number
    )
    IS
    cursor all_tests is
    select * from autoperftests;
    TYPE test_tabl_type is table of AUTOPERFTESTS%rowtype index by binary_integer;
    test_tabl test_tabl_type;
    test_name varchar2(40);
    test_result number;
    BEGIN
    dbms_output.enable(buffer_size => NULL);
    open all_tests;
    fetch all_tests BULK COLLECT INTO test_tabl;
    close all_tests;
    --execute immediate 'truncate table auto_perf_results';
    DBMS_OUTPUT.ENABLE (buffer_size => NULL);
    for myindex IN 1..test_tabl.count loop
    test_detail := test_tabl(myindex).test_name;
    dbms_output.put_line('User:' || myindex);
    execute immediate 'begin :this := ' || test_detail ||'; end;' using in out time_taken;
    if time_taken > 5 then
    --dbms_output.enable(1000000);
    dbms_output.put_line('User:' || myindex);
    dbms_output.put_line('Test:' || test_detail || 'Failed. Time taken:' || time_taken || ' > 5 mins!');
    insert into AUTO_PERF_RESULTS(test_name,test_result) values (test_detail,time_taken);
    else
    execute immediate 'begin :this := ' || test_detail ||'; end;' using in out time_taken;
    dbms_output.put_line('Test is: ' || test_detail);
    end if;
    end loop;
    dbms_output.put_line('All tests have ran ... check logs for details');
    END CONCURRENT_TESTS;