-1

I have a bash script in which I would like to do such a query in SqlPlus 

CREATE TABLE tab1(digits VARCHAR2(100));

INSERT INTO tab1(digits) VALUES (5,6);
COMMIT;


#!/bin/bash
.
.
DECLARE
lv_digits VARCHAR2(100):='';
BEGIN
SELECT digits INTO digits FROM tab1;
FOR i IN (SELECT column1 FROM tab2 WHERE column_id IN (lv_digits) AS text);
LOOP
DBMS_OUTPUT.PUT_LINE(i.text);
END LOOP;
END;
/

I don't know how to properly enter a condition into WHERE to return a value

How to manually introduc WHERE column_id IN (5,6) AS text it works correctly and the value must be loaded from another table with a column that is VARCHAR2.

DECLARE
lv_digits VARCHAR2(100):='';
BEGIN
SELECT digits INTO digits FROM tab1;
FOR i IN (SELECT column1 FROM tab2 WHERE column_id IN (5,6) AS text);
LOOP
DBMS_OUTPUT.PUT_LINE(i.text);
END LOOP;
END;
/

Does anyone have any idea how to convert this?

MartinusP
  • 41
  • 11
  • 1
    Edit your question and provide sample data and desired results. Don't make other people figure out what your code should be doing. What does the title have to do with the code? – Gordon Linoff Aug 19 '17 at 12:44
  • It is not clear why you need to do any of this in PL/SQL as opposed to plain SQL. Either way, your question simply comes down to "given a string that is nothing but a comma-separated list of numbers, how do I convert it into individual values that can be used in an IN condition?". This question has been asked on SO at least 100 times and has many excellent answers for different versions of Oracle. (The version is very relevant since different methods may or may not work depending on version). Do a search for "split a comma-separated list" to find them. –  Aug 19 '17 at 14:01
  • I plan to use LISTAGG (digits, ', ') I still is not working properly, but I think it would be a good solution. – MartinusP Aug 19 '17 at 14:08
  • Possible duplicate of [oracle -- Split multiple comma separated values in oracle table to multiple rows](https://stackoverflow.com/questions/18770581/oracle-split-multiple-comma-separated-values-in-oracle-table-to-multiple-rows) and over 100 other questions on SO. –  Aug 19 '17 at 14:08
  • `listagg` is used to create a comma-separated VARCHAR2 string from individual tokens. What you want is the reverse - which is more complicated. –  Aug 19 '17 at 14:09
  • Heh. You're right. :) – MartinusP Aug 19 '17 at 14:12
  • And how do I change the data type of varchar2 to something else that would be easier? – MartinusP Aug 19 '17 at 14:19

1 Answers1

0

This can be useful to someone

DECLARE
lv_digits VARCHAR2(100):='';
BEGIN
SELECT digits INTO digits FROM tab1;
FOR i IN (SELECT column1 FROM tab2 WHERE column_id IN (select regexp_substr(digits,'[^,]+', 1, level) from karuzela_loop 
connect by regexp_substr(digits, '[^,]+', 1, level) is not NULL) AS text);
LOOP
DBMS_OUTPUT.PUT_LINE(i.text);
END LOOP;
END;
/

@mathguy thanks for the tips

MartinusP
  • 41
  • 11