Home » SQL & PL/SQL » SQL & PL/SQL » Array/In Ref Curosr
Array/In Ref Curosr [message #293641] Mon, 14 January 2008 06:12 Go to next message
rich09
Messages: 3
Registered: January 2008
Junior Member
Hi all,

I need to create a stroed procedure and pass in 30 arguments. This though will be messy as i dont want to have to define each parameter. Is there a way to pass in an array of values or an IN Ref Cursor.

Any help or suggestions would be greatly appreciated.

Thanks

Rich
Re: Array/In Ref Curosr [message #293644 is a reply to message #293641] Mon, 14 January 2008 06:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, for instance:
SQL> create or replace type my_array is table of number;
  2  /

Type created.

SQL> create or replace procedure p (param in my_array) is
  2  begin
  3    for i in 1..param.count loop
  4      dbms_output.put_line ('i='||i||' val='||param(i));
  5    end loop;
  6  end;
  7  /

Procedure created.

SQL> exec p(my_array(3,6,19));
i=1 val=3
i=2 val=6
i=3 val=19

PL/SQL procedure successfully completed.

Regards
Michel

Re: Array/In Ref Curosr [message #293646 is a reply to message #293641] Mon, 14 January 2008 06:32 Go to previous messageGo to next message
rich09
Messages: 3
Registered: January 2008
Junior Member
Thanks for the reply......However will it be possible to pass in different data types, e.g. number, varchar2, date etc?

Thanks
Re: Array/In Ref Curosr [message #293647 is a reply to message #293646] Mon, 14 January 2008 06:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this is your type, you can put what you want inside:
SQL> create type my_type is object (
  2    col_integer integer,
  3    col_string varchar2(10),
  4    col_date   date)
  5  /

Type created.

SQL> create or replace procedure p (param in my_type) is
  2  begin
  3    dbms_output.put_line('integer='||param.col_integer);
  4    dbms_output.put_line('string ='||param.col_string);
  5    dbms_output.put_line('date   ='||to_char(param.col_date,'DD/MM/YYYY HH24:MI:SS'));
  6  end;
  7  /

Procedure created.

SQL> exec p (my_type(1,'Hello',sysdate));
integer=1
string =Hello
date   =14/01/2008 13:37:18

PL/SQL procedure successfully completed.

Regards
Michel
Re: Array/In Ref Curosr [message #293651 is a reply to message #293641] Mon, 14 January 2008 07:06 Go to previous messageGo to next message
rich09
Messages: 3
Registered: January 2008
Junior Member
Does that mean that inside my type i will have to specify everything that i then want to use in my insert?
Re: Array/In Ref Curosr [message #293653 is a reply to message #293651] Mon, 14 January 2008 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It means that you can specify what you want.
I don't you finally want.
If this is just typing less characters when you define a procedure, it is just a waste of time.
If this is just typing less characters when you call a procedure, you always have to pass all the values and so type them. If you don't want to pass all values, you can define default ones.
In the end, it depends of what you want to achieve.
I gave you examples, these are just examples, they can have many other ones.

Regards
Michel
Re: Array/In Ref Curosr [message #293794 is a reply to message #293651] Tue, 15 January 2008 01:03 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
rich09 wrote on Mon, 14 January 2008 14:06
Does that mean that inside my type i will have to specify everything that i then want to use in my insert?

If you want your parameters to resemble the table-columns you want to insert into, you can use an in-parameter of your_table%rowtype
SQL> create table faq_tab (id number, my_text varchar2(10));

Table created.

SQL> create or replace procedure faq_proc
  2  ( p_param in faq_tab%rowtype
  3  ) is
  4  begin
  5    dbms_output.put_line(p_param.my_text);
  6  end;
  7  /

Procedure created.

SQL> insert into faq_tab values (1, 'Test');

1 row created.

SQL> begin
  2    for r_rec in (select * from faq_tab)
  3    loop
  4      faq_proc(r_rec);
  5    end loop;
  6  end;
  7  /
Test

PL/SQL procedure successfully completed.



[Edit: added example]

[Updated on: Tue, 15 January 2008 01:06]

Report message to a moderator

Previous Topic: how to store procedures in the database
Next Topic: creating procedure to move data from one table to other in modified format
Goto Forum:
  


Current Time: Wed Dec 07 05:12:31 CST 2016

Total time taken to generate the page: 0.12829 seconds