Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
format buffer and history

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
-
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.
Answers
-
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.