oracle stored proc

create or replace PROCEDURE CreateAliasPaths AS
        pos1 NUMBER:=0;
        pos2 NUMBER:=0;
        origName VARCHAR2(4000);
        origSearchStr VARCHAR2(4000);
        aliasName VARCHAR2(4000);
        aliasPath VARCHAR2(4000);
       
BEGIN
    dbms_output.put_line ('------------ CreateAliasPaths Beginn:' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));

    FOR r IN (SELECT * FROM Table0) --for testing: where rownum<=3
    LOOP
        pos1:=INSTR(r.ALIAS,' ');
        origName:=SUBSTR(r.ALIAS,1, pos1-1); 
        origSearchStr:='%' || origName  || '%';
        pos2:=INSTR(r.ALIAS,' ',pos1+1);
        if (0=pos2) THEN
            pos2:=LENGTH(r.ALIAS);
        END IF;
        LOOP
            aliasName:=SUBSTR(r.ALIAS,pos1+1, pos2-pos1-1); 
            --dbms_output.put_line('AliasTabelle:' || r.ALIAS);
            --dbms_output.put_line('originalName:' || origName || ',' || pos1 || ' ' || pos2 || ' aliasName:>' || aliasName || '<' );

            FOR rRepro IN (SELECT * FROM table1 WHERE LOWER(PATH) like LOWER(origSearchStr))
            LOOP
                aliasPath:=REPLACE(rRepro.PATH, origName, aliasName);
                --dbms_output.put_line('aliasPath:' || aliasPath );
                UPDATE table1 SET ALIAS=rRepro.ALIAS || ' ' || aliasPath WHERE table1ID=rRepro.table1ID;
            END LOOP;
            --dbms_output.put_line( '-------------------------------------------------' );
            pos1:=pos2;
            EXIT WHEN (pos1>=LENGTH(r.ALIAS));
            pos2:=INSTR(r.ALIAS,' ',pos1+1);
            if (0=pos2) THEN
                pos2:=LENGTH(r.ALIAS);
            END IF;
         END LOOP;   
         --dbms_output.put_line( '=============================================================================' );
    END LOOP;
    COMMIT;   
    dbms_output.put_line ('------------ CreateAliasPaths End:' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END CreateAliasPaths;

Oracle Sql Developper Debuggen:

Stored Procedure rechte Maustaste Debuggen, dann für Debug kompelieren
Breakpoint durch Click in linke breite Leiste

Rechte zum Oracle Debuggen:

grant debug connect session to myDebugUser;
grant debug any procedure to myDebugUser;
-- Wenn: ORA-24247: Netzwerkzugriff von Zugriffskontrollliste abgelehnt
grant execute on DBMS_DEBUG_JDWP to myDebugUser;

oracle substr stored proc

create or replace
PROCEDURE TESTHM AS
BEGIN
    dbms_output.put_line ('TestHm');
    DECLARE str VARCHAR2(4000);
    BEGIN
        FOR r IN (SELECT * FROM myTable where rownum<=5)
        LOOP
            str:=SUBSTR(r.myStrField,1, 10);     
            dbms_output.put_line( str );
            dbms_output.put_line( r.myStrField);
        END LOOP;
    END;   

    dbms_output.put_line (CURRENT_DATE);
END TESTHM;

oracle stored procedure loop over records (withouth cursor)

create or replace
PROCEDURE TESTHM AS
BEGIN
    dbms_output.put_line ('TestHm');

    FOR r IN (SELECT * FROM myTable)
    LOOP
        dbms_output.put_line( r.myField );
    END LOOP;

    dbms_output.put_line (CURRENT_DATE);
END TESTHM;

set serveroutput on;     
execute testhm;