CODEWITHSUNDEEP
sqloracle-databaseshellplsql
Priya Viji
Priya Viji

Reputation: 39

pls-00103 error while executing a procedure

I am trying to execute a procedure from shell script and ending up in error:

Please help me on this:

default_shipment_date()
{
log_writer "$pgm_name" "[INFO] $pgm_name - In default_shipment_date Function"
sqlplus -S $CTGAUTH_LOGIN/$CTGAUTH_PWD@$CTGAUTH_TNSNAMES <<-EOF >>  $LOG_DIR/${pgm_name}_${TODAY}.log 2> $LOG_DIR/${pgm_name}_${TODAY}.err

SET SERVEROUTPUT ON
BEGIN
    EXECUTE DEFAULT_SHIPMENT_DATE();
END;
/

exit
EOF
log_writer "$pgm_name" "[INFO] $pgm_name - Exiting default_shipment_date   Function"
}

The error message is show below:

Thu Jul  2 03:24:39 EDT 2015) - [INFO] ctg_new_prds - In default_shipment_date Function 
EXECUTE DEFAULT_SHIPMENT_DATE();
        *
ERROR at line 2:
ORA-06550: line 2, column 9:
PLS-00103: Encountered the symbol "DEFAULT_SHIPMENT_DATE" when expecting one  of
the following:
:= . ( @ % ; immediate
The symbol ":=" was substituted for "DEFAULT_SHIPMENT_DATE" to continue.

Upvotes: 0

Views: 336

Answers (1)

Ollie
Ollie

Reputation: 17538

You do not need to use the "EXECUTE" statement within PL/SQL.

If you NEED to use PL/SQL (including the context switch from SQL) then your code should look like this:

default_shipment_date()
{
log_writer "$pgm_name" "[INFO] $pgm_name - In default_shipment_date Function"
sqlplus -S $CTGAUTH_LOGIN/$CTGAUTH_PWD@$CTGAUTH_TNSNAMES <<-EOF >>  $LOG_DIR/${pgm_name}_${TODAY}.log 2> $LOG_DIR/${pgm_name}_${TODAY}.err

SET SERVEROUTPUT ON
BEGIN
    DEFAULT_SHIPMENT_DATE();
END;
/

exit
EOF
log_writer "$pgm_name" "[INFO] $pgm_name - Exiting default_shipment_date   Function"
}

However, why switch into PL/SQL at all, just execute the procedure from your SQL environment:

default_shipment_date()
{
log_writer "$pgm_name" "[INFO] $pgm_name - In default_shipment_date Function"
sqlplus -S $CTGAUTH_LOGIN/$CTGAUTH_PWD@$CTGAUTH_TNSNAMES <<-EOF >>  $LOG_DIR/${pgm_name}_${TODAY}.log 2> $LOG_DIR/${pgm_name}_${TODAY}.err

SET SERVEROUTPUT ON
EXECUTE DEFAULT_SHIPMENT_DATE();

exit
EOF
log_writer "$pgm_name" "[INFO] $pgm_name - Exiting default_shipment_date   Function"
}

N.B. This assumes your shell script is all correct etc.

Hope it helps, Ollie

Upvotes: 1

Related Questions