Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    5

    Post Unanswered: Insert data to a temp table inside a stored function

    Hi,

    I want to store the results of a select query in a temporary table for later use. I tried to use a global temporary table but you can't do this from inside a stored function/procedure.
    I also tried the following (use a type of table):

    CREATE OR REPLACE FUNCTION test_func
    RETURN VARCHAR2
    IS
    DN_NUM VARCHAR2(63);
    -- Declare the PL/SQL table
    type dn_num_array is table of VARCHAR2(63)
    index by binary_integer;
    dn_num_arr dn_num_array;
    BEGIN
    INSERT INTO dn_num_arr(
    SELECT DN.DN_NUM AS DN_NUM
    FROM ...
    WHERE ...;
    SELECT MAX(DN_NUM)
    INTO DN_NUM
    FROM dn_num_arr;
    RETURN DN_NUM;
    END;
    /


    It complains that the dn_num_arr is not declared.

    Any thoughts?

    Thanks in advance.

    John.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Global temporary table example:
    Code:
    SQL> create global temporary table gtt_emp as select ename from emp where 1 = 2;
    
    Table created.
    
    SQL> create or replace procedure prc_emp_gtt as
      2  begin
      3    for cur_r in (select ename from emp) loop
      4      insert into gtt_emp values (cur_r.ename);
      5    end loop;
      6  end;
      7  /
    
    Procedure created.
    
    SQL> execute prc_emp_gtt
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from gtt_emp;
    
    ENAME
    ----------
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    ADAMS
    JAMES
    FORD
    MILLER
    DRAKE
    
    15 rows selected.
    
    SQL>
    Why do you think this won't work? Did you try something like this?

    And, as of your question, you can't INSERT data into an array just like it was an "ordinary" table ... for such purposes you might use something like this:
    Code:
    create or replace function test_func return varchar2 is
      dn_num varchar2(63);
      type dn_num_array is table of varchar2(63) index by binary_integer;
      dn_num_arr dn_num_array;
      cursor cur_emp is select ename from emp;
      i number := 1;
    begin
      for cur_r in cur_emp loop
        dn_num_arr(i) := cur_r.ename;
        i := i + 1;
      end loop;
    
      dn_num := dn_num_arr.last;
    
      return dn_num;
    end;
    /
    
    Function created.
    
    SQL> select test_func from dual;
    
    TEST_FUNC
    -----------
    
    15
    Last edited by Littlefoot; 02-14-05 at 07:43.

  3. #3
    Join Date
    Feb 2005
    Posts
    5
    Thanks for your help. It did work.
    I 'm in the process of mastering these things!

Posting Permissions

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