My Mistakes

Mistake1: Not using / at the end of script while running pl-sql script on sqlplus.

It took me almost half day that why my script giving different different errors while running but not returning output.

I was running below code:

spool C:\Users\anandy\Scripts\out.txt
select * from dual;
select * from emp;

spool off;

But it should be(Notice / at the end of the script)

spool C:\Users\anandy\Scripts\out.txt
select * from dual;
select * from emp;

spool off;
/

Due to this script giving following errors

SQL> @C:/Users/anandy/Scripts/EvenOdd.sql
4
5
6
.
.
.

And I had to close sqlplus to come out of it.

I could have used / either in the script or after running it.

Also it was returning some weird results


Mistake2: Giving spool off; before terminating pl-sql code block.SPOOL is SQL*Plus command and can not be used in PL/SQL. So, either use SPOOL outside of PL/SQL or use UTL_FILE

SQL> @C:/Users/anandy/Scripts/Exception.sql
 14  /
spool off;
*
ERROR at line 13:
ORA-06550: line 13, column 1:
PLS-00103: Encountered the symbol "SPOOL"

Incorrect use:

---set heading off lin 3000 pagesize 0;

spool C:\Users\anandy\Scripts\out.txt
select * from dual;
select * from emp;
spool off;
/

Correct use:

---set heading off lin 3000 pagesize 0;

spool C:\Users\anandy\Scripts\out.txt
select * from dual;
select * from emp;
/
spool off;

Mistake3: Using + as string concatenation.
Example:
dbms_output.put_line('Say HI to '+a+' ');

But it should be always in oracle.
dbms_output.put_line('Say HI to '||a||' ');