This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,682 Users
  • 2,269,776 Discussions
  • 7,916,823 Comments

Discussions

format buffer and history

BrianDwyer
BrianDwyer Member Posts: 23
edited May 4, 2016 3:51PM in SQLcl

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

Best Answer

Answers