DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Anchored datatypes in stored procedures

  1. #1
    Join Date
    Oct 2002
    Location
    london
    Posts
    41

    Question Anchored datatypes in stored procedures

    Im trying to use anchored datatypes in the argument section of my stored procedure. However I keep getting this error when I try and compile:

    Errors for PROCEDURE PR_LOGSCRIPTSTART:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/49 PLS-00103: Encountered the symbol ";" when expecting one of the
    following:
    := ) , default character
    The symbol ";" was ignored.

    3/38 PLS-00103: Encountered the symbol ";" when expecting one of the
    following:
    := ) , default character
    The symbol ";" was ignored.

    The documentation doesnt seem to state, that there are restrictions where %TYPE is used so im not sure what the problem is. Here is the procedure header and source table:

    CREATE OR REPLACE Procedure PR_LogScriptStart
    (i_DatabaseType IN DatabaseInfo.Version%TYPE;
    ,i_name IN DatabaseInfo.Name%TYPE ;
    ,i_Version IN varchar2
    ,i_ActivityType IN varchar2
    ,i_Description IN varchar2
    ,i_StaticDataDependencyList IN varchar2 DEFAULT NULL
    ,i_VersionDependency IN varchar2 DEFAULT NULL
    )
    IS

    CREATE TABLE DATABASEINFO (
    NAME VARCHAR2 (255) NOT NULL,
    VERSION VARCHAR2 (30) NOT NULL,
    STARTTIME DATE,
    ENDTIME DATE,
    ACTIVITYBYUSER VARCHAR2 (250),
    ACTIVITYTYPE VARCHAR2 (20),
    ACTIVITYONDBVERSION VARCHAR2 (20),
    COMPLETIONSTATUS VARCHAR2 (20),
    PROPERTIES VARCHAR2 (255),
    DESCRIPTION VARCHAR2 (2000) ) ;
    OCP 8i DBA

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    The datatype spec in the procedure parameters list would just be "Varchar2", or "Number", Or "Date" etc, -- you have no need for the scale or precision, which may be why the anchored type doesn't work there.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by slimdave
    The datatype spec in the procedure parameters list would just be "Varchar2", or "Number", Or "Date" etc, -- you have no need for the scale or precision, which may be why the anchored type doesn't work there.
    Huh???

    The problem here is:
    Code:
    CREATE OR REPLACE Procedure PR_LogScriptStart
    (i_DatabaseType IN DatabaseInfo.Version%TYPE;
    ,i_name IN DatabaseInfo.Name%TYPE ;
    ,i_Version IN varchar2
    ,i_ActivityType IN varchar2
    ,i_Description IN varchar2
    ,i_StaticDataDependencyList IN varchar2 DEFAULT NULL
    ,i_VersionDependency IN varchar2 DEFAULT NULL
    )
    IS
    should be
    Code:
    CREATE OR REPLACE Procedure PR_LogScriptStart
    (i_DatabaseType IN DatabaseInfo.Version%TYPE
    ,i_name IN DatabaseInfo.Name%TYPE
    ,i_Version IN varchar2
    ,i_ActivityType IN varchar2
    ,i_Description IN varchar2
    ,i_StaticDataDependencyList IN varchar2 DEFAULT NULL
    ,i_VersionDependency IN varchar2 DEFAULT NULL
    )
    IS
    Jeff Hunter

  4. #4
    Join Date
    Apr 2002
    Posts
    36

    Procedure Error.....

    Errors for PROCEDURE PR_LOGSCRIPTSTART:
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    2/49 PLS-00103: Encountered the symbol ";" when expecting one of the
    following:
    := ) , default character
    The symbol ";" was ignored.

    3/38 PLS-00103: Encountered the symbol ";" when expecting one of the
    following:
    := ) , default character
    The symbol ";" was ignored.
    ====================================================================
    CREATE OR REPLACE Procedure PR_LogScriptStart
    (i_DatabaseType IN DatabaseInfo.Version%TYPE;
    ,i_name IN DatabaseInfo.Name%TYPE ;
    ,i_Version IN varchar2
    ,i_ActivityType IN varchar2
    ,i_Description IN varchar2
    ,i_StaticDataDependencyList IN varchar2 DEFAULT NULL
    ,i_VersionDependency IN varchar2 DEFAULT NULL
    )



    NOTE 1.
    ====
    Your Error is stating that you are using (semicolon instead of comma(,) in front of the Each Anchored datatype Parameters:
    =====================================================
    CREATE OR REPLACE Procedure PR_LogScriptStart
    (i_DatabaseType IN DatabaseInfo.Version%TYPE; --take semicolon out
    ,i_name IN DatabaseInfo.Name%TYPE; --take the semicolon out.
    ======================================================

    NOTE 2.
    ========

    You must put your 'CREATE TABLE' CLAUSE inside the Execution SECTION.
    BEGIN
    create table
    ...
    END;
    xuduro_2000

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width