CODEWITHSUNDEEP
sqloracle-databaseplsql
Sean
Sean

Reputation: 667

Oracle PL/SQL open cursor text for update/insert rejected with: "target of OPEN must be a query"

I'm trying to understand why OPEN CURSOR FOR will allow SELECT but not UPDATE/INSERT

For example:

create table users( name varchar2(100) );

declare
  cur sys_refcursor;
begin
  open cur for 'insert into users values (''Sean'')';
  dbms_sql.return_result( cur );
end;

Returns

target of OPEN must be a query

== Edit: more info

I tried to create a proc that will handle inline SQL, I was hoping it would be able to handle SELECT and INSERT/UPDATE/DELETE, but it sounds like I need to have it switch to EXEC IMMEDIATE for the non-SELECTs. Or maybe I can append the INSERT with a SELECT 1 ? Off topic but finding a way to dynamically allow any number of arguments would be great too.

The proc:

create or replace procedure exec_sql_text (
  session_id      int
  ,sql_text       varchar2
  ,param_ar_text  varchar2 -- default '[]'

  ,cur out sys_refcursor
) as

  param_ar_text_default varchar2(16384) := case when param_ar_text is null then '[]' else param_ar_text end;

  param_json_ar json_array_t := json_array_t ( param_ar_text_default );
  len int := param_json_ar.get_size();

  type string_varray is varray(5) of varchar2(4096); -- not null;
  ar string_varray := string_varray();

begin
  enforce_session( session_id );
  
  -- convert json_array_t to varray
  for i in 0 .. param_json_ar.get_size - 1 loop
    ar.extend; ar(i + 1) := param_json_ar.get_String(i);
  end loop;

  if    len = 0 then open cur for sql_text;
  elsif len = 1 then open cur for sql_text using ar(1);
  elsif len = 2 then open cur for sql_text using ar(1), ar(2);
  elsif len = 3 then open cur for sql_text using ar(1), ar(2), ar(3);

  else  Raise_Application_Error( -20001, 'exec_sql_text: param-array length out of range, length: ' || len );
  end if;

end exec_sql_text;

== Edit 2:

I'm guessing the best option is to dynamically generate the OPEN FOR and EXECUTE IMMEDIATE with the params, then run it inside another OPEN/EXEC. It feels a little crazy but it'll make switching between OPEN and EXEC a lot easier.

Upvotes: 2

Views: 289

Answers (1)

Koen Lostrie
Koen Lostrie

Reputation: 18695

Well... I googled "oracle sql what is a cursor" and this came back:

In Oracle, a cursor is a mechanism by which you can assign a name to a SELECT statement and manipulate the information within that SQL statement.

and

A cursor is a pointer that points to a result of a query

Cursors are only about selects. They have nothing to do with update/delete/insert statements.

Upvotes: 2

Related Questions