set serveroutput on;
DECLARE
n NUMBER := 0;
BEGIN
WHILE n < 5 LOOP
dbms_output.put_line(n);
n := n + 1;
END LOOP;
END;
DECLARE
n NUMBER := 0;
BEGIN
LOOP
dbms_output.put_line(n);
n := n + 1;
exit when n=10;
END LOOP;
END;
;
select level from dual connect by level<11;
select rownum from dual connect by rownum<11;
SELECT rownum
FROM XMLTABLE('1 to 10');
declare
n varchar2(100) :='chidambaram';
begin
for i in 1 .. length(n)
loop
dbms_output.put_line(substr(n,length(n)-i+1,1));
end loop;
end;
DECLARE
prim BOOLEAN := true;
BEGIN
FOR i IN 1..25 LOOP
FOR j IN 1..i LOOP
IF MOD(i, j) = 0 AND j != 1 AND i != j THEN
prim := false;
EXIT;
ELSE
prim := true;
END IF;
END LOOP;
IF prim = true THEN
dbms_output.put_line('prim:' || i);
END IF;
END LOOP;
END;
select reverse('chidambaram') from dual;
================================================================
select * from tab;
set serveroutput OFF;
select /*+ gather_plan_stastics */ * from DEPARTMENTS;
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
select /*+ gather_plan_statistics CHIDAM*/ count(*) from dba_objects;
SELECT *
FROM TABLE (DBMS_XPLAN.display_cursor ('73pq2vs0hc86n', null, 'ALLSTATS LAST'));
SELECT * FROM V$SQL;
SELECT * FROM V$SQLTEXT WHERE SQL_TEXT LIKE '%CHIDAM%';
===================================
SELECT a.msg_state,
a.user_data.event_name,
a.user_data.send_date
FROM applsys.aq$wf_deferred a
WHERE a.user_data.event_name LIKE 'oracle.apps.ar.transaction.Invoice.complete' --Here We need to pass Business event name
GROUP BY a.msg_state, a.user_data.event_name, a.user_data.send_date
ORDER BY a.user_data.event_name,a.user_data.send_date DESC;
select w_even.name
,w_even.status event_status
,w_e_subs.status subscription_status
,nvl(w_e_subs.phase,0) subscription_phase
,w_e_subs.rule_function
from wf_events w_even
,wf_event_subscriptions w_e_subs
where
w_even.name like 'oracle.apps.ar.transaction.Invoice.complete'
and w_e_subs.event_filter_guid = w_even.guid;
SELECT wd.user_data.event_name,
wd.user_data.event_key,
rank() over(PARTITION BY wd.user_data.event_name, wd.user_data.event_key ORDER BY n.NAME) AS serial_no,
n.NAME parameter_name,
n.VALUE parameter_value,
wd.user_data.error_message,
wd.user_data.error_stack,
wd.msgid,
wd.delay
FROM apps.wf_deferred wd, TABLE(wd.user_data.parameter_list) n
WHERE 1=1
AND wd.user_data.event_name ='oracle.apps.ar.transaction.Invoice.complete'
ORDER BY wd.user_data.send_date DESC,
wd.user_data.event_name,
wd.user_data.event_key,
n.NAME;
select regexp_substr('chidam-baram-k--B','[^-]+',1,level) val_ou
from dual
connect by regexp_substr('chidam-baram-k--B','[^-]+',1,level);
with t as (select 'chidam,ram,baram,,k,,,J' descript from dual
)
select regexp_substr(descript,'[^,]+',1,level) val_ou
from t
connect by regexp_substr(descript,'[^,]+',1,level) is not null;
select regexp_substr('chidam-baram-k--B','([^-]*)(-|$)',1,5,null,1) from dual;
select * from wf_events where upper(name) like 'ORACLE.APPS.AR.TRANSACTION.INVOICE.COMPLETE';
set serveroutput off;
select /+ gather_plan_statistics/ * from dual;
select /+ gather_plan_statistics/ count(p) from t where x > sysdate - 30;
select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));
SELECT /*+ gather_plan_statistics */ description,lookup_code
FROM fnd_lookup_values
WHERE enabled_flag = 'Y'
and language='US'
AND SYSDATE BETWEEN NVL(start_date_active,SYSDATE-1) AND NVL(end_date_active,SYSDATE+1)
-- AND lookup_code = p_lookup_code_in
AND lookup_type = 'XXSWPE_WEBSERVICE_CONFIG';
select * from table(dbms_xplan.display(FORMAT=>'ALLSTATS LAST'));
===================================
select * from WF_ROLES WHERE NAME='ERPSCHOOLS_DEMO_ROLE';
select * from WF_USER_ROLES WHERE ROLE_NAME='ERPSCHOOLS_DEMO_ROLE';
select * from WF_LOCAL_ROLES WHERE NAME='ERPSCHOOLS_DEMO_ROLE';
select * from WF_USER_ROLE_ASSIGNMENTS WHERE ROLE_NAME='ERPSCHOOLS_DEMO_ROLE';
select * from mtl_system_items_b where segment1='CDM_TEST1';
select * from fnd_user where user_name='CXK952';
SELECT * FROM wf_deferred;
select * from wf_messages where name='XOA_TEST_MSG';
select * from wf_notifications ;where recipient_role='ERPSCHOOLS_DEMO_ROLE'; message_name='ERP_SEND_ITEM_DET_MSG';
CREATE TABLE debug (msg varchar2(1000));
SELECT * FROM DEBUG;
SELECT * FROM wf_item_attribute_values WHERE item_type = 'CHIDAM_W';
SELECT * FROM wf_items WHERE item_type = 'CHIDAM_W';
SELECT * FROM wf_item_activities_history_v WHERE item_type = 'CHIDAM_W';
SELECT * FROM wf_item_attribute_values WHERE item_type = 'CHIDAM_W';
SELECT * FROM wf_items WHERE item_type = 'ENGCSTEP' AND ITEM_KEY='11505372';
SELECT * FROM wf_item_activities_history_v WHERE item_type = 'CHIDAM_W';
===================================
