Hello, couple of issues rolled into one. was testing the format buffer command and wanted to see if I could combine a call from the history buffer and format it.
Created a simple view
SQL> create view scott.dept_count (department, location, emp_count) as (select dept.dname, dept.loc, (select count(*) from scott.emp where dname = dept.dname) from scott.dept dept);
View scott.dept_count created.
Created a simple table from the view
SQL> create table dept_bob as select * from dept_count;
Table dept_bob created.
Dropped the table (just wanted to add another line to the history)
SQL> drop view scott.dept_count;
View scott.dept_count dropped.
Listed the history
SQL> history
History: show no failures
1 clear screen
2 create view scott.dept_count (department, location, emp_count) as (select dept.dname, dept.loc, (select count(*) from scott.emp where dname = dept.dname) from scott.dept dept)
3 create table dept_bob as select * from dept_count
4 drop view scott.dept_count
First issue, notice what happened when i pull back history row 3, I get what should be history row 2,
SQL> history 3
1* create view scott.dept_count (department, location, emp_count) as (select dept.dname, dept.loc, (select count(*) from scott.emp where dname = dept.dname) from scott.dept dept)
Ok, so i have the SQL for the view, let us see if I can format it.
SQL> format buffer
1 create view scott.dept_count (department, location, emp_count) as (select dept.dname, dept.loc, (select count(*) from scott.emp where dname = dept.dname) from scott.dept dept)
2 /*** Syntax Error at line 0, column 175
3
4 create view scott.dept_count (department, location, emp_count) as (select dept.dname, dept.loc, (select count(*) from scott.emp where dname = dept.dname) from scott.dept dept)
5 ^^^
6
7 Expected: 'ORDER',order_by_clause,';','UNION',SET_OPER,'FETC
8* ***/
Failed, so is it just create views that it is failing on or all historic calls from the buffer?
So list history again
SQL> history
History: show no failures
1 clear screen
2 create view scott.dept_count (department, location, emp_count) as (select dept.dname, dept.loc, (select count(*) from scott.emp where dname = dept.dname) from scott.dept dept)
3 create table dept_bob as select * from dept_count
4 drop view scott.dept_count
Pull back line 3 (using history 4)
SQL> history 4
1* create table dept_bob as select * from dept_count
format the buffer
Let us see if I can format it.
SQL> format buffer
1 CREATE TABLE dept_bob
2 AS
3 SELECT
4 *
5 FROM
6* dept_count
SQL>
This works. so what is the issue with trying to format buffer when it is a create view statement and why do i have to increment the history line number to pull back the correct line?
For the record, if you type in the create view statement and then do format buffer it does format it correctly, issue is only when you call it from the sql history.
Using sqlcl 4.2.0.16.112.0616
Cheers
Brian