CODEWITHSUNDEEP
sqloracle-databaseplsql
Alonso Contreras
Alonso Contreras

Reputation: 675

PL SQL error symbol in insert statement from stored procedure

I start to work with Oracle PL/SQL. I am trying to create a simple stored procedure to insert to a table but there is no way I will find the problem that is throwing me.

This is error:

PLS-00103: Encountered the symbol "(" when expecting one of the following:

:= . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

My stored procedure:

CREATE OR REPLACE PROCEDURE TECH.SP_INSERT
(
   p_date_now    IN DATE,
   p_user_id     IN NUMBER,
   p_summary     IN VARCHAR2(1000)
)
AS 
BEGIN 

INSERT INTO SUMMARY_TABLE
          (
            id,
            date,
            user_id,
            summary_text
          )
      VALUES
          (
            SEQ_TBL_SUMMARY_ID.NEXTVAL,
            p_date_now,
            p_user_id,
            p_summary
          );

END TECH.SP_INSERT;

Upvotes: 1

Views: 110

Answers (1)

Kazi Mohammad Ali Nur Romel
Kazi Mohammad Ali Nur Romel

Reputation: 15905

You cannot specify a size for a VARCHAR2 parameter to a procedure.

The procedure should happily accept strings up to 32k in size (the maximum size of a VARCHAR2 in PL/SQL). If it were a function that was being called from SQL rather than PL/SQL, the limit would be 4k because the maximum size of a VARCHAR2 in SQL is only 4k.

Here goes your code.

CREATE OR REPLACE PROCEDURE SP_INSERT(
   p_date_now    IN DATE,
   p_user_id     IN NUMBER,
   p_summary     IN VARCHAR2 )
is
BEGIN 

INSERT INTO SUMMARY_TABLE
          (
            id,
            date,
            user_id,
            summary_text
          )
      VALUES
          (
            SEQ_TBL_SUMMARY_ID.NEXTVAL,
            p_date_now,
            p_user_id,
            p_summary
          );

END;

Upvotes: 3

Related Questions