MSSQL TO oracle

[hannah00]

How can I convert the below SQL from MSSQL to Oracle.

select * from TEST_USER
where (UPPER(USER_ID) = UPPER(CAST(@USER_ID as varchar)) OR @USER_ID is null)


the @USER_ID is I want to have all of the records if it's NULL and matching records if not NUL.

thanks

[KenEwald]

Is user_id a primary key?

[hannah00]

kEN,

it's not PK.

thanks,

[KenEwald]

Is it indexed? What's it's datatype?

[hannah00]

kEN,

SO SORRY , IT'S PRIMARY KEY AND VARCHAR

[KenEwald]

Does it actually contain alpha-numeric values?

[hannah00]

NO, it's parameter

I have the parameter @user_id. I want all records if the parameter has a NULL value. I only want records that match the parameter if the parameter is not null. Because the parameter can be null, I have to cast it to a varchar before I send it into the UPPER function in MSSQL

[KenEwald]

user_id like decode(@user_id,null,'%',@user_id);

Here's the test case:
create table test_user (user_id number);
desc test_user;
--Name Null? Type
--USER_ID NUMBER
insert into test_user values (1);
insert into test_user values (2);
select * from test_user where user_id like decode(null,null,'%',null);
--returns all records because @user_id is null
select * from test_user where user_id like decode(1,null,'%',1);
--returns only user_id 1