CODEWITHSUNDEEP
oracle-databasestored-procedurespls-00103
RRTW
RRTW

Reputation: 3190

Oracle stored procedure runtime error with pls 00103

(Update 2019/07/23) New way to call procedure

SET SERVEROUTPUT ON
declare
  variable res sys_refcursor;
begin
  my_schema.SP_READ_MEMBER('11223344', '1970/01/01', res);
EXCEPTION
  WHEN OTHERS
  THEN DBMS_OUTPUT.put_line ('ERROR ' || SQLERRM);
end;
/

Result

Error at line 2
ORA-06550: line 2, column 16:
PLS-00103: Encountered the symbol "SYS_REFCURSOR" when expecting one of the following:

   := . ( @ % ; not null range default character
The symbol ":=" was substituted for "SYS_REFCURSOR" to continue.

(Original post)

I'm not really good on Oracle's Stored Procedure, so this error confused me much time. Had read 10 more threads on this site about PLS-00103. but none of them seems helped with my error.

Here's my Stored Procedure

create or replace procedure my_schema.SP_READ_MEMBER(keywordP in varchar2, birthdayP in varchar2, resultP out sys_refcursor)
is
v_prg_name varchar2(20) := 'SP_READ_MEMBER';
sys_sql    varchar2(1000);

begin
  Insertlog(SYSDATE, v_prg_name, '1.0 Start');
  sys_sql :=  sys_sql || 'select a.no, a.name, a.id_no, to_char(a.birthday, ''yyyy/MM/dd'') as birthday, ''REGISTERED'' as type, email, mobile from rep  a where 1=1 ';
  if keywordP is not null then
    sys_sql :=  sys_sql || ' and (a.no=''' || keywordP || ''' or a.name=''' || keywordP || ''' or a.id_no=''' || keywordP || ''') ';
  end if;
  if birthdayP is not null then
    sys_sql :=  sys_sql || ' and a.birthday=to_date(''' || birthdayP || ''', ''yyyy/MM/dd'') ';
  end if;

  open resultP for sys_sql;
  Insertlog(SYSDATE, v_prg_name, '2.0 Finished w/o error');

  exception
  when others then
  declare
    error_time VARCHAR2(30) := RTRIM(TO_CHAR(SYSDATE, 'YYYY/MM/DD, HH24:MI:SS'));
    error_code NUMBER := SQLCODE;
    error_msg  VARCHAR2(300) := SQLERRM;
  begin
    rollback;
    DBMS_OUTPUT.PUT_LINE(error_time || ',' || TO_CHAR(error_code) || ',' || error_msg);
    Insertlog(SYSDATE, v_prg_name,  error_msg || ', 3.0 ERROR, sql:' || sys_sql);
  end;
end;
/

And run it in toad, with following script :

SET SERVEROUTPUT ON
declare
  res varchar2(1000);
begin
  call my_schema.SP_READ_MEMBER('11223344', '1970/01/01', res);
EXCEPTION
  WHEN OTHERS
  THEN DBMS_OUTPUT.put_line ('ERROR ' || SQLERRM);
end;
/

This error message really confused me much hours...

Error at line 2
ORA-06550: line 4, column 8:
PLS-00103: Encountered the symbol "my_schema" when expecting one of the following:

   := . ( @ % ;
The symbol ":=" was substituted for "my_schema" to continue.

Now I'm stuck here, please gives some suggestions, really need this ...

PS: Got same error message while called from c#

Upvotes: 0

Views: 889

Answers (2)

Popeye
Popeye

Reputation: 35910

As mentioned in the previous answer, There are several mistakes in calling the procedure itself.

Your code to call the procedure should look like the following:

SET SERVEROUTPUT ON
declare
  res SYS_REFCURSOR; -- Changed data type of this variable
begin
  my_schema.SP_READ_MEMBER('11223344', '1970/01/01', res); -- removed 'call'
EXCEPTION
  WHEN OTHERS
  THEN DBMS_OUTPUT.put_line ('ERROR ' || SQLERRM);
end;
/

Cheers!!

Upvotes: 0

Aleksej
Aleksej

Reputation: 22949

You have ro remove the call; for example:

SQL> begin
  2      call testProc;
  3  end;
  4  /
    call testProc;
         *
ERROR at line 2:
ORA-06550: line 2, column 10:
PLS-00103: Encountered the symbol "TESTPROC" when expecting one of the
following:
:= . ( @ % ;
The symbol ":=" was substituted for "TESTPROC" to continue.


SQL> begin
  2      testProc;
  3  end;
  4  /

PL/SQL procedure successfully completed.

Also, notice that your procedure has a sys_refcursor out parameter, but you call it by passing a varchar2.

As an aside, using a varchar2 to handle dates is not a good idea; the date type would be better.

Upvotes: 0

Related Questions