Passing "undefined" variables [message #133878] |
Mon, 22 August 2005 09:35  |
isolapojken
Messages: 26 Registered: August 2005
|
Junior Member |
|
|
Hi,
I'm rather new to PL/SQL and I have encountered a small problem with a program I'm currently working with.
I have a main program which calls on a procedure, which in its turn calls on another procedure. I plan to make this recursive, so that my program calls itself to go on. Each recursion finds a number of entries of the type VARCHAR(20).
My problem is that I would like to keep track of the found entries and then for each recursion check if a found entry has been found before, that is if it already is in the "record" of found entries. This "record" (i have tried using an varray) has to be passed as an input argument to the next procedure.
I have declared a varray where the entries are VARCHAR(20), but can how can I use that as an input argument in another procedure?
Let's say my procedure is called proc_test:
CREATE OR REPLACE PROCEDURE proc_test (a NUMBER) AS...
It is easy to have an input argument that is a defined type ("a NUMBER"), but how do one pass a "special" type?
Is there any other smart way to do this?
The thing I want to do is:
1. Put each found entry in a list
2. Pass the list of found entries to the next procedure
3. Check if a certain entry is in the list
How can I do this? Please help me...
I do not want to make any changes or additions to the database it self.
Very very thankful for any help I can get!
Thanks in advance
/E
|
|
|
Re: Passing "undefined" variables [message #133880 is a reply to message #133878] |
Mon, 22 August 2005 10:16   |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
If I'm reading your question right, you want to know how to pass something like an array? Here is an example using a nested table, which is one of the available collection types at your disposal. This collection creates a type that is an "array" of varchar2's in the database. It is common for a db to have a few basic scalar array types like varchar2 and number.
Another approach is to use an all plsql method, by for example using associative arrays. You can read more about such options in chapter 5 of the 10g plsql user guide and reference, which has examples on how to create, define, and use each collection type.
MYDBA@ORCL >
MYDBA@ORCL > create type vcarray is table of varchar2(30);
2 /
Type created.
MYDBA@ORCL >
MYDBA@ORCL > create procedure test(v vcarray)
2 is
3 begin
4 dbms_output.put_line(v(1));
5 end;
6 /
Procedure created.
MYDBA@ORCL >
MYDBA@ORCL > create procedure test2(v vcarray)
2 is
3 begin
4 for i in v.first .. v.last loop
5 dbms_output.put_line(v(i));
6 end loop;
7 end;
8 /
Procedure created.
MYDBA@ORCL >
MYDBA@ORCL > declare
2 l_array vcarray := vcarray('Hello','out','there');
3 begin
4 test(l_array);
5
6 l_array(1) := 'Goodbye';
7 test(l_array);
8
9 test2(l_array);
10 end;
11 /
Hello
Goodbye
Goodbye
out
there
PL/SQL procedure successfully completed.
MYDBA@ORCL >
MYDBA@ORCL > drop procedure test;
Procedure dropped.
MYDBA@ORCL > drop procedure test2;
Procedure dropped.
MYDBA@ORCL > drop type vcarray;
Type dropped.
MYDBA@ORCL >
MYDBA@ORCL > set echo off;
|
|
|
Re: Passing "undefined" variables [message #133979 is a reply to message #133880] |
Tue, 23 August 2005 01:04   |
isolapojken
Messages: 26 Registered: August 2005
|
Junior Member |
|
|
Thank you!
Just one question. By doing it the way you suggested, will I somehow alter the database (for instance by adding a table to it)? I do not want to do that since it is an "important" database used by many user.
Will your solution alter the database in some way? Or does the comands "drop type..." take away the changes (additions) to it?
Thanks!
/E
|
|
|
Re: Passing "undefined" variables [message #134057 is a reply to message #133878] |
Tue, 23 August 2005 07:57  |
smartin
Messages: 1803 Registered: March 2005 Location: Jacksonville, Florida
|
Senior Member |
|
|
You will "alter" the database in the sense that you will be adding an permanent object to the database. The drop statements at the end of my example are just to clean up the example. You wouldn't actually drop the type that you create or else the program would stop working.
But none of this is a bad thing. That is what a database is for, to hold objects. You create and use persistent objects in a database, regardless of how "important" that database is.
When you create your program, ideally in a plsql package, you will be creating an object as well. The type you are creating is just another object, although it is a general purpose object that can be used and reused by all the programs in your db.
If you don't want to create the type, you could use something like associative arrays as documented in the plsql user guide chapter referenced above.
|
|
|