Morning,

I'm trying to write a script that will convert Unload tables (UNLD to HDL files) creating a flat file using PLSQL. I keep getting syntax errors trying to run it and would appreciate some help from an expert out there!

Here are the errors:

> 
> Error(53,21): PLS-00330: invalid use of type name or subtype name
> 
> 
> Error(57,32): PLS-00222: no function with name 'UNLDTABLE' exists in this scope
> 
> 
> 

Our guess is that the `unldTable` variable is being treated as a String, rather than a database table object (Not really expereinced in PLSQL)

```
CREATE OR REPLACE PROCEDURE UNLD_TO_HDL (processComponent IN VARCHAR2)
IS
fHandle UTL_FILE.FILE_TYPE;
concatData VARCHAR2(240);
concatHDLMetaTags VARCHAR2(240);
outputFileName VARCHAR2(240);
TYPE rowArrayType IS TABLE OF VARCHAR2(240);
rowArray rowArrayType;
emptyArray rowArrayType;
valExtractArray rowArrayType;
hdlFileName VARCHAR2(240);
unldTable VARCHAR2(240);
countUNLDRows Number;
dataType VARCHAR2(240);
current_table VARCHAR2(30);
value_to_char VARCHAR2(240);

BEGIN 

SELECT HDL_FILE_NAME
INTO hdlFileName
FROM GNC_HDL_CREATION_PARAMS
WHERE PROCESS_COMPONENT = processComponent;

SELECT UNLD_TABLE
INTO unldTable
FROM GNC_HDL_CREATION_PARAMS
WHERE PROCESS_COMPONENT = processComponent
FETCH NEXT 1 ROWS ONLY;

SELECT LISTAGG(HDL_META_TAG,'|')
WITHIN GROUP(ORDER BY HDL_META_TAG)
INTO concatHDLMetaTags
FROM GNC_MIG_CONTROL
WHERE HDL_COMP = processComponent;

SELECT DB_FIELD
BULK COLLECT INTO valExtractArray
FROM GNC_MIG_CONTROL
WHERE HDL_COMP = processComponent
ORDER BY HDL_META_TAG;

fHandle := UTL_FILE.FOPEN('./', hdlFileName, 'W');
UTL_FILE.PUTF(fHandle, concatHDLMetaTags + '\n');

SELECT num_rows INTO countUNLDRows FROM user_tables where table_name = unldTable;

FOR row in 1..countUNLDRows LOOP
    rowArray := emptyArrayType;
    FOR value in 1..valExtractArray.COUNT LOOP
        rowArray.extend();
        SELECT data_type INTO dataType FROM all_tab_columns where table_name = unldTable AND column_name = valExtractArray(value);

        IF dataType = 'VARCHAR2' THEN (SELECT valExtractArray(value) INTO value_to_char FROM current_table WHERE ROWNUM = row); 
        ELSIF dataType = 'DATE' THEN (SELECT TO_CHAR(valExtractArray(value),'YYYY/MM/DD') INTO value_to_char FROM current_table WHERE ROWNUM = row);  
        ELSIF dataType = 'NUMBER' THEN (SELECT TO_CHAR(valExtractArray(value)) INTO value_to_char FROM current_table WHERE ROWNUM = row);
        ENDIF;

        rowArray(value) := value_to_char;
    END LOOP;
    concatData := NULL;
    FOR item in 1..rowArray.COUNT LOOP
        IF item = rowArray.COUNT
        THEN concatData := (COALESCE(concatData,'') || rowArray(item));
        ELSE concatData := (COALESCE(concatData,'') || rowArray(item) || '|');
        END IF;
    END LOOP;
    UTL_FILE.PUTF(fHandle, concatData + '/n');
END LOOP;
UTL_FILE.FCLOSE(fHandle);
END; 

```

Thanks,

Adam