Re: Call procedure from a Job, Test a Job in pgAdmin?

The job has to pick up a record with the status ‘Waiting’, and insert it in another table (test table by now).

Later, I will need to send data to another database, using db link.

requests_curr_req() code is as follows:

CREATE OR REPLACE FUNCTION requests_curr_req()

  RETURNS void AS

$BODY$

DECLARE

   process_status   varchar (10);

   request_id           varchar (100);

   v_request_id      varchar (100);

   v_retries               integer := 0;

   v_suceeded        boolean := false;

   emp_rec               RECORD;

BEGIN

   for emp_rec in

   (select  * from    request_queue

    where    process_status = 'Waiting')

    loop

        v_suceeded := false;

        v_request_id := emp_rec.request_id;

        while not v_suceeded or v_retries = 10

        loop

            begin

                insert into requests_test (request_id, form_type, submit_date, request_email, request_description

                                                            )

               select    request_id,

                                form_type,

                                submit_date,

                                request_email,

                                request_description

                      from    requests

                     where    request_id = v_request_id;

                v_suceeded := true;

            exception

                when others

                then

                    v_retries := v_retries + 1;

                    update    request_queue

                        set    retry_counter = v_retries

                     where    request_id = v_request_id;

                    v_suceeded := false;

            end;

        end loop;

        if v_suceeded

        then

            update    request_queue

                set    process_status = 'Completed',

                        processed_time = LOCALTIMESTAMP,

                        retry_counter = v_retries

             where    request_id = v_request_id;

        end if;

     end loop;

    commit;

  RETURN;

END;

$BODY$

 LANGUAGE plpgsql

Thank you for your consideration,

Leon

What’s a Job?

SELECT function() is the simpliest way to way to call a function…

What does “requests_curr_req()” do?

EXECUTE function() will “work” depending upon what function does…

So, what do you mean “nothing worked”? Errors, zero results (but no failure), what?

You may want to look at section 39.12 in the documentation as well “Porting from Oracle PL/SQL” (section 39 itself is going to be helpful as well).

Have you considered professional support services that have previously done Oracle migrations?  And no, I am not one of them.

David J.