SET SERVEROUTPUT ON;

DECLARE
    TYPE d_col_name IS
        TABLE OF odi_work_repo_dev_d.snp_col.col_name%TYPE;
    TYPE d_source_dt IS
        TABLE OF odi_work_repo_dev_d.snp_col.source_dt%TYPE;
    TYPE d_longc IS
        TABLE OF odi_work_repo_dev_d.snp_col.longc%TYPE;
    TYPE d_scalec IS
        TABLE OF odi_work_repo_dev_d.snp_col.scalec%TYPE;
    CURSOR c_daten IS SELECT
        col_name,
        source_dt,
        longc,
        scalec
                      FROM
        odi_work_repo_dev_d.snp_col
                      WHERE
        i_table = 7931
    ORDER BY
        pos;

    t_col_name    d_col_name;
    t_source_dt   d_source_dt;
    t_longc       d_longc;
    t_scalec      d_scalec;
BEGIN
    OPEN c_daten;
    FETCH c_daten BULK COLLECT INTO t_col_name,t_source_dt,t_longc,t_scalec;
    FOR i IN t_col_name.first..t_col_name.last LOOP
        IF
            t_source_dt(i) = 'DECIMAL'
        THEN
            dbms_output.put_line('"C'
            || substr(t_col_name(i),2,length(t_col_name(i) ) - 1)
            || '" NUMBER('
            || t_longc(i)
            || ','
            || t_scalec(i)
            || '),');

        ELSE
            dbms_output.put_line('"C'
            || Substr(T_Col_Name(I),2,Length(T_Col_Name(I) ) - 1)
            || '" VARCHAR2('
            || T_Longc(I)
            || ' CHAR),');
        END IF;
    END LOOP;

    CLOSE c_daten;
END;