PL/SQL function is "stuck"

Question

I have the following simple PL/SQL function, which compiles without any errors if I execute the script. However, when I call the function it gives no output or error and doesn't end.

Function:

CREATE OR REPLACE FUNCTION columns_of_table(table_name_given in varchar) 
    return varchar 
    is
    
    to_return varchar(999) := '';
    CURSOR col_cursor IS SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM user_tab_columns WHERE TABLE_NAME = table_name_given;
    tab_name user_tab_columns.table_name%TYPE;
    col_name user_tab_columns.COLUMN_NAME%TYPE;
    data_type user_tab_columns.data_type%TYPE;      
        
    BEGIN
        OPEN col_cursor;

        LOOP
        FETCH col_cursor into tab_name, col_name, data_type;
        to_return := col_name || ' ' || data_type || ', ';
        END LOOP;
        to_return := SUBSTR(to_return, 1, LENGTH(to_return) - 2);
        CLOSE col_cursor;
        return to_return;
    END;
/

Adding the function and executing it in PLUS SQL:

SQL> @H:\DBI\05\Script.sql

Funktion wurde erstellt.

SQL> select columns_of_table('rezl') from user_tables;

Afterwards nothing happens and no new imput prompt shows up.

Answer 1

You've coded an infinite loop.

Using an explicit cursor to do row-by-row processing is almost always a poor design decision. An implicit cursor has far fewer steps (so far fewer sources of bugs) and is generally much easier to read.

CREATE OR REPLACE FUNCTION columns_of_table(table_name_given in varchar) 
    return varchar 
is
    to_return varchar(999) := '';
BEGIN
    FOR col IN (SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE 
                  FROM user_tab_columns 
                 WHERE TABLE_NAME = table_name_given)
    LOOP
      to_return := col.column_name || ' ' || col.data_type || ', ';
    END LOOP;
    to_return := SUBSTR(to_return, 1, LENGTH(to_return) - 2);
    return to_return;
END;

If you really need to use an explicit cursor for some reason, you'd need to have an explicit exit statement

    LOOP
      FETCH col_cursor into tab_name, col_name, data_type;
      EXIT WHEN col_cursor%NOTFOUND;
      to_return := col_name || ' ' || data_type || ', ';
    END LOOP;

Of course, you could also replace your manual looping with a simpler listagg statement

select listagg( column_name || ' ' || data_type, ', ' )  
  from user_tab_columns 
 where table_name = table_name_given;