SQL & PL/SQL

I'm trying to convert the following sql from sql server to oracle and need to understand the problems I'm having:

First I understand the concatenation issue using + instead of || but I'm wondering what the syntax in oracle is for the join:

Here is the script I'm playing with in sql:


--research into finding patterns within tables and flagging them
--use RespAnalysis


--drop table PolicyPrint
create table PolicyPrint
(
AllPolicyNumbersIncluded varchar(2000) null,
cicPolicyNumber varchar(255) null,
cicPolicyPrintFlag varchar(1) null
)
GO
--drop table PolicyDetails
create table PolicyDetails
(
policyNumber varchar(255) null,
CompanyName varchar(255) null,
Address varchar(255) null,
)
--truncate table PolicyPrint
insert into PolicyPrint values ('123456A 123456B 123456C 123456D','123345A','')
insert into PolicyPrint values ('123456E 123456F 1234533 123456D','123345A','')
insert into PolicyPrint values ('Bad','123345A','')
insert into PolicyPrint values ('TEST','123345A','')



insert into PolicyDetails values ('123456A','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('123456B','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('123456C','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('123456D','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('123456E','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('123456F','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('123456G','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('123457H','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('1234533','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('1234534','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('12345feg','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('12345sdr','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('123sdfd','ABC Company','12344 Drury Lane')
insert into PolicyDetails values ('1234fs','ABC Company','12344 Drury Lane')

select * from PolicyDetails

select * from PolicyPrint

SELECT me.policyNumber, me.CompanyName, me.Address, pp.AllPolicyNumbersIncluded
FROM PolicyPrint pp INNER JOIN PolicyDetails me
ON (pp.cicPolicyNumber = me.policyNumber
OR PATINDEX('%' + me.policyNumber + '%',pp.AllPolicyNumbersIncluded)> 0 )

update PolicyPrint set cicPolicyPrintFlag = 'X' where AllPolicyNumbersIncluded in (
SELECT pp.AllPolicyNumbersIncluded
FROM PolicyPrint pp INNER JOIN PolicyDetails me
ON (pp.cicPolicyNumber = me.policyNumber
OR PATINDEX('%' + me.policyNumber + '%',pp.AllPolicyNumbersIncluded)> 0 )
)

select * from PolicyPrint

This works fine for sql server but not with oracle I'm wondering if the problem isn't that oracle doesn't like joining on strings????

Guys post your answers to this Thanks.