convert cell to multiple rows

with t as
(select 'Simpson, Homer//Flintstone, Fred//Jetson, George' txt from dual
)
--
-- actual query #1:
--
select substr( txt
, instr (txt, '//', 1, level)+2
, instr (txt, '//', 1, level+1) - instr (txt, '//', 1, level)-2
) txt
from ( select '//'||txt||'//' txt
from t
)
connect by level <= (length(txt)-length(replace(txt,'//'))) / 2 - 1


Output
TXT
=======
Simpson, Homer
Flintstone, Fred
Jetson, George

How to flush cache in oracle

Use the below code in System login

alter system flush shared_pool;
alter system flush buffer_cache;

Query to calculate week number from a given date taking monday as first date of week in pl sql

SELECT CEIL ( ( 6
+ (TRUNC((TO_DATE('24-Jan-10','dd-Mon-yy')
+ DECODE(TRIM(TO_char(TO_DATE('24-Jan-10','dd-Mon-yy'),'DAY')),'MONDAY', +1,'SUNDAY',-1,0))
, 'd')
- TRUNC(TO_DATE('24-Jan-10','dd-Mon-yy'), 'Y')
)
)
/ 7
) from dual

Query to calculate week number from a given date

SELECT CEIL ( ( 7
+ ( TRUNC (TO_DATE (p_booking_date), 'd')
- TRUNC (TO_DATE (p_booking_date), 'Y')
)
)
/ 7
)
INTO l_qtr
FROM DUAL;

Update Null to attributes in hz_party_v2pub.update_organization

I am calling API hz_party_v2pub.update_organization to update party information.
I am fetching the already existing organization record and updating it.

orgrec hz_party_v2pub.organization_rec_type;

hz_party_v2pub.get_organization_rec (p_party_id => partyid,

x_organization_rec => orgrec,

x_return_status => x_return_status,

x_msg_count => x_msg_count,

x_msg_data => x_msg_data

);

orgrec.party_rec.attribute4:=null;

The Current Value of attribute4 is 12
When i am trying to update the attribute4 to null through api, I am
getting the old value 12 getting retained. But when i update other value like 13,
it is working. It does not update null alone. After doing R&D I came to find that
the following will solve the problem.

orgrec.party_rec.attribute4 := chr(0);

Now it is updating the value to null.

Query to calculate week number from a given date taking monday as first date of week in pl sql

Here is the Query to calculate week number from a given date taking monday as first date of week in pl sql.

SELECT ceil(( 7+(trunc(to_date('01-Jan-2009'),'d')-trunc(to_date('01-Jan-2009'),'Y')) )/7) FROM dual