format buffer and history

BrianDwyer

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

thatJeffSmith-Oracle

thanks for reporting the HISTORY item - i did a demo last week and thought I was just temporarily insane. Will log.

I can reproduce the format from the history as well, also logging.

thatJeffSmith-Oracle

thanks for reporting the HISTORY item - i did a demo last week and thought I was just temporarily insane. Will log.

I can reproduce the format from the history as well, also logging.