试图通过传递动态光标来生成导出到csv

Z时代
2024-01-10
分类：问答

我想构建可修复的函数来导出数据表格用户选择任意列的用户选择任意数量的列用户选择csv文件我搜索，发现他们是远离像... link here ...和另一种方式使用我选择的动态光标，但我得到如下所示的错误试图通过传递动态光标来生成导出到csv

我试图使用具有RETURN类型的CURSOR但是，只有通过嵌入式动态OPEN语句，无RETURN类型的REF CURSOR可以OPEN“

NOTE此过程尚未完成，因为I需要制作UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.x);也可以解决我的工作就可以了，但我需要摆脱这种错误的任何建议是非常赞赏

[Error] PLS-00320 (9: 13): PLS-00320: the declaration of the type of this expression is incomplete or malformed 
[Error] PLS-00382 (27: 22): PLS-00382: expression is of wrong type 
[Error] PLS-00320 (29: 29): PLS-00320: the declaration of the type of this expression is incomplete or malformed 
[Error] PLS-00320 (30: 44): PLS-00320: the declaration of the type of this expression is incomplete or malformed 
[Error] PLS-00320 (31: 44): PLS-00320: the declaration of the type of this expression is incomplete or malformed 
[Error] PLS-00320 (32: 44): PLS-00320: the declaration of the type of this expression is incomplete or malformed 
[Error] PLS-00320 (33: 44): PLS-00320: the declaration of the type of this expression is incomplete or malformed 
[Error] PLS-00320 (34: 44): PLS-00320: the declaration of the type of this expression is incomplete or malformed 
[Error] PLS-00320 (35: 44): PLS-00320: the declaration of the type of this expression is incomplete or malformed 
[Error] PLS-00320 (36: 44): PLS-00320: the declaration of the type of this expression is incomplete or malformed

CREATE OR REPLACE FUNCTION EXPORT_TO_CSV_NEW (P_SQL_STMT IN VARCHAR2, 
               P_TABLE_NAME IN VARCHAR2, 
               P_CREATED_BY IN VARCHAR2) RETURN NUMBER IS 
    TYPE CUR_TYPE IS REF CURSOR; --- RETURN WAREHOUSE%ROWTYPE; 
    CUR CUR_TYPE; 
    CUR_RG CUR%ROWTYPE; 
    SQL_STMT VARCHAR2 (4000); 
    FILETYPE UTL_FILE.FILE_TYPE; 
    FILENAME VARCHAR2(256); 
    DIRECTORYNAME VARCHAR2(1280) := 'SRJD_DEFAULT_TEMP_DIR'; 
    ERR_CODE VARCHAR2(64); 
    ERR_MSG VARCHAR2(1024); 
BEGIN 
SAVEPOINT SETP1; 
    FILENAME := TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') || '_WAREHOUSE.CSV'; 
    FILETYPE := UTL_FILE.FOPEN (DIRECTORYNAME,FILENAME,'w',32767); 
    SQL_STMT := P_SQL_STMT; 
    OPEN CUR FOR SQL_STMT; 
    LOOP 
     FETCH CUR INTO CUR_RG; 
      UTL_FILE.PUT (FILETYPE,  CUR_RG.PRODUCT_ID); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.CLASSIFICATION_ID); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.PRODUCT_DESC); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.MANUFACTORY); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.BOX_COUNT); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.BOX_COST); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.BOX_ITEM_COUNT); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.ITEM_COST); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.MEASUREMENT_UNIT); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.REVENUE_PCT); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.ITEM_PRICE); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.TOTAL_ITEM_QUANTITY); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.TOTAL_COST); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.NOTES); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.CREATED_BY); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.CREATED_ON); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.UPDATED_BY); 
      UTL_FILE.PUT (FILETYPE, ',' || CUR_RG.UPDATED_ON); 
      UTL_FILE.NEW_LINE (FILETYPE); 
     EXIT WHEN CUR%NOTFOUND;  
    END LOOP; 
    UTL_FILE.FCLOSE (FILETYPE); 
    CLOSE CUR; 
    RETURN (1); 
EXCEPTION WHEN OTHERS THEN 
    ROLLBACK TO STEP1; 
    ERR_CODE := SQLCODE; 
    ERR_MSG := SUBSTR(SQLERRM, 1, 1024); 
    SQL_STMT := F_INS_ERROR_LOG(SYSDATE,    --P_ERROR_TIME, 
           P_CREATED_BY,   --P_USER_ID, 
           'EXPORT_TO_CSV_NEW',  --P_PROGRAM_UNIT, 
           NULL,     --P_ERROR_LOCATION, 
           'FileName '||FILENAME,--P_KEY_DATA_DESC, 
           ERR_CODE,    --P_ERROR_CODE, 
           ERR_MSG);    --P_ERROR_MSG'; 
RETURN -1; 
END EXPORT_TO_CSV_NEW; 
/

回答：

问题是这条线。

CUR_RG CUR%ROWTYPE;

您试图定义一个记录作为REF CURSOR变量的rowtype - 这是不允许的。

您正在传递查询作为过程参数。因此，它不能被初始化为您procedure.You内光标只能OPEN它作为REF CURSOR.

一种方法来解决，这将是使用查询此过程中申报CURSOR，而不是将其作为参数传递。但是，如果您需要将查询作为参数传递，那么您可以定义一个带有您要获取的所有必需列的RECORD TYPE。

TYPE prodrec IS RECORD ( PRODUCT_ID WAREHOUSE.PRODUCT_ID%TYPE, CLASSIFICATION_ID WAREHOUSE.CLASSIFICATION_ID%TYPE, PRODUCT_DESC WAREHOUSE.PRODUCT_DESC%TYPE, MANUFACTORY WAREHOUSE.MANUFACTORY%TYPE, BOX_COUNT WAREHOUSE.BOX_COUNT%TYPE, BOX_COST WAREHOUSE.BOX_COST%TYPE, BOX_ITEM_COUNT WAREHOUSE.BOX_ITEM_COUNT%TYPE, ITEM_COST WAREHOUSE.ITEM_COST%TYPE, MEASUREMENT_UNIT WAREHOUSE.MEASUREMENT_UNIT%TYPE, REVENUE_PCT WAREHOUSE.REVENUE_PCT%TYPE, ITEM_PRICE WAREHOUSE.ITEM_PRICE%TYPE, TOTAL_ITEM_QUANTITY WAREHOUSE.TOTAL_ITEM_QUANTITY%TYPE, TOTAL_COST WAREHOUSE.TOTAL_COST%TYPE, NOTES WAREHOUSE.NOTES%TYPE, CREATED_BY WAREHOUSE.CREATED_BY%TYPE, CREATED_ON WAREHOUSE.CREATED_ON%TYPE, UPDATED_BY WAREHOUSE.UPDATED_BY%TYPE, UPDATED_ON WAREHOUSE.UPDATED_ON%TYPE );

然后声明这种记录类型的变量。