CODEWITHSUNDEEP
oracle-databaseplsql
refresh
refresh

Reputation: 1329

PLS 00302 : component DATA_TYPE must de declared in function

I have to following oracle function, build_select, which create a select request. The return value is in the following format:

select col1 ||'|'||col2 ||'|'||col3 from table;

Below is the build_select function:

create or replace FUNCTION build_select (
  p_table_name   IN VARCHAR2
)
    RETURN VARCHAR2
AS
    l_ret   VARCHAR2 (32767);
BEGIN
    FOR eachcol IN (  SELECT column_name, data_type
                           , LEAD (column_name), LEAD (data_type)
                                 OVER (
                                     PARTITION BY table_name ORDER BY column_id
                                 )
                                 next_column
                        FROM all_tab_cols
                       WHERE table_name = p_table_name
                    ORDER BY column_id)
    LOOP
      IF eachcol.data_type = 'CLOB' THEN
        l_ret   := l_ret || dbms_lob.substr( eachcol.column_name, 3000, 1 ) || CASE WHEN eachcol.next_column IS NULL THEN NULL ELSE ' ||''|''||' END;
      ELSE 
        l_ret   := l_ret || eachcol.column_name || CASE WHEN eachcol.next_column IS NULL THEN NULL ELSE ' ||''|''||' END;
      END IF;
    END LOOP;

    IF l_ret IS NULL
    THEN
        raise_application_error (-20001, 'table ' || p_table_name || ' not found');
    END IF;

    l_ret   := 'select ' || l_ret || ' from ' || p_table_name || ';';

    RETURN l_ret;
END build_select;

What I want to do is to test if the data type of the column is CLOB and if so then return it as

dbms_lob.substr( eachcol.column_name, 3000, 1 )

I have added the if else condition in the loop part. But I am getting the error :

PLS 00302 : component DATA_TYPE must de declared.

Any help pls?

I need to do so cause when I am doing a spool of the returned select, it is not returning all the columns cause of the CLOB data type.

Upvotes: 3

Views: 753

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

I think your function should be this:

create or replace FUNCTION build_select (
  p_table_name   IN VARCHAR2
)
    RETURN VARCHAR2
AS
    l_ret   VARCHAR2 (32767);
BEGIN
    FOR eachcol IN (  SELECT column_name, data_type
                        FROM all_tab_cols
                       WHERE table_name = p_table_name
                    ORDER BY column_id)
    LOOP
      IF eachcol.data_type = 'CLOB' THEN
        l_ret   := l_ret || 'dbms_lob.substr( '||eachcol.column_name||', 3000, 1 ),';
      ELSE 
        l_ret   := l_ret || eachcol.column_name||',';
      END IF;
    END LOOP;

    IF l_ret IS NULL
    THEN
        raise_application_error (-20001, 'table ' || p_table_name || ' not found');
    END IF;

    l_ret   := 'select ' || regexp_replace(l_ret, ',$', NULL) || ' from ' || p_table_name || ';';

    RETURN l_ret;
END build_select;

Note, ALL_TAB_COLS selects also system-generated hidden columns and invisible columns which could be a problem. Query ALL_TAB_COLUMNS if you like to filter them.

Upvotes: 1

Related Questions