How to execute sql file from batch file and display output to screen

Question

I have (test.batch) a batch file that calls (gett.sql) sql file and executes it.

The problem is how to display the output of sql file on the screen to user.

test.batch file:

@echo off
set mypath=%cd%
@ECHO Counter...
set user_name=hr
set password=hr
set net_service_name=ORCL
echo exit | sqlplus -s %user_name%/%password%@%net_service_name% @%mypath%\gett.sql
pause

gett.sql file:

SET ECHO ON
DECLARE
BEGIN
  FOR i IN 1..5 LOOP
      DBMS_OUTPUT.PUT_LINE('Message# '||i);
  END LOOP;
END;

PAUSE

Littlefoot · Accepted Answer · 2019-08-08 10:26:17Z

4

You did everything right (OK, almost everything):

M:\a1_maknuto>@test
Counter...

Message# 1
Message# 2
Message# 3
Message# 4
Message# 5

PL/SQL procedure successfully completed.

Press any key to continue . . .

M:\a1_maknuto>

What you miss, is a slash that terminates PL/SQL block in gett.sql script; also, don't forget to enable output by SET SERVEROUTPUT ON:

SET ECHO ON
SET SERVEROUTPUT ON       --> this
DECLARE
BEGIN
  FOR i IN 1..5 LOOP
      DBMS_OUTPUT.PUT_LINE('Message# '||i);
  END LOOP;
END;
/                         --> this
PAUSE

answered Aug 8, 2019 at 10:26

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

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

2 Comments

Md Faramawy Over a year ago

thanks, it works great , however i got 'Input truncated to n characters' and found I must insert new line at the end of sql file

Littlefoot Over a year ago

You're welcome. If you had to fix truncated input and managed to do that - good for you! I'm glad you made it.

Collectives™ on Stack Overflow

How to execute sql file from batch file and display output to screen

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related