Parameter issue

[ekta22]

Hi,

I have a parameter in my stored procedure called pCODE. Right now the field this parameter is based can be of variable length and is of type varchar2. . The way I want to use this pCODE parameter is that if the user enters the value "National" for this parameter it should show only those records where the length of the field is 6 and the last 2 characters are alphanumeric.
This is what I tried but keep getting an error

length(task_code) = 6 and regexp_instr(substr(task_code, 5, 6), '[A-Z][A-Z]')

What am I doing wrong?
Secondly, how should I incorporate "National" in my where clause so that it should return records based on above condition. I am a little unclear on that.

Thanks,

-E

 

[karluk]

This could very well be a version compatibility issue. "REGEXP_INSTR" is a function that is new to Oracle 10g. It doesn't exist in Oracle 9i.

 

[SantaMufasa]

...where...the last 2 characters are alphanumeric.

Your regexp_instr function arguments imply that what you are looking for is actually limited to alphabetic. Can you confirm that?


If you really want alphabetic, then the following query should do what you want (in any version of Oracle):

if pcode = 'National' then
    for x in (SELECT <your expressions>
                FROM <table_name>
               WHERE length(task_code) = 6
                 and upper(substr(task_code,5,1))
                     between 'A' and 'Z'
                 and upper(substr(task_code,6,1))
                     between 'A' and 'Z') loop
        <do processing here>
    end loop;
end if;
...
end;
/

Let us know if this is useful.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ekta22]

Sorry, my bad . Last two characters should be alphabets.
Right now my select statement looks something like this

SELECT <your expressions>
FROM <table_name>
WHERE (this is where I want to check for pcode parameter)

How do I incorporate the above code in the WHERE clause?

Thanks!

 

[SantaMufasa]

We can make the SELECT do what you want, but first, I need you to clarify what you want the query to do (or check for) if "PCODE <> 'National' ".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]