使用游标PL/SQL在表中插入行 [英] insert row in a table using a cursor PL/SQL

问题描述

我的包装中有此程序.打包的问题是此过程不会在表中插入行

I have this procedure in a package. the problem of package is this procedure that not insert row in the table

步骤bcy_alimenta_supporto_G2 IS

PROCEDURE bcy_alimenta_supporto_G2 IS

CURSOR cur_request IS (
      SELECT p.concurrent_program_name,
         u.user_name,
         u.user_id,
         r.conc_login_id,
         r.request_id,
         r.request_date,
         r.phase_code,
         r.status_code,
         r.actual_completion_date,
         r.argument1,
         r.argument2,
         r.argument3,
         r.argument4,
         r.argument5,
         r.argument6,
         r.argument7,
         r.argument8,
         r.argument9,
         r.argument10
    FROM fnd_concurrent_requests r
         JOIN fnd_concurrent_programs p
            ON p.concurrent_program_id = r.concurrent_program_id
         JOIN fnd_user u ON u.user_id = r.requested_by
         JOIN bcy_concurrent_garante_2 bcg
            ON p.concurrent_program_name = bcg.concurrent_program_name
   WHERE     1 = 1
         AND r.request_date >= trunc(SYSDATE) - 2
         AND r.request_date >= g_start_date
         AND r.phase_code = 'C'         -- Prendo solo le richieste completate
         AND r.request_id NOT IN (SELECT request_id FROM XXBCYIN.BCY_SUPPORTO_GARANTE_2));-- non importo le transazioni completate e già importate il giorno precedente
         --ORDER BY r.request_id ASC) ;   ---viene errore

    v_status_code varchar2 (4);

gret_code_exception exception;  

BEGIN


     FOR c in cur_request loop



       insert into XXBCYIN.BCY_SUPPORTO_GARANTE_2 
        values (
        BCY_SUPPORTO_GARANTE_2_S.NEXTVAL,
       'QUERY_MASSIVA',
       (g_applicazione  || c.USER_ID),
        'c.USERNAME',
        null,
        c.request_date,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        g_codice_istituto, --defined before in an other procedure of package
        null,
        null,
        null,
        null,
        null,
        'X',
        null,
        g_tipo_client,  --defined before
        null,
        null,
        null,
        null,
        null,
        v_role_desc,    --defined before
        c.conc_login_id,  --defined before
        null,
        null,
        null,
        null,
        null,
        null,
        null,
        g_applicazione,  --defined before
        null,
        null,
        null,
        null,
        v_status_code,  --defined before
        v_return,         --defined before
        'X' ,       
        null, 
        null,
        c.concurrent_program_name,
        c.request_id,
        c.phase_code ,
        c.status_code,
        sysdate);

   end loop;

      commit;

使用此插入没有错误，但是表仍然为空！光标的选择是正确的.插入具有正确的字段数. 有什么想法吗?

With this insert there are no errors but the table remains empty!?!. The select of the cursor is correct..the insert has the correct number of fields. Any ideas?

推荐答案

我从未听说过使用VALUES列表中的游标字段使用Oracle的问题，但是，我认为我自己还没有做过.试试这个:

I've never heard of a problem with Oracle using cursor fields in a VALUES list, but then, I don't think I've ever done it myself. Try this:

dbms_output.put_line( 'Inserting ' || c.username );
insert into XXBCYIN.BCY_SUPPORTO_GARANTE_2 
select
    BCY_SUPPORTO_GARANTE_2_S.NEXTVAL,
    'QUERY_MASSIVA',
    (g_applicazione  || c.USER_ID),
    'c.USERNAME',
    null,
    c.request_date,
    null,    null,    null,    null,    null,    null,    null,    null,
    g_codice_istituto, --defined before in an other procedure of package
    null,    null,    null,    null,    null,    'X',    null,
    g_tipo_client,  --defined before
    null,    null,    null,    null,    null,
    v_role_desc,    --defined before
    c.conc_login_id,  --defined before
    null,    null,    null,    null,    null,    null,    null,
    g_applicazione,  --defined before
    null,    null,    null,    null,
    v_status_code,  --defined before
    v_return,         --defined before
    'X' ,    null,     null,
    c.concurrent_program_name,
    c.request_id,
    c.phase_code ,
    c.status_code,
    sysdate
from dual;

当然，您可以完全跳过光标来重写插入":

Of course, you can rewrite the Insert skipping the cursor altogether:

insert into XXBCYIN.BCY_SUPPORTO_GARANTE_2
select BCY_SUPPORTO_GARANTE_2_S.NEXTVAL,
    'QUERY_MASSIVA',
    (g_applicazione  || c.USER_ID),
    'c.USERNAME',
    null,
    c.request_date,
    etc...
from whatever_table c
where everthing_else_as_in_cursor;

这篇关于使用游标PL/SQL在表中插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！