Constrained

just what is the restraint talking about?

select * from dba/user_constraints
where constraint_name like 'nnn';

SQL to find where a record is used

This SQL will return all pages, including secondary/subpages, and components that use a given record:

-- all panels that have record and the associated panelgroup
select distinct
a.pnlname as panel, 'subpage' as type
, c.pnlgrpname as component
, a.recname as record
from
sysadm.pspnlfield a
, sysadm.psrecdefn b
, sysadm.pspnlgroup c
where a.recname = 'GU_EX_WORK'
and b.recname = a.recname
--and b.rectype = 2
and c.pnlname = a.pnlname
union
select distinct
a.pnlname as page, 'subpage' as type
, c.pnlgrpname as component
, a.recname as record
from
sysadm.pspnlfield a
, sysadm.pspnlfield b
, sysadm.pspnlgroup c
where a.recname = 'GU_EX_WORK'
and b.subpnlname = a.pnlname
and c.pnlname = b.pnlname
;

Collections - SQL to create new from old

For Office of Research at Griffith University - Code to rollover an existing Collection to a new Collection keeping the existing one's Questions and Responses.

-- GUR_COLL_RES
insert into sysadm.ps_gur_coll_res
select
'RQF_2007'
, to_date('08/05/2007', 'dd/mm/yyyy')
, EMPLID
, 'NEW'
, ' '
, 0
, ''
from sysadm.ps_gur_coll_res
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy')
;


-- GUR_COLL_RESDTL
DECLARE
CURSOR PS_CURSOR IS
SELECT
'RQF_2007' as GUR_COLLECT_CD,
to_date('08/05/2007', 'dd/mm/yyyy') as GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
GUR_FLD_EVIDENCE,
GUR_FLD_RETAINED,
GUR_FLD_EVI_RET,
GUR_RESPONSE_LNG
FROM sysadm.PS_GUR_COLL_RESDTL
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy');
PS_REC PS_CURSOR%ROWTYPE;
COMMIT_INTERVAL CONSTANT INTEGER := 10000;
RECORDS_PROCESSED INTEGER := 0;
BEGIN
OPEN PS_CURSOR;
LOOP
FETCH PS_CURSOR INTO PS_REC;
EXIT WHEN PS_CURSOR%NOTFOUND;
INSERT INTO sysadm.PS_GUR_COLL_RESDTL (
GUR_COLLECT_CD,
GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
GUR_FLD_EVIDENCE,
GUR_FLD_RETAINED,
GUR_FLD_EVI_RET,
GUR_RESPONSE_LNG)
VALUES (
PS_REC.GUR_COLLECT_CD,
PS_REC.GUR_OPEN_DATE,
PS_REC.EMPLID,
PS_REC.GUR_SECTION_NR,
PS_REC.GUR_QUESTION_NBR,
PS_REC.GUR_FLD_EVIDENCE,
PS_REC.GUR_FLD_RETAINED,
PS_REC.GUR_FLD_EVI_RET,
PS_REC.GUR_RESPONSE_LNG);
RECORDS_PROCESSED := RECORDS_PROCESSED + 1;
IF RECORDS_PROCESSED = COMMIT_INTERVAL THEN
COMMIT;
RECORDS_PROCESSED := 0;
END IF;
END LOOP;
COMMIT;
CLOSE PS_CURSOR;
END;


-- GUR_COLL_FILE
/
DECLARE
CURSOR PS_CURSOR IS
SELECT
'RQF_2007' as GUR_COLLECT_CD,
to_date('08/05/2007', 'dd/mm/yyyy') as GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
SEQNO,
ATTACHSYSFILENAME,
ATTACHUSERFILE,
GUR_PRE_POST_VERS,
GUR_EMPLID,
DATETIME_STAMP,
GUR_EP_STATUS,
GUR_EP_URL,
GUR_ADD_EPRINTS,
GUR_DEL_EPRINTS,
GUR_ALLOW_PUBLSH,
GUR_CONF_EMAIL,
GUR_AUTHORISED,
GUR_VERIFIED,
GUR_DECLINED,
GUR_REFORMATTED,
GUR_ENTBY_EP_STAFF,
GUR_PROCESS_FLAG,
DESCRLONG
FROM sysadm.PS_GUR_COLL_FILE
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy');
PS_REC PS_CURSOR%ROWTYPE;
COMMIT_INTERVAL CONSTANT INTEGER := 10000;
RECORDS_PROCESSED INTEGER := 0;
BEGIN
OPEN PS_CURSOR;
LOOP
FETCH PS_CURSOR INTO PS_REC;
EXIT WHEN PS_CURSOR%NOTFOUND;
INSERT INTO sysadm.PS_GUR_COLL_FILE (
GUR_COLLECT_CD,
GUR_OPEN_DATE,
EMPLID,
GUR_SECTION_NR,
GUR_QUESTION_NBR,
SEQNO,
ATTACHSYSFILENAME,
ATTACHUSERFILE,
GUR_PRE_POST_VERS,
GUR_EMPLID,
DATETIME_STAMP,
GUR_EP_STATUS,
GUR_EP_URL,
GUR_ADD_EPRINTS,
GUR_DEL_EPRINTS,
GUR_ALLOW_PUBLSH,
GUR_CONF_EMAIL,
GUR_AUTHORISED,
GUR_VERIFIED,
GUR_DECLINED,
GUR_REFORMATTED,
GUR_ENTBY_EP_STAFF,
GUR_PROCESS_FLAG,
DESCRLONG)
VALUES (
PS_REC.GUR_COLLECT_CD,
PS_REC.GUR_OPEN_DATE,
PS_REC.EMPLID,
PS_REC.GUR_SECTION_NR,
PS_REC.GUR_QUESTION_NBR,
PS_REC.SEQNO,
PS_REC.ATTACHSYSFILENAME,
PS_REC.ATTACHUSERFILE,
PS_REC.GUR_PRE_POST_VERS,
PS_REC.GUR_EMPLID,
PS_REC.DATETIME_STAMP,
PS_REC.GUR_EP_STATUS,
PS_REC.GUR_EP_URL,
PS_REC.GUR_ADD_EPRINTS,
PS_REC.GUR_DEL_EPRINTS,
PS_REC.GUR_ALLOW_PUBLSH,
PS_REC.GUR_CONF_EMAIL,
PS_REC.GUR_AUTHORISED,
PS_REC.GUR_VERIFIED,
PS_REC.GUR_DECLINED,
PS_REC.GUR_REFORMATTED,
PS_REC.GUR_ENTBY_EP_STAFF,
PS_REC.GUR_PROCESS_FLAG,
PS_REC.DESCRLONG);
RECORDS_PROCESSED := RECORDS_PROCESSED + 1;
IF RECORDS_PROCESSED = COMMIT_INTERVAL THEN
COMMIT;
RECORDS_PROCESSED := 0;
END IF;
END LOOP;
COMMIT;
CLOSE PS_CURSOR;
END;
/


-- GUR_COLL_RFCD
insert into sysadm.ps_gur_coll_rfcd
select
'RQF_2007'
, to_date('08/05/2007', 'dd/mm/yyyy')
, EMPLID
, GUR_SECTION_NR
, GUR_QUESTION_NBR
, GUR_PBLCTN_REF_NR
, EFFDT
, GUR_FLD_RSRCH
, GUR_PERCENT
from sysadm.ps_gur_coll_rfcd
where gur_collect_cd = 'RQF_TRIAL'
and gur_open_date = to_date('30/03/2006', 'dd/mm/yyyy')
;