SP2-0027: Input Is Too Long

If you are attempting to execute DBMS_TTS.TRANSPORT_SET_CHECK over a long list of tablespaces, then you may encounter "SP2-0027: Input Is Too Long".

The solution is to use PL/SQL, where the VARCHAR2 length limit is 32767 (i.e. 32K - 1).

The following example loops through a list of schemas, and, per schema, creates a comma-separated list of all tablespaces, and then executes DBMS_TTS.TRANSPORT_SET_CHECK per schema:

DECLARE
LONG_VARCHAR2 VARCHAR2(32767);

BEGIN

    FOR X IN
    (
    SELECT DISTINCT TABLESPACE_NAME
      FROM DBA_SEGMENTS
     WHERE OWNER IN ('SCOTT','FRED','MEGA_APP')
    )
    LOOP
        LONG_VARCHAR2 := LONG_VARCHAR2||X.TABLESPACE_NAME||',';
    END LOOP;

-- remove the final comma
LONG_VARCHAR2 := SUBSTR(LONG_VARCHAR2,1,LENGTH(LONG_VARCHAR2)-1);

-- do the work
DBMS_TTS.TRANSPORT_SET_CHECK('' || LONG_VARCHAR2 || '', TRUE);

-- show the list of tablespaces we just worked on
DBMS_OUTPUT.PUT_LINE(LONG_VARCHAR2);
END;
/