Today I have code I get error

PLS-00306: wrong number or types of arguments in call to

and I make sure the code working fine but when I use in dynamic code not worked

CREATE OR REPLACE PROCEDURE TB_DATA(P_TB_NAME VARCHAR2)
IS
 V_COLUMNS VARCHAR2(32000) := GET_ALL_COLUMNS(P_TB_NAME) ;
 V_COLUMNS_IN_LOOP VARCHAR2(32000) :=  'I.'||REPLACE ( GET_ALL_COLUMNS(P_TB_NAME) , ',' , '||'',''||I.') ;
 V_FILE UTL_FILE.FILE_TYPE ;
BEGIN

  EXECUTE IMMEDIATE 'DECLARE
  CURSOR C1 IS 
            SELECT '||V_COLUMNS || ' 
            FROM '||P_TB_NAME || ';
               
BEGIN 
' ||V_FILE ||' := UTL_FILE.FOPEN ( ''MY_DIR'' , ''TASK9.CSV'' ,   ''W'' ) ;
    UTL_FILE.PUT_LINE ( '||V_FILE ||' , '|| V_COLUMNS||');
     FOR I IN C1 LOOP 
     UTL_FILE.PUT_LINE ( '||V_FILE ||', '|| V_COLUMNS_IN_LOOP||') ;
     END LOOP ; 
END ;';

END ;

Function:

CREATE OR REPLACE FUNCTION HR.GET_ALL_COLUMNS (P_TABLE VARCHAR2)
RETURN VARCHAR2
IS
  CURSOR C IS 
            SELECT *
            FROM USER_TAB_COLUMNS
            WHERE TABLE_NAME = P_TABLE ;
  CURSOR C1 IS           
            SELECT *
            FROM TAB 
            WHERE TNAME = P_TABLE ;
            
            

V_COLS VARCHAR2(32000); 
V_COLS2 VARCHAR2(32000);       
BEGIN
  FOR I IN C LOOP 
V_COLS :=  V_COLS ||','|| I.COLUMN_NAME  ;
  END LOOP ;
   FOR V IN C1 LOOP 
   V_COLS2  := V_COLS2 ||','|| P_TABLE ;
   END LOOP ;
  
 
  RETURN LTRIM( V_COLS , ',') ;

END ;

I just need to know where is my mistake or what is missing.

CodePudding user response：

The function is OK; it is procedure that fails because you have to declare V_FILE within the dynamic SQL part of code, not outside of it. Something like this:

SQL> CREATE OR REPLACE PROCEDURE TB_DATA (P_TB_NAME VARCHAR2)
  2  IS
  3     V_COLUMNS          VARCHAR2 (32000) := GET_ALL_COLUMNS (P_TB_NAME);
  4     V_COLUMNS_IN_LOOP  VARCHAR2 (32000)
  5        := 'I.' || REPLACE (GET_ALL_COLUMNS (P_TB_NAME), ',', '||'',''||I.');
  6     L_STR              VARCHAR2 (10000);
  7  BEGIN
  8     L_STR :=
  9           'DECLARE
 10    V_FILE UTL_FILE.FILE_TYPE;
 11    CURSOR C1 IS
 12              SELECT '
 13        || V_COLUMNS
 14        || '
 15              FROM '
 16        || P_TB_NAME
 17        || ';
 18  BEGIN
 19       V_FILE := UTL_FILE.FOPEN ( ''MY_DIR'' , ''TASK9.CSV'' ,   ''W'' ) ;
 20       FOR I IN C1 LOOP
 21       UTL_FILE.PUT_LINE ( V_FILE, '
 22        || V_COLUMNS_IN_LOOP
 23        || ') ;
 24       END LOOP ;
 25       UTL_FILE.FCLOSE(V_FILE);
 26  END ;';
 27
 28     EXECUTE IMMEDIATE L_STR;
 29  END;
 30  /

Procedure created.

Testing:

SQL> EXEC TB_DATA('DEPT');

PL/SQL procedure successfully completed.

SQL>