Oracle and Windows

[IMAUser]

Hi,

This is my first instance of doing a script on Windows for connecting to an oracle database. I have only used Unix all this while.

So has anyone got an idea of how can I do something, similar as below, on a windows platform from the command prompt. On UNIX I would do
--------------
run_date=`date +%D`
sqlplus -s username/password@db <<EOF

execute my_proc($run_date);

EOF
--------------

Any help is much appreciated.

 

[kwil38]

IMAUser,

Typically what I do is create a batch script which calls a sql script. Then the batch script can be scheduled to run accordingly via Windows Scheduled Tasks.

For example,

I have a batch script file called drop_employees.bat which contains the following line of code -

sqlplusw /nolog @drop_employees.sql

The line of code above calls a sql script file called drop_employees.sql which contains the following lines of code -

conn username/password@dbname;
drop table employees;
disconnect
exit

Hope that answers your question. If not, let me know.

 

[IMAUser]

Thanks Kwil38.

But how do you pass the system date to the sql script. And how do you add tasks to Windows Scheduled Tasks.

TIA.

 

[kwil38]

Can you not use SYSDATE in your execute statement -

execute my_proc(SYSDATE);

Depending on which version of Windows you are using (I'm on Windows Server 2003 Enterprise Edition), it may be slightly different but -

1. Go to Start/All Programs/Accessories/System Tools/Scheduled Tasks

2. Double-click Add Scheduled Task

3. Browse for and select the batch script you created

4. Select how often you want the task to run

5. Fill in remaining details as required and select Finish

6. Your task should now be scheduled to run.

 

[IMAUser]

We could use SYSDATE, but was wondering if there was an equivalnet of the UNIX command date. And how do we set variables in a windows script.

 

[kwil38]

I'm not aware of an equivalent to the UNIX command date, but I'm still pretty new to the Windows/Oracle game as well. Anyone else have an idea?

As far as declaring a variable goes, you should be able to do anything in the .sql file that you can do in SqlPlus. So you declare a variable as follows -

VARIABLE variablename VARCHAR2(20);

 

[gunjan14]

Hi,
The windows equivalent for unix date is DATE itself.
You can use like this

H:\>echo %DATE%
Tue 08/01/2006

so if you want to pass the date from windows, you can refer to date inside the sql block as %DATE%. Let us know if this resolves your issue.

Regards,
Gunjan