Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2012
    Posts
    10

    Unanswered: No response when fetching the data using sys_refcursors as OUT param

    Hi...

    can any one help in sorting the issue.

    Code:
    CREATE OR REPLACE
    PROCEDURE SP_TEST(C1 OUT SYS_REFCURSOR)
    AS 
    dname varchar2(255);
    description varchar2(255); 
    BEGIN
    OPEN C1 FOR
    
    SELECT SCN_NAME,
    SCN_DESCRIPTION 
    FROM dataobject WHERE SC_ID ='283649';
    loop
    fetch c1 into dname,
    description;
    end loop;
    CLOSE C1;
    END SP_TEST;
    actually it should return 160 rows.

    but, when i execute via below commands, there is no response .
    Code:
    VAR D REFCURSOR
    EXEC SP_TEST()
    print r
    Thanks in Advance.

    Regards,
    Shiva
    Last edited by ShivaG; 03-01-12 at 01:43. Reason: added code tag

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Answered in your previous topic. That's what happens when you multipost; you never know what will be the result.

    By the way - congratulations! You managed to properly use CODE tags!

  3. #3
    Join Date
    Feb 2012
    Posts
    10
    Thanks Littlefoot

    I have some Query regarding performance.
    can you please suggest best way to improve the performance.

    current implementation

    Tables:

    tmp_table1
    tmp_table2
    tmp_table3

    views:
    vw_tmp1
    vw_tmp2
    view1,view2,view3,view4,view5


    process is like this

    chk1 method
    1.fills the data in Tmp_table1(file names alone(ex:800 rows))
    2.inserts into tmp_table2 from vw_tmp1(had some logic in this uses CTE in this view)
    3.select data from view1(gets the data from tmp_table2); data may be some times more then 4000 rows
    4.count the rows fetched

    chk2 method
    1.fills the data in Tmp_table1(file names alone(ex:800 rows))
    2.inserts into tmp_table2 from vw_tmp1(had some logic in this,uses CTE in this view)
    3.select data from view2(gets the data from tmp_table2)
    4.count the rows fetched

    chk3 method
    1.fills the data in Tmp_table1(file names alone(ex:800 rows))
    2.inserts into tmp_table2 from vw_tmp1(had some logic in this uses CTE in this view)
    3.select data from view3(gets the data from tmp_table2)
    4.count the rows fetched

    chk4 method
    1.fills the data in Tmp_table1(file names alone(ex:800 rows))
    2.inserts into tmp_table2 from vw_tmp1(had some logic in this uses CTE in this view)
    3.select data from view4(gets the data from tmp_table2)
    4.count the rows fetched

    chk5 method
    1.fills the data in Tmp_table1(file names alone(ex:800 rows))
    2.inserts into tmp_table2 from vw_tmp1(had some logic in this uses CTE in this view)
    3.select data from view5(gets the data from tmp_table3)
    4.count the rows fetched

    chk6 method
    1.fills the data in Tmp_table1(file names alone(ex:800 rows))
    2.inserts into tmp_table2 from vw_tmp1(had some logic in this uses CTE in this view)
    3.select data from view6(gets the data from tmp_table3)
    4.count the rows fetched

    these
    Mering of fetched rows after the each method to single list.

    for executing all these methods, processing time is 10 to 12 min.

    I am planning to import all the process in single package and return all the rows.


    Is there any way so that i can improve the performance.

    what if i use procedures to fetch the data instead of views?

    Thanks in Advance .

    Regards,
    Shiva

  4. #4
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Sorry ShivaG, from your description it is really really difficult to understand what this process is and impossible to help you improve performance. You will need to post extracts of your code with more specific questions.

    Quote Originally Posted by ShivaG View Post
    what if i use procedures to fetch the data instead of views?
    As a general rule, try to accomplish as much as possible in a single SQL statement. If you find yourself writing lots of stored procedures, functions and specifically PL/SQL for loops, you are most probably doing something wrong.

    For me, LOOPs are most often a source of performance problems and 9 out of 10 times can be re-written to use a single SQL statement to accomplish the same.

    Post examples of your code.

  5. #5
    Join Date
    Feb 2012
    Posts
    10
    Hi Dayneo,

    please find the code.

    Code:
    CREATE OR REPLACE PACKAGE PKG_CHECKS AS
    TYPE CHK_CURSOR IS REF CURSOR;
    PROCEDURE SP_MODULE_CHECKS(CHECKS IN OUT VARCHAR2, IMP_DATA_UND OUT CHK_CURSOR);
    end PKG_CHECKS;
    
    create or replace
    PACKAGE BODY PKG_CHECKS AS 
    PROCEDURE SP_MODULE_CHECKS(checks in out VARCHAR2, IMP_DATA_UND OUT CHK_CURSOR)
    IS
    BEGIN
    OPEN IMP_DATA_UND FOR
    WITH TMP_IMP_DATA_UND AS (
    --chk1
    IF INSTR(CHECKS,'IMPORTED DATA UNDEFINED') = 0 THEN
    SELECT DATAOBJECTNAME,SPECNAME,AGGREGATE,DESCRIPTION,CHECKTYPE,CLASSIFICATION
    FROM VW_IMPDATAUNDEFINED_1
    ELSE
    SELECT 1 FROM DUAL
    END IF
    --chk2
    IF INSTR(CHECKS,'MULTIPLE DATA DEFINITION') = 0 THEN
    SELECT * FROM VW_MULDEFDATA
    ELSE
    SELECT 1 FROM DUAL
    END IF
    --chk3
    --chk4
    --chk5
    --chk6
    )
    -- retun all the rows from all the chk's
    SELECT DATAOBJECTNAME,SPECNAME,AGGREGATE,DESCRIPTION,CHECKTYPE,CLASSIFICATION FROM TMP_IMP_DATA_UND;
    --count of each check too...
    --statement
    end SP_MODULE_CHECKS;
    end PKG_CHECKS;
    I am trying to fetch the data from all the 6 views and retuning the data via cursor.
    And I need count of the each check which return via checks param.

    Please do need full.

    Thanks in Advance.

    Regards,
    ShivaG

  6. #6
    Join Date
    Feb 2012
    Posts
    10
    Hi Dayneo,

    I have written some dynamic select stament.

    Code:
    create or replace
    PACKAGE BODY PKG_CHECKS AS 
    PROCEDURE SP_MODULE_CHECKS(CHECKS IN OUT VARCHAR2, IMP_DATA_UND OUT CHK_CURSOR)
    IS
    Dyn_Proc varchar2 (4000);
    BEGIN
    DYN_PROC := 'SELECT * FROM ';
    IF INSTR(CHECKS,'IMPORTED DATA UNDEFINED') = 0 THEN
    DYN_PROC := DYN_PROC || 'VW_IMPDATAUNDEFINED_1 union all ';
    end if;
    IF INSTR(CHECKS,'MULTIPLE DATA DEFINITION') = 0 THEN
    DYN_PROC := DYN_PROC || 'VW_MULDEFDATA union all ';
    end if;
    IF INSTR(CHECKS,'UNUSED OUTPUT DATA') = 0 THEN
    DYN_PROC := DYN_PROC || 'VW_UNUSEDOUTPUTDATA union all ';
    end IF;
    IF INSTR(CHECKS,'INPUT OUTPUT COHERENCY') = 0 THEN
    DYN_PROC := DYN_PROC || 'VW_IO_COHERENCY union all ';
    end if;
    IF INSTR(CHECKS,'IMPORTED ACTION UNDEFINED') = 0 THEN
    DYN_PROC := DYN_PROC || 'VW_IMPACTIONUNDEFINED union all ';
    end IF;
    IF INSTR(CHECKS,'MULTIPLE ACTION DEFINITION') = 0 THEN
    DYN_PROC := DYN_PROC || 'VW_MULDEFACTIONS ';
    END IF;
    OPEN IMP_DATA_UND FOR DYN_PROC;
    END;
    end PKG_CHECKS;
    but i need count in this, how can i get count, with ount another count statement.


    Regards,
    Shivag

  7. #7
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Question

    @ShivaG.
    Have you tested this procedure at all? Try execute this and let us know what happens:
    Code:
    begin
       PKG_CHECKS.SP_MODULE_CHECKS('VW_IMPDATAUNDEFINED_1');
    end;
    /

  8. #8
    Join Date
    Feb 2012
    Posts
    10
    Hi Dayneo,

    i got the out put after small modifications.

    Code:
    CREATE OR REPLACE
    PACKAGE BODY PKG_CHECKS AS 
    PROCEDURE SP_MODULE_CHECKS(CHECKS IN OUT VARCHAR2, IMP_DATA_UND OUT CHK_CURSOR)
    IS
    DYN_PROC VARCHAR2 (4000);
    DYN_PROC_dummy VARCHAR2 (20) := 'SELECT * FROM ';
    BEGIN
    DYN_PROC := 'SELECT * FROM ';
    IF INSTR(CHECKS,'IMPORTED DATA UNDEFINED') = 1 THEN
    DYN_PROC := DYN_PROC || 'VW_IMPDATAUNDEFINED_1  union all' || DYN_PROC_dummy;
    end if;
    IF INSTR(CHECKS,'MULTIPLE DATA DEFINITION') = 1 THEN
    DYN_PROC := DYN_PROC || 'VW_MULDEFDATA_1 union all ' || DYN_PROC_dummy;
    END IF;
    IF INSTR(CHECKS,'UNUSED OUTPUT DATA') = 1 THEN 
    DYN_PROC := DYN_PROC || 'VW_UNUSEDOUTPUTDATA_1 union all ' || DYN_PROC_dummy;
    end IF;
    IF INSTR(CHECKS,'INPUT OUTPUT COHERENCY') = 1 THEN
    DYN_PROC := DYN_PROC || 'VW_IO_COHERENCY union all ' || DYN_PROC_dummy;
    end if;
    IF INSTR(CHECKS,'IMPORTED ACTION UNDEFINED') = 1 THEN
    DYN_PROC := DYN_PROC || 'VW_IMPACTIONUNDEFINED_1 union all ' || DYN_PROC_dummy;
    END IF;
    IF INSTR(CHECKS,'MULTIPLE ACTION DEFINITION') = 1 THEN
    DYN_PROC := DYN_PROC || 'VW_MULDEFACTIONS_1 union all ' || DYN_PROC_dummy;
    END IF;
    DYN_PROC := SUBSTR(DYN_PROC, 1, LENGTH('select * from VW_IMPDATAUNDEFINED_1  union all select * from')-23);
    OPEN IMP_DATA_UND FOR DYN_PROC;
    CHECKS := '100';
    END;
    end PKG_CHECKS;

    Execute Statement

    Code:
    set serveroutput on;
    DECLARE     
    C1 SYS_REFCURSOR;     
    dataobjectname varchar2(255);
        description varchar2(255); 
        SPECNAME VARCHAR2(255);
        aggregate varchar2(20) ; 
        classification varchar2(10);
        ERROR_ID VARCHAR2(275);
        CHK VARCHAR2(100) := 'IMPORTED DATA UNDEFINED';
    BEGIN     
    PKG_CHECKS.SP_MODULE_CHECKS(CHK, C1);      
    LOOP       
    FETCH C1 INTO dataobjectname, description, SPECNAME,aggregate,classification,ERROR_ID;       
    EXIT WHEN C1%NOTFOUND;         
    dbms_output.put_line(dataobjectname|| description|| SPECNAME||aggregate||classification||ERROR_ID);
    END LOOP;
    CLOSE C1; 
    dbms_output.put_line(CHK);
    END;

    I just need to get the count.

    Thanks for your support.

    Regards,
    ShivaG

  9. #9
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    @ShivaG
    I see a greater problem here than getting the count. You need to pay more attention when coding. You must be more critical about your code. Consider what inputs will be provided and consider how your program will react when it receives those inputs. I can see from the way you make changes, that you have no plan before making the changes and have not considered the possible inputs.

    For example, given the procedure you provided in your previous post, I can see that your procedure will probably only ever execute without error when you provide 'IMPORTED DATA UNDEFINED' as input. You haven't considered how your procedure would work if you gave it 'MULTIPLE DATA DEFINITION' instead. Try it:
    Code:
    declare
       l_rc PKG_CHECKS.CHK_CURSOR;
    begin
       PKG_CHECKS.SP_MODULE_CHECKS('MULTIPLE DATA DEFINITION', l_rc);
    end;
    /
    Does it complete without error. I wouldn't think so. Let us know either way...

    There are a number of ways you could get the count:
    a) Loop through the cursor and count the rows returned. This would probably be one of the slowest and most resource intensive methods. You would have to close the cursor and re-open the cursor to return to the calling procedure.
    Cursor attributes would help with this: Example 6-8 Using SQL%ROWCOUNT
    b) You could change the cursor declaration to include a count column. The count could then be calculated in the cursor's SQL using analytic functions. Still a bit of a messy solution. Oracle Database Documentation: COUNT, in particular see the analytic example.
    c) You could run a count SQL statement first, using the cursor SQL as the base statement, and then open the cursor separately. Probably the neatest solution.
    In all scenarios, there is a chance your count could be wrong (depending on if the data you are reading has changed).

  10. #10
    Join Date
    Feb 2012
    Posts
    10
    Hi dayneo,

    Thanks for your time.

    I have initially want to test only 1 condition i.e. for "IMPORTED DATA UNDEFINED"

    Now i have changed the code and it is working fine for all possibilities.
    Code:
    CREATE OR REPLACE
    PACKAGE BODY PKG_CHECKS AS 
    PROCEDURE SP_MODULE_CHECKS(CHECKS IN VARCHAR2, IMP_DATA_UND OUT CHK_CURSOR)
    IS
    DYN_PROC VARCHAR2 (4000);
    DYN_PROC_dummy VARCHAR2 (20) := 'SELECT * FROM ';
    BEGIN
    DYN_PROC := 'SELECT * FROM ';
    IF INSTR(CHECKS,'IMPORTED DATA UNDEFINED') <> 0 THEN
    DYN_PROC := DYN_PROC || 'VW_IMPDATAUNDEFINED_1  union all ' || DYN_PROC_dummy;
    end if;
    IF INSTR(CHECKS,'MULTIPLE DATA DEFINITION') <> 0 THEN
    DYN_PROC := DYN_PROC || 'VW_MULDEFDATA_1 union all ' || DYN_PROC_dummy;
    END IF;
    IF INSTR(CHECKS,'UNUSED OUTPUT DATA') <> 0 THEN 
    DYN_PROC := DYN_PROC || 'VW_UNUSEDOUTPUTDATA_1 union all ' || DYN_PROC_dummy;
    END IF;
    IF INSTR(CHECKS,'INPUT/OUTPUT COHERENCY') <> 0 THEN
    DYN_PROC := DYN_PROC || 'VW_IO_COHERENCY union all ' || DYN_PROC_dummy;
    end if;
    IF INSTR(CHECKS,'IMPORTED ACTION UNDEFINED') <> 0 THEN
    DYN_PROC := DYN_PROC || 'VW_IMPACTIONUNDEFINED_1 union all ' || DYN_PROC_dummy;
    END IF;
    IF INSTR(CHECKS,'MULTIPLE ACTION DEFINITION') <> 0 THEN
    DYN_PROC := DYN_PROC || 'VW_MULDEFACTIONS_1 union all ' || DYN_PROC_dummy;
    END IF;
    DYN_PROC := SUBSTR(DYN_PROC, 1, LENGTH(DYN_PROC)-24);
    OPEN IMP_DATA_UND FOR DYN_PROC;
    END;
    end PKG_CHECKS;
    I think getting count is too risky in performance, so i writen in my application.
    Now the performance is awesome and reduced 60% of process time comapared as earlier.

    c# code
    Code:
    int nImpDataUnd = 0;
    nImpDataUnd = tester.Tables["Errors"].Select("Description = 'Undefined data declared in input table'").Length;
    It returns the count with some condition, similar to LINQ.

    Regards,
    ShivaG

  11. #11
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Wink

    One more word of advise:
    You are using dynamic SQL in your stored procedure. This is not a good practice in general. You know what cases you are testing for in your procedure (you have 6 test cases) and this does not appear to change. Based on that, it would be more efficient and more logical to:
    Code:
    .....
    IF INSTR(CHECKS,'IMPORTED DATA UNDEFINED') <> 0 THEN
      OPEN IMP_DATA_UND FOR 
        SELECT * FROM VW_IMPDATAUNDEFINED_1;
    end if;
    .....
    Another thing to consider is: what happens if I call your check procedure with a check case that you don't have defined in your procedure? eg:
    Code:
    declare
       l_rc PKG_CHECKS.CHK_CURSOR;
    begin
       PKG_CHECKS.SP_MODULE_CHECKS('DAYNEO got the input wrong', l_rc);
    end;
    /
    I predict your app to crash in this case. Not that it shouldn't, just that it will give a nasty error about malformed SQL rather than a logical error stating that the input value was invalid. Consider using RAISE_APPLICATION_ERROR for this.

Posting Permissions

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