Using batch file to create new table in oracle/sqlplus

Question

I want create new table in oracle with sqlplus and batch file.

sqlplus user/password @create_tables.sql

it is successfully

but that's not what I want. I want the user to input year. My batch file look like :

@echo off

set /p year=__YEAR (YYYY)?
sqlplus user/password
create table data%year%
(Nama varchar2(25),
value number);
echo Successfull
pause

script stops here

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Aug 25 06:07:26 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL>

what's wrong?

AjV Jsy · Accepted Answer · 2013-08-25 09:34:22Z

1

In your example you have launched an interactive shell (with SQL> prompt) and when you exit from that, the batch interpreter will run the create line as a new command (and fail).

If there isn't any other way to do it, you could echo the required commands to a temporary filename.sql in %temp% and then call that. Syntax here might not be 100%, but I hope it's a starting point :

@echo off
set /p year=__YEAR (YYYY)?
  rem  Set command in temp file :
echo create table data%year% (Name varchar2(25), value number); > @%temp%\temp.sql
  rem  Now run the above command :
sqlplus user/password @%temp%\temp.sql
echo Successfull
pause

edited Aug 25, 2013 at 9:34

answered Aug 25, 2013 at 9:11

AjV Jsy

6,1154 gold badges39 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

flyingbird013 Over a year ago

Thank U very much. i added echo exit > @%temp%\temp.sql syntax before rem Now run the above command. script work for me. thanks again.

Collectives™ on Stack Overflow

Using batch file to create new table in oracle/sqlplus

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related