I'm struggling with a cursor.
I currently have following query:
DECLARE
rc SYS_REFCURSOR;
BEGIN
OPEN rc FOR SELECT
sro.sysrepobject_id,
sro.name as sysrepobject_name,
configtrace.object_id,
upper(nvl(sro.dbtablename,sro.name)) as table_name,
upper(sra.dbcolumnname) as column_name
FROM
configtrace
LEFT OUTER JOIN sysrepobject sro ON sro.sysrepobject_id = configtrace.sysrepobject_id
INNER JOIN sysrepattribute sra ON sra.sysrepobject_id = sro.sysrepobject_id and sra.representation = 1
WHERE
configtrace.task = 'Task_1';
dbms_sql.return_result(rc);
END;
/
It contains a list of all objects that have been changed in our program for a certain task 'Task_1'.
The result can look like this:
SYSREPOBJECT_ID SYSREPOBJECT_NAME OBJECT_ID TABLE_NAME COLUMN_NAME
--------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------
93 Selection 1000158 SELECTION DESCRIPTION
93 Selection 1000158 SELECTION DESCRIPTION
93 Selection 1000158 SELECTION DESCRIPTION
93 Selection 1000158 SELECTION DESCRIPTION
93 Selection 1000158 SELECTION DESCRIPTION
94 SysRepEnumerationValue 402100 SYSENUMERATION DESCRIPTION
457 BulkProcessor 1000300 BULKPROCESSOR NAME
457 BulkProcessor 1000322 BULKPROCESSOR NAME
457 BulkProcessor 1000300 BULKPROCESSOR NAME
457 BulkProcessor 1000321 BULKPROCESSOR NAME
457 BulkProcessor 1000321 BULKPROCESSOR NAME
The first column "Sysrepobject_id" is the ID of the type of object (e.g. 93 is for Selection, 457 is for a BulkProcessor). The second column is the name of the type. The third column is for the ID of the specific object, e.g. 1000158 is the ID of a specific selection.
What I would like to do is add an extra column with the name of e.g. the specific selection or the specific bulkprocessor. To do that, I need to make an extra join to the specific SELECTION or BULKPROCESSOR table. I already added these in my query, both the tablename and the column name that I want to look for. Only problem I have is how I do this.
What I want is that oracle executes following queries after each other and print the result:
SELECT
sro.sysrepobject_id,
sro.name as sysrepobject_name,
configtrace.object_id,
selection.description
FROM
configtrace
LEFT OUTER JOIN sysrepobject sro ON sro.sysrepobject_id = configtrace.sysrepobject_id
INNER JOIN sysrepattribute sra ON sra.sysrepobject_id = sro.sysrepobject_id and sra.representation = 1
LEFT OUTER JOIN selection ON selection.selection_id = configtrace.object_id
WHERE
configtrace.task = 'Task_1'
and configtrace.sysrepobject_id = 93;
SELECT
sro.sysrepobject_id,
sro.name as sysrepobject_name,
configtrace.object_id,
SYSENUMERATION.name
FROM
configtrace
LEFT OUTER JOIN sysrepobject sro ON sro.sysrepobject_id = configtrace.sysrepobject_id
INNER JOIN sysrepattribute sra ON sra.sysrepobject_id = sro.sysrepobject_id and sra.representation = 1
LEFT OUTER JOIN SYSENUMERATION ON SYSENUMERATION.SYSENUMERATION_id = configtrace.object_id
WHERE
configtrace.task = 'Task_1'
and configtrace.sysrepobject_id = 94;
SELECT
sro.sysrepobject_id,
sro.name as sysrepobject_name,
configtrace.object_id,
BulkProcessor.name
FROM
configtrace
LEFT OUTER JOIN sysrepobject sro ON sro.sysrepobject_id = configtrace.sysrepobject_id
INNER JOIN sysrepattribute sra ON sra.sysrepobject_id = sro.sysrepobject_id and sra.representation = 1
LEFT OUTER JOIN BulkProcessor ON BulkProcessor.BulkProcessor_id = configtrace.object_id
WHERE
configtrace.task = 'Task_1'
and configtrace.sysrepobject_id = 457;
Does someone know how I can do this dynamically?
Thanks in advance.