Having Issue spooling data to CSV file

Question

I am trying to spool data using the sql script shown below creating a file without data where it has 3 rows of data when ran select statement.

  SPOOL "C:\Users\nmirza\Desktop\\YourFile.csv" REP

  SELECT * FROM  SATURN.SPRIDEN A1
  WHERE
    A1.SPRIDEN_PIDM = 3075205;
  /
  SPOOL OFF;
  /

Is someone can help me why I am not getting any data in CSV file?

  SPOOL "C:\Users\nmirza\Desktop\\YourFile.csv" REP

    SELECT * FROM  SATURN.SPRIDEN A1
    WHERE
   A1.SPRIDEN_PIDM = 3075205;
 /
 SPOOL OFF;

/

Answer 1

That code should create a CSV file. I don't have your table, but I tested it on mine, more or less literally typing what you did:

SQL> spool "C:\temp\\yourfile.csv" REP
SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> spool off;
SQL> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

type command lists contents of the file:

SQL> $type "C:\temp\\yourfile.csv"

This is the result - as you can see, everything is here:

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> spool off;

SQL>

---

If you wonder why there's triplicate of data in SQLPlus (and duplicate in CSV file), that's because you misused slash. In SQLPlus, it means "re-execute the last statement", which - applied to your code - does this:

SELECT * FROM  SATURN.SPRIDEN A1
  WHERE
    A1.SPRIDEN_PIDM = 3075205;      --> semi-colon terminates statement and executes it
  /                                 --> this slash re-executes previous SELECT
  SPOOL OFF;
  /                                 --> this re-executes it again (but it 

isn't contained in CSV file because of spool off)

---

How come YOU don't see anything in CSV file? I wouldn't know. You tagged the question with PLSQL tag, but that's hardly true. spool works in SQL*Plus and has nothing to do with PL/SQL. I removed that tag and put another ones that seem to be more appropriate.

Therefore, could you explain what you exactly did? Which tool do you use? Post a screenshot if you think it might help us help you.