Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    21

    Unanswered: Is there a way to dynamically set the 'into' variables?

    So basically I was using refcursors to pull up a substantial amount of data that did not pan out well performance wise when large sets or numerous joins were invoked.

    I am trying to create a stored procedure that basically does a bulk collect and return an array of varchar2 arrays.

    here is some a simplistic model of the procedure that is in my package. Each ITYPE has its own post processing.

    please note I am using simpler select statements to keep the code more readable.


    Part of the package
    Code:
    Type StringArray is table of varchar2(255) index by binary_integer;
    Type varArray is table of StringArray index by binary_integer;
    Procedure proc_test(
          ITYPE IN VARCHAR,
          RESULT OUT varArray
          )
          
      as
      oSQL CLOB;
      Begin
        
    	if(ITYPE = 'TARGET')then	
    		oSQL := '
    		select  *                --8 columns
    		from my_target_vw;
    	elsif(ITYPE = 'PERSON') then
    		oSQL := 'select * from my_person_vw';  --4 columns		
        else
    		oSQL := 'select * from my_result_vw'; -- 12 columns
    	end if;
        execute immediate oSQL bulk collect into 
          --some way to dynamic variable setup for varArray;
    end proc_test;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >--some way to dynamic variable setup for varArray;
    or another IF, THEN, ELSE construct
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by fff398 View Post
    I am trying to create a stored procedure that basically does a bulk collect and return an array of varchar2 arrays.
    But now, you are returning array of records. To match the given data type, you would have to convert rows to collections, something like this:
    Code:
    SELECT StringArray( <column1>, <some function to convert column2 to VARCHAR2>, ... )
    FROM my_person_view;
    (although I am not sure if it is allowed with INDEX BY tables; you would have to investigate what would be the use of it anyway).
    Quote Originally Posted by fff398 View Post
    here is some a simplistic model of the procedure that is in my package. Each ITYPE has its own post processing.
    What do you mean with "processing"? The SELECT statement itself or other actions made on this? Because, data stored in this "generic" data model are limited for treatment of only "generic" actions (unless you want to use another IF THEN ELSE blocks to differentiate this).

    Also, if performance suffered when using REF CURSORS, I doubt it will be better when using this additional row/set conversion. I wonder, why did you did not rather tune those "large sets or numerous joins" using standard methods. They are described in Performance Tuning Guide, available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/
    Anyway, if you plan to go this way, good luck with this approach.

Posting Permissions

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