Proecdure/Function with conditional inputs

[Benny the Guard]

I have a task. I have to create a query that has optional elements for a where clause. This code is needed in a few different places so I fuigured a function to create a cursor wouold be best. So I have:

FUNCTION get_info
(
    p_status  IN  VARCHAR2,
    p_user     IN  VARCHAR2,
    p_type     IN  VARCHAR2
)
RETURN SYS_REFCURSOR;

Now the complexity is the inputs above affect my where clause, but are all optional. So I could have none of them specified (ie null) or any combination of them (ie status = 'update', user='benny'). I may also add other elements in the future. So my question is how do I do this in PL/SQL? In any other real programming language I would create my select call in a string and then conditionally append to the where clause, then execute the statement. But I am not sure PL/SQL has this capability. Any suggestions?

[amitpatel66]

I have a task. I have to create a query that has optional elements for a where clause. This code is needed in a few different places so I fuigured a function to create a cursor wouold be best. So I have:

FUNCTION get_info
(
p_status IN VARCHAR2,
p_user IN VARCHAR2,
p_type IN VARCHAR2
)
RETURN SYS_REFCURSOR;

Now the complexity is the inputs above affect my where clause, but are all optional. So I could have none of them specified (ie null) or any combination of them (ie status = 'update', user='benny'). I may also add other elements in the future. So my question is how do I do this in PL/SQL? In any other real programming language I would create my select call in a string and then conditionally append to the where clause, then execute the statement. But I am not sure PL/SQL has this capability. Any suggestions?

Are you looking at executing the Dynamic statements?

Something like this:

[code=oracle]

FUNCTION get_info
(
p_status IN VARCHAR2,
p_user IN VARCHAR2,
p_type IN VARCHAR2
) IS
EXECUTE IMMEDIATE 'SELECT * FROM table_name WHERE status = '||CHR(39)||p_s tatus||CHR(39)| |' AND user = '||CHR(39)||p_u ser||CHR(39)||' AND type = '||CHR(39)||p_t ype||CHR(39);

RETURN SYS_REFCURSOR;

[/code]

[Benny the Guard]

I think this is indeed the rigth track. But not sure what the

'||CHR(39)||p_status||CHR(39)||'

does. Is this some sort of conditional notation?

[amitpatel66]

I think this is indeed the rigth track. But not sure what the

'||CHR(39)||p_status||CHR(39)||'

does. Is this some sort of conditional notation?

CHR(39) denotes Single Quotes " ' ". Its just that I am enclosing the values of the input parameters with single quotes.

[Benny the Guard]

Ok. It was the '||' that confused me. Also the inputs may not exist. So for example statsu may not be passed in and thus should not be in the where clause. So that compilcates things some, but I guess I would build my base SQL call then conditionally add strings to it to build up things, then call EXECUTE IMMEDIATE on it.

[amitpatel66]

Ok. It was the '||' that confused me. Also the inputs may not exist. So for example statsu may not be passed in and thus should not be in the where clause. So that compilcates things some, but I guess I would build my base SQL call then conditionally add strings to it to build up things, then call EXECUTE IMMEDIATE on it.

Not a problem. Try this one:

[code=oracle]

FUNCTION get_info
(
p_status IN VARCHAR2,
p_user IN VARCHAR2,
p_type IN VARCHAR2
) IS
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM table_name WHERE status = NVL('||CHR(39)| |p_status||CHR( 39)||',status) AND user = NVL('||CHR(39)| |p_user||CHR(39 )||',user) AND type = NVL('||CHR(39)| |p_type||CHR(39 )||',type)';

RETURN SYS_REFCURSOR;
END;
[/code]

[Benny the Guard]

Seems to work, only issue remaing is how to deal with the user column. This col can be null. And for some odd reason the code above returns eevrything that is not null, apparently in PL/SQL null != null. So I may need a IF block for this one input, but the other work well.