PL/SQL Use table variable as query table

Question

In PL/SQL is it possible to use a variable as a query a table?

Tried:

declare
   TYPE t_name IS TABLE OF varchar(50) INDEX BY PLS_INTEGER;
   v_names t_name;
begin
select name bulk collect into v_names from my_table;

select name from v_names where name = 'Max';
end;

MT0 · Accepted Answer · 2021-05-12 19:58:23Z

1

Yes ... but not how you are doing it, for two reasons:

Firstly, you do not have a collection (what you are calling a table variable) as you have used INDEX BY PLS_INTEGER so what you have is an associative array.
Secondly, you can only use collections in SQL queries where the data type has been declared in the SQL scope (and yours is declared in PL/SQL).

So, first you need to create the type:

CREATE TYPE t_name IS TABLE OF VARCHAR2(50);

Then you can run the PL/SQL block:

DECLARE
  v_names t_name;
  v_name  VARCHAR2(50);
BEGIN
  SELECT name
  BULK COLLECT INTO v_names
  FROM my_table;

  SELECT COLUMN_VALUE
  INTO   v_name
  FROM   TABLE(v_names)
  WHERE  COLUMN_VALUE = 'Max';
  
  DBMS_OUTPUT.PUT_LINE( v_name );
END;
/

(Note: the table collection expression in the second query has the pseudo-column COLUMN_VALUE rather than any particular identifier from a table.)

db<>fiddle here

answered May 12, 2021 at 19:58

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user5507535 Over a year ago

Thank you! I don't understand why they make this so complicated, I don't want to trash the database with types everywhere :/ Just want it to work! If someone drops my type it will break the system, god damn.

MT0 Over a year ago

@user5507535 If you want to filter the collection to find a single value then you can do it in the initial SELECT to only get a single value or you can use a FOR loop to iterate over the collection in PL/SQL and then you don't need to try to put it back into the SQl scope.

Belayer Over a year ago

@user5507535. It is not complicated, it is just unfamiliar to you. As far a trashing the database that not an issue. In 20 years I never had a type dropped. After all varchar2, integer, date are all defined as TYPE (well mostly sub-types) in the package STANDARD. So doing it that way is consistent. Just different.

user5507535 Over a year ago

@Belayer I understand you, but this design decision makes no sense, it's like asking for you to create a global variable when you don't need to. I would only use the type on that very specific file and scope, so why need to have if shared globally in the whole system? There are many things in PL/SQL and SQL that are like this, in my opinion, it's bad design and not ergonomic for the programmer.

MT0 Over a year ago

@user5507535 It makes perfect sense if you consider that they are two different languages. It is like writing a Java program that wraps some low-level C code and asking why when I declare a data-type in Java can I not use it in the C code? You cannot because they are two different languages and the wrapping is from Java to C and not the other way round. It is the same for PL/SQL and SQL; you can use SQL types in PL/SQL but you cannot use PL/SQL types in SQL.

|

Collectives™ on Stack Overflow

PL/SQL Use table variable as query table

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related