Procedure using Scalar and variables 1

[eyetry]

Not really sure what I'm doing here but I offered to write a stored proc (never have before) that a related tool I use will call. Pretty much have the procedure working but have a simple question.... how do I define the variables (var1 and var2) below as input variables? Do I have to declare something? Are they simple surrounded by symbols?

drop type memTableType;
drop type memScalarType;

create or replace type memScalarType as object
( set_id number (10),
file_id number (10),
config number (10),
id varchar2 (40),
last_name varchar2 (35),
first_name varchar2 (25) );
/

create or replace type memTableType as table of memScalarType;
/

drop function f_mem_proc;

create or replace
function f_mem_proc( p_start_row in number,
p_end_row in number )
return memTableType
as
l_data memTableType := memTableType();
l_cnt number default 0;
begin
for x in ( select distinct xref.member_set_id set_id,
hdr.file_id,
hdr.file_config_id config,
id2.identifier id,
mem1.last_name,
mem1.first_name
from user01.tbl_member_xref xref,
user01.tbl_file_config cfg,
user01.tbl_header hdr,
user01.tbl_member mem1,
user02.enr_identifier id
where hdr.file_config_id = var1
and hdr.file_id = var2
and hdr.header_id = mem1.header_id
and cfg.file_config_id = hdr.file_config_id
and xref.member_set_id = cfg.member_set_id
and id.owner = xref.customer_id
and id.association_table = 'MEMBER'
and id.identifier_type_vc = 1234
and mem1.member_identifier = id.identifier
order by id )
loop
l_cnt := l_cnt + 1;
if ( l_cnt >= p_start_row )
then
l_data.extend;
l_data(l_data.count) :=
memScalarType( x.set_id,
x.file_id,
x.config,
x.ssn_vc,
x.id,
x.relationship_code,
x.last_name,
x.first_name);
end if;
exit when l_cnt = p_end_row;
end loop;

return l_data;
end;
/
select *
from the ( select cast( f_mem_proc(2,6) as memTableType )
from dual ) a
/

 

[SantaMufasa]

Eyetry,

At what point do you want to provide values for "var1" and "var2"?...Compile time?...Invocation/Run-time?

If Compile time, then you can use SQL*Plus's "ACCEPT...PROMPT" syntax.

If Run-time, then you are probably best served if you just add two more incoming arguments to your function.

Let us know what you do.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[eyetry]

run-time. The procedure will be invoked by a related tool which will pass values for the variables at that time. The procedure will pass back the results.

 

[SantaMufasa]

So, will it work to add two additional arguments to the function?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[eyetry]

Its not going to hurt to try. What do you recommend?

 

[SantaMufasa]

you are probably best served if you just add two more incoming arguments to your function.

I still recommend your adding two more incoming arguments to your function. Did I misinterpret your question?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[eyetry]

No, perhaps I misinterpreted.... Never wrote a SP before.. what two arguments would you recomend? At this point I'm lost.

Something along the lines of a declare?

 

[SantaMufasa]

Eyetry,

You can implement my suggestion by adding the bold code, below:

create or replace
   function f_mem_proc(p_start_row in number,
                       p_end_row in number[b],
                       var1 number,
                       var2 number[/b])
   return memTableType
   as...

Then also change your invocation to read:

select *
   from the (select cast( f_mem_proc(2,6[b],<some val1>,<some val2>[/b])
                 as memTableType )
     from dual ) a
/

Let us know how this works for you


Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[eyetry]

Worked great once I changed the way the tool executes the procedure.

Thanks a lot!