本文介绍了使用`getclobval()`执行oracle过程时`无效字符错误`的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

以下过程在 ctrll + S 上成功编译(显示 VALID),但在执行时抛出错误:Invalid character error while execution this procedure> 在 SQL DEVELOPER 上.我没有看到任何我有无效字符的地方.

The following procedure compiles successfully on ctrll + S (shows VALID) but on execution throws the error : Invalid character error while executing this procedure on SQL DEVELOPER.I don't see anywhere I have a invalid character.

以下是创建表和插入值的代码:-

The following is the code to Create table and insert values:-

 CREATE TABLE tempt( "set" VARCHAR2(1), "level" VARCHAR2(1), category VARCHAR2(3), value INT );

INSERT INTO tempt
SELECT 'A', 'Z', 'ABC', 847549 FROM dual UNION ALL
SELECT 'A', 'Y', 'ABC', 955808 FROM dual UNION ALL
SELECT 'A', 'X', 'ABC', 983462 FROM dual UNION ALL
SELECT 'A', 'Z', 'GHI', 762369 FROM dual UNION ALL
SELECT 'A', 'Y', 'DEF', 615863 FROM dual UNION ALL
SELECT 'A', 'X', 'DEF', 474257 FROM dual UNION ALL
SELECT 'B', 'Z', 'ABC', 959843 FROM dual UNION ALL
SELECT 'B', 'Y', 'ABC', 821704 FROM dual UNION ALL
SELECT 'B', 'X', 'ABC', 377211 FROM dual UNION ALL
SELECT 'B', 'Z', 'DEF', 945053 FROM dual UNION ALL
SELECT 'B', 'Y', 'DEF', 919120 FROM dual UNION ALL
SELECT 'B', 'Y', 'ABC', 821704 FROM dual UNION ALL
SELECT 'C', 'X', 'ABC', 377211 FROM dual UNION ALL
SELECT 'C', 'Z', 'DEF', 945053 FROM dual UNION ALL
SELECT 'C', 'Y', 'DEF', 919120 FROM dual UNION ALL
SELECT 'D', 'X', 'ABC', 377211 FROM dual UNION ALL
SELECT 'D', 'Z', 'DEF', 945053 FROM dual UNION ALL
SELECT 'D', 'Y', 'GHI', 919120 FROM dual UNION ALL
SELECT 'E', 'X', 'ABC', 377211 FROM dual UNION ALL
SELECT 'E', 'Z', 'DEF', 945053 FROM dual UNION ALL
SELECT 'E', 'Y', 'ABC', 919120 FROM dual UNION ALL
SELECT 'E', 'Z', 'ABC', 945053 FROM dual UNION ALL
SELECT 'E', 'Y', 'DEF', 919120 FROM dual UNION ALL
SELECT 'B', 'X', 'IJK', 326886 FROM dual

这是在 SQL Developer/DB 上的过程窗口内运行的,即:-

This is run inside the procedure window on the the SQL Developer/DB viz:-

CREATE OR REPLACE PROCEDURE "SCHEMA.pivot"(v_recordset out sys_refcursor)
AS
--v_recordset SYS_REFCURSOR;
  v_sql       long;
  v_cols_1    long;
  v_cols_2    clob;

BEGIN
  SELECT LISTAGG( ''''||"level"||''' AS "'||"level"||'"' , ',' )
          WITHIN GROUP ( ORDER BY "level" DESC )
    INTO v_cols_1
    FROM (
          SELECT DISTINCT "level"
            FROM tempt
          );

SELECT DBMS_XMLGEN.CONVERT (
          RTRIM (
           XMLAGG (XMLELEMENT (
                        e,
                          'MAX(CASE WHEN CATEGORY = '
                      || CHR (39)
                       || CATEGORY
                       || CHR (39)
                       || ' THEN '
                      || CHR (39)
                      || "level"
                       || CHR (39)
                      || ' END) AS '
                       || "level"
                      || '_'
                       || CATEGORY
                      || '',
                       ',')
                    ORDER BY 1 DESC).EXTRACT ('//text()').getclobval (),
           ','))
    INTO v_cols_2
   FROM (SELECT DISTINCT "level", CATEGORY
          FROM tempt);


  v_sql :=
  'SELECT "set", ('|| v_cols_2 ||')
     FROM
     (
      SELECT *
        FROM tempt
           PIVOT
           (
            MAX(value) FOR "level" IN ( '|| v_cols_1 ||' )
           )
          )
          GROUP BY "set"
          ORDER BY "set"';
      v_sql := REPLACE (v_sql, ''', CHR (39));
      DBMS_OUTPUT.PUT_LINE(v_sql);

      OPEN v_recordset FOR v_sql;
 end pivot;

EDIT 1:我运行了答案中提供的查询,虽然编译成功,但我得到一个 Invalid Character错误:-

EDIT 1 : I ran the query provided in the answer ,though compiled sucessfully I get a Invalid CharacterError :-

00911.00000 - 无效字符";*原因:标识符不能以字母和数字以外的任何 ASCII 字符开头.$#_ 也允许在第一个字符之后.双引号括起来的标识符可以包含双引号以外的任何字符.替代引号 (q'#...#') 不能使用空格、制表符或回车作为分隔符.对于所有其他上下文,请参阅 SQL 语言参考手册.我看过其他有同样问题的帖子并尝试了所有方法,但似乎没有解决问题.它显示的错误行是 OPEN v_recordset FOR v_sql;

00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual.I have seen the other posts with same question and tried out everything but doesnt seem to fix the issue. Line it shows for the error is OPEN v_recordset FOR v_sql;

推荐答案

应该

SQL> CREATE OR REPLACE PROCEDURE pivot (v_recordset OUT SYS_REFCURSOR)
  2  AS
  3     --v_recordset SYS_REFCURSOR;
  4     v_sql     LONG;
  5     v_cols_1  LONG;
  6     v_cols_2  CLOB;
  7  BEGIN
  8     SELECT LISTAGG ('''' || "level" || ''' AS "' || "level" || '"', ',')
  9               WITHIN GROUP (ORDER BY "level" DESC)
 10       INTO v_cols_1
 11       FROM (SELECT DISTINCT "level"
 12               FROM tempt);
 13
 14     SELECT DBMS_XMLGEN.CONVERT (
 15               RTRIM (
 16                  XMLAGG (XMLELEMENT (
 17                             e,
 18                                'MAX(CASE WHEN CATEGORY = '
 19                             || CHR (39)
 20                             || CATEGORY
 21                             || CHR (39)
 22                             || ' THEN '
 23                             || CHR (39)
 24                             || "level"
 25                             || CHR (39)
 26                             || ' END) AS '
 27                             || "level"
 28                             || '_'
 29                             || CATEGORY
 30                             || '',
 31                             ',')
 32                          ORDER BY 1 DESC).EXTRACT ('//text()').getclobval (),
 33                  ','))
 34       INTO v_cols_2
 35       FROM (SELECT DISTINCT "level", CATEGORY
 36               FROM tempt);
 37
 38     v_sql := 'SELECT "set", ' || v_cols_2 || '
 39       FROM
 40       (
 41        SELECT *
 42          FROM tempt
 43             PIVOT
 44             (
 45              MAX(value) FOR "level" IN ( ' || v_cols_1 || ' )
 46             )
 47            )
 48            GROUP BY "set"
 49            ORDER BY "set"';
 50     v_sql := REPLACE (v_sql, ''', CHR (39));
 51     DBMS_OUTPUT.PUT_LINE (v_sql);
 52
 53     OPEN v_recordset FOR v_sql;
 54  END pivot;
 55  /

Procedure created.

SQL>

测试:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     l_rc  SYS_REFCURSOR;
  3  BEGIN
  4     pivot (l_rc);
  5  END;
  6  /
SELECT "set", MAX(CASE WHEN CATEGORY = 'GHI' THEN 'Y' END) AS Y_GHI,MAX(CASE
WHEN CATEGORY = 'ABC' THEN 'Y' END) AS Y_ABC,MAX(CASE WHEN CATEGORY = 'ABC' THEN
'Z' END) AS Z_ABC,MAX(CASE WHEN CATEGORY = 'DEF' THEN 'Z' END) AS Z_DEF,MAX(CASE
WHEN CATEGORY = 'IJK' THEN 'X' END) AS X_IJK,MAX(CASE WHEN CATEGORY = 'GHI' THEN
'Z' END) AS Z_GHI,MAX(CASE WHEN CATEGORY = 'DEF' THEN 'X' END) AS X_DEF,MAX(CASE
WHEN CATEGORY = 'ABC' THEN 'X' END) AS X_ABC,MAX(CASE WHEN CATEGORY = 'DEF' THEN
'Y' END) AS Y_DEF
     FROM
     (
      SELECT *
        FROM tempt

PIVOT
           (
            MAX(value) FOR "level" IN ( 'Z' AS "Z",'Y' AS
"Y",'X' AS "X" )
           )
          )
          GROUP BY "set"

ORDER BY "set"

PL/SQL procedure successfully completed.

SQL>

运行结果查询会产生以下结果(截取):

Running the resulting query produces the following result (snipped):

SQL> SELECT "set", MAX(CASE WHEN CATEGORY = 'GHI  <snip>
ORY = 'DEF' THEN 'Z' END) AS Z_DEF,MAX(CASE WHEN  <snip>
(CASE WHEN CATEGORY = 'ABC' THEN 'X' END) AS X_A  <snip>
  2       FROM
  3       (
  4        SELECT *
  5          FROM tempt
  6             PIVOT
  7             (
  8              MAX(value) FOR "level" IN ( 'Z'
  9             )
 10            )
 11            GROUP BY "set"
 12            ORDER BY "set"
 13  /

s Y Y Z Z X Z X X Y
- - - - - - - - - -
A Y Y Z Z   Z X X Y
B   Y Z Z X   X X Y
C   Y Z Z     X X Y
D Y Y Z Z   Z X X Y
E   Y Z Z     X X Y

SQL>


附言忘了说:真的,真的真的在双引号下使用小写命名列是个坏主意.


P.S. Forgot to say: really, really, REALLY bad idea naming column using lower case under double quotes.

这篇关于使用`getclobval()`执行oracle过程时`无效字符错误`的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-26 01:28