0

I am accepting the table name and the export type from the user inside the procedure and calling the script inside the procedure. The script will accept the table name as a parameter.

------SQL SCRIPT----- edit csvfilename

     set heading off
spool myfile.csv
select * from &1;
set colsep ','
spool off;

-----STORED PROCEDURE---------

SQL> CREATE OR REPLACE PROCEDURE Export_Table
  2  (
  3     tbl_nme IN varchar2,
  4          export_type IN varchar2
  5  )
  6  AS
  7  BEGIN
  8  IF export_type = 'csv' THEN
  9  EXECUTE IMMEDIATE '@csvfilename.sql tbl_name';
 10  END IF;
 11  END Export_Table;
 12   /

Procedure created.
 /

------ERROR----

SQL> EXECUTE Export_Table('Mail','csv');
BEGIN Export_Table('Mail','csv'); END;

ERROR at line 1:
ORA-00900: invalid SQL statement
ORA-06512: at "TESTADMIN.EXPORT_TABLE", line 9
ORA-06512: at line 1

Any help would be appreciated.

Dillano Blake
  • 27
  • 1
  • 5
  • 2
    [`@` is an SQL\*Plus client command](http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve002.htm), not SQL - so you just can't use it from PL/SQL like that (or even in most other clients). PL/SQL also can't see files on your client machine. Why are you using PL/SQL at all here? – Alex Poole Apr 09 '17 at 19:56
  • @AlexPoole is there another way to export without using UTL_FILE or using UTL_FILE without specifying the columns of the table I want to export – Dillano Blake Apr 09 '17 at 21:58
  • Not simply, and the file it writes will be on the server not you client machine. Why do you need a procedure, why not just run your script from the SQL\*Plus command line - instead of calling `exec`? In other words, what problem are you really trying to solve with your procedure? – Alex Poole Apr 09 '17 at 22:03
  • @AlexPoole because this is for a project and it specifically requires it to be done in a procedure. – Dillano Blake Apr 09 '17 at 22:20
  • You'd have to use dynamic SQL and the `dbms_sql` package, [which isn't trivial](http://stackoverflow.com/a/30399491/266304) - it seems a bit of leap from what you've done so far. But without knowing exactly what you've been asked to do, no idea if that is what is actually required. (Maybe you're supposed to use data pump;maybe the table structure is always the same; who knows...) – Alex Poole Apr 10 '17 at 08:49

0 Answers0