#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0

    Create procedure


    how can i create this query on a procedure:

    Insert into COMPUSOFT.PESAJE@DB_2
    (PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA,
    PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD,
    USR_COD, PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO)
    select /*+ FULL(Tbl1) */
    PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA
    , PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD
    , PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO
    from COMPUSOFT.PESAJE Tbl1
    where PSJ_OPERACION='IMP'

    minus
    select /*+ FULL(Tbl2) */
    PSJ_GESTION, PSJ_COD, PSJ_PLACA, PSJ_PESO, PSJ_FECHA
    , PSJ_ESTADO, BLZ_COD, MNF_COD, DMN_COD, USR_COD
    , PSJ_OPERACION, TIC_COD, PSJ_TARA, PSJ_NETO
    from COMPUSOFT.PESAJE@"DB_2" Tbl2
    where PSJ_OPERACION='IMP';

    plus i would like to insert also that when it runs the query also change a value in table pesaje column dmn_cod to "yes" default "no" in db_2

    any help please???
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Wink


    Originally Posted by miguel_q@hotmai
    how can i create this query on a procedure:

    Insert into COMPUSOFT.PESAJE@DB_2
    . . . E t c . . .
    any help please???
    If the tables have a primary key, the preferred method would be somewhat like this:
    Code:
    INSERT INTO /*+ APPEND */ compusoft.pesaje@db_2 
        ( psj_gestion, psj_cod, psj_placa, psj_peso
        , psj_fecha, psj_estado, blz_cod, mnf_cod
        , dmn_cod, usr_cod, psj_operacion, tic_cod
        , psj_tara, psj_neto
        )
      SELECT psj_gestion, psj_cod, psj_placa, psj_peso
           , psj_fecha, psj_estado, blz_cod, mnf_cod
           , dmn_cod, usr_cod, psj_operacion, tic_cod
           , psj_tara, psj_neto
        FROM compusoft.pesaje tbl1
       WHERE psj_operacion = 'IMP'
         AND NOT EXISTS
               (SELECT '?'
                  FROM compusoft.pesaje@"DB_2" tbl2
                 WHERE tbl2.{primary key} = tbl1.{primary key});

    PS: I did not understand the "pesaje column dmn_cod" change you want to do.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0

    Create Procedure


    when i do this procedure i would like that in the table pesaje where there is a column dmn_cod that the default value is 'no' and i would like to change that value every time i do an insertion.
    i am doing a dbms schedule and i have make that every hour it will do this query and to do this i neede help to make it to a procedure so i can call it in the job(dbms_schedule), i have made a db_link also because i insert this values in a remote dbase.
    if you like i show you all the process i did.
    and in your question yes the primary keys are: PSJ_GESTION, PSJ_COD
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Cool


    Originally Posted by miguel_q@hotmai
    when i do this procedure i would like that in the table pesaje where there is a column dmn_cod that the default value is 'no' and i would like to change that value every time i do an insertion.
    Just replace the value in the select statement with 'YES'


    i am doing a dbms schedule and i have make that every hour it will do this query and to do this i neede help to make it to a procedure so i can call it in the job(dbms_schedule), i have made a db_link also because i insert this values in a remote dbase.
    if you like i show you all the process i did.
    and in your question yes the primary keys are: PSJ_GESTION, PSJ_COD
    Like this?
    Code:
    CREATE OR REPLACE PROCEDURE upd_db2_pesaje
    AS
    BEGIN
      INSERT INTO /*+ APPEND */
                 compusoft.pesaje@db_2 
             ( psj_gestion, psj_cod, psj_placa, psj_peso
             , psj_fecha, psj_estado, blz_cod, mnf_cod
             , dmn_cod, usr_cod, psj_operacion, tic_cod
             , psj_tara, psj_neto, dmn_cod
             )
        SELECT psj_gestion, psj_cod, psj_placa, psj_peso
             , psj_fecha, psj_estado, blz_cod, mnf_cod
             , 'YES' dmn_cod, usr_cod, psj_operacion, tic_cod
             , psj_tara, psj_neto
          FROM compusoft.pesaje tbl1
         WHERE psj_operacion = 'IMP'
           AND NOT EXISTS
                 (SELECT '?'
                    FROM compusoft.pesaje@db_2 tbl2
                   WHERE tbl2.psj_gestion = tbl1.psj_gestion
                     AND tbl2.psj_cod = tbl1.psj_cod);
    END;
    /
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    5
    Rep Power
    0

    create procedure


    many thanks i would like to update this also when it compares how should i put it into the query:

    this what i want to do:
    update dmn_sinc FROM COMPUSOFT.MANIFIESTO_DET@"DB_2" set DNM_SINC='SI';
  10. #6
  11. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388

    Cool


    Originally Posted by miguel_q@hotmai
    many thanks i would like to update this also when it compares how should i put it into the query:

    this what i want to do:
    update dmn_sinc FROM COMPUSOFT.MANIFIESTO_DET@"DB_2" set DNM_SINC='SI';
    Not clear, but I will speculate it's the following:
    Code:
    CREATE OR REPLACE PROCEDURE upd_db2_pesaje
    AS
      --
      -- Get all new rows
      --
      CURSOR p_csr
      IS
        SELECT psj_gestion, psj_cod, psj_placa, psj_peso
             , psj_fecha, psj_estado, blz_cod, mnf_cod
             , 'SI' dmn_cod, usr_cod, psj_operacion, tic_cod
             , psj_tara, psj_neto
          FROM compusoft.pesaje tbl1
         WHERE psj_operacion = 'IMP'
           AND NOT EXISTS
                 (SELECT '?'
                    FROM compusoft.pesaje@db_2 tbl2
                   WHERE tbl2.psj_gestion = tbl1.psj_gestion
                     AND tbl2.psj_cod = tbl1.psj_cod);
    
      TYPE p_csr_tab IS TABLE OF p_csr%ROWTYPE
        INDEX BY PLS_INTEGER;
    
      p_csr_arr   p_csr_tab;
    
    BEGIN
    
      OPEN p_csr;
      --
      -- Get new rows into array
      --
      FETCH p_csr
        BULK COLLECT INTO p_csr_arr;
      --
      -- Insert all rows into compusoft.pesaje@db_2
      --
      FORALL i IN 1 .. p_csr_arr.COUNT
        INSERT INTO /*+ APPEND */
                   compusoft.pesaje@db_2 
               (
                psj_gestion
              , psj_cod
              , psj_placa
              , psj_peso
              , psj_fecha
              , psj_estado
              , blz_cod
              , mnf_cod
              , dmn_cod
              , usr_cod
              , psj_operacion
              , tic_cod
              , psj_tara
              , psj_neto
              , dmn_cod
               )
        VALUES (
                p_csr_arr (i).psj_gestion
              , p_csr_arr (i).psj_cod
              , p_csr_arr (i).psj_placa
              , p_csr_arr (i).psj_peso
              , p_csr_arr (i).psj_fecha
              , p_csr_arr (i).psj_estado
              , p_csr_arr (i).blz_cod
              , p_csr_arr (i).mnf_cod
              , p_csr_arr (i).dmn_cod
              , p_csr_arr (i).usr_cod
              , p_csr_arr (i).psj_operacion
              , p_csr_arr (i).tic_cod
              , p_csr_arr (i).psj_tara
              , p_csr_arr (i).psj_neto
               );
      --
      -- Update compusoft.manifiesto_det@"DB_2"(dnm_sic)
      -- from all rows inserted
      --
      FORALL i IN 1 .. p_csr_arr.COUNT
        UPDATE compusoft.manifiesto_det@"DB_2"
           SET dnm_sinc    = 'SI'
         WHERE psj_gestion = p_csr_arr (i).psj_gestion
           AND psj_cod     = p_csr_arr (i).psj_cod;
    
      COMMIT;
      
    END;
    /
    Correct?

IMN logo majestic logo threadwatch logo seochat tools logo