ORA-00922:执行"set long 100000&"时缺少选项或无效的选项甲骨文声明 [英] ORA-00922: missing or invalid option when execute "set long 100000" statement in oracle

问题描述

我正在开发一个Java示例，在其中我一个接一个地执行查询以获取索引的ddl.我的Java代码是-

I am developing a java sample in which I execute query one after other to get ddl of indexces.My java code is -

Statement stmt2 = con.createStatement();
ResultSet rs2 =null;                             
String query = "set long 100000";
rs2 =stmt2.execute(query);
query = "set longchucksize 100000";
rs2 = stmt2.executeQuery(query);
query = "SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000091971C00001$$','CCEEXPERTS') FROM dual";
rs2 = stmt2.executeQuery(queryForScript);

运行以下语句时，它会引发 ORA-00922:缺少选项或无效选项异常

when the following statement run it throws ORA-00922: missing or invalid option exception

字符串查询="set long 100000";

String query = "set long 100000";

rs2 = stmt2.execute(query);

rs2 =stmt2.execute(query);

我如何执行这些语句?

推荐答案

Set long 10000是SQL * PLUS命令，而不是标准SQL，这就是为什么您遇到此错误.

Set long 10000 is a SQL*PLUS command, not a standard SQL that's why you hit the error.

ResultSet rs = stmt.executeQuery("SELECT DBMS_METADATA.GET_DDL('TABLE','YOUR_OBJECTS','JAY') as ddl FROM dual");

              while(rs.next()){
                  System.out.println(rs.getString(1));
              }

输出

CREATE TABLE "JAY"."YOUR_OBJECTS" 
   (    "OWNER" VARCHAR2(128) NOT NULL ENABLE, 
    "OBJECT_NAME" VARCHAR2(128) NOT NULL ENABLE, 
    "SUBOBJECT_NAME" VARCHAR2(128), 
    "OBJECT_ID" NUMBER NOT NULL ENABLE, 
    "DATA_OBJECT_ID" NUMBER, 
    "OBJECT_TYPE" VARCHAR2(23), 
    "CREATED" DATE NOT NULL ENABLE, 
    "LAST_DDL_TIME" DATE NOT NULL ENABLE, 
    "TIMESTAMP" VARCHAR2(19), 
    "STATUS" VARCHAR2(7), 
    "TEMPORARY" VARCHAR2(1), 
    "GENERATED" VARCHAR2(1), 
    "SECONDARY" VARCHAR2(1), 
    "NAMESPACE" NUMBER NOT NULL ENABLE, 
    "EDITION_NAME" VARCHAR2(128), 
    "SHARING" VARCHAR2(13), 
    "EDITIONABLE" VARCHAR2(1), 
    "ORACLE_MAINTAINED" VARCHAR2(1)
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "ORAPDB1_TBS1" 

在SQL * PLUS上运行相同的语句时，我得到了部分DDL.

I got partial DDL while running the same statement on SQL*PLUS.

SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000092981C00086$$','JAY') as ddl FROM dual;

DDL
--------------------------------------------------------------------------------

  CREATE UNIQUE INDEX "JAY"."SYS_IL0000092981C00086$$" ON "JAY"."SYS_EXPORT_SCH

在这里，我可以通过设置SET LONG来获取整个DDL，但是我可以在Java应用程序上获得完整的输出.

Here I can obtain whole DDL by setting SET LONG BUT I get complete output on Java Application.

  CREATE UNIQUE INDEX "JAY"."SYS_IL0000092981C00086$$" ON "JAY"."SYS_EXPORT_SCHEMA_01" (
  PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "ORAPDB1_TBS1" 
  PARALLEL (DEGREE 0 INSTANCES 0) 

这篇关于ORA-00922:执行"set long 100000&"时缺少选项或无效的选项甲骨文声明的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！