日期:2014-05-17  浏览次数:21135 次

Oracle 返回游标 PLS-00103: 出现符号 "CREATE"
oracle中,在存储过程返回数据集;返回游标的时候报错:PLS-00103: 出现符号 "CREATE";这是什么问题呢?SQL如下:

CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE TEST_CURSOR IS REF CURSOR;
END TESTPACKAGE;

CREATE OR REPLACE PROCEDURE TEST1 ( TEST_CUSROR OUT TESTPACKAGE.TEST_CURSOR) AS
BEGIN
OPEN TEST_CURSOR FOR SELECT * FROM test_table;
END TEST1;

------解决方案--------------------
SQL code
定义写在PACKAGE里面,具体实现的代码要写在PACKAGE BODY里面。
所以你的PROCEDURE具体实现代码在被包含在PACKAGE BODY里面。

------解决方案--------------------
PLS-00103 found 'string' but expected one of the following: 'string'"},

Cause: This error message is from the parser. It found a token (language element) that is inappropriate in this context.

Action: Check previous tokens as well as the one given in the error message. The line and column numbers given in the error message refer to the end of the faulty language construct.

------解决方案--------------------
CREATE OR REPLACE PROCEDURE TEST1 ( TEST_CUSROR OUT sys_refcursor) AS 
BEGIN 
OPEN TEST_CURSOR FOR SELECT * FROM test_table; 
END TEST1;

------解决方案--------------------
SQL code
-- TRY THIS ..
CREATE OR REPLACE PACKAGE TESTPACKAGE IS
TYPE TEST_CURSOR IS REF CURSOR;
PROCEDURE TEST1 (TEST_CUSROR OUT TEST_CURSOR);
END TESTPACKAGE;

CREATE OR REPLACE PACKAGE BODY TESTPACKAGE IS
PROCEDURE TEST1 ( TEST_CUSROR OUT TEST_CURSOR) IS
BEGIN
    OPEN TEST_CURSOR FOR SELECT * FROM bi_request_sim_card;
END TEST1;
end TESTPACKAGE;

------解决方案--------------------
SQL code
-- TRY IT ..
SQL> CREATE OR REPLACE PACKAGE TESTPACKAGE IS
  2  TYPE TEST_CURSOR IS REF CURSOR;
  3  PROCEDURE TEST1 (V_CURSOR OUT TEST_CURSOR);
  4  END TESTPACKAGE;
  5  /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY TESTPACKAGE IS
  2  PROCEDURE TEST1 (V_CURSOR OUT TEST_CURSOR) IS
  3  BEGIN
  4      OPEN V_CURSOR FOR SELECT * FROM bi_request_sim_card;
  5  END TEST1;
  6  end TESTPACKAGE;
  7  /

Package body created