Home » SQL & PL/SQL » SQL & PL/SQL » Initialize a variable with a whole array (Oracle Database 11g Release 11.1.0.7.0 - 64bit Production Windows)
Initialize a variable with a whole array [message #571904] Mon, 03 December 2012 14:11 Go to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi all,
Sorry if this sounds silly, but I'm having some hard times with attempts to initialize a set of values,
Of a specific column from a table into a variable containing it as an array(like a single column table).


I've created & populated table TEST_TAB:

SQL>
SQL>   CREATE TABLE "TEST_TAB"
  2     (       "KEY1" NUMBER(10));

Table created.

SQL>
SQL>    insert into "TEST_TAB" values ('1');

1 row created.

SQL>    insert into "TEST_TAB" values ('2');

1 row created.

SQL>    insert into "TEST_TAB" values ('3');

1 row created.

SQL>    insert into "TEST_TAB" values ('4');

1 row created.

SQL>    commit;

Commit complete.

SQL>
SQL>


Then I am trying to compile a function that gets a parameter - a number.

The function should check whether this number exists in this list(return 1), or not(return 0).

I want to initialize key1 column of table TEST_TAB *into* v_my_array(this will be my list),
just to see that I can have a set of values in this variable.
Then I will want to see if the number is in that list, and return 1 or 0 accordingly.

I get the following error compiling, and can't seem to understand what's wrong:

SQL> create or replace function is_num_in_list (p_number  number)
  2                                             return number
  3  is
  4  TYPE varray_type IS TABLE OF NUMBER(10) INDEX BY PLS_INTEGER;
  5  v_my_array varray_type;
  6  begin
  7  select key1 into v_my_array from TEST_TAB;
  8    --if the number provided exists in the list, then 1(TRUE)
  9  if p_number in v_my_array then
 10  return(1);
 11  else
 12      --if the number provided *IS NOT* in the list, then 0(FALSE)
 13    return(0);
 14  end if;
 15  end;
 16  /

Warning: Function created with compilation errors.

SQL> show error
Errors for FUNCTION IS_NUM_IN_LIST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/16     PLS-00103: Encountered the symbol "V_MY_ARRAY" when expecting one
         of the following:
         (

SQL>



Thanks in advance,
Andrey
Re: Initialize a variable with a whole array [message #571907 is a reply to message #571904] Mon, 03 December 2012 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the test case NOT its execution (bur verify the test case works: we cannot copy and paste an execution:
SQL> CREATE TABLE "TEST_TAB"
  2    2     (       "KEY1" NUMBER(10));
  2     (       "KEY1" NUMBER(10))
  *
ERROR at line 2:
ORA-00922: missing or invalid option



 insert into "TEST_TAB" values ('1');

This is wrong. '1' is a STRING not a number.



7  select key1 BULK COLLECT into v_my_array from TEST_TAB;

if p_number MEMBER OF v_my_array then


SQL> select * from TEST_TAB order by 1;
      KEY1
----------
        10
        20
        30
        40

4 rows selected.

SQL> create or replace function is_num_in_list (p_number  number)
  2   return number
  3  is
  4  TYPE varray_type IS TABLE OF NUMBER(10);
  5  v_my_array varray_type;
  6  begin
  7    select key1 bulk collect into v_my_array from TEST_TAB;
  8    if p_number member of v_my_array then
  9      return(1);
 10    else return (0);
 11    end if;
 12  end;
 13  /

Function created.

SQL> select is_num_in_list (0) from dual;
IS_NUM_IN_LIST(0)
-----------------
                0

1 row selected.

SQL> select is_num_in_list (10)  from dual;
IS_NUM_IN_LIST(10)
------------------
                 1

1 row selected.


Of course the correct code should be:
SQL> create or replace function is_num_in_list (p_number  number)
  2   return number
  3  is
  4   v pls_integer;
  5  begin 
  6    select 1 into v from TEST_TAB where key1 = p_number and rownum = 1;
  7    return 1;
  8  exception
  9    when no_data_found then return 0;
 10  end;
 11  /

Function created.

SQL> select is_num_in_list (0) from dual;
IS_NUM_IN_LIST(0)
-----------------
                0

1 row selected.

SQL> select is_num_in_list (10)  from dual;
IS_NUM_IN_LIST(10)
------------------
                 1

1 row selected.

Without any array and temporary storage of the table.

Regards
Michel

[Edit: typos]

[Updated on: Mon, 03 December 2012 23:56]

Report message to a moderator

Re: Initialize a variable with a whole array [message #571909 is a reply to message #571907] Mon, 03 December 2012 16:09 Go to previous message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Michel Cadot wrote on Mon, 03 December 2012 22:55
Post the test case NOT its execution (bur verify the test case works: we cannot copy and paste an execution:



Taken into account. Will do next time.


Thank you very much for the reply, Michel.
I have amended my code as you suggested and it seems to work well.
Many thanks!

Regards,
Andrey

[Updated on: Mon, 03 December 2012 16:09]

Report message to a moderator

Previous Topic: Partitioned Index-Organized table
Next Topic: Date Functions
Goto Forum:
  


Current Time: Tue Oct 21 22:25:55 CDT 2014

Total time taken to generate the page: 0.13114 seconds