oracle 使用引号时出现问题：PL/SQL：ORA-00933：SQL命令未正确结束

kyvafyod 于 2023-11-17 发布在 Oracle

关注(0)|答案(3)|浏览(142)

我有这个PL/SQL查询：

DECLARE
  v_table_owner VARCHAR2(30) := 'TEST_USER';
  v_table_name VARCHAR2(30) := 'TEST_TABLE';
  v_view_name VARCHAR2(30) := 'PARTITION_TABLE_VIEW';
  BEGIN 
  -- Create a view to associate partitions with tables  
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || v_view_name || ' AS 
  SELECT aip.index_owner, aip.index_name, aip.partition_name, ait.table_name 
   FROM all_ind_partitions aip 
    JOIN all_tab_partitions ait ON aip.index_owner = ait.table_owner  AND aip.index_name = ait.table_name 
WHERE aip.status = ''UNUSABLE''';
-- Rebuild unusable indexes for the specified table  
FOR rec_index IN (SELECT
    owner,
    index_name
FROM
    all_indexes
WHERE
    table_name = ' || v_table_name || '
    AND status = ''UNUSABLE'')   
 LOOP    
EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_index.owner || '.' || rec_index.index_name || ' REBUILD';

 END LOOP;
-- Rebuild unusable partitions for the specified table using the view   
  FOR rec_partition IN (SELECT index_owner,index_name,partition_name
FROM
    ' || v_view_name || '
WHERE
    table_name = ' || v_table_name || ') 
    LOOP 
    DECLARE 
    v_sql VARCHAR2(1000);
   BEGIN v_sql := 'ALTER INDEX ' || rec_partition.index_owner || '.' || rec_partition.index_name || ' REBUILD PARTITION ' || rec_partition.partition_name;
   EXECUTE IMMEDIATE v_sql USING v_table_name; -- Bind the variable
   EXCEPTION WHEN OTHERS THEN -- Handle exceptions, e.g., log or ignore
   NULL;
  END;
  END LOOP;
  END;

字符串
问题是，当我运行查询时收到此错误：

Error report -
   ORA-06550: line 25, column 24:
   PL/SQL: ORA-00933: SQL command not properly ended
   ORA-06550: line 18, column 23:
   PL/SQL: SQL Statement ignored
   ORA-06550: line 36, column 9:
   PL/SQL: ORA-00903: invalid table name
   ORA-06550: line 31, column 27:
   PL/SQL: SQL Statement ignored
   06550. 00000 -  "line %s, column %s:\n%s"
   *Cause:    Usually a PL/SQL compilation error.
   *Action:

型
我想这是因为使用了错误的引号，我知道要在字符串中转义一个单引号，必须使用两个单引号（“）。我使用了，但它也不起作用。
请你指导我如何解决这个问题好吗？
任何帮助真的很感激。

oracle

来源：https://stackoverflow.com/questions/77402004/problem-in-using-quotes-pl-sql-ora-00933-sql-command-not-properly-ended

3条答案

按热度按时间

9jyewag01#

FOR rec_index IN (SELECT
    owner,
    index_name
FROM
    all_indexes
WHERE
    table_name = ' || v_table_name || '
    AND status = ''UNUSABLE'')

字符串
不对SQL语句使用字符串文字，因此' || v_table_name || '是单个字符串文字，而不是串联，''UNUSABLE''是无效语法。
您需要：

FOR rec_index IN (
    SELECT owner,
           index_name
    FROM   all_indexes
    WHERE  table_name = v_table_name
    AND    status = 'UNUSABLE'
  )

型
同样：

FOR rec_partition IN (SELECT index_owner,index_name,partition_name
FROM
    ' || v_view_name || '
WHERE
    table_name = ' || v_table_name || ')

型
尝试动态使用v_view_name，从上面的语法标记可以看出，它将' || v_view_name || '和' || v_table_name || '视为字符串而不是串联，这应该是：

DECLARE
    v_cur   SYS_REFCURSOR;
    v_owner ALL_IND_PARTITIONS.INDEX_OWNER%TYPE;
    v_name  ALL_IND_PARTITIONS.INDEX_NAME%TYPE;
    v_pname ALL_IND_PARTITIONS.PARTITION_NAME%TYPE;
  BEGIN
    OPEN v_cur FOR 'SELECT index_owner,
                           index_name,
                           partition_name
                    FROM   ' || v_view_name || '
                    WHERE  table_name = ''' || v_table_name || '''';
    LOOP
      FETCH v_cur INTO v_owner, v_name, v_pname;
      EXIT WHEN v_cur%NOTFOUND;
      BEGIN
        EXECUTE IMMEDIATE 'ALTER INDEX ' || v_owner || '.' || v_name || ' REBUILD PARTITION ' || v_pname;
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(sqlerrm);
      END;
    END LOOP;
  END;

型

注意：您似乎没有在最后的EXECUTE IMMEDIATE语句中声明bind变量，因此不清楚为什么您认为需要USING子句。

这给出了代码：

DECLARE
  v_table_owner VARCHAR2(30) := 'TEST_USER';
  v_table_name VARCHAR2(30) := 'TEST_TABLE';
  v_view_name VARCHAR2(30) := 'PARTITION_TABLE_VIEW';
BEGIN 
  -- Create a view to associate partitions with tables  
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW ' || v_view_name || ' AS 
    SELECT aip.index_owner, aip.index_name, aip.partition_name, ait.table_name 
    FROM   all_ind_partitions aip 
           JOIN all_tab_partitions ait
           ON     aip.index_owner = ait.table_owner
              AND aip.index_name = ait.table_name 
    WHERE  aip.status = ''UNUSABLE''';

  -- Rebuild unusable indexes for the specified table  
  FOR rec_index IN (
    SELECT owner,
           index_name
    FROM   all_indexes
    WHERE  table_name = v_table_name
    AND    status = 'UNUSABLE'
  )   
  LOOP    
    EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_index.owner || '.' || rec_index.index_name || ' REBUILD';
  END LOOP;

  -- Rebuild unusable partitions for the specified table using the view
  DECLARE
    v_cur   SYS_REFCURSOR;
    v_owner ALL_IND_PARTITIONS.INDEX_OWNER%TYPE;
    v_name  ALL_IND_PARTITIONS.INDEX_NAME%TYPE;
    v_pname ALL_IND_PARTITIONS.PARTITION_NAME%TYPE;
  BEGIN
    OPEN v_cur FOR 'SELECT index_owner,
                           index_name,
                           partition_name
                    FROM   ' || v_view_name || '
                    WHERE  table_name = ''' || v_table_name || '''';
    LOOP
      FETCH v_cur INTO v_owner, v_name, v_pname;
      EXIT WHEN v_cur%NOTFOUND;
      BEGIN
        EXECUTE IMMEDIATE 'ALTER INDEX ' || v_owner || '.' || v_name || ' REBUILD PARTITION ' || v_pname;
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(sqlerrm);
      END;
    END LOOP;
  END;
END;
/

型

注意事项：如果您的任何动态标识符是混合或小写的，那么您的查询将失败。在这种情况下，您需要使用带引号的标识符。*

您也可以简化它并摆脱动态视图：

DECLARE
  v_table_owner VARCHAR2(30) := 'TEST_USER';
  v_table_name VARCHAR2(30) := 'TEST_TABLE';
BEGIN 
  -- Rebuild unusable indexes for the specified table  
  FOR rec_index IN (
    SELECT owner,
           index_name
    FROM   all_indexes
    WHERE  table_name = v_table_name
    AND    status = 'UNUSABLE'
  )   
  LOOP    
    EXECUTE IMMEDIATE 'ALTER INDEX ' || rec_index.owner || '.' || rec_index.index_name || ' REBUILD';
  END LOOP;

  -- Rebuild unusable partitions for the specified table using the view
  FOR rec_partition IN (
    SELECT aip.index_owner,
           aip.index_name,
           aip.partition_name
    FROM   all_ind_partitions aip 
           JOIN all_tab_partitions ait
           ON     aip.index_owner = ait.table_owner
              AND aip.index_name = ait.table_name 
    WHERE  aip.status = 'UNUSABLE'
    AND    table_name = v_table_name
  )
  LOOP
    BEGIN
      EXECUTE IMMEDIATE
        'ALTER INDEX ' || rec_partition.index_owner || '.' || rec_partition.index_name
          || ' REBUILD PARTITION ' || rec_partition.partition_name;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(sqlerrm);
    END;
  END LOOP;
END;
/

型
fiddle

赞(0）回复(0）举报 2023-11-17

gmxoilav2#

如果你buetify你的代码，你会看到，在WHERE aip.status = ''UNUSABLE''';之后，你的源代码在字符串之外。但是我们应该如何猜测，如果这段代码应该在字符串内部还是外部？也许它应该在字符串内部，因为你使用了这个奇怪的contrast for循环：
WHERE table_name = ' || v_table_name || '个
这在常规的源代码中是不可能的，也许它应该在字符串内部？
下面我把你的源代码转换后的网站https://www.dpriver.com/pp/sqlformat.htm

DECLARE
  v_table_owner VARCHAR2(30) := 'TEST_USER';
  v_table_name  VARCHAR2(30) := 'TEST_TABLE';
  v_view_name   VARCHAR2(30) := 'PARTITION_TABLE_VIEW';
BEGIN
  -- Create a view to associate partitions with tables
  EXECUTE IMMEDIATE 'CREATE OR REPLACE VIEW '
  || v_view_name
  || ' AS   SELECT aip.index_owner, aip.index_name, aip.partition_name, ait.table_name    FROM all_ind_partitions aip     JOIN all_tab_partitions ait ON aip.index_owner = ait.table_owner  AND aip.index_name = ait.table_name WHERE aip.status = ''UNUSABLE''';
  -- Rebuild unusable indexes for the specified table
  FOR rec_index IN
  (
         SELECT owner,
                index_name
         FROM   all_indexes
         WHERE  table_name = ' || v_table_name || '
         AND    status = ''unusable'')
  LOOP
    EXECUTE IMMEDIATE 'ALTER INDEX '
    || rec_index.owner
    || '.'
    || rec_index.index_name
    || ' REBUILD';
  END LOOP;
  -- Rebuild unusable partitions for the specified table using the view
  FOR rec_partition IN
  (
         SELECT index_owner,
                index_name,
                partition_name
         FROM   ' || v_view_name || '
         WHERE  table_name = ' || v_table_name || ')
  LOOP
    DECLARE
      v_sql VARCHAR2(1000);
    BEGIN
      v_sql := 'ALTER INDEX '
      || rec_partition.index_owner
      || '.'
      || rec_partition.index_name
      || ' REBUILD PARTITION '
      || rec_partition.partition_name;
      EXECUTE IMMEDIATE v_sql USING v_table_name; -- Bind the variable
    EXCEPTION
    WHEN OTHERS THEN -- Handle exceptions, e.g., log or ignore
      NULL;
    END;
  END LOOP;
END;

字符串

赞(0）回复(0）举报 2023-11-17

inb24sb23#

你可以尝试使用q-syntax来换行，以避免与引号混淆：

BEGIN  
   DBMS_OUTPUT.put_line (q'['Hello,' said the child, who didn't like goodbyes.]');  
END;

字符串
你也可以使用成对的符号，比如[]（）<>，这样文字中的任何单引号都不需要加倍。

赞(0）回复(0）举报 2023-11-17

我来回答

oracle 使用引号时出现问题：PL/SQL：ORA-00933：SQL命令未正确结束

3条答案

相关问题

热门标签

最新问答