Oracle SQLcl User's Guide

Transcription

Oracle SQLclUser's GuideRelease 21.2F41844-01June 2021

Oracle SQLcl User's Guide, Release 21.2F41844-01Copyright 2019, 2021, Oracle and/or its affiliates.Primary Author: Apoorva SrinivasContributing Authors: Celin Cherian, Tulika DasContributors: Syme Kutz, Jeff D. SmithThis software and related documentation are provided under a license agreement containing restrictions onuse and disclosure and are protected by intellectual property laws. Except as expressly permitted in yourlicense agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license,transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverseengineering, disassembly, or decompilation of this software, unless required by law for interoperability, isprohibited.The information contained herein is subject to change without notice and is not warranted to be error-free. Ifyou find any errors, please report them to us in writing.If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it onbehalf of the U.S. Government, then the following notice is applicable:U.S. GOVERNMENT END USERS: Oracle programs (including any operating system, integrated software,any programs embedded, installed or activated on delivered hardware, and modifications of such programs)and Oracle computer documentation or other Oracle data delivered to or accessed by U.S. Government endusers are "commercial computer software" or "commercial computer software documentation" pursuant to theapplicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use,reproduction, duplication, release, display, disclosure, modification, preparation of derivative works, and/oradaptation of i) Oracle programs (including any operating system, integrated software, any programsembedded, installed or activated on delivered hardware, and modifications of such programs), ii) Oraclecomputer documentation and/or iii) other Oracle data, is subject to the rights and limitations specified in thelicense contained in the applicable contract. The terms governing the U.S. Government’s use of Oracle cloudservices are defined by the applicable contract for such services. No other rights are granted to the U.S.Government.This software or hardware is developed for general use in a variety of information management applications.It is not developed or intended for use in any inherently dangerous applications, including applications thatmay create a risk of personal injury. If you use this software or hardware in dangerous applications, then youshall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure itssafe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of thissoftware or hardware in dangerous applications.Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks oftheir respective owners.Intel and Intel Inside are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks areused under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Epyc,and the AMD logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registeredtrademark of The Open Group.This software or hardware and documentation may provide access to or information about content, products,and services from third parties. Oracle Corporation and its affiliates are not responsible for and expresslydisclaim all warranties of any kind with respect to third-party content, products, and services unless otherwiseset forth in an applicable agreement between you and Oracle. Oracle Corporation and its affiliates will not beresponsible for any loss, costs, or damages incurred due to your access to or use of third-party content,products, or services, except as set forth in an applicable agreement between you and Oracle.

ContentsPrefaceAudienceviDocumentation AccessibilityviConventionsviThird-Party License Informationvi1Changes in Release 21.2 for Oracle SQLcl User's Guide2Using Oracle SQLcl2.1Alphabetic List of SQLcl Commands2-12.2List of Unsupported SQL*Plus Commands and Features2-42.3Starting and Leaving SQLcl2-42.4Starting Up and Shutting Down a Database2-52.5Entering and Executing Commands2-62.6Manipulating SQL, SQLcl, and PL/SQL Commands2-72.7Formatting Query Results2-102.8Accessing Databases2-142.9Miscellaneous 2-182.9.4SET system variable value2-202.9.5SHOW 113Loading a File and Unloading Table DataCalling Oracle Cloud Infrastructure REST APIs Using the OCI Command2-26Liquibase Support in SQLcl3.1About Liquibase in SQLcl3-1iii

453.2Requirements for Using Liquibase3-23.3Supported Types3-23.4Supported Liquibase Commands in SQLcl3-43.4.1LB GENOBJECT3-53.4.2LB GENSCHEMA3-93.4.3LB GENCONTROLFILE3-103.4.4LB UPDATE3-113.4.5LB UPDATESQL3-123.4.6LB ROLLBACK3-153.4.7LB ROLLBACKSQL3-163.4.8LB DIFF3-183.4.9LB DBDOC3-213.4.10LB CHANGELOGSYNC3-233.4.11LB CLEARCHECKSUMS3-243.4.12LB LISTLOCKS3-243.4.13LB RELEASELOCKS3-253.4.14LB STATUS3-253.4.15LB VALIDATE3-263.4.16LB VERSION3-263.4.17LB HELP3-263.5DATABASECHANGELOG DETAILS VIEW3-273.6ChangeSets in Liquibase3-283.7Examples Using Liquibase3-293.7.1Capture and Deploy an Object3-293.7.2Capture and Deploy a Schema3-303.7.3Generate the Master Control File3-323.7.4Capture and Deploy a Schema and then Upgrade it and Redeploy3-323.7.5Execute Custom SQL with RunOracleScript3-36Using Cloud Storage4.1Using DBMS CLOUD for Authentication4-14.2Creating the OCI profile for OCI Authentication4-44.3Cloud Storage Command Options4-44.4Examples4-5PGQL Plug-in for SQLcl5.1Downloading and Installing5-15.2About PGQL Commands5-1iv

5.3Examples5-2v

PrefacePrefaceThis guide provides usage information about Oracle SQLcl (SQL Developer CommandLine), a Java-based command-line interface for Oracle Database.AudienceThis guide is intended for those using Oracle SQLcl.Documentation AccessibilityFor information about Oracle's commitment to accessibility, visit the OracleAccessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx acc&id docacc.Access to Oracle SupportOracle customers that have purchased support have access to electronic supportthrough My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx acc&id info or visit http://www.oracle.com/pls/topic/lookup?ctx acc&id trs if you are hearing impaired.ConventionsThe following text conventions are used in this document:ConventionMeaningboldfaceBoldface type indicates graphical user interface elements associatedwith an action, or terms defined in text or the glossary.italicItalic type indicates book titles, emphasis, or placeholder variables forwhich you supply particular values.monospaceMonospace type indicates commands within a paragraph, URLs, codein examples, text that appears on the screen, or text that you enter.Third-Party License InformationOracle SQLcl contains third-party code. Oracle is required to provide the followingnotices. Note, however, that the Oracle program license that accompanied this productdetermines your right to use the Oracle program, including the third-party software,and the terms contained in the following notices do not change those rights.vi

PrefaceApache Commons Codec 1.14Apache License Version 2.0January 2004http://www.apache.org/licenses/TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION1. Definitions."License" shall mean the terms and conditions for use, reproduction, and distribution asdefined by Sections 1 through 9 of this document."Licensor" shall mean the copyright owner or entity authorized by the copyright owner that isgranting the License."Legal Entity" shall mean the union of the acting entity and all other entities that control, arecontrolled by, or are under common control with that entity. For the purposes of this definition,"control" means (i) the power, direct or indirect, to cause the direction or management of suchentity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of theoutstanding shares, or (iii) beneficial ownership of such entity."You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted bythis License."Source" form shall mean the preferred form for making modifications, including but notlimited to software source code, documentation source, and configuration files."Object" form shall mean any form resulting from mechanical transformation or translation ofa Source form, including but not limited to compiled object code, generated documentation,and conversions to other media types."Work" shall mean the work of authorship, whether in Source or Object form, made availableunder the License, as indicated by a copyright notice that is included in or attached to thework (an example is provided in the Appendix below)."Derivative Works" shall mean any work, whether in Source or Object form, that is based on(or derived from) the Work and for which the editorial revisions, annotations, elaborations, orother modifications represent, as a whole, an original work of authorship. For the purposes ofthis License, Derivative Works shall not include works that remain separable from, or merelylink (or bind by name) to the interfaces of, the Work and Derivative Works thereof."Contribution" shall mean any work of authorship, including the original version of the Workand any modifications or additions to that Work or Derivative Works thereof, that isintentionally submitted to Licensor for inclusion in the Work by the copyright owner or by anindividual or Legal Entity authorized to submit on behalf of the copyright owner. For thepurposes of this definition, "submitted" means any form of electronic, verbal, or writtencommunication sent to the Licensor or its representatives, including but not limited tocommunication on electronic mailing lists, source code control systems, and issue trackingsystems that are managed by, or on behalf of, the Licensor for the purpose of discussing andimproving the Work, but excluding communication that is conspicuously marked or otherwisedesignated in writing by the copyright owner as "Not a Contribution.""Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom aContribution has been received by Licensor and subsequently incorporated within the Work.2. Grant of Copyright License. Subject to the terms and conditions of this License, eachContributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royalty-vii

Prefacefree, irrevocable copyright license to reproduce, prepare Derivative Works of, publiclydisplay, publicly perform, sublicense, and distribute the Work and such DerivativeWorks in Source or Object form.3. Grant of Patent License. Subject to the terms and conditions of this License, eachContributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge,royalty-free, irrevocable (except as stated in this section) patent license to make, havemade, use, offer to sell, sell, import, and otherwise transfer the Work, where suchlicense applies only to those patent claims licensable by such Contributor that arenecessarily infringed by their Contribution(s) alone or by combination of theirContribution(s) with the Work to which such Contribution(s) was submitted. If Youinstitute patent litigation against any entity (including a cross-claim or counterclaim in alawsuit) alleging that the Work or a Contribution incorporated within the Workconstitutes direct or contributory patent infringement, then any patent licenses grantedto You under this License for that Work shall terminate as of the date such litigation isfiled.4. Redistribution. You may reproduce and distribute copies of the Work or DerivativeWorks thereof in any medium, with or without modifications, and in Source or Objectform, provided that You meet the following conditions:(a) You must give any other recipients of the Work or Derivative Works a copy of thisLicense; and(b) You must cause any modified files to carry prominent notices stating that Youchanged the files; and(c) You must retain, in the Source form of any Derivative Works that You distribute, allcopyright, patent, trademark, and attribution notices from the Source form of the Work,excluding those notices that do not pertain to any part of the Derivative Works; and(d) If the Work includes a "NOTICE" text file as part of its distribution, then anyDerivative Works that You distribute must include a readable copy of the attributionnotices contained within such NOTICE file, excluding those notices that do not pertainto any part of the Derivative Works, in at least one of the following places: within aNOTICE text file distributed as part of the Derivative Works; within the Source form ordocumentation, if provided along with the Derivative Works; or, within a displaygenerated by the Derivative Works, if and wherever such third-party notices normallyappear. The contents of the NOTICE file are for informational purposes only and donot modify the License. You may add Your own attribution notices within DerivativeWorks that You distribute, alongside or as an addendum to the NOTICE text from theWork, provided that such additional attribution notices cannot be construed asmodifying the License.You may add Your own copyright statement to Your modifications and may provideadditional or different license terms and conditions for use, reproduction, or distributionof Your modifications, or for any such Derivative Works as a whole, provided Your use,reproduction, and distribution of the Work otherwise complies with the conditionsstated in this License.5. Submission of Contributions. Unless You explicitly state otherwise, any Contributionintentionally submitted for inclusion in the Work by You to the Licensor shall be underthe terms and conditions of this License, without any additional terms or conditions.Notwithstanding the above, nothing herein shall supersede or modify the terms of anyseparate license agreement you may have executed with Licensor regarding suchContributions.viii

Preface6. Trademarks. This License does not grant permission to use the trade names, trademarks,service marks, or product names of the Licensor, except as required for reasonable andcustomary use in describing the origin of the Work and reproducing the content of theNOTICE file.7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing, Licensorprovides the Work (and each Contributor provides its Contributions) on an "AS IS" BASIS,WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied,including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT,MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. You are solelyresponsible for determining the appropriateness of using or redistributing the Work andassume any risks associated with Your exercise of permissions under this License.8. Limitation of Liability. In no event and under no legal theory, whether in tort (includingnegligence), contract, or otherwise, unless required by applicable law (such as deliberate andgrossly negligent acts) or agreed to in writing, shall any Contributor be liable to You fordamages, including any direct, indirect, special, incidental, or consequential damages of anycharacter arising as a result of this License or out of the use or inability to use the Work(including but not limited to damages for loss of goodwill, work stoppage, computer failure ormalfunction, or any and all other commercial damages or losses), even if such Contributorhas been advised of the possibility of such damages.9. Accepting Warranty or Additional Liability. While redistributing the Work or DerivativeWorks thereof, You may choose to offer, and charge a fee for, acceptance of support,warranty, indemnity, or other liability obligations and/or rights consistent with this License.However, in accepting such obligations, You may act only on Your own behalf and on Yoursole responsibility, not on behalf of any other Contributor, and only if You agree to indemnify,defend, and hold each Contributor harmless for any liability incurred by, or claims assertedagainst, such Contributor by reason of your accepting any such warranty or additional liability.END OF TERMS AND CONDITIONS APPENDIXAPPENDIX: How to apply the Apache License to your work.To apply the Apache License to your work, attach the following boilerplate notice, with thefields enclosed by brackets "[]" replaced with your own identifying information. (Don't includethe brackets!) The text should be enclosed in the appropriate comment syntax for the fileformat. We also recommend that a file or class name and description of purpose be includedon the same "printed page" as the copyright notice for easier identification within third-partyarchives.Copyright [yyyy] [name of copyright owner]Licensed under the Apache License, Version 2.0 (the "License"); you may not use this fileexcept in compliance with the License. You may obtain a copy of the License GHT NOTICE Apache Commons Codec Copyright 2002-2019 The Apache Software FoundationThis product includes software developed at The Apache Software Foundation ns/codec/language/DoubleMetaphoneTest.java contains test datafrom http://aspell.net/test/orig/batch0.tab. Copyright (C) 2002 Kevin Atkinson(kevina@gnu.org)ix

Preface The content of package org.apache.commons.codec.language.bm has beentranslated from the original php source code available at http://stevemorse.org/phoneticinfo.htm with permission from the original authors. Original source copyright:Copyright (c) 2008 Alexander Beider & Stephen P. Morse.Apache Commons Logging 1.2Copyright 2003-2007The Apache Software FoundationThis product includes software developed by The Apache Software Foundation (http://www.apache.org/).Apache License Version 2.0January 2004http://www.apache.org/licenses/TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION1. Definitions."License" shall mean the terms and conditions for use, reproduction, and distributionas defined by Sections 1 through 9 of this document."Licensor" shall mean the copyright owner or entity authorized by the copyright ownerthat is granting the License."Legal Entity" shall mean the union of the acting entity and all other entities thatcontrol, are controlled by, or are under common control with that entity. For thepurposes of this definition, "control" means (i) the power, direct or indirect, to cause thedirection or management of such entity, whether by contract or otherwise, or (ii)ownership of fifty percent (50%) or more of the outstanding shares, or (iii) beneficialownership of such entity."You" (or "Your") shall mean an individual or Legal Entity exercising permissionsgranted by this License."Source" form shall mean the preferred form for making modifications, including butnot limited to software source code, documentation source, and configuration files."Object" form shall mean any form resulting from mechanical transformation ortranslation of a Source form, including but not limited to compiled object code,generated documentation, and conversions to other media types."Work" shall mean the work of authorship, whether in Source or Object form, madeavailable under the License, as indicated by a copyright notice that is included in orattached to the work (an example is provided in the Appendix below)."Derivative Works" shall mean any work, whether in Source or Object form, that isbased on (or derived from) the Work and for which the editorial revisions, annotations,elaborations, or other modifications represent, as a whole, an original work ofauthorship. For the purposes of this License, Derivative Works shall not include worksthat remain separable from, or merely link (or bind by name) to the interfaces of, theWork and Derivative Works thereof.x

Preface"Contribution" shall mean any work of authorship, including the original version of the Workand any modifications or additions to that Work or Derivative Works thereof, that isintentionally submitted to Licensor for inclusion in the Work by the copyright owner or by anindividual or Legal Entity authorized to submit on behalf of the copyright owner. For thepurposes of this definition, "submitted" means any form of electronic, verbal, or writtencommunication sent to the Licensor or its representatives, including but not limited tocommunication on electronic mailing lists, source code control systems, and issue trackingsystems that are managed by, or on behalf of, the Licensor for the purpose of discussing andimproving the Work, but excluding communication that is conspicuously marked or otherwisedesignated in writing by the copyright owner as "Not a Contribution.""Contributor" shall mean Licensor and any individual or Legal Entity on behalf of whom aContribution has been received by Licensor and subsequently incorporated within the Work.2. Grant of Copyright License. Subject to the terms and conditions of this License, eachContributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royaltyfree, irrevocable copyright license to reproduce, prepare Derivative Works of, publicly display,publicly perform, sublicense, and distribute the Work and such Derivative Works in Source orObject form.3. Grant of Patent License. Subject to the terms and conditions of this License, eachContributor hereby grants to You a perpetual, worldwide, non-exclusive, no-charge, royaltyfree, irrevocable (except as stated in this section) patent license to make, have made, use,offer to sell, sell, import, and otherwise transfer the Work, where such license applies only tothose patent claims licensable by such Contributor that are necessarily infringed by theirContribution(s) alone or by combination of their Contribution(s) with the Work to which suchContribution(s) was submitted. If You institute patent litigation against any entity (including across-claim or counterclaim in a lawsuit) alleging that the Work or a Contribution incorporatedwithin the Work constitutes direct or contributory patent infringement, then any patentlicenses granted to You under this License for that Work shall terminate as of the date suchlitigation is filed.4. Redistribution. You may reproduce and distribute copies of the Work or Derivative Worksthereof in any medium, with or without modifications, and in Source or Object form, providedthat You meet the following conditions:(a) You must give any other recipients of the Work or Derivative Works a copy of this License;and(b) You must cause any modified files to carry prominent notices stating that You changed thefiles; and(c) You must retain, in the Source form of any Derivative Works that You distribute, allcopyright, patent, trademark, and attribution notices from the Source form of the Work,excluding those notices that do not pertain to any part of the Derivative Works; and(d) If the Work includes a "NOTICE" text file as part of its distribution, then any DerivativeWorks that You distribute must include a readable copy of the attribution notices containedwithin such NOTICE file, excluding those notices that do not pertain to any part of theDerivative Works, in at least one of the following places: within a NOTICE text file distributedas part of the Derivative Works; within the Source form or documentation, if provided alongwith the Derivative Works; or, within a display generated by the Derivative Works, if andwherever such third-party notices normally appear. The contents of the NOTICE file are forinformational purposes only and do not modify the License. You may add Your own attributionnotices within Derivative Works that You distribute, alongside or as an addendum to theNOTICE text from the Work, provided that such additional attribution notices cannot beconstrued as modifying the License.xi

PrefaceYou may add Your own copyright statement to Your modifications and may provideadditional or different license terms and conditions for use, reproduction, or distributionof Your modifications, or for any such Derivative Works as a whole, provided Your use,reproduction, and distribution of the Work otherwise complies with the conditionsstated in this License.5. Submission of Contributions. Unless You explicitly state otherwise, any Contributionintentionally submitted for inclusion in the Work by You to the Licensor shall be underthe terms and conditions of this License, without any additional terms or conditions.Notwithstanding the above, nothing herein shall supersede or modify the terms of anyseparate license agreement you may have executed with Licensor regarding suchContributions.6. Trademarks. This License does not grant permission to use the trade names,trademarks, service marks, or product names of the Licensor, except as required forreasonable and customary use in describing the origin of the Work and reproducingthe content of the NOTICE file.7. Disclaimer of Warranty. Unless required by applicable law or agreed to in writing,Licensor provides the Work (and each Contributor provides its Contributions) on an"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, eitherexpress or implied, including, without limitation, any warranties or conditions of TITLE,NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULARPURPOSE. You are solely responsible for determining the appropriateness of using orredistributing the Work and assume any risks associated with Your exercise ofpermissions under this License.8. Limitation of Liability. In no event and under no legal theory, whether in tort(including negligence), contract, or otherwise, unless required by applicable law (suchas deliberate and grossly negligent acts) or agreed to in writing, shall any Contributorbe liable to You for damages, including any direct, indirect, special, incidental, orconsequential damages of any character arising as a result of this License or out ofthe use or inability to use the Work (including but not limited to damages for loss ofgoodwill, work stoppage, computer failure or malfunction, or any and all othercommercial damages or losses), even if such Contributor has been advised of thepossibility of such damages.9. Accepting Warranty or Additional Liability. While redistributing the Work or DerivativeWorks thereof, You may choose to offer, and charge a fee for, acceptance of support,warranty, indemnity, or other liability obligations and/or rights consistent with thisLicense. However, in accepting such obligations, You may act only on Your own behalfand on Your sole responsibility, not on behalf of any other Contributor, and only if Youagree to indemnify, defend, and hold each Contributor harmless for any liabilityincurred by, or claims asserted against, such Contributor by reason of your acceptingany such warranty or additional liability.END OF TERMS AND CONDITIONS APPENDIXAPPENDIX: How to apply the Apache License to your work.To apply the Apache License to your work, attach the following boilerplate notice, withthe fields enclosed by brackets "[]" replaced with your own identifying information.(Don't include the brackets!) The text should be enclosed in the appropriate commentsyntax for the file format. We also recommend that a file or class name and descriptionof purpose be included on the same "printed page" as the copyright notice for easieridentification within third-party archives.Copyright [yyyy] [name of copyright owner]xii

PrefaceLicensed under the Apache License, Version 2.0 (the "License"); you may not use this fileexcept in compliance with the License. You may obtain a copy of the License athttp://www.apache.org/licenses/LICENSE-2.0Unless required by applicable law or agreed to in writing, software distributed under theLicense is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OFANY KIND, either express or implied. See the License for the specific language governingpermissions and limitations under the License.Oracle must give recipients a copy of this license (doc)Apache httpmimeApache HttpClient MimeCopyright 1999-2019 The Apache Software FoundationThis product includes software developed at The Apache Software Foundation (http://www.apache.org/).From the license fileApache License Version 2.0January 2004http://www.apache.org/licenses/TERMS AND CONDITIONS FOR USE, REPRODUCTION, AND DISTRIBUTION1. Definitions."License" shall mean the terms and conditions for use, reproduction, and distribution asdefined by Sections 1 through 9 of this document."Licensor" shall mean the copyright owner or entity authorized by the copyright owner that isgranting the License."Legal Entity" shall mean the union of the acting entity and all other entities that control, arecontrolled by, or are under common control with that entity. For the purposes of this definition,"control" means (i) the power, direct or indirect, to cause the direction or management of suchentity, whether by contract or otherwise, or (ii) ownership of fifty percent (50%) or more of theoutstanding shares, or (iii) beneficial ownership of such entity."You" (or "Your") shall mean an individual or Legal Entity exercising permissions granted bythis License."Source" form shall mean the preferred form for making modifications, including but notlimited to software source code, documentation source, and configuration files."Object" form shall mean any form resulting from mechanical transformation or translation ofa Source form, including but not limited to compiled object code, generated documentation,and conversions to othe

This guide provides usage information about Oracle SQLcl (SQL Developer Command Line), a Java-based command-line interface for Oracle Database. Audience. This guide is intended for those using Oracle SQLcl. Documentation Accessibility. For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at