How do I set LINESIZE and PAGESIZE with a substitution variable?

Question

I am trying to use a PL/SQL script in SQL*Plus to set the linesize and pagesize based on a developer input on whether a report should print in landscape or portrait orientation. If it is landscape, I want linesize 132 and pagesize 60. If it is portrait, I want 88 and 80 respectively.

I am trying to use substitution variables to do so like this:

DEFINE PRINT_ORIENTATION 'PORTRAIT'

COLUMN LINESIZE_VALUE NOPRINT new_value LINE
COLUMN PAGESIZE_VALUE NOPRINT new_value PAGE

SELECT DECODE('&PRINT_ORIENTATION', 'PORTRAIT', 88, 'LANDSCAPE', 132) AS LINE,
       DECODE('&PRINT_ORIENTATION', 'PORTRAIT', 80, 'LANDSCAPE', 60) AS PAGE
FROM DUAL
/

SET LINESIZE &LINE
SET PAGESIZE &PAGE

However, the system then just prompts me to manually enter values for line and page. What do I need to do to use a substitution variable in the SET command?

Look here https://stackoverflow.com/questions/16674252/sqlplus-how-to-accept-text-variable-from-prompt — XING, Nov 17 '17 at 15:25
Manually typing in the values works, but I was trying to specify them in predefined substitution variables. I can't get the SET command to take them. — SandPiper, Nov 17 '17 at 15:28
You need to look for `ACCEPT` usgae. the example i posted has SET only to enable `DBMS_OUTPUT`. You need not have to set anything — XING, Nov 17 '17 at 15:31
Isn't the point to set both linesize and pagesize based on the string value though, rather than prompting for them explicitly? — Alex Poole, Nov 17 '17 at 15:32

score 2 · Accepted Answer · answered Nov 17 '17 at 15:26

2

With the column .. new_value ... syntax:

COLUMN Q_LINE NEW_VALUE LINE
COLUMN Q_PAGE NEW_VALUE PAGE

SELECT DECODE('&PRINT_ORIENTATION', 'PORTRAIT', 88, 'LANDSCAPE', 132) AS Q_LINE,
       DECODE('&PRINT_ORIENTATION', 'PORTRAIT', 80, 'LANDSCAPE', 60) AS Q_PAGE
FROM DUAL
/

SET LINESIZE &LINE
SET PAGESIZE &PAGE

That allows you to define a substitution value from a query result.

answered Nov 17 '17 at 15:26

Alex Poole

183,384
11
179
318

1

I was using the new_value syntax incorrectly. Thank you, I appreciate it. – SandPiper Nov 17 '17 at 15:34

score 0 · Answer 2 · answered Nov 17 '17 at 15:37

With a "shell" substitution variable: What we do here to handle a similar issue, is we generate the correct sizing settings in a temporary sql file.

Not sure it'll help, but here's a hint on how to set the number of columns from the size of your terminal, with this in @$HOME/sqlmlogin.sql:

def_editor=emacs
set null °
set arraysize 1
set pagesize 0
set serveroutput on
host > $HOME/sqltmp_size.sql ; tty -s && ( eval $( resize ) ; echo "set lines $COLUMNS\n set pages 0" >> $HOME/sql/tmp_size.sql )
-- then call the temp script
@$HOME/sqltmp_size.sql
-- remove afterwards
host rm -f $HOME/sqltmp_size.sql

And call sqlplus like this:

sqlplus / @$HOME/sqlmlogin.sql

So you can also generate the static values you want for your users from a script like that.

How do I set LINESIZE and PAGESIZE with a substitution variable?

2 Answers2

Linked