Using subselect with CONCAT function in Oralcle Function

Question

Are the rules for Oracle SQL and PL/SQL different when using the CONCAT function? How can I use subselect with function in Oracle for CONCAT function.

If I have a simple query like below, it works

SELECT CONCAT (' A, B.',
               (SELECT REGEXP_SUBSTR ('CC, DDD, E, ',
                                      '[^,]+',
                                      1,
                                      1)
                  --INTO RESULT_PATTERN
                  FROM DUAL))
  FROM DUAL;

But, if I add this in a PL/SQL function, I get error

   FUNCTION TEST_SELECTION (BIT_PATTERN   IN VARCHAR2,
                                 SEL_PATTERN   IN VARCHAR2)
      RETURN VARCHAR2
   IS
      RESULT_PATTERN     VARCHAR2 (250);
      LOOP_BIT_PATTERN   VARCHAR2 (10);
      LOOP_SEL_PATERN    VARCHAR2 (300);
      TEMP_VAR           VARCHAR2 (200);
   BEGIN
      IF (LENGTH (BIT_PATTERN) > 0 AND SUBSTR (BIT_PATTERN, 0, 1) = '1')
      THEN
         RESULT_PATTERN :=
            CONCAT (RESULT_PATTERN,
                    (SELECT REGEXP_SUBSTR (SEL_PATTERN,   **--This is line 35**
                                           '[^,]+',
                                           1,
                                           1)
                               --TEMP_VAR
                       FROM DUAL));      **--This is line 40**
         BIT_PATTERN := SUBSTR (BIT_PATTERN, 1);
         SEL_PATTERN := '';
         --TEST_SELECTION (BIT_PATTERN, SEL_PATTERN);
      END IF;
    END TEST_SELECTION;

The error is

[Error] PLS-00103 (35: 22): PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable>
   continue
[Error] PLS-00103 (40: 33): PLS-00103: Encountered the symbol ")" when expecting one of the following:

   . , @ ; for <an identifier>
   <a double-quoted delimited-identifier> group having intersect
   minus order partition sta

Answer 1

You forgot to return the value at the end of the block.

In your case It is unnecessary to make a select ... into ... from dual in a pl block.

-- by default all parameters are IN
FUNCTION TEST_SELECTION (BIT_PATTERN VARCHAR2,
                         SEL_PATTERN VARCHAR2)
RETURN VARCHAR2
IS
    RESULT_PATTERN     VARCHAR2 (250);
    LOOP_BIT_PATTERN   VARCHAR2 (10);
    LOOP_SEL_PATERN    VARCHAR2 (300);
BEGIN
    IF (LENGTH (BIT_PATTERN) > 0 AND SUBSTR (BIT_PATTERN, 0, 1) = '1')
    THEN
        -- Are you sure RESULT_PATTERN has a value at this point?
        RESULT_PATTERN := RESULT_PATTERN || 
                          REGEXP_SUBSTR(SEL_PATTERN, '[^,]+', 1,1);
        BIT_PATTERN := SUBSTR(BIT_PATTERN, 1);
        SEL_PATTERN := '';
    END IF;
    -- I supose you want to return this variable
    RETURN RESULT_PATTERN;
END TEST_SELECTION;