Re: PL/SQL errors...

On 26 Jul 2005 10:52:27 -0700, "Joel" <Joel.DuBien_at_gmail.com> wrote:

>Hi, I'm trying to get the following package to compile, but I keep
>getting cryptic error messages... Please help!
>
>SQL> CREATE OR REPLACE PACKAGE BODY AUDIT_PKG
> 2 AS
> 3 PROCEDURE CHECK_VAL( L_TNAME IN VARCHAR2, L_TNAMEID IN NUMBER,
>L_CNAME IN VARCHAR2,
> 4 L_NEW IN VARCHAR2, L_OLD IN VARCHAR2, L_USER
>IN VARCHAR2)
> 5 IS
> 6 BEGIN
> 7 IF ( L_NEW <> L_OLD OR
> 8 (L_NEW IS NULL AND L_OLD IS NOT NULL) OR
> 9 (L_NEW IS NOT NULL AND L_OLD IS NULL) )
> 10 THEN
> 11 BEGIN
> 12 INSERT INTO LOG VALUES
> 13 ((SELECT LOG_SEQ.NEXTVAL FROM DUAL),(SELECT
>TRANSACTION_SEQ.CURRVAL FROM DUAL),
> 14
>UPPER(L_TNAME),L_TNAMEID,UPPER(L_CNAME),L_OLD,L_NEW,L_USER,SYSDATE,'');
> 15 END;
> 16 END IF;
> 17 END;
> 18
> 19 PROCEDURE CHECK_VAL( L_TNAME IN VARCHAR2, L_TNAMEID IN NUMBER,
>L_CNAME IN VARCHAR2,
> 20 L_NEW IN DATE, L_OLD IN DATE, L_USER IN
>VARCHAR2)
> 21 IS
> 22 BEGIN
> 23 IF ( L_NEW <> L_OLD OR
> 24 (L_NEW IS NULL AND L_OLD IS NOT NULL) OR
> 25 (L_NEW IS NOT NULL AND L_OLD IS NULL) )
> 26 THEN
> 27 BEGIN
> 28 INSERT INTO LOG VALUES
> 29 ((SELECT LOG_SEQ.NEXTVAL FROM DUAL),(SELECT
>TRANSACTION_SEQ.CURRVAL FROM DUAL),
> 30 UPPER(L_TNAME),L_TNAMEID,UPPER(L_CNAME),TO_CHAR( L_OLD,
>'DD-MON-YYYY HH24:MI:SS' ),
> 31 TO_CHAR( L_NEW, 'DD-MON-YYYY HH24:MI:SS'
>),L_USER,SYSDATE,'');
> 32 END;
> 33 END IF;
> 34 END;
> 35
> 36 PROCEDURE CHECK_VAL( L_TNAME IN VARCHAR2, L_TNAMEID IN NUMBER,
>L_CNAME IN VARCHAR2,
> 37 L_NEW IN NUMBER, L_OLD IN NUMBER, L_USER IN
>VARCHAR2)
> 38 IS
> 39 BEGIN
> 40 IF ( L_NEW <> L_OLD OR
> 41 (L_NEW IS NULL AND L_OLD IS NOT NULL) OR
> 42 (L_NEW IS NOT NULL AND L_OLD IS NULL) )
> 43 THEN
> 44 BEGIN
> 45 INSERT INTO LOG VALUES
> 46 ((SELECT LOG_SEQ.NEXTVAL FROM DUAL),(SELECT
>TRANSACTION_SEQ.CURRVAL FROM DUAL),
> 47 UPPER(L_TNAME),L_TNAMEID,UPPER(L_CNAME), L_OLD,
>L_NEW,L_USER,SYSDATE,'' );
> 48 END;
> 49 END IF;
> 50 END;
> 51
> 52 END AUDIT_PKG;
> 53 /
>
>Warning: Package Body created with compilation errors.
>
>SQL> SHOW ERR;
>Errors for PACKAGE BODY AUDIT_PKG:
>
>LINE/COL
>--------------------------------------------------------------------------------
>ERROR
>--------------------------------------------------------------------------------
>12/10
>PL/SQL: SQL Statement ignored
>
>12/22
>PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
>ORA-06553: PLS-320: the declaration of the type of this expression is
>incomplete
> or malformed
>
>28/10
>
>LINE/COL
>--------------------------------------------------------------------------------
>ERROR
>--------------------------------------------------------------------------------
>PL/SQL: SQL Statement ignored
>
>28/22
>PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
>ORA-06553: PLS-320: the declaration of the type of this expression is
>incomplete
> or malformed
>
>45/10
>PL/SQL: SQL Statement ignored
>
>LINE/COL
>--------------------------------------------------------------------------------
>ERROR
>--------------------------------------------------------------------------------
>
>45/22
>PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
>ORA-06553: PLS-320: the declaration of the type of this expression is
>incomplete
> or malformed

No version of course. As if PL/SQL never changes, and/or people can mindread can guess which version you are using. We can't and we also don't keep track of them.

You should simply change the inline subquery (select .... nextval from dual)
into
<sequencename.nextval>

--
Sybrand Bakker, Senior Oracle DBA