0
Well I have a field in my table that is called status, I need to have my bank update this field automatically if the visitor does not leave the company after 18:00.
Does anyone know how I start? I’m using Oracle
1 answer
0
Procedure is the solution to your problem.
Develop your Store Procedure:
Barium syntax:
CREATE [OR REPLACE] PROCEDURE procedure_nome
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_nome];
Example of how it would apply to the problem:
CREATE OR REPLACE Procedure AtualizaStatusVisitante
BEGIN
UPDATE visitante SET vistiante_status = 'Ausente' WHERE vistiante_status = 'Pendente';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001, 'Ocorreu um erro');
END;
In this example, run an UPDATE when the visitor is with STATUS = Pendente
With the term created time to apply a schedule, this will be used Jobs that allows you to create this type of activity.
begin
sys.dbms_job.submit(job => :job,
what => 'begin PRC_sua_rotina; end;',
next_date => to_date('26-01-2018 20:41:52', 'dd-mm-yyyy hh24:mi:ss'),
interval => 'TRUNC(sysdate+1) + 1/24*4');
commit;
end;
Just change the NEXT DATE to the date you want to run the FIRST time. Then, this date will be automatically updated to the date that gives that "interval", IE, the next day + 4 hours.
Sources:
-
What you wrote fits more as a comment. You didn’t show any solution, just posted links.
-
This is the solution, see his question. It starts with procedures. Thank you for negative and then comment. I can remove the answer if applicable.
-
Or could improve by showing in addition to the references a significant code/explanation snippet
-
I, for example, in my ignorance of Oracle banks did not realize how to meet this demand via Procedure, so your answer is not useful to me if I have a similar problem
-
I understand what you mean. Thinking of a context beyond the question. I will elaborate a coherent answer to what you recommend.
-
@Jeffersonquesado Thanks for the push, I made the improvements to my response so that adds value to the community. Open to suggestions.
-
Thank you very much I will try to use yours as an example ta well explained!
-
I managed to do Procedure, but when I run the job it gives an error look
-
ORA-01008: not all variables bound Details: Begin sys.dbms_job.Submit(job => :job, what => 'Begin Updatsvisitant; end;', next_date => to_date('26-01-2018 20:41:52', 'dd-mm-yyyy hh24:mi:ss'), interval => 'TRUNC(sysdate+1) + 1/24*12'); commit; end; Error at line 1 ORA-01008: not all variables bound
-
I’ll check the problem.
Browser other questions tagged database oracle
You are not signed in. Login or sign up in order to post.
How will you know the visitor is at the company? If a point record is made, you can monitor through that point record, and when someone leaves, beat the point and update the status to "outside the company" if you do not maintain the current status "this in the company".
– EmanuelF
It is a screen of visitor registration and visit, the guy will register as pending status, and in case he does not leave the company until 6 hours I need to update the screen to Absent
– Carlos Rovai
Procedure is the solution to your problem. You can find more links here: https://www.devmedia.com.br/como-workcom-stored-procedures-e-cursores-no-oracle-sql-server-e-firebird-e-postgresql/33023 https://www.devmedia.com.br/como-workcom-stored-procedurese-cursoresnooracle-sql-server-e-firebird-e-postgresql/33023
– EmanuelF