Results 1 to 8 of 8
  1. #1
    Join Date
    May 2013
    Posts
    4

    Unanswered: 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
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Why would you want to convert it to a procedure? PL/SQL is (generally speaking) slower than SQL that does the same job. What benefit do you expect?

    Therefore, run that INSERT statement. Once it is done, update DMN_COD to "yes" in DB_2.

  3. #3
    Join Date
    May 2013
    Posts
    4

    create procedure

    hi many thanks for the answer, i have to make it to a procedure because i am making a dbms_schedule.
    like this:
    Dbms_schedule.create_schedule
    (schedule_name => 'CADA_2',
    Repeat_interval => 'FREQ=MINUTELY; INTERVAL=60',
    Comments => 'Execute this task every 60 minutes.');
    Dbms_scheduler.create_program
    (program_name => 'EXAMPLE_PROGRAM',
    Program_type =>'STORED_PROCEDURE',
    Program_action => 'name of the procedure'
    Enabled => TRUE,
    Comments => 'This creates a program.');
    Dbms_scheduler.create_job
    (Job_name => 'Example_job',
    Program_name => 'Example_program',
    Schedule_name => 'cada_2',
    Enabled => TRUE,
    Comments => 'This job will run the example_program program according to the cada_2 schedule.');
    And i have made a dblink to the query
    this is why i need the procedure.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    create or replace procedure procedure_name_here is
    begin
      insert into COMPUSOFT.PESAJE@DB_2 ...
      your_code_goes_here;
    
      update ... set
        dmn_code = 'yes'
        where ...
    end;

  5. #5
    Join Date
    May 2013
    Posts
    4
    i have try this with only the query to make sure first that it works and it doesnt its like this:
    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'
    update dmn_sinc set DNM_SINC='SI' where COMPUSOFT.MANIFIESTO_DET@"DB_2"; dont work and:



    update dmn_sinc FROM COMPUSOFT.MANIFIESTO_DET@"DB_2" set DNM_SINC='SI'; dont work either

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Since we don't have your tables or data, we can't run, test or improve posted SQL

    my car doesn't work.
    tell me how to make my car go.

    while "doesn't work" is 100% correct, it is also 100% devoid of any actionable detail.

    Is COPY & PASTE broken for you?
    It would be most helpful is actually post the complete & entire Oracle error code & message for each SQL statement.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    May 2013
    Posts
    4
    Ok i understand why you answer me like this i am trying to solved it and i miss to give you the why it doesnt work here it says to me this:

    ORA-00933: SQL command not properly ended
    when i run this:
    SELECT dmn_sinc FROM COMPUSOFT.MANIFIESTO_DET@"DB_2"
    it shows me the column but i need to update this column to "si" every time i run the query:
    Insert into COMPUSOFT.PESAJE@DB_2
    .............................
    ....................
    ............

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Wrong syntax leads to errors. Therefore, perhaps you should first get familiar with commands you'd like to use, write them correctly, and - finally - execute them.

    You can't have INSERT and UPDATE in a single statement (like you posted):
    Code:
    insert ...
    where PSJ_OPERACION='IMP'
    update dmn_sinc set ...
    Code:
    update dmn_sinc FROM ... set ...
    is rubbish.

    Have a look at MERGE statement; you might find it interesting.

    Furthermore, a database trigger might be what you are looking for. You'd INSERT, database trigger would UPDATE.

    Finally, in order to improve readability, please, format your code and enclose it into [code] tags (have a look at Forum's BB code page for more information).

Tags for this Thread

Posting Permissions

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