How to call and execute a SQL+ file (Oracle DB) with parameters from BATCH or SHELL

Question

I need your help. I have an SQL file (Oracle DB) like:

drop user &1 cascade;

CREATE USER &1 IDENTIFIED BY &2
DEFAULT TABLESPACE Cmpny_USR TEMPORARY TABLESPACE TEMP;

grant alter session,
      create cluster,
      create database link,
      create procedure,
      create sequence,
      create session,
      create synonym,
      create role,
      create table,
      create trigger,
      create view
  to &1;

alter user &1 quota unlimited on Cmpny_INDX;
alter user &1 quota unlimited on Cmpny_USR;
alter user &1 quota 0K on system;

With a batch or shell file, I want to automate the process to drop and create the same user. So if I pass a parameter to the batch file like "user1", I want that the input is passed to the sql file. The sql script should be executed with this parameter. My latest batch file looks like this:

@echo off
PUSHD \\%~P0

:ask
set /p portnr=Please select user to be deleted: 
echo User with %portnr% will be deleted. To continue press j, else n. 
set /p jodern=j or n:
timeout /t 3

if "%jodern%"=="n" goto ask
if "%jodern%"=="j" goto execute

:execute
???

Why do you want to drop existing user? You will drop all his objects. — Wernfried Domscheit
– Wernfried Domscheit, Commented Dec 22, 2020 at 8:49
@WernfriedDomscheit I want to "update" them and clean the data. So at first, I drop them and after that I create them again. — ask4asc
– ask4asc, Commented Dec 22, 2020 at 8:54
What if someone enters something other than j or n? for instance, J? Consider to use choice rather than set /P… — aschipfl
– aschipfl, Commented Dec 22, 2020 at 14:19

EdStevens · Accepted Answer · 2020-12-22 14:36:55Z

0

Your '&1' is simply the first command line parm passed to sqlplus.

So, with this simple sql script, doit.sql:

prompt You entered &1
select '&1' from dual;

Start sqlplus, passing it the name of the script to execute followed by the command line parms:

C:\>sqlplus estevens/****@myddb @doit heresmyparm

SQL*Plus: Release 12.1.0.1.0 Production on Tue Dec 22 08:33:24 2020

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

Last Successful login time: Tue Dec 22 2020 08:33:06 -06:00

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
With the Automatic Storage Management option

You entered heresmyparm
old   1: select '&1' from dual
new   1: select 'heresmyparm' from dual

'HERESMYPAR
-----------
heresmyparm

SQL>

answered Dec 22, 2020 at 14:36

EdStevens

3,9203 gold badges12 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

How to call and execute a SQL+ file (Oracle DB) with parameters from BATCH or SHELL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related