Wednesday, September 21, 2022
Wednesday, August 30, 2017
How to use DML Statements in a Function
We can not place any DML statement inside a function simply and
also a function with a DML statement cannot be used inside a SELECT
query.
Here is a small example supporting the above statement:
If we try to run the above function in a select query, the oracle engine throws the error as shown below:
But we can run the above DML statement based function in an anonymous block as shown below:
To use a DML statement inside a Function and also to use that function inside a SELECT query, we have to use PRAGMA AUTONOMOUS_TRANSACTION inside the function.
You can use the above function ‘fun2’ in a select query as shown below:
Here is a small example supporting the above statement:
CREATE OR REPLACE FUNCTION fun1(i_empno IN NUMBER) RETURN NUMBER AS i_count NUMBER; BEGIN -- DELETE FROM emp WHERE empno = i_empno; -- i_count:=sql%rowcount; -- RETURN i_count; END; / |
SQL> SELECT fun1(1) FROM dual; ORA-14551: cannot perform a DML operation inside a query ORA-06512: at "SYSTEM.FUN1", line 6 14551. 00000 - "cannot perform a DML operation inside a query " *Cause: DML operation LIKE INSERT, UPDATE, DELETE OR select-for-UPDATE cannot be performed inside a query OR UNDER a PDML slave. *Action: Ensure that the offending DML operation IS NOT performed OR USE an autonomous TRANSACTION TO perform the DML operation WITHIN the query OR PDML slave. |
DECLARE a NUMBER; BEGIN a:=fun1(7369); dbms_output.put_line(a); END; / |
CREATE OR REPLACE FUNCTION fun2( i_empno IN NUMBER) RETURN NUMBER AS pragma autonomous_transaction; i_count NUMBER; BEGIN DELETE FROM emp WHERE empno=i_empno; i_count:=sql%rowcount; COMMIT; RETURN i_count; END; / |
Function can use Out/Inout Parameter
Can we use an OUT/INOUT parameter in a function?
Yes of course.
A Small example:
CREATE OR REPLACE FUNCTION TEST(A IN NUMBER, B IN OUT NUMBER,
C OUT NUMBER) RETURN NUMBER IS
D NUMBER(3);
BEGIN
B := B + A;
C := B;
D := A + B + C;
RETURN D;
END;
DECLARE
A1 NUMBER(3) := 10;
B1 NUMBER(3) := 20;
C1 NUMBER(3) := 30;
D1 NUMBER(3) := 40;
BEGIN
D1 := TEST(A1,B1,C1);
DBMS_OUTPUT.PUT_LINE(A1 || ' ' || B1 || ' ' || C1 || ' ' || D1);
END;
Yes of course.
A Small example:
CREATE OR REPLACE FUNCTION TEST(A IN NUMBER, B IN OUT NUMBER,
C OUT NUMBER) RETURN NUMBER IS
D NUMBER(3);
BEGIN
B := B + A;
C := B;
D := A + B + C;
RETURN D;
END;
DECLARE
A1 NUMBER(3) := 10;
B1 NUMBER(3) := 20;
C1 NUMBER(3) := 30;
D1 NUMBER(3) := 40;
BEGIN
D1 := TEST(A1,B1,C1);
DBMS_OUTPUT.PUT_LINE(A1 || ' ' || B1 || ' ' || C1 || ' ' || D1);
END;
Thursday, August 3, 2017
Query to get frieght lines in R12
select a.trx_number,a.trx_date,b.line_type,sum(B.EXTENDED_AMOUNT) from ra_customer_trx_all a,RA_CUSTOMER_TRX_LINES_ALL b
where
a.CUSTOMER_TRX_ID = b.CUSTOMER_TRX_ID
--and a.TRX_NUMBER ='1891978'
and a.trx_date like '%-%-17'
and b.LINE_TYPE = 'FREIGHT'
group by a.trx_number,a.trx_date,b.line_type;
select a.trx_number,a.trx_date,b.line_type,sum(B.EXTENDED_AMOUNT) from ra_customer_trx_all a,RA_CUSTOMER_TRX_LINES_ALL b
where
a.CUSTOMER_TRX_ID = b.CUSTOMER_TRX_ID
--and a.TRX_NUMBER ='1891978'
and a.trx_date like '%-%-17'
and b.LINE_TYPE = 'FREIGHT'
group by a.trx_number,a.trx_date,b.line_type;
Tuesday, July 25, 2017
As an oracle apps technical consultant, we often deal with Oracle Workflow issues.To debug these issues we found following queries to be of immense use:
SQL Query To Find Out All Workflow Items For A Given Item Type:
SELECT item_type,
item_key,
to_char(begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
to_char(end_date,
'DD-MON-RR HH24:MI:SS') end_date,
root_activity activity
FROM apps.wf_items
WHERE item_type = '&item_type'
AND end_date IS NULL
ORDER BY to_date(begin_date,
'DD-MON-YYYY hh24:mi:ss') DESC;
SQL Query To Find Out All Notifications Sent By A Particular Workflow:
select wn.notification_id nid,
wn.context,
wn.group_id,
wn.status,
wn.mail_status,
wn.message_type,
wn.message_name,
wn.access_key,
wn.priority,
wn.begin_date,
wn.end_date,
wn.due_date,
wn.callback,
wn.recipient_role,
wn.responder,
wn.original_recipient,
wn.from_user,
wn.to_user,
wn.subject
from wf_notifications wn, wf_item_activity_statuses wias
where wn.group_id = wias.notification_id
and wias.item_type = '&item_type'
and wias.item_key = '&item_key';
/
SQL Query To Find the Activity Statuses For All Workflow Activities For A Particular Item Type and Item key:
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2,
1
/
SQL Query To Find All Errored Workflow Activities For A Particular Item Type/ Item Key:
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time
/
SQL Query To Find Out Errored Process Activity Statuses For A Particular Item Type/Item Key:
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2,
1
/
SQL Query To Find Out The Errored Activities For A Particular Item Type/Item Key:
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time
/
SQL Query To Find Out Attribute Values Of A Workflow:
SELECT NAME attr_name,
nvl(text_value,
nvl(to_char(number_value),
to_char(date_value))) VALUE
FROM wf_item_attribute_values
WHERE item_type = upper('&item_type')
AND item_key = nvl('&item_key',
item_key)
/
SQL Query To Find Out Number Of Deferred Workflow Activities:
SELECT COUNT(1),
was.item_type
FROM apps.wf_items wi,
apps.wf_item_activity_statuses was,
apps.wf_process_activities pra
WHERE wi.item_type = was.item_type
AND wi.item_key = was.item_key
AND wi.end_date IS NULL
AND was.end_date IS NULL
AND was.activity_status = 'DEFERRED'
--AND was.item_type = 'REQAPPRV'
AND was.item_type = wi.item_type
AND pra.instance_id(+) = was.process_activity
GROUP BY was.item_type;
SQL Query To Get The Details Of Various Workflow Agent Listeners And Their Statuses
SELECT t.component_name,
p.owner,
p.queue_table,
t.correlation_id
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';
SQL Query To Find Records That Are Pending In Each Of The Workflow Agent Listener Queues;
SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||
')''||'' Count: ''||count(*) c from ' || p.owner || '.' || p.queue_table ||
' where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ' ||
nvl2(t.correlation_id,
'and corrid like ''' || t.correlation_id || ''' ',
NULL) || 'having count(*)>0;'
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';
SQL Query To Track The Status Of Notifications/Business Events That Are Waiting To Be Processed/That Have Errored Out:
SELECT a.user_data.geteventname(),
decode(a.state,
0,
'0 = Ready',
1,
'1 = Delayed',
2,
'2 = Retained/Processed',
3,
'3 = Exception',
to_char(a.state)) state,
a.user_data.PARAMETER_LIST,
a.user_data.event_data,
a.user_data.event_key,
a.*
FROM apps.wf_deferred a
WHERE corrid LIKE '%oracle.apps.wsh.sup.ssro'
AND rownum < 10;
'DD-MON-RR HH24:MI:SS') begin_date,
to_char(end_date,
'DD-MON-RR HH24:MI:SS') end_date,
root_activity activity
FROM apps.wf_items
WHERE item_type = '&item_type'
AND end_date IS NULL
ORDER BY to_date(begin_date,
'DD-MON-YYYY hh24:mi:ss') DESC;
SQL Query To Find Out All Notifications Sent By A Particular Workflow:
select wn.notification_id nid,
wn.context,
wn.group_id,
wn.status,
wn.mail_status,
wn.message_type,
wn.message_name,
wn.access_key,
wn.priority,
wn.begin_date,
wn.end_date,
wn.due_date,
wn.callback,
wn.recipient_role,
wn.responder,
wn.original_recipient,
wn.from_user,
wn.to_user,
wn.subject
from wf_notifications wn, wf_item_activity_statuses wias
where wn.group_id = wias.notification_id
and wias.item_type = '&item_type'
and wias.item_key = '&item_key';
/
SQL Query To Find the Activity Statuses For All Workflow Activities For A Particular Item Type and Item key:
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2,
1
/
SQL Query To Find All Errored Workflow Activities For A Particular Item Type/ Item Key:
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = '&item_type'
AND ias.item_key = '&item_key'
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.item_type = '&item_type'
AND i.item_key = ias.item_key
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time
/
SQL Query To Find Out Errored Process Activity Statuses For A Particular Item Type/Item Key:
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
UNION ALL
SELECT execution_time,
to_char(ias.begin_date,
'DD-MON-RR HH24:MI:SS') begin_date,
ap.display_name || '/' || ac.display_name activity,
ias.activity_status status,
ias.activity_result_code RESULT,
ias.assigned_user ass_user
FROM wf_item_activity_statuses_h ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY 2,
1
/
SQL Query To Find Out The Errored Activities For A Particular Item Type/Item Key:
SELECT ac.display_name activity,
ias.activity_result_code RESULT,
ias.error_name error_name,
ias.error_message error_message,
ias.error_stack error_stack
FROM wf_item_activity_statuses ias,
wf_process_activities pa,
wf_activities_vl ac,
wf_activities_vl ap,
wf_items i
WHERE ias.item_type = i.item_type
AND ias.item_key = i.item_key
AND ias.activity_status = 'ERROR'
AND ias.process_activity = pa.instance_id
AND pa.activity_name = ac.name
AND pa.activity_item_type = ac.item_type
AND pa.process_name = ap.name
AND pa.process_item_type = ap.item_type
AND pa.process_version = ap.version
AND i.parent_item_type = '&item_type'
AND i.parent_item_key = '&item_key'
AND i.begin_date >= ac.begin_date
AND i.begin_date < nvl(ac.end_date,
i.begin_date + 1)
ORDER BY ias.execution_time
/
SQL Query To Find Out Attribute Values Of A Workflow:
SELECT NAME attr_name,
nvl(text_value,
nvl(to_char(number_value),
to_char(date_value))) VALUE
FROM wf_item_attribute_values
WHERE item_type = upper('&item_type')
AND item_key = nvl('&item_key',
item_key)
/
SQL Query To Find Out Number Of Deferred Workflow Activities:
SELECT COUNT(1),
was.item_type
FROM apps.wf_items wi,
apps.wf_item_activity_statuses was,
apps.wf_process_activities pra
WHERE wi.item_type = was.item_type
AND wi.item_key = was.item_key
AND wi.end_date IS NULL
AND was.end_date IS NULL
AND was.activity_status = 'DEFERRED'
--AND was.item_type = 'REQAPPRV'
AND was.item_type = wi.item_type
AND pra.instance_id(+) = was.process_activity
GROUP BY was.item_type;
SQL Query To Get The Details Of Various Workflow Agent Listeners And Their Statuses
SELECT t.component_name,
p.owner,
p.queue_table,
t.correlation_id
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';
SQL Query To Find Records That Are Pending In Each Of The Workflow Agent Listener Queues;
SELECT 'select ''' || t.component_name || ' (queue_table: ' || p.queue_table ||
')''||'' Count: ''||count(*) c from ' || p.owner || '.' || p.queue_table ||
' where deq_time is null and nvl(delay,enq_time)<sysdate-1/24 ' ||
nvl2(t.correlation_id,
'and corrid like ''' || t.correlation_id || ''' ',
NULL) || 'having count(*)>0;'
FROM applsys.fnd_svc_components t,
applsys.wf_agents o,
dba_queues p
WHERE t.inbound_agent_name || t.outbound_agent_name = o.name
AND p.owner || '.' || p.name = o.queue_name
AND t.component_type LIKE 'WF_%AGENT%';
SQL Query To Track The Status Of Notifications/Business Events That Are Waiting To Be Processed/That Have Errored Out:
SELECT a.user_data.geteventname(),
decode(a.state,
0,
'0 = Ready',
1,
'1 = Delayed',
2,
'2 = Retained/Processed',
3,
'3 = Exception',
to_char(a.state)) state,
a.user_data.PARAMETER_LIST,
a.user_data.event_data,
a.user_data.event_key,
a.*
FROM apps.wf_deferred a
WHERE corrid LIKE '%oracle.apps.wsh.sup.ssro'
AND rownum < 10;
Saturday, July 22, 2017
How to generate the XML Debug Logs
How to generate the
XML Debug Logs (Output Post Processor) for 'Payment Register'.
How to generate the
XML Debug Logs (Output Post Processor) for 'Payment Register'.
1. Create the temp directory in $XDO_TOP
On the applications tier move to $XDO_TOP
- create a temp directory - mkdir temp
- set the permissions - chmod 777 temp
- Use pwd and copy this directory location - it will be used in step 2
[applmgr@hostname ~]$ cd $XDO_TOP
[applmgr@hostname 12.0.0]$ mkdir temp
2. Create the Debug Config file
On the Applications Tier - change to $OA_JRE_TOP/bin - cd $OA_JRE_TOP/bin
- Execute this command - ./java -verbose | grep jre | grep 'java.lang.Runtime '
-In the results - the complete path is present showing where the rt.jar resides
- change to this directory - this is where the xdedebug.cfg should be created
- Use vi to create a new file called xdodebug.cfg
Enter these two lines:
LogLevel=STATEMENT
LogDir=complete_path_to_$XDO_TOP/temp directory you created
[applmgr@hostname 12.0.0]$ cd $OA_JRE_TOP/bin
[applmgr@hostname bin]$ ./java -verbose | grep jre | grep 'java.lang.Runtime '
[Loaded java.lang.Runtime from /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/rt.jar]
[applmgr@hostname bin]$ cd /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ cat xdodebug.cfg
LogLevel=STATEMENT
LogDir=/app/apps/apps_st/appl/xdo/12.0.0/temp
[applmgr@hostname lib]$
3. Restart the Middle tier
cd $INST_TOP/admin/scripts
./adstopall.sh apps/apps
./adstrtall.sh apps/apps
4. Engage PO Output for Communication - using standard style sheet
Purchasing - Reports/Run
- Pass in the parameters to print the purchase order
- Leave the Debug parameter as the default
- Submit the request
5. Retrieve the logs
Move to $XDO_TOP/temp
- Here there are files crearted, please collect these and put into a zip file.
1. Create the temp directory in $XDO_TOP
On the applications tier move to $XDO_TOP
- create a temp directory - mkdir temp
- set the permissions - chmod 777 temp
- Use pwd and copy this directory location - it will be used in step 2
[applmgr@hostname ~]$ cd $XDO_TOP
[applmgr@hostname 12.0.0]$ mkdir temp
2. Create the Debug Config file
On the Applications Tier - change to $OA_JRE_TOP/bin - cd $OA_JRE_TOP/bin
- Execute this command - ./java -verbose | grep jre | grep 'java.lang.Runtime '
-In the results - the complete path is present showing where the rt.jar resides
- change to this directory - this is where the xdedebug.cfg should be created
- Use vi to create a new file called xdodebug.cfg
Enter these two lines:
LogLevel=STATEMENT
LogDir=complete_path_to_$XDO_TOP/temp directory you created
[applmgr@hostname 12.0.0]$ cd $OA_JRE_TOP/bin
[applmgr@hostname bin]$ ./java -verbose | grep jre | grep 'java.lang.Runtime '
[Loaded java.lang.Runtime from /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/rt.jar]
[applmgr@hostname bin]$ cd /app/apps/tech_st/10.1.3/appsutil/jdk/jre/lib/
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ vi xdodebug.cfg
[applmgr@hostname lib]$ cat xdodebug.cfg
LogLevel=STATEMENT
LogDir=/app/apps/apps_st/appl/xdo/12.0.0/temp
[applmgr@hostname lib]$
3. Restart the Middle tier
cd $INST_TOP/admin/scripts
./adstopall.sh apps/apps
./adstrtall.sh apps/apps
4. Engage PO Output for Communication - using standard style sheet
Purchasing - Reports/Run
- Pass in the parameters to print the purchase order
- Leave the Debug parameter as the default
- Submit the request
5. Retrieve the logs
Move to $XDO_TOP/temp
- Here there are files crearted, please collect these and put into a zip file.
Enabling XDO logging
in EBS in 12.1.3
Introduction:
Some times ORACLE support team asks to get XDO logs to trouble
sheet problems related to XML Publisher reports or where XDO processing is
used.
Follow below steps to enable XDO logging
Steps:
a. Connect to the Apache server as applmgr
b. Create an $XDO_TOP/temp and an $XDO_TOP/resource directory
c. Create an xdodebug.cfg file in the $XDO_TOP/resource
directory (do not re-create if the file already exists), containing the
following 2 lines:
LogLevel=STATEMENT
LogDir=[full XDO_TOP]/temp
d. Restart the Apache server.
Note full full XDO_TOP means complete path
Note: The xdodebug.cfg file can also be created in the
$OA_JRE_TOP/jre/lib directory.
Thursday, July 6, 2017
Querry to Find Depended objects
Querry to Find Depended objects
SELECT "NAME", "OWNER", "TYPE", "OBJECT_ID", "STATUS", "TYPE_LINK" FROM(
SELECT DISTINCT
'SQLDEV:LINK:'||b.owner||':'||b.object_type||':'/*||lpad(' ',4*l)*/||b.object_name||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' as NAME,
b.owner,
b.object_type TYPE,
B.OBJECT_ID,
b.status,
replace(b.object_type,' ','_') type_link,
b.owner sdev_link_owner,
b.object_name sdev_link_name,
b.object_type sdev_link_type
FROM
sys.all_objects b,
(SELECT object_id, referenced_object_id, level l, rownum ord
FROM public_dependency
START WITH object_id = :OBJECT_ID
CONNECT BY NOCYCLE PRIOR referenced_object_id = object_id) c
WHERE b.object_id = c.referenced_object_id
AND b.owner NOT IN ('SYS', 'SYSTEM')
AND b.object_name <> 'DUAL'
--order by ord
);
Subscribe to:
Posts (Atom)