Wild Card Symbol 2

[CIMTEET]

I have an explicit cursor that normally looks for a payload value to be = 1 as an input. Occasionally the input will be a 0. This will change the output of the search. But even more occasionally there is a need for both values. The payload field itself is either 1 or 0. I was wondering if there was a wild card character I could put in there instead of 1 or 0 that will tell the cursor to search for both?

rough example
payload = * or maybe % (which is used for the like clause)
Cursor get_stuff(payload_in number field_name_in varchar2)is
Select payload, field_name
From tbl_sm
Where payload = payload_in and field_name = field_name_in;

Background. Payload = 1 is a read message that was stored in the database. Payload = 0 is just an acknowledgement. The acknowledgement has less data in it. Sometimes both are needed. I was wanting to keep from multiple cursors but if I have to I have to. Thanks.

Greg

 

[Dagon]

I don't understand the context in which you want to do this. If you just want either 0 or 1 records all the time, couldn't you just change it to:

payload in (0,1)

? Alternatively, if the payload is being passed in, why is there a problem ? The calling routine would tell the procedure which value to search for.

 

[CIMTEET]

98% of the time its 0 or a 1. Which is simple because of the pass in. But every once in a while both would be needed and I was wondering of there was some sort of character that would tell the cursor to return whatever is in payload.

The inputs for this cursor is generated from a user created text file with a syntax I am trying to develop. Payload is only part of the search string.

msg_name,'0'field_name1 ,
msg_name,field_name1 ,

I have to find an id for the msg_name first to find the data I want for the field_name. The '0' tells the cursor (after I parse it) that payload will be 0 and return the acknowledgement portion of the msg information. The second line using the same message and field name that payload will be an implied 1. Most of the time information is needed from the full message where payload is 1 and not the partial message (ack)where payload <> 1

Currently if I want both messages I have to print two lines in my input. I was looking for a way to print one. So it works without the symbol, I just thought it would be snazzy to have this symbol. Thanks

Greg

 

[Turkbear]

Hi,
Pass some token value ( like 3, maybe) and test for it..
If 3, then bypass the part of the where clause that references payload...
( IOW, build a dynamic where clause dependent on the input value(s)..)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Dagon]

I suppose you could pass '%' and then use a dynamic cursor e.g.

declare
   type t_curs is ref cursor;
   v_curs t_curs;
begin
...
if p_payload = '%' then
   open v_curs for
    Select payload, field_name
    From tbl_sm
    Where payload in (0,1) and field_name = field_name_in;
else
   open v_curs for
    Select payload, field_name
    From tbl_sm
    Where payload = payload_in and field_name = field_name_in;
end if;

 

[CIMTEET]

Dagon,

I haven't used the open method before. I normally have a declared cursor like my first post and then reference it with a for loop. Then I can process each record that is returned. Is that possible with the open v_curs? I am going to be returning multiple records.

Greg

 

[Dagon]

The dynamic cursor is just like a normal cursor. You can fetch as many records as you want. You just need a loop along the lines of:

loop
fetch v_curs into var1, var2...;
if v_cursor%notfound then
exit
end if;
..
end loop;
close v_curs;