Home » SQL & PL/SQL » SQL & PL/SQL » Stored procedure results into a temp table
Stored procedure results into a temp table [message #379935] Thu, 08 January 2009 09:34 Go to next message
decarlomw
Messages: 12
Registered: January 2009
Junior Member
Hi Guys,

I have a quick question, what's the best way to get procedure results insert into a global temporary table. I've google'd but can't seem to find anything on it.
PROCEDURE send_string
IS
   x                       NUMBER;
   l_head_string           VARCHAR2 (100)  := '        VALOTL  ';
   l_mss_return            VARCHAR2 (1000);
   l_valotl_string         VARCHAR2 (5000);
   l_performing_area_val   VARCHAR2 (50);
   l_fixed_dist            VARCHAR2 (1);
   l_employee_number       VARCHAR2 (10);
   valotl                  VARCHAR2 (5000);
   val                     VARCHAR2 (5000);
--
BEGIN
--
   write_log ('---> Start mlgw_otl_validation_pkg.send_string <---');
--
   x := out_rec.FIRST;

   LOOP
      EXIT WHEN x IS NULL;

      --
      --
      BEGIN
         SELECT   SUBSTR (hou.NAME, 2, 6),
                  (SELECT DECODE (NVL (SUM (pca.proportion), 0), 0, 'N', 'Y')
                     FROM pay_cost_allocations_f pca
                    WHERE pca.assignment_id = paf.assignment_id
                      AND l_effective_date BETWEEN pca.effective_start_date
                                               AND pca.effective_end_date)
                                                                   proportion,
                  LPAD (ppf.employee_number, 5, '0')
             INTO l_performing_area_val,
                  l_fixed_dist,
                  l_employee_number
             FROM per_all_assignments_f paf,
                  hr_all_organization_units hou,
                  per_all_people_f ppf
            WHERE paf.organization_id = hou.organization_id
              AND l_effective_date BETWEEN paf.effective_start_date
                                       AND paf.effective_end_date
              AND l_effective_date BETWEEN ppf.effective_start_date
                                       AND ppf.effective_end_date
              AND ppf.person_id = paf.person_id
              AND paf.assignment_type = 'E'
              AND paf.primary_flag = 'Y'
              AND paf.person_id = out_rec (x).person_id
         GROUP BY SUBSTR (hou.NAME, 2, 6),
                  ppf.employee_number,
                  paf.assignment_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_performing_area_val := '000000';
            l_fixed_dist := 'N';
            l_employee_number := '00000';
      END;

      --
      out_rec (x).performing_area := LPAD (l_performing_area_val, 6, '0');
      --
      out_rec (x).work_ticket :=
                     UPPER (NVL (out_rec (x).work_ticket, LPAD (' ', 8, ' ')));
      out_rec (x).repair_order_num :=
                          NVL (out_rec (x).repair_order_num, LPAD (0, 7, '0'));
      out_rec (x).crew_number :=
                               NVL (out_rec (x).crew_number, LPAD (0, 4, '0'));
      out_rec (x).equipment_number :=
                          NVL (out_rec (x).equipment_number, LPAD (0, 4, '0'));
      out_rec (x).charge_code :=
                             NVL (out_rec (x).charge_code, RPAD ('0', 4, '0'));
      out_rec (x).requesting_area :=
                           NVL (out_rec (x).requesting_area, LPAD (0, 6, '0'));
      out_rec (x).wr_nbr :=
                          UPPER (NVL (out_rec (x).wr_nbr, RPAD (' ', 8, ' ')));
      out_rec (x).performing_area :=
                           NVL (out_rec (x).performing_area, LPAD (0, 6, '0'));
      out_rec (x).start_miles :=
                               NVL (out_rec (x).start_miles, LPAD (0, 6, '0'));
      out_rec (x).ending_miles :=
                              NVL (out_rec (x).ending_miles, LPAD (0, 6, '0'));
      out_rec (x).legacy_earn_code :=
                                     NVL (out_rec (x).legacy_earn_code, '000');
      out_rec (x).woc_job_class :=
                           NVL (out_rec (x).woc_job_class, LPAD (' ', 6, ' '));
      --
      l_valotl_string :=
         (l_head_string
          || out_rec (x).work_ticket
          || out_rec (x).repair_order_num
          || out_rec (x).crew_number
          || out_rec (x).equipment_number
          || out_rec (x).charge_code
          || out_rec (x).requesting_area
          || out_rec (x).wr_nbr
          || out_rec (x).performing_area
          || out_rec (x).start_miles
          || out_rec (x).ending_miles
          || out_rec (x).legacy_earn_code
          || l_fixed_dist
          || l_employee_number
          || out_rec (x).woc_job_class
         );
      --
      -- Call MSS Function to validate timecard data
      valotl := valotl || l_valotl_string;
      --
      x := out_rec.NEXT(x);
      write_log ( valotl);
      insert into transaction_tab
(valotl_string) values (l_valotl_string);
   END LOOP;



   err_tbl.DELETE;
   
   --if out_rec.count = 3 then  
   --write_log (   'EE#:'|| l_employee_number|| '|time_building_block_id:'||x|| ' |To MSS:'||  l_valotl_string||l_valotl_string||l_valotl_string||l_valotl_string);
  --valotl := ' ';
   --elsif out_rec.count = 2 then 
   --write_log (   'EE#:'|| l_employee_number|| '|time_building_block_id:'||x|| ' |To MSS:'||l_valotl_string||l_valotl_string);
   --valotl := ' ';
   --elsif out_rec.count = 1 then 
   --write_log (   'EE#:'|| l_employee_number|| '|time_building_block_id:'||x|| ' |To MSS:'||l_valotl_string);
   --valotl := ' ';
   --end if;
   write_log ('---> End mlgw_otl_validation_pkg.send_string <---');
END send_string;

As you can see the valotl holds the data how can that be put into a temp table?

[Updated on: Thu, 08 January 2009 09:44]

Report message to a moderator

Re: Stored procedure results into a temp table [message #379937 is a reply to message #379935] Thu, 08 January 2009 09:45 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
You have not read or followed Posting Guideline as stated in URL above.

> I've google'd but can't seem to find anything on it.
I am sorry to see both GOOGLE & SEARCH of this forum are broken for you.

What is wrong with simply using INSERT against global temporary table(s)?

Re: Stored procedure results into a temp table [message #379938 is a reply to message #379937] Thu, 08 January 2009 09:49 Go to previous messageGo to next message
decarlomw
Messages: 12
Registered: January 2009
Junior Member
The data is stored in a variable. how can you insert variable data into a table? I tried

 INSERT INTO transaction_tab
            (valotl_string
            )
     VALUES (l_valotl_string
            );

but it did not work.
Re: Stored procedure results into a temp table [message #379941 is a reply to message #379935] Thu, 08 January 2009 09:56 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
You have not read or followed Posting Guideline as stated in URL above.

>but it did not work.
Above is NOT an Oracle error message.
My motorcycle did not work.
Please tell me how to make it work.
Re: Stored procedure results into a temp table [message #379942 is a reply to message #379938] Thu, 08 January 2009 09:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Define "it did not work".
Did you query from a different session to see if the data was inserted?
If so, you should be aware that temp-tables are session-specific. It is impossible to view data from one session in another session.

If you queried from the same session and did not see any results, it might be caused by the fact you defined your temp-table as 'on commit delete rows', and you committed.
Re: Stored procedure results into a temp table [message #379943 is a reply to message #379942] Thu, 08 January 2009 10:06 Go to previous messageGo to next message
decarlomw
Messages: 12
Registered: January 2009
Junior Member
I did not get an error "did not work" means it did not insert the data into the table.

CREATE GLOBAL TEMPORARY TABLE transaction_tab
(valotl_string VARCHAR2(4000)) ON COMMIT PRESERVE ROWS;



This procedure is inside a package when I create the package I go to Oracle e-business suite and enter timecard data when I submit this timecard it runs this package which holds this procedure.
Re: Stored procedure results into a temp table [message #379944 is a reply to message #379941] Thu, 08 January 2009 10:07 Go to previous messageGo to next message
decarlomw
Messages: 12
Registered: January 2009
Junior Member
BlackSwan,

I would rather for you not to comment if you are going to continue to make sarcastic remarks.
Re: Stored procedure results into a temp table [message #379953 is a reply to message #379935] Thu, 08 January 2009 10:36 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the insert statement ran and you didn't get an error then it's safe to assume it did insert the data.

Possibilities:

1) It did error out but you've got an exception handler somewhere that masked the error.
2) The insert statement never got executed in the first place.
3) You're querying the table from a different oracle session to the one that inserted it.

Based on the information you've supplied I've no idea which it is. You're going to have to do some serious debugging.
Why not use your write_log procedure to see if the table is populated at the end of the procedure.
Previous Topic: Records between current date and 2 weeks from the current date?
Next Topic: trouble with non existent constraint
Goto Forum:
  


Current Time: Fri Dec 09 07:46:38 CST 2016

Total time taken to generate the page: 0.06331 seconds