CODEWITHSUNDEEP
oracle-databaseplsqlexecuteora-24338
Aufal Ahdy
Aufal Ahdy

Reputation: 105

How to execute PL/SQL Oracle in sqlplus?

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

Upvotes: 3

Views: 1764

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

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.

Upvotes: 3

Related Questions