Как можно использовать оператор Execute Immediate, вложенный внутрь в Execute Immediate

У вашего неправильного SQL есть несколько проблем:

1. В конце присваивания sql_text отсутствует точка с запятой.

2. В том же операторе NUMBER не является допустимым идентификатором в Oracle, если он не заключен в кавычки.

3. Инициализация SN должна иметь апострофы вокруг текста.

Таким образом, назначение sql_text должно читаться

sql_txt := ''select count(1) from productDetails  where ID = :parameter1
          AND "NUMBER" = :parameter2'';

Таким образом, ваш блок должен быть

DECLARE
    SQL_TEXT VARCHAR2(1000);
    MSG VARCHAR2(500);
    SN VARCHAR2(500) := 'parameter1/parameter2';
BEGIN

   sql_text := '
   DECLARE
   parameters VARCHAR2(50);
   parameter1 VARCHAR2(50);
   parameter2 VARCHAR2(50);
   sql_txt VARCHAR2(1000);
   BEGIN
      parameters := :SN;
      parameter1 := substr(parameters,0,10);
      parameter2 := substr(parameters,11,10);
      
      sql_txt = ''select count(1) from productDetails  where ID = :parameter1
      AND "NUMBER" = :parameter2'';
      EXECUTE IMMEDIATE sql_txt USING parameter1 RETURNING INTO MSG;
   END;'; 
   
    EXECUTE IMMEDIATE sql_text USING SN RETURNING INTO MSG;
    DBMS_OUTPUT.put_line(MSG);
END;

Однако вышеизложенное можно значительно упростить, используя:

SELECT COUNT(*)
  INTO MSG
  FROM PRODUCTDETAILS
  WHERE ID = (SELECT REGEXP_SUBSTR(SN, '[^/]+', 1, 1) FROM DUAL) AND
        "NUMBER" = (SELECT REGEXP_SUBSTR(SN, '[^/]+', 1, 2) FROM DUAL);

вместо встроенного динамического блока PL/SQL. Итак, если мы создадим PRODUCTDETAILS, используя

CREATE TABLE PRODUCTDETAILS(ID, "NUMBER", OTHER_FIELD) AS
  SELECT 'parameter1', 'parameter2', 'DATA1' FROM DUAL UNION ALL
  SELECT 'parameter1', 'parameter2', 'DATA2' FROM DUAL UNION ALL
  SELECT 'parameter2', 'parameter3', 'DATA3' FROM DUAL UNION ALL
  SELECT 'parameter3', 'parameter4', 'DATA4' FROM DUAL UNION ALL
  SELECT 'parameter1', 'parameter2', 'DATA5' FROM DUAL UNION ALL
  SELECT 'parameter4', 'parameter2', 'DATA6' FROM DUAL UNION ALL
  SELECT 'parameter5', 'parameter6', 'DATA7' FROM DUAL

приведенный выше SQL правильно вернет '3' в MSG.

db‹›скрипка здесь

Ваш подход кажется запутанным. Вы пробовали это:

DECLARE
    SQL_TEXT VARCHAR2(1000);
    MSG VARCHAR2(500);
    SN VARCHAR2(500) := '[your_parameter_values]';
    parameter1 VARCHAR2(50);
    parameter2 VARCHAR2(50);
BEGIN
    parameter1 := substr(SN,0,10);
    parameter2 := substr(SN,11,10);
  
    SQL_TEXT = 'select count(1) from productDetails where ID = :p1 AND NUMBER = :p2';
    EXECUTE IMMEDIATE SQL_TEXT USING parameter1, paramemter2 RETURNING INTO MSG;
    DBMS_OUTPUT.put_line(MSG);
END;

У вас есть несколько синтаксических ошибок, в том числе:

• = вместо :=
• RETURNING INTO вместо INTO или параметра OUT.
• NUMBER — это ключевое слово, и его нельзя использовать в качестве идентификатора (если только вы не указали его в кавычках, чего у вас нет).
• SUBSTR имеет индекс 1, а не индекс 0 (что не является синтаксической ошибкой, но может привести к тому, что ваш код даст неожиданные результаты).

Вы можете использовать:

DECLARE
    SQL_TEXT VARCHAR2(1000);
    MSG VARCHAR2(500);
    SN VARCHAR2(500) := 'parameter1/parameter2';
BEGIN

sql_text := 'DECLARE
   parameters VARCHAR2(50);
   parameter1 VARCHAR2(50);
   parameter2 VARCHAR2(50);
   sql_txt VARCHAR2(1000);
BEGIN
   parameters := :SN;
   parameter1 := substr(parameters,1,10);
   parameter2 := substr(parameters,12,10);
    
   sql_txt := ''select count(1) from productDetails  where ID = :parameter1 AND value = :parameter2'';
   EXECUTE IMMEDIATE sql_txt INTO :msg USING IN parameter1, IN parameter2;
END;'; 
   
    EXECUTE IMMEDIATE sql_text USING IN SN, OUT MSG;
    DBMS_OUTPUT.put_line(MSG);
END;
/

Что для примера данных:

CREATE TABLE productDetails ( id, value ) AS
SELECT 'parameter1', 'parameter2' FROM DUAL;

Выходы:

1

db‹›fiddle здесь