How to generate a spool CSV file with current date in windows envrionment discarding the select statement.

Hi Friends,

Sometimes we face problem in generating the CSV spool file which contains only records based on the filter condition on the select statement.

Basically we face this problem in Windows environment because whatever we mention after the set trimspool on , the same is copied to the CSV file also including the select statement and no of rows selected etc.

So to avoid such issue please proceed as mentioned below.


PROMPT
PROMPT *** my_script.sql *** START ***
PROMPT
set termout off
set echo off
set verify off
set heading off
set lines 420
set trimspool on
set pagesize 0
Set feedback off
col dcol new_value mydate noprint
select to_char(sysdate,'YYYY-MM-DD') dcol from dual;
spool C:\&mydate._MY_SPOOL_FILENAME.csv
select 'INIT,'|| e.employee_id ||','|| e.department_id||','||d.manager_id||','|| d.department_id
from employees e, departments d 
where e.department_id = d.department_id 
order by 1
/
spool off
set echo on
set termout on
PROMPT
PROMPT *** my_script.sql *** END ***



Cheers
Rajani

Re-generating .fmx in UNIX Platform

Move the form from desktop to server ($AU_TOP/forms) in binary mode and also generate the .fmx file.

Connect to Putty and run the below code.

F60gen module='/apps/aptest/visappl/au/11.5.0/forms/US/NA_QUERY_ONLY_FORM.fmb' module_type=form userid=apps/apps compile_all=special output_file='/apps/aptest/visappl/xxcus/11.5.0/forms/US/NA_QUERY_ONLY_FORM.fmx'

Moving Forms Applications From One Platform To Another

For this example the assumption is that the "development" machine is a MS Windows platform and the "deployment" machine is a Unix platform.
IMPORTANT: FMX, MMX, and PLX files are NOT portable between platforms. They are also not portable between major versions. For example, you cannot run a 9.0.4 FMX in a 10.1.2 environment. Source code (FMB, MMB, PLL) must be recompiled when moving from one platform and/or major version to another. This includes moving from one Unix platform to another (i.e. Sun Solaris to Linux) or one Windows platform version to another.
( FRM-40031: File xxxxx.fmx is not a Forms Runtime File.)
Failure to correctly compile source files on the machine where they are being deployed will cause instability or prevent the application from running or may cause trigger code to not fire.
Requirements for "deployment" machine:
Log-in access as the "oracle" user
X-session (GUI)
It is NOT supported to perform the following via a remote session. 

1. Verify that the deployment environment has been correctly installed and configured as documented in the product Installation Guide.

2. Create a staging directory where the application source files (FMB, MMB, PLL, OLB) can be stored permanently or temporarily. This directory will also be where the compiled executables (FMX, MMX, PLX) will be created. For example:
mkdir /u02/oracle/ias904_mid/forms90/myApplication

3. Copy/transfer all of the Forms components which make up the application to the directory created in step 2. These will include FMB, MMB, PLL and OLB files.
IMPORTANT: Unix is a case sensitive operating system. Be sure that any references to files within your application have been corrected to match files on the new file system. This will usually impact references to image (icon) files, other forms, menus, and libraries.

4. Copy/transfer all other application files as needed. These files may include custom resource (.res), image or custom JARs files. These files will need to be placed in specific locations based on how you developed the application. Refer to the product documentation for more details.
IMPORTANT: If transferring using FTP, all files must be transferred in binary mode.
The following steps must be performed from the local "deployment" machine. Performing these steps remotely is not supported. 

5. Open a shell session.

6. Set the ORACLE_HOME variable and point it to the Application Server installation. Be sure to use the appropriate syntax for the particular shell you are using. For example in csh the command would look something like this:
export ORACLE_HOME=/u02/oracle/ias904_mid

7. Set the FORMS90_PATH variable to the directory which was created in step 2. For example:
export FORMS90_PATH=/u02/oracle/ias904_mid/forms90/myApplication
Additional variables may be necessary or desired based on your needs and system configuration. Here are a few examples:
TNS_ADMIN
NLS_LANG
CLASSPATH
TERM
DISPLAY
In most cases, setting these will not be necessary if you use the provided script (.sh file) noted in the next step.
When compiling a Forms application it is important to understand the application. Most important is to understand the dependencies which may exist between components. In other words, for example you will not be able to compile an FMB if it has a dependant PLL which has not yet been compiled. In most cases the order in which compiling should occur is as follows:
1. PLL
2. MMB
3. FMB
There are exceptions, but this will work in most cases.

8. Using the compiler, generate "X" files for all of the application's binaries (PLL, MMB, FMB). The command will be something like the following:
f90genm.sh module=myForm module_type=form compile_all=yes userid=scott/tiger@orcl
Other possible module_type values:
library
menu
form
Addition compiler options can be found in the Forms Builder online help. For Forms 9.0.4, the Builder help is also available online at:http://www.oracle.com/forms/10g/help/
Click on Reference then Form Compiler Options
NOTE: In Forms 10.1.2.0.2 and newer, the script names have changed. f90genm.sh is now frmcmp.sh and f90gen.sh is now frmcmp_batch.sh. Also, FORMS90_PATH is now FORMS_PATH. Please refer to the product documentation for more details.

9. Once all of the executables have been generated, the path to these files will need to be included in the Forms deployment environment. In most cases this will be the FORMS90_PATH value in the Forms env file, "default.env"

How to limit a column in oracle to support only character

The below example shows test_name column can't take NUMBER values.


CREATE TABLE test_1
(
  test_id number,
  test_name varchar(50),
  CONSTRAINT
    name_test
    CHECK (
      regexp_like(test_name,'^([aA-zZ])+$')
    )
);

Table created.


SQL> insert into test_1 (test_id,test_name) values (200,'Akash')
  2  /

1 row created.

SQL> insert into test_1 (test_id,test_name) values (200,'Akash1');
insert into test_1 (test_id,test_name) values (200,'Akash1')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.NAME_TEST) violated

--------------------------------------------****************************--------------------------



Secondly, If we want to change all the column values to UPPER-CASE ,  this can be done as follows.

SQL> select * from test_1;

   TEST_ID TEST_NAME
---------- ------------------------------------------------
       200 Akash

SQL> UPDATE test_1
  2  set    test_name= upper(test_name);

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from test_1;

   TEST_ID TEST_NAME
---------- ------------------------------------------------
       200 AKASH

Master-Detail Relationship in Oracle forms Blocks

First create Master data Block , then detail data block.

From HR schema  : Master Data Block is Departments and Detail Data Block is Employees.

Layout Style 'Form' for Departments Block with 1 record displayed.

Layout Style 'Tabular' for Employees  Block with 5 record displayed.


Setting Relations: ( During Layout Wizard) 

( At runtime the window will look like the above )



By Default the in the Relation -Delete Record Behaviour property is Non-Isolated.

And likely below ON-POPULATE-DETAILS and ON-CHECK-DELETE-MASTER triggers gets automatically generated under the master data block.

If we will change the Delete Record Behaviour property , the triggers will automatically change accordingly.

1. Non-Isolated :( Triggers are automatically generated under the master data block)

a) ON-POPULATE-DETAILS
b) ON-CHECK-DELETE-MASTER

2. Cascading :( Triggers are automatically generated under the master data block)

a) ON-POPULATE-DETAILS
b) PRE-DELETE 

3. Isolated :( Triggers are automatically generated under the master data block)

a) ON-POPULATE-DETAILS






What does the above three properties mean basically ?


Non-Isolated : Prevents deletion of master record if detail record is present.

Cascading : Deletes the detail record once master record is deleted.

Isolated : Only deletes the master record.



Hope this helps.


Cheers
Rajani

Oracle session waiting , enque event , locked session , session killing

1. 
-- Title 'ENUQUE wait events sessions more than 30 minutes '

SELECT vsw.event, vsw.seconds_in_wait "Wait(Secs)", vs.sid, vsa.sql_text
FROM v$session vs,
v$session_wait vsw, 
v$sqlarea vsa
WHERE vs.sid = vsw.sid
AND vs.sql_hash_value = vsa.hash_value
AND lower(vsw.event) LIKE '%enqueue%'
AND vsw.seconds_in_wait > 1800
/

2. 
-- Title 'OTHER wait events sessions more than 30 minutes '
SELECT vs.username,vsw.event, vs.logon_time, vsw.seconds_in_wait "Wait(Secs)", vs.sid,o.object_name,vl.block,vl.type
FROM v$session_wait vsw,
v$session vs,
v$locked_object vlo,
dba_objects o,
v$lock vl
WHERE vsw.seconds_in_wait > 1800
AND vl.block <> 0
AND vs.sid = vsw.sid
AND vlo.session_id = vsw.sid
AND o.object_id = vlo.object_id
AND vl.sid = vsw.sid
/

3.-- Which session is blocking what session. 
-- 
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

4. To Kill a session:

SQL> select USERNAME,SID,SERIAL#,STATUS from v$session
 where USERNAME='USERNAME'; 

-- user name will start from OPS$ if configured in Unix system


USERNAME      SID       SERIAL#            STATUS
------------------------------ ---------- -           --------- --------
USERNAME     66            7730              INACTIVE


SQL> alter system kill session '66,7730';


5. Session information and lockwait:


SELECT NVL(s.username, '(oracle)') AS username,
       s.osuser,
       s.sid,
       s.serial#,
       p.spid,
       s.lockwait,
       s.status,
       s.module,
       s.machine,
       s.program,
       TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM   v$session s,
       v$process p
WHERE  s.paddr = p.addr

ORDER BY s.username, s.osuser;

ORA-00980: synonym translation is no longer valid

"ORA-00980: synonym translation is no longer valid" is a common error encountered in a development environment. This can happen for many reasons.

Some of them are:

1. You created a synonym on non-existing object by mistake.
• For example, you created a synonym on SCOTT.DEPT where either the SCOTT schema in not present or the DEPT table is missing.
2. You dropped an object but you did not drop the synonyms referencing the object.
3. You dropped a user, but you did not drop synonyms referencing the objects owned by that user.

When an object is dropped, synonyms referring to the object are not dropped. The following script lists all such invalid synonyms:

select * from dba_synonyms s

where table_owner not in('SYSTEM','SYS')

and db_link is null

and not exists

(select 1

from dba_objects o

where s.table_owner=o.owner

and s.table_name=o.object_name);

The following script generates DDL to drop synonyms whose translation is no longer valid. USE WITH CAUTION.

rem 

rem  Exludes SYS and SYSTEM users

rem

select 'drop '||decode (s.owner,'PUBLIC','PUBLIC SYNONYM ',

'SYNONYM'||s.owner||'.')||s.synonym_name||';'

from dba_synonyms  s

where table_owner not in('SYSTEM','SYS')

and db_link is null

and not exists

     (select  1

      from dba_objects o

      where s.table_owner=o.owner

      and s.table_name=o.object_name)

/

Cheers

Rajani

summit office supply schema

Hi Friends,

While practicing oracle forms , you may require summit office supply schema .

Below is the link  for the schema and also the way you have to import the script to the local DB.


Steps:

 1. 1st download the zip file to your D: drive from following URL.

  https://docs.google.com/file/d/0B0ES7eZT7FMZbnNsSWZDVXNyREU/edit?usp=sharing

summit10gr2-128838.zip : From File Menu click download ( Ctrl+S) : From google doc/drive and save it in your D:\drive.
 2.  Extract summit folder to D:\ drive.
 3. Then import the sql script from DMP files from command prompt.

C:\Documents and Settings\mohantyr>d:
D:\>cd summit
D:\Summit>imp userid=rajani/rajani@sys file=summit.dmp full=y
Import: Release 10.1.0.4.2 - Production on Fri Jul 26 19:39:32 2013
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by SUMMIT, not by you
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing SUMMIT's objects into RAJANI
IMP-00008: unrecognized statement in the export file:
. . importing table                            "A"          0 rows imported
. . importing table                        "ACCTS"          5 rows imported
. . importing table                   "ACCT_ADDRS"          5 rows imported
. . importing table                         "BLOB"          0 rows imported
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "BONUS" ("ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "SAL" NUMBER"
 ", "COMM" NUMBER)  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INI"
 "TIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)              "
 "       LOGGING NOCOMPRESS"
. . importing table              "CALENDAR_RANGES"         44 rows imported
. . importing table                   "CHESS_SAVE"          0 rows imported
. . importing table        "CHESS_SAVE_BOARDSTATE"          0 rows imported
. . importing table          "CHESS_SAVE_CAPTURED"          0 rows imported
. . importing table            "CHESS_SAVE_PLAYER"          0 rows imported
. . importing table                       "CITIES"        205 rows imported
. . importing table              "COMPANY_SUMMARY"          3 rows imported
. . importing table                     "CONCLASS"          9 rows imported
. . importing table                     "CONLABEL"          6 rows imported
. . importing table                   "CONTAINERS"        169 rows imported
. . importing table                     "CUSTOMER"          9 rows imported
. . importing table                    "CUSTOMERS"         14 rows imported
. . importing table                         "DAYS"          7 rows imported
. . importing table              "DB_STATE_SUMMIT"         18 rows imported
. . importing table                        "DUMMY"          1 rows imported
. . importing table                    "FLRPEOPLE"         65 rows imported
. . importing table                        "FUNDS"          5 rows imported
. . importing table                 "FUND_CONTRIB"         16 rows imported
. . importing table                    "FUND_XACT"         45 rows imported
. . importing table                    "F_EMPCOMP"          2 rows imported
. . importing table                  "F_XACT_TYPE"          7 rows imported
. . importing table               "GAME_SEMAPHORE"          0 rows imported
. . importing table                  "GRID_LABELS"         10 rows imported
. . importing table                       "INDCAT"         11 rows imported
. . importing table                      "INVINFO"          5 rows imported
. . importing table                   "INVREQUEST"         12 rows imported
. . importing table                         "ITEM"         64 rows imported
. . importing table                     "NEWS_LET"         13 rows imported
. . importing table                          "ORD"         21 rows imported
. . importing table                      "ORDPICT"         21 rows imported
. . importing table                       "O_DEPT"          4 rows imported
. . importing table                        "O_EMP"         14 rows imported
. . importing table             "PERF_RES_MONITOR"         51 rows imported
. . importing table                     "PORTDESC"          5 rows imported
. . importing table                    "PORTFOLIO"         39 rows imported
. . importing table                        "PRICE"         17 rows imported
. . importing table                      "PRODUCT"         10 rows imported
. . importing table                     "PRODUCT1"         70 rows imported
. . importing table                     "PRODUCT2"         71 rows imported
. . importing table                   "SALES_FACT"      34815 rows imported
. . importing table                "SALES_REVENUE"         16 rows imported
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "SALGRADE" ("GRADE" NUMBER, "LOSAL" NUMBER, "HISAL" NUMBER)  P"
 "CTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELIST"
 "S 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)                     LOGGING NOCO"
 "MPRESS"
. . importing table                       "STOCKS"        328 rows imported
. . importing table                "STOCK_HISTORY"         11 rows imported
. . importing table                        "STORE"         20 rows imported
. . importing table                   "S_CUSTOMER"         15 rows imported
. . importing table                       "S_DEPT"         12 rows imported
. . importing table                        "S_EMP"         25 rows imported
. . importing table                      "S_IMAGE"         28 rows imported
. . importing table                  "S_INVENTORY"        114 rows imported
. . importing table                       "S_ITEM"      12509 rows imported
. . importing table                 "S_ITEM_AUDIT"    1798249 rows imported
. . importing table                   "S_LONGTEXT"         33 rows imported
. . importing table                        "S_ORD"       3725 rows imported
. . importing table                    "S_PRODUCT"         33 rows imported
. . importing table                     "S_REGION"          5 rows imported
. . importing table                      "S_TITLE"          8 rows imported
. . importing table                  "S_WAREHOUSE"          5 rows imported
. . importing table                        "TIMES"        731 rows imported
. . importing table                       "TRIVIA"         60 rows imported
. . importing table                      "TVTABLE"         25 rows imported
. . importing table                          "USA"         50 rows imported
. . importing table        "VIDEO_CATEGORY_BY_QTR"        640 rows imported
. . importing table                 "VID_SUMM_ALL"          0 rows imported
. . importing table            "VID_SUMM_DEPT_REG"          0 rows imported
. . importing table                  "VID_SUMM_YR"          0 rows imported
. . importing table            "VID_SUM_DEPTREGYR"          0 rows imported
. . importing table                      "VIEWDEF"          3 rows imported
. . importing table                   "VIEWDEFCOL"         17 rows imported
. . importing table                 "WORLD_CITIES"         68 rows imported
About to enable constraints...
Import terminated successfully with warnings.
D:\Summit>

What is difference between View and Materialized View in Oracle ?

Difference between View and Materialized view is one of the popular SQL interview question, much like truncate vs delete

, correlated vs noncorrelated subquery or primary key vs unique key. This is one of the classic question which keeps appearing in SQL interview now and then and you simply can’t afford not to learn about them. Doesn’t matter if you are a programmer, developer or DBA, this SQL questions is common to all. Views allows a level of separation than original table in terms of access rights but it always fetch updated data. Let’s see What is View in database, What is materialized View and difference between view and materialized view in database. 　
What is View in database
Views are logical virtual table created by "select query"
but the result is not stored anywhere in the disk and every time we need to fire the query when we need data, so always we get updated or latest data from original tables. Performance of the view depend upon our select query. If we want to improve the performance of view we should avoid to use join statement in our query or if we need multiple joins between table always try to use index based column for joining as we know index based columns are faster than non index based column. View allow to store definition of the query in the database itself.

What is Materialized View in database Materialized views are also logical view of our data driven by select query but the result of the query will get stored in the table or disk, also definition of the query will also store in the database .When we see the performance of Materialized view it is better than normal View because the data of materialized view will stored in table and table may be indexed
so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

Difference between View vs Materialized View in databaseBased upon on our understanding of View and Materialized View, Let’s see, some short difference between them :
1) 1st difference is that, In Views query result is not stored in the disk or database but Materialized view allow to store query result in disk or table.
2) 2nd difference is that, when we create view using any table, rowid of view is same as original table but in case of Materialized view rowid is different.
3) 3rd difference is that , In case of View we always get latest data but in case of Materialized view we need to refresh the view for getting latest data.
4) 4th difference is that , Performance of View is less than Materialized view.
5) 5th difference is that, In case of view its only the logical view of table no separate copy of table but in case of Materialized view we get physically separate copy of table
6) 6th and last difference is that , In case of Materialized view we need extra trigger or some automatic method so that we can keep MV refreshed, this is not required for views in database.

When to Use View vs Materialized View in SQL Mostly in application we use views because they are more feasible, only logical representation of table data no extra space needed. We easily get replica of data and we can perform our operation on that data without affecting actual table data but when we see performance which is crucial for large application they use materialized view where Query Response time matters so Materialized views are used mostly with data ware housing or business intelligence application.

That’s all on difference between View and materialized View in database or SQL.

Setting default boot time out in Solaris 10 and later

To locate the menu.lst file, type:


# /sbin/bootadm list-menu
The location and contents of the file are displayed.

The location for the active GRUB menu is: /boot/grub/menu.lst
default 0
timeout 10
0 Solaris
1 Solaris failsafe
2 second_disk
3 second_disk failsafe

edit this file in  /boot/grub/menu.lst.


To edit time out from 10 sec to 1 Sec


login as: root
Using keyboard-interactive authentication.
Password:
Last login: Sun Jun 30 14:56:50 2013 from 192.168.1.18
Sun Microsystems Inc.   SunOS 5.10      Generic January 2005
You have new mail.
ROOT@ORASUN02$ /sbin/bootadm list-menu
The location for the active GRUB menu is: /boot/grub/menu.lst
default 0
timeout 1
0 Solaris 10 10/09 s10x_u8wos_08a X86
1 Solaris failsafe
ROOT@ORASUN02$


Cheers
Rajani

Diffrence between %type and %rowtype

-- %TYPE is used to declare a field with the same type as
-- that of a specified table's column:

DECLARE
   v_EmpName  emp.ename%TYPE;
BEGIN
   SELECT ename INTO v_EmpName FROM emp WHERE ROWNUM = 1;
   DBMS_OUTPUT.PUT_LINE('Name = ' || v_EmpName);
END;
/



-- %ROWTYPE is used to declare a record with the same types as
-- found in the specified database table, view or cursor:

DECLARE
  v_emp emp%ROWTYPE;
BEGIN
  v_emp.empno := 10;
  v_emp.ename := 'Rajani';
   v_emp.dept := 'IT';
 
END;
/
 

How to display logname @ hostname in command prompt for Solaris 10

Hi Friends,

By default Solaris prompts you with $ for individual users and # for root user.

But if you want to modify your login terminal , please follow below steps.

You can either edit it though VI editor else use winscp to edit it remotely .

The advantage of WINSCP over File Zilla is it opens the file in ANSI / UNIX mode and modifies remotely without locally downloading.

If we want to set it locally for individual users:

Logged in as fred user :

/export/home/fred

# This is the .profile file of default home directory of user fred.
# This is the default standard profile provided to a user.
# They are expected to edit it to meet their own needs.

#MAIL=/usr/mail/${LOGNAME:?}
export PS1="$(logname)@$(hostname) $" 

--------------------------------------------------------------------------------------------------------------------
If we want to set it Globally for every user including the root.

Logged in as root user 

/etc/profile 

# This is the /etc/profile loggen in as root user to change the login prompt.
#iDent "@(#)profile 1.19 01/03/13 SMI" /* SVr4.0 1.3 */

# The profile that all logins get before using their own .profile.
trap ""  2 3
export LOGNAME PATH

if [ "$TERM" = "" ]
then
if /bin/i386
then
TERM=sun-color
else
TERM=sun
fi
export TERM
fi

# Login and -su shells get /etc/profile services.
# -rsh is given its environment in its .profile.

case "$0" in
-sh | -ksh | -jsh | -bash)

if [ ! -f .hushlogin ]
then
/usr/sbin/quota
# Allow the user to break the Message-Of-The-Day only.
trap "trap '' 2"  2
/bin/cat -s /etc/motd
trap "" 2

/bin/mail -E
case $? in
0) 
echo "You have new mail."
  ;;
2) 
echo "You have mail."
  ;;
esac
fi
esac

umask 022
trap  2 3
# Added below lines with logged in as root user to modify the login terminal for root and  individual users. 
#
if [ "$LOGNAME" = "root" ]
then
PS1="ROOT@`uname -n`$ " ; export PS1
else
PS1="$LOGNAME@`uname -n`$ " ; export PS1
fi

How can you call a form on a form

Hi,

We can call a form in 3 different ways :

1.Call Form
2.New Form
3.Open Form

The communication between forms can be done in the following 3 ways
let say Form A calls FormB

1.Using New_Form: this Terminates Form A and activates Form B

The NewForm will terminates your current active Form A and releases the memory which is used by Form A and Then activates the called Form B.

2.Using Call_Form:
This suspends the Form A and activates Form B.When you close Form B then Form A will active again.
User cant able to navigate between Forms A and B.

3.Using Open Form:
It opens the indicated form B.
User can be able to navigate between forms A and B.
This is used in multi form applications.

The above is just brief on "Functionality".You have to research more when you are implementing.

Cheers
Rajani

How to generate a spool CSV file with Current Date

PROMPT
PROMPT *** my_script.sql *** START ***
PROMPT
set lines 420
set trimspool on
setpages 0
set head offset
termout offset
feedback offset
echo off
col dcol new_value mydate noprint
select to_char(sysdate,'YYYY-MM-DD') dcol from dual;
spool C:\&mydate._MY_SPOOL_FILENAME.csv

select 'INIT,'|| e.employee_id ||','|| e.department_id||','||d.manager_id||','|| d.department_id

from employees e, departments d
where e.department_id = d.department_id
order by 1
/
spool off
set echo on
set termout on
PROMPT
PROMPT *** my_script.sql *** END ***
PROMPT --Explanation:
/* The first query puts the date in the substitution variable "mydate". There is no visible output from this query because of the NOPRINT option in the COLUMN command. In the SPOOL command the first period (.) indicates the end of the variable name and is not included in the resulting string. If "mydate" contained "2013-05-14" from the first query then the spool file name would be "2013-05-14_MY_SPOOL_FILENAME.csv ". */

-- The Spool path C:\ means in 'C' drive for windows and
-- for Unix : Spool &mydate._MY_SPOOL_FILENAME.csv (file will be spooled in working directory)

Hope this clears all your Doubt.

Cheers
Rajani

Database Schema Objects

Schema Objects:

1. Tables
2. Views
3. Synonyms
4. Functions
5. Procedures
6. Packages
7. Triggers
8. Indexes
9. Constraints
10. Sequences
11. Java
12. DB Links
13. Users
14. Jobs
15. Types
16. Queue Tables
17. Queues 
18. Directories
19. Policy Groups
20. Policies
21. Rollback Segments
22. Tablespaces
23. Libraries
24. Materailized Views
25. Favourites
26. Materialized View Logs
27. Dimensions
28. Sys privs
29. Clusters
30. Refresh Groups
31. Recyclebin
32. Sched. Chains
33. Sched.Programs
34. Sched.Schedules
35. Sched. Jobs
36. Sched. Job Classes
37. Sched. windows
38. Sched. Window Groups
39. Invalid Objects
40. Contexts
41. Transformations
42. Audit Policies