Узнать актуальность данных в глобальнике по сети — различия между версиями

Текущая версия на 16:36, 29 августа 2016

Отправьте по g-tasks запрос:

SET TERM ^ ;
create or alter procedure PR_GET_LAST_MGN_UPDATE
as
declare variable CODE_PROFILE DM_ID;
declare variable MGN_CONNSTR DM_TEXT;
declare variable INSERTDT DM_DATETIME;
begin
  select P.PARAM_VALUE
  from PARAMS P
  where P.PARAM_ID = 'CODE_PROFILE'
  into :CODE_PROFILE;

  select P.PARAM_VALUE
  from PARAMS P
  where P.PARAM_ID = 'MGN_CONNSTR'
  into :MGN_CONNSTR;

  if (:CODE_PROFILE is not null) then
  if (:CODE_PROFILE>0) then
  begin
    code_profile=(-1)*(100+:code_profile);
    execute statement 'select first 1 z.insertdt from zmain_dict z order by z.insertdt desc'
        on external :MGN_CONNSTR
        as user 'SYSDBA' password 'masterkey'
        into :INSERTDT;
    update or insert into REPORTS (ID, PARENT_ID, STATUS, REPORTTYPE, SORTING, CAPTION, PARAMS)
    values (:code_profile, 0, -1, -1, 0, 'mgn_last_update', :INSERTDT)
    matching (ID);
    suspend;
  end
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON PARAMS TO PROCEDURE PR_GET_LAST_MGN_UPDATE;
GRANT SELECT,INSERT,UPDATE ON REPORTS TO PROCEDURE PR_GET_LAST_MGN_UPDATE;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE PR_GET_LAST_MGN_UPDATE TO SYSDBA;

execute procedure PR_GET_LAST_MGN_UPDATE;

drop procedure PR_GET_LAST_MGN_UPDATE;

После того как он выполнится, выполните запрос на сервере:

select P.ID, P.CAPTION, P.DESCRIPTION, R.PARAMS
from G$PROFILES P
left join REPORTS R on R.G$PROFILE_ID = P.ID and
      R.ID between -200 and -100
where 1 = 1 and
      P.STATUS = 0 and
      P.RELATIONTYPE = 1 and
      P.DBSECUREKEY is not null
order by R.PARAMS asc