PLS-00103: Encountered the symbol "SQL" ...

[pigggy]

Hi Guys, i am new to oracle and today i was trying to run a exec a procedure in a script but gave me this errors. any ideas/suggest from anyone will be very helpful. many thanks ..


SQL> exec ERARCH.ER_ARCH.sql.er_arch_and_purge (1) ;
BEGIN ERARCH.ER_ARCH.sql.er_arch_and_purge (1) ; END;
*
ERROR at line 1:
ORA-06550: line 1, column 22:
PLS-00103: Encountered the symbol "SQL" when expecting one of the following:
delete
exists prior
The symbol " was inserted before "SQL" to continue.



============
The sql script
============
bash-3.1$ cat ERARCH.ER_ARCH.sql
CREATE OR REPLACE PACKAGE "ERARCH"."ER_ARCH"
AS
PROCEDURE er_arch_and_purge (p_days_offset IN INTEGER);
END Er_Arch;

[davey23uk]

wow

anyway the correct way to do that is
exec ERARCH.ER_ARCH.er_arch_and_purge (1) ;

[pigggy]

yes.i tried that before, it was giving

SQL> exec ERARCH.ER_ARCH.er_arch.er_arch_and_purge (1);
BEGIN ERARCH.ER_ARCH.er_arch.er_arch_and_purge (1); END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'ERARCH.ER_ARCH' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

i also granted execute priviledge to sys user

[davey23uk]

you typed it wrong

[pigggy]

sorry i did meant "exec ERARCH.ER_ARCH.er_arch_and_purge (1) ;"

SQL> exec ERARCH.ER_ARCH.er_arch_and_purge (1) ;
BEGIN ERARCH.ER_ARCH.er_arch_and_purge (1) ; END;

*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'ERARCH.ER_ARCH' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

[davey23uk]

does the package actually exist - did you create it in the database?

[gandolf989]

Try select owner, object_type from all_objects where object_name='ER_ARCH'; or just desc ERARCH.ER_ARCH.

If I type in delete press enter and then try to do something different sqlplus will think that the second thing I wanted to do related to the delete command. Commands in sqlplus are terminated with a / or ;.

[pigggy]

hmm..it does not seem to be locating the procedure. is there a specify path i need to put the script at? at the moment, i am just putting in for example, /orcl/erarch and from there i login to sqlplus.

SQL> select owner, object_type from all_objects where object_name='ER_ARCH'
2 ;

no rows selected

SQL> desc ERARCH.ER_ARCH
ERROR:
ORA-04043: object ERARCH.ER_ARCH does not exist

[gandolf989]

So it might exist, but you don't have execute privilege on it. Do you have a dba that could help you run this? You mist otherwise how would you get the database?

hmm..it does not seem to be locating the procedure. is there a specify path i need to put the script at? at the moment, i am just putting in for example, /orcl/erarch and from there i login to sqlplus.

SQL> select owner, object_type from all_objects where object_name='ER_ARCH'
2 ;

no rows selected

SQL> desc ERARCH.ER_ARCH
ERROR:
ORA-04043: object ERARCH.ER_ARCH does not exist

[pigggy]

i am the having the database (installed and controlled by me entirely) in my laptop with two sql script that has the procedure.

i granted the execution priviledge to sys. i am connected with sqlplus / as sysdba;

and from there i am executing the package and gave me the error below. i know the scripts work because it is in production now.