CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
David Ramírez
David Ramírez

Reputation: 71

How to insert data into a temporary table from a cursor returned by a stored procedure

I'm using Oracle 11g and i have an stored procedure that select some data and put it into a cursor to be returned. I need to insert that data from that cursor into a temporary table, but i don't know how. Hope you can help me.

This is the way i tried (exactly how it works in SQL Server):

INSERT INTO tt_IDIOMALISTAS(Idtt, CampoOriginal, CampoModificado, Estado) VALUES sp_SearchSelect(v_CY,v_Idioma,v_SubModule);

This is the cursor inside the SP:

open v_refcur for SELECT
   SPD.Id_CombosPorDefecto, SPD.vrc_NombreCombos1, S.vrc_Idioma1, S.vrc_Idioma2, S.vrc_Idioma3
   FROM tbl_Adm_CombosPorDefecto SPD
   LEFT JOIN  tbl_Adm_Combos S
   ON SPD.Id_CombosPorDefecto = S.int_IdComboPorDefecto
   WHERE SPD.int_IdSubModulo = v_SubModule
   AND SPD.FK_CY = v_CY;

Upvotes: 1

Views: 934

Answers (1)

Vinod
Vinod

Reputation: 36

SQL ( Insert Script ) is better than PL/SQL & SQL ( Cursor and Insert Script) in terms of performance.

INSERT INTO tt_idiomalistas
    SELECT spd.id_combospordefecto
           , spd.vrc_nombrecombos1
           , s.vrc_idioma1
           , s.vrc_idioma2
           , s.vrc_idioma3
      FROM tbl_adm_combospordefecto spd
      LEFT JOIN tbl_adm_combos s ON spd.id_combospordefecto = s.int_idcombopordefecto
     WHERE spd.int_idsubmodulo = v_submodule
       AND spd.fk_cy = v_cy

Upvotes: 1

Related Questions