How to execute PL/SQL Oracle in sqlplus?

Question

I have sp in oracle. My SP is shown below

create or replace

PROCEDURE GETMONITORING
(
v_namabarang in varchar2 default null,
v_JenisLayanan in varchar2 default null,
cv_1 IN OUT SYS_REFCURSOR
)
AS
  v_where  VARCHAR2(200);
  v_Select VARCHAR2(200);
  v_from   VARCHAR2(200);
  v_final  VARCHAR2(200);
  v_result VARCHAR2(200);
BEGIN
  v_Select:='select * ';
  v_from  :='from permohonan ';
  v_where :='where sysdate=sysdate ';

IF nvl(length(v_namabarang),0) <> 0 then 
      v_Where := v_Where || ' AND namabarang like ''' || v_namabarang|| '%'' ';
  end if;

IF nvl(length(v_jenislayanan),0) <> 0 then 
      v_Where := v_Where || ' AND jenislayanan like ''' || v_jenislayanan || '%'' ';
  end if;

  v_final :=v_select|| v_from|| v_where;

 dbms_output.put_line(v_result);

END;

I tried to exec in sqlplus by

SQL> var r refcursor;

SQL> exec getmonitoring('AC','1',:r);

SQL>print :r;

and the result is "ORA-24338": Statement handle not executed

So, how I exec my SP in sqlplus ? Thanks

Lalit Kumar B · Accepted Answer · 2015-11-12 06:58:37Z

3

The error is evident from the fact that you never OPEN the CURSOR but making a reference to the SYS_REFCURSOR as OUT parameter.

ORA-24338: statement handle not executed

Cause: A fetch or describe was attempted before executing a statement handle.

Action: Execute a statement and then fetch or describe the data.

You need to use OPEN cursor FOR... statement:

v_final :=v_select|| v_from|| v_where;

-- open the cursor
OPEN cv_1 FOR v_final;

On a side note, while compiling PL/SQL in SQL*Plus, if you see errors, you should always use SHOW ERRORS to see the full error stack.

For example,

SQL> create or replace procedure p
  2  as
  3  begin
  4  null
  5  end;
  6  /

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE P:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PLS-00103: Encountered the symbol "END" when expecting one of the
         following:
         ;
         The symbol ";" was substituted for "END" to continue.

So, now you know the exact line number and the error message which will help you to debug and fix the error.

edited Nov 12, 2015 at 6:58

answered Nov 12, 2015 at 6:39

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Aufal Ahdy Over a year ago

I've input "OPEN cv_1 FOR v_final;" and delete " dbms_output.put_line(v_result);" . But when I exec my SP still error ORA-0094. thanks

Lalit Kumar B Over a year ago

Do as I have shown, see the updated answer. Use SHOW ERRORS and edit your question to show what you re actually doing.

Aufal Ahdy Over a year ago

solved. I was wrong input "OPEN cv_1 FOR v_final;". Thanks

Lalit Kumar B Over a year ago

@AufalAhdy Good. Please mark it as answered, it would others too!

Collectives™ on Stack Overflow

How to execute PL/SQL Oracle in sqlplus?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related