CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-functionsql-in
Marllon Nasser
Marllon Nasser

Reputation: 390

Passing values to IN clause on a function oracle

I need to return a cursor within a function:

CREATE OR REPLACE FUNCTION test_cursor (
   bigstring   IN   VARCHAR2
)
   RETURN cursor

IS
  row_test table_colors := table_colors(bigstring);
  c1 CURSOR;
BEGIN
   OPEN c1 FOR
      select * from cars where color IN (select column_value
                                    from table(row_test));

   RETURN c1;

END test_cursor;

table_colors is:

create or replace type table_colors as table of varchar2(20);

But when I test it passing like blue, red, pink, white or 'blue', 'red', 'pink', 'white' always throws the same error

ORA-06502: PL/SQL; numeric or value error: character string buffer too small

on this line row table_colors := table_colors(bigstring);

What I am doing wrong here?

Upvotes: 0

Views: 1236

Answers (2)

Brian Leach
Brian Leach

Reputation: 2101

Please show the definition of table_colors. It appears that table_colors(bigstring) is returning a value incompatible with assignment to table_colors.

As a matter of good practice, initialization of non trivial values should be done inside of the begin ... end rather than in the definition section. That allows you to trap the error within the function or procedure rather than the error cascading outwards. For example, rather than:

IS
  row_test table_colors := table_colors(bigstring);
 c1 CURSOR;
BEGIN ...

You should use

IS
  row_test table_colors;
  c1 CURSOR;
BEGIN
  row_test := row_test;
  ...

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

The problem is that bigstring is a single scalar value that may happen to contain commas and single quotes not a list of values. You would need to parse the string to extract the data elements. If each of the individual elements within bigstring happens to be a valid Oracle identifier, you could use the built-in dbms_utility.comma_to_table function. Were it my system, though, I'd feel more comfortable with my own parsing function. Assuming that bigstring is just a comma-separated list, I'd use a version of Tom Kyte's str2tbl function

create or replace function str2tbl( p_str in varchar2 ) 
  return table_colors
as
  l_str   long default p_str || ',';
  l_n        number;
  l_data    table_colors := table_colors();
begin
  loop
    l_n := instr( l_str, ',' );
    exit when (nvl(l_n,0) = 0);
    l_data.extend;
    l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
    l_str := substr( l_str, l_n+1 );
  end loop;
  return l_data;
end;

Now, you can realistically implement str2tbl using regular expressions in a single SQL statement as well. That might be a touch more efficient. I'd expect, however, that string parsing is well down on your list of performance issues so I would tend to stick with the simplest thing that could possibly work.

Your procedure would then become

CREATE OR REPLACE FUNCTION test_cursor (
   bigstring   IN   VARCHAR2
)
   RETURN sys_refcursor
IS
  row_test table_colors := str2tbl(bigstring);
  c1 sys_refcursor;
BEGIN
   OPEN c1 FOR
      select * from cars where color IN (select column_value
                                           from table(row_test));

   RETURN c1;

END test_cursor;

Upvotes: 1

Related Questions