oracle的无效游标,ORACLE：带有动态查询的游标 - 为游标字段抛出错误“无效标识符”(ORACLE: Cursor with dynamic query - throws error “in...

ORACLE：带有动态查询的游标 - 为游标字段抛出错误“无效标识符”(ORACLE: Cursor with dynamic query - throws error “invalid identifier” for cursor field)

我有一个逻辑来实现我必须使用动态sql( 列名和where子句在运行中决定 )。所以我的光标( emp_ref_cursor )有一个动态sql，并有3个光标字段( emp_id,emp_name,dept ) 。

在WHERE子句中使用这些游标字段我试图在循环内执行另一个动态sql.Bt oracle无法识别游标字段并抛出错误，如“ORA-00904： "EMP_REC"."EMP_ID": invalid identifier"虽然我能够通过DBMS_OUTPUT输出emp_rec.emp_id 。

注意：请不要评论代码质量，这不是实际代码。 这只是用来描述问题。 由于某些与合规相关的内容，我无法发布实际代码。

DECLARE

emp_ref_cursor sys_refcursor;

v_sql varchar2(3900);

TYPE emp_rec_type IS RECORD (emp_id number,emp_name varchar2(100),dept_id varchar2(100));

emp_rec emp_rec_type;

v_dept_id number:='1234';

v_dob varchar2(100);

v_desig varchar2(100);

x_dynamic_col_1 varchar2(100):='dob'; --dynamic column(based on some condition)

x_dynamic_col_2 varchar2(100):='designation'; --dynamic column(based on some condition)

x_dynamic_col_3 varchar2(100):='emp_id'; --dynamic column(based on some condition)

BEGIN

v_sql:='SELECT emp_id,emp_name,dept FROM employee WHERE dept_id=' || v_dept_id;

OPEN emp_ref_cursor FOR v_sql;

LOOP

FETCH emp_ref_cursor INTO emp_rec;

exit WHEN emp_ref_cursor%NOTFOUND;

stmt:='SELECT ' || x_dynamic_col_1 || ',' || x_dynamic_col_2 || '

FROM employee A

WHERE emp_id=emp_rec.' || x_dynamic_col_3;

DBMS_OUTPUT.PUT_LINE(stmt);

--Prints the SQL query as expected

DBMS_OUTPUT.PUT_LINE('emp_rec.emp_id:'||emp_rec.emp_id);

--Displays the value!!!

execute immediate stmt into v_dob, v_desig;

--But why is it saying emp_rec.emp_id is invalid identifier??

END LOOP;

END;

I have a logic to implement where I have to use dynamic sql(column names and where clause is decided on the fly).So here my cursor(emp_ref_cursor) has a dynamic sql, and has 3 cursor fields(emp_id,emp_name,dept).

Using these cursor fields in WHERE clause I am trying to execute another dynamic sql inside the loop.Bt oracle isn't able to identify the cursor field and throws an error like "ORA-00904: "EMP_REC"."EMP_ID": invalid identifier" though I am able to output emp_rec.emp_id through DBMS_OUTPUT.

NOTE: Please don't comment on the code quality this is not the actual code. This is just used to describe the problem. I can't post the actual code due to some compliance related stuff.

DECLARE

emp_ref_cursor sys_refcursor;

v_sql varchar2(3900);

TYPE emp_rec_type IS RECORD (emp_id number,emp_name varchar2(100),dept_id varchar2(100));

emp_rec emp_rec_type;

v_dept_id number:='1234';

v_dob varchar2(100);

v_desig varchar2(100);

x_dynamic_col_1 varchar2(100):='dob'; --dynamic column(based on some condition)

x_dynamic_col_2 varchar2(100):='designation'; --dynamic column(based on some condition)

x_dynamic_col_3 varchar2(100):='emp_id'; --dynamic column(based on some condition)

BEGIN

v_sql:='SELECT emp_id,emp_name,dept FROM employee WHERE dept_id=' || v_dept_id;

OPEN emp_ref_cursor FOR v_sql;

LOOP

FETCH emp_ref_cursor INTO emp_rec;

exit WHEN emp_ref_cursor%NOTFOUND;

stmt:='SELECT ' || x_dynamic_col_1 || ',' || x_dynamic_col_2 || '

FROM employee A

WHERE emp_id=emp_rec.' || x_dynamic_col_3;

DBMS_OUTPUT.PUT_LINE(stmt);

--Prints the SQL query as expected

DBMS_OUTPUT.PUT_LINE('emp_rec.emp_id:'||emp_rec.emp_id);

--Displays the value!!!

execute immediate stmt into v_dob, v_desig;

--But why is it saying emp_rec.emp_id is invalid identifier??

END LOOP;

END;

原文：https://stackoverflow.com/questions/35350691

更新时间：2019-07-23 00:12

最满意答案

您将emp_rec定义为本地PL / SQL变量。 PL / SQL数据都不在动态SQL执行的范围内。 执行时，就好像您尝试运行该语句一样 - 因为dbms_output独立显示在单独的SQL上下文中。 如果你这样做，很明显查询不存在emp_rec 。

你引用它需要使用绑定变量：

WHERE emp_id=:dynamic_col_3';

然后执行它：

execute immediate stmt using emp_rec.emp_id;

但是你不能在using子句中使用x_dynamic_col_3局部变量。 因为 - 在这个例子中无论如何 - 查询还需要更改为使用不同的表列，动态记录字段已更改 - 这似乎不是太大的问题。 但是你说where子句也会随时改变。 在这种情况下，您可以在执行之前将另一个局部变量设置为相关的x字段。

You have emp_rec defined as a local PL/SQL variable. None of the PL/SQL data is in scope to the dynamic SQL execution. When it is executed it as if you tried to run the statement - as it is displayed by your dbms_output standalone in a separate SQL context. If you did that it would be clear that emp_rec doesn't exist to the query.

You refer to it you would need to use a bind variable:

WHERE emp_id=:dynamic_col_3';

And then execute it with:

execute immediate stmt using emp_rec.emp_id;

But you can't use the x_dynamic_col_3 local variable in the using clause. Since - in this example anyway - the query would also need to change to use a different table column is the dynamic record field changed - that doesn't seem too much of a problem. But you said the where clause will change on the fly too. In that case you could have another local variable that you set to the relevant x field before the executin.

2016-02-11

相关问答

这就是问题，django期望一个名为id的PK字段 您可以轻松解决它，了解有关集成旧数据库的信息： https ： //docs.djangoproject.com/en/dev/howto/legacy-databases/ 。 对于您的代码，找到主键并通知django。 le'ts supose equipment_id可以充当pk： class RunableFilters(models.Model):

equipment_id = models.BigIntegerField( p

...

您将emp_rec定义为本地PL / SQL变量。 PL / SQL数据都不在动态SQL执行的范围内。 执行时，就好像您尝试运行该语句一样 - 因为dbms_output独立显示在单独的SQL上下文中。 如果你这样做，很明显查询不存在emp_rec 。 你引用它需要使用绑定变量： WHERE emp_id=:dynamic_col_3';

然后执行它： execute immediate stmt using emp_rec.emp_id;

但是你不能在using子句中使用x_dynamic_

...

简而言之，IN子句不支持绑定变量..它只支持你使用的方式的值..你需要像IN (var1, var2)那样指定它; 在不了解您的情况下，您使用了绑定变量。 一种解决方法是使用REFCURSOR通过动态形成查询字符串。 DECLARE

VAR1 VARCHAR2(500);

CUR1 SYs_REFCURSOR;

QUERY_STRING VARCHAR2(2000) := 'SELECT T.COL1 FROM TABLE1 T WHERE T.COL1 IN';

MYREC IS RE

...

问题是PSTRING是单个变量而不是数组。 所以你的陈述实际上等同于 .... where IT.SL_NO = PSTRING

这就是为什么当你通过4时它会起作用而在你通过1,2失败的原因。 使用动态SQL没有任何价值(我们可以在不使用字符串的情况下打开引用游标)。 但是，利用动态SQL是解决问题的一种方法： query_string := 'Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in ('

...

删除db.close(); 从你的开关盒。 在onDestroy()中执行此操作。 remove db.close(); from your switch case. Do this in onDestroy().

您正在使用动态SQL ，因此您必须告诉Oracle哪个单词是标识符，哪个单词是变量。 考虑直接在SQLPlus中运行的以下语句： select t.a,t.b, t.c from test t, table(testCodes) tc

它将失败，因为没有对象在您的数据库中被命名为testCodes 。 您必须告诉SQL引擎testCodes实际上是一个变量。 您必须执行此操作，因为您已选择使用动态SQL，而静态SQL中的变量绑定是自动的。 在大多数情况下，您可以使用与标准变量相同的方式绑定“对象

...

我得到了解决方案 TYPE BRNGB_EXTRACT IS REF CURSOR;

C_BRNGB_EXTRACT BRNGB_EXTRACT;

-- TYPE BRNGB_EXTRACT1 IS TABLE OF C_BRNGB_EXTRACT%ROWTYPE;

TYPE BRNGB_EXTRACT1 IS RECORD (

record_id varchar(30),

card_no varchar(30),

TEST_CARD_NO varchar(30)

...

你没有在访问erg.setId(rs2.getInt(1));之前调用rs2.next() erg.setId(rs2.getInt(1)); you have not called rs2.next() before accessing erg.setId(rs2.getInt(1));

使用窗口功能： SELECT driver_name, cnt

FROM (SELECT d.driver_name, COUNT(*) AS cnt,

MAX(COUNT(*)) OVER () as MAXcnt

FROM Drivers d JOIN

Fastest_laps fl

ON d.ID_driver = fl.ID_driver

GROUP BY d.driver_name

)

...

这是一个微妙的。 从静态语句开始，正确，然后将其转换为动态SQL通常很有用。 在非动态SQL中，我们可能会这样做： SELECT column1, column2

FROM my_table

WHERE column1 LIKE '%' || local_var || '%';

动态等价物是 query := 'SELECT column1, column2

FROM my_table

WHERE column1 LIKE ''%''||:

...