Need assistance in creating a procedure to load the data into CSV file using plsql

Question

I have a three tables as below.

Create table t1_Fact ( Cur_date Date, Name varchar2(10), Event varchar2(50), Price Number(10,0), TAX Number(10,0), Flag Number );
Create table App_Fact ( Application_ID Number, Application_Name varchar2(100), Application_Price Number, Appliation_Tax Number, Flag Number );
Create table t2 ( Table_Name Varchar2(100), Table_Columns Varchar(100), Table_Measure varchar2(100), t3_columns varchar2(100), t3_measures varchar2(100), t3_Where_Clause varchar2(100) );
Create table t3 ( Cur_date Date, Name varchar2(10), Event varchar2(50), Application_ID Number, Application_Name varchar2(100), Application_Price Number, Appliation_Tax Number, Price Number(10,0), TAX Number(10,0), Flag Number );

table t2 contains all the table names,column names of each source and destination tables and where clause conditions. [t2 Details][1]

Here I need to insert the data from t3 to particular fact table by using group by the column names of fact table, measures and where clause by passing the fact table name as parameter.

Like if we pass t1_Fact table in procedure, we must get all the details from t2 and get the details from t3 and insert into t1_Fact and also save them into CSV file

I have tried the following procedure however I'm not able to insert the data into csv file

Create or Replace Procedure CommonProcedure(sourceTableName IN VARCHAR2) Is
  tablename t2.Table_Name%TYPE;
  destcolumns t2.Table_Columns%TYPE;
  destMeasures t2.Table_Measure%TYPE;
  whereClause t2.t3_Where_Clause%TYPE;
  sourceColumns t2.t3_columns%TYPE;
  sourceMeasures t2.t3_measures%TYPE;
  q1 VARCHAR2(3000 BYTE);
  pathInfo VARCHAR2(3000 BYTE);

   v_file  UTL_FILE.FILE_TYPE;

 Cursor TableName Is  SELECT Table_Name FROM t2;

Begin

--Path will be getting from another table using the function in the format of '/data/Oracle-files/Table_CSV'

pathInfo := getDBConfigParamValue('FILE_LOCATION');

 Open c1;
 Loop
   Fetch TableName Into tablename;
   Exit When TableName%notfound;

    SELECT Table_Columns, Table_Measure, t3_columns, t3_measures INTO destcolumns,destMeasures,sourceColumns,sourceMeasures FROM t2 where Table_Name = tablename;

    q1 := 'INSERT INTO '||tablename||'('||destColumns||','||destMeasures||')'||
        ' ( SELECT '||sourceColumns||','||sourceMeasures||','||sourceTableName
          ||' FROM '||sourceTableName||' GROUP BY '||sourceColumns||')';

    Execute Immediate q1;



    --Need to load the data into  tablename.CSV

    v_file := UTL_FILE.FOPEN('' || pathInfo ||',' ||destinationTableName ||'.csv' || '','W');

    UTL_FILE.PUT_LINE(v_file,'' ||destColumns ||',' ||destMeasures ||'');

    UTL_FILE.FCLOSE(v_file);

 End Loop;

 Close TableName;

End;

When I compile the above procedure getting following error

LINE/COL ERROR
-------- -----------------------------------------------------------------
47/13    PL/SQL: Statement ignored
47/23    PLS-00306: wrong number or types of arguments in call to 'FOPEN'

Please assist me further.

Thanks in advance.

Is there any specific reason you are using `UTL_FILE` for this? There are few other simpler ways to export data to csv in Oracle. https://stackoverflow.com/questions/643137/how-do-i-spool-to-a-csv-formatted-file-using-sqlplus https://stackoverflow.com/questions/4168398/how-to-export-query-result-to-csv-in-oracle-sql-developer — Kaushik Nayak, Jul 03 '18 at 08:52
@KaushikNayak I need to insert in specific path so i'm using UTL_FILE and this is automatic process on daily basis — Ram, Jul 03 '18 at 09:27

score 0 · Answer 1 · answered Jul 03 '18 at 08:27

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

Your fopen call is only passing two arguments; you are concatenating a comma into the first value, rather than using it as an argument separator, so you are passing the constructed string '<pathInfo>,<destinationTableName>.csv' as the location, 'W' as the filename, and no third argument for the open mode.

You can see the problem if I highlight it:

v_file := UTL_FILE.FOPEN('' || pathInfo ||',' ||destinationTableName ||'.csv' || '','W');
                                        ^^^^^^^^

Continuing your pattern of concatenating empty strings (?) you have it the wrong side of a closing quote:

v_file := UTL_FILE.FOPEN('' || pathInfo ||'', destinationTableName ||'.csv' || '','W');

or maybe even:

v_file := UTL_FILE.FOPEN('' || pathInfo ||'', '' || destinationTableName ||'.csv' || '','W');

but you don't need to do all that confusing concatenation, you can just do:

v_file := UTL_FILE.FOPEN(pathInfo, destinationTableName ||'.csv', 'W');

Hi @Alex, Thank you for the help, i'm still getting the error` ORA-29280: invalid directory path ORA-06512: at "SYS.UTL_FILE", line 41 ORA-06512: at "SYS.UTL_FILE", line 478 ORA-06512: at "AMTEL_MIS.CUMULATETMPTABLES_GENERATECSV", line 44 ORA-06512: at line 1 29280. 00000 - "invalid directory path" *Cause: A corresponding directory object does not exist. *Action: Correct the directory object parameter, or create a corresponding directory object with the CREATE DIRECTORY command.` however I have the path as /data/pentahouser/ram/Oracle_Scripts/DBServerScripts/Table_CSV — Ram, Jul 03 '18 at 08:39
That's a completely separate issue. You need to see what `pathInfo` is actually being set to, and if the procedure owner can see a directory with that exact name in `all_directories` and has permissions on that directory - which need to be granted directly to the user, not via a role. — Alex Poole, Jul 03 '18 at 08:55

Need assistance in creating a procedure to load the data into CSV file using plsql

1 Answers1