CODEWITHSUNDEEP
sqldatabaseoracle-databasestored-procedures
ArtisanSamosa
ArtisanSamosa

Reputation: 877

What is wrong with my Oracle syntax?

I have an SQL stored procedure that I need to convert to Oracle. I'm having some trouble with the syntax.

The current error I get is:

Error(75,1): PLS-00103: Encountered the symbol "DROP" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge

I assume I have plenty more. Can someone help me figure out what I'm doing wrong?

Here is what I have: 

create or replace PROCEDURE "PROCEDURE1" 
(
  IN_FEATURENAME IN NVARCHAR2 
, OUT_O_RC OUT SYS_REFCURSOR 
) AS


BEGIN
execute immediate 'CREATE TABLE t_name_match( ' || 
'KBID       int     NOT NULL, ' || 
'SYMBOLID   int     NOT NULL,' || 
'FEATURE_NAME   varchar(30) NULL';

execute immediate 'CREATE TABLE t_compat_match( ' || 
'KBID       int     NOT NULL, ' || 
'SYMBOLID   int     NOT NULL,' || 
'FEATURE_NAME   varchar(30) NULL';
/*******************************************************************************    ***
** Name matches
    ********************************************************************************    ***/
/*
** Load matches to Name Table
*/
INSERT INTO t_name_match (KBID, SYMBOLID, FEATURE_NAME)
SELECT  KBID, SYMBOLID, FEATURENAME
  from  FEATURE_MASTER
 where  FEATURENAME like IN_FEATURENAME;

/*******************************************************************************    ***
** Compatibility Statement matches
********************************************************************************    ***/
/*
** Load matches to Compatibility table
*/
 INSERT INTO t_compat_match (KBID, CHARID, SYMBOLID)
SELECT  KBID, CHARID, SYMBOLID
  from  FEATURE_COMPAT
 where  INSTR(IN_FEATURENAME, LINE) > 0;

INSERT INTO t_name_match (KBID, SYMBOLID, FEATURE_NAME)
 SELECT cm.KBID, cm.SYMBOLID, NULL
   from (SELECT DISTINCT KBID, SYMBOLID
        from t_compat_match)    cm
  where NOT EXISTS  (SELECT 'X'
               from t_name_match nm
              where nm.KBID     = cm.KBID
                and nm.SYMBOLID = cm.SYMBOLID);


UPDATE  t_name_match

  SET FEATURE_NAME = (SELECT FEATURENAME
  from  FEATURE_MASTER

  where nm.FEATURE_NAME IS NULL
   and  fm.KBID     = nm.KBID
    and fm.SYMBOLID = nm.SYMBOLID);

 OPEN OUT_O_RC FOR
/*******************************************************************************    ***
** Return result set
********************************************************************************     ***/
SELECT  DISTINCT m.MODELNAME, m.VERSION, m.PLANTID, m.FROMDATE, m.TODATE,
     m.BUILD, m.KBID, nm.SYMBOLID, nm.FEATURE_NAME as FEATURENAME
  FROM  t_name_match        nm,
     KB_MASTER      m
  WHERE     m.KBID = nm.KBID 
    AND     m.MODELGROUP    = 'F'
 ORDER BY m.MODELNAME, m.VERSION;

/*
** Clean up
*/

DROP TABLE #t_name_match
DROP TABLE #t_compat_match

RETURN 0;
END PROCEDURE1;

Here is the original SQL code:

**  Create the stored procedure
*/
ALTER PROCEDURE [dbo].[fcs_feature_usage]
(   @search_string      varchar(50),
    @debug_flag     char(1)     = 'N'
)
as

DECLARE @error          int,
    @search_string_like varchar(51)

SELECT  @search_string_like     = Upper(@search_string) + '%'

IF (@debug_flag = 'Y') OR (@debug_flag = 'X')
    SELECT  GetDate() as "fcs_feature_usage Started"

/*
** Store matching results in intermediate tables (required to avoid 
** JOIN, DISTINCT, and SORT between two, 2 million row tables).
*/
CREATE TABLE #t_name_match
(   KBID        int     NOT NULL,
    SYMBOLID    int     NOT NULL,
    FEATURE_NAME    varchar(30) NULL
)

CREATE TABLE #t_compat_match
(   KBID        int     NOT NULL,
    CHARID      int     NOT NULL,
    SYMBOLID    int     NOT NULL
)
/*******************************************************************************    ***
** Name matches
********************************************************************************    ***/
/*
** Load matches to Name Table
*/
INSERT INTO #t_name_match (KBID, SYMBOLID, FEATURE_NAME)
SELECT  KBID, SYMBOLID, FEATURE_NAME
  from  FCS..T_FEATURE_MASTER
 where  feature_name like @search_string_like

select  @error = @@error
IF (@error <> 0)
   BEGIN
    GOTO error_routine
   END

IF (@debug_flag = 'Y')
    SELECT  GetDate() as "#t_name_match Loaded"

/*******************************************************************************    ***
** Compatibility Statement matches
********************************************************************************    ***/
/*
** Load matches to Compatibility table
*/
INSERT INTO #t_compat_match (KBID, CHARID, SYMBOLID)
SELECT  KBID, CHARID, SYMBOLID
  from  FCS..T_FEATURE_COMPAT
 where  charindex(@search_string, LINE) > 0

select  @error = @@error
IF (@error <> 0)
   BEGIN
    GOTO error_routine
   END

IF (@debug_flag = 'Y')
    SELECT  GetDate() as "#t_compat_match Loaded"

/*
** Add to the name matches table a list of features which had 
** the given string in one of their compatibility statements.
**
** The below join, results in a complete index scan, so broke into
** two statements (INSERT & UPDATE):
**
INSERT INTO #t_name_match (KBID, SYMBOLID, FEATURE_NAME)
SELECT  cm.KBID, cm.SYMBOLID, fm.FEATURE_NAME
  from  FCDB..T_FEATURE_MASTER      fm,
    (SELECT DISTINCT KBID, SYMBOLID
       from #t_compat_match)    cm
 where  fm.KBID     = cm.KBID
   and  fm.SYMBOLID = cm.SYMBOLID
   and  NOT EXISTS  (SELECT 'X'
               from #t_name_match nm
              where nm.KBID     = cm.KBID
                and nm.SYMBOLID = cm.SYMBOLID)
*/
INSERT INTO #t_name_match (KBID, SYMBOLID, FEATURE_NAME)
SELECT  cm.KBID, cm.SYMBOLID, NULL
  from  (SELECT DISTINCT KBID, SYMBOLID
       from #t_compat_match)    cm
 where  NOT EXISTS  (SELECT 'X'
               from #t_name_match nm
              where nm.KBID     = cm.KBID
                and nm.SYMBOLID = cm.SYMBOLID)

select  @error = @@error
IF (@error <> 0)
   BEGIN
    GOTO error_routine
   END

UPDATE  #t_name_match
   set  FEATURE_NAME = fm.FEATURE_NAME
  from  #t_name_match       nm,
    FCS..T_FEATURE_MASTER   fm
 where  nm.FEATURE_NAME IS NULL
   and  fm.KBID     = nm.KBID
   and  fm.SYMBOLID = nm.SYMBOLID

select  @error = @@error
IF (@error <> 0)
   BEGIN
    GOTO error_routine
   END

IF (@debug_flag = 'Y')
    SELECT  GetDate() as "Added #t_compat_match to #t_name_match"

/*******************************************************************************    ***
** Return result set
    ********************************************************************************        ***/
SELECT  DISTINCT m.Model_Name, m.Version, m.plant_id fac_cd,     m.FROM_DATE,           m.TO_DATE,
        m.build, m.KBID, nm.SYMBOLID, left(nm.FEATURE_NAME,12) featureName
      FROM  #t_name_match       nm,
       T_MODEL_MASTER       m
         WHERE  m.KBID      = nm.KBID 
       AND  m.Model_Group   = 'F'
    ORDER BY m.Model_Name, m.version

    /*
    ** Clean up
    */
DROP TABLE #t_name_match
DROP TABLE #t_compat_match

IF (@debug_flag = 'Y') OR (@debug_flag = 'X')
    SELECT  GetDate() as "fcs_feature_usage Completed"

RETURN 0

/*********************************************************************
** Error Processing Routine. 
**********************************************************************/
error_routine:

DROP TABLE #t_name_match
 DROP TABLE #t_compat_match

RETURN  @error

Upvotes: 0

Views: 130

Answers (2)

Justin Cave
Justin Cave

Reputation: 231661

You almost certainly don't want create and drop tables in an Oracle procedure. Oracle temporary tables are very different than SQL Server temporary tables. In SQL Server, the definition of the temporary table is local to the session (assuming the table name is prefixed with a #). Oracle has no concept of this sort of local temporary table. A temporary table in Oracle is global-- the definition is visible to all sessions, the data is visible only to the local session. This means that you would create the temporary tables once-- outside of your procedure at the same time that you are defining your permanent tables-- and that you wouldn't drop them in the procedure.

Something like

CREATE GLOBAL TEMPORARY TABLE t_name_match
(   KBID        int     NOT NULL,
    SYMBOLID    int     NOT NULL,
    FEATURE_NAME    varchar2(30) NULL
)
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE t_compat_match
(   KBID        int     NOT NULL,
    CHARID      int     NOT NULL,
    SYMBOLID    int     NOT NULL
)
ON COMMIT DELETE ROWS;


create or replace PROCEDURE "PROCEDURE1" 
(
  IN_FEATURENAME IN NVARCHAR2 
, OUT_O_RC OUT SYS_REFCURSOR 
) AS


BEGIN
/*******************************************************************************    ***
** Name matches
    ********************************************************************************    ***/
/*
** Load matches to Name Table
*/
INSERT INTO t_name_match (KBID, SYMBOLID, FEATURE_NAME)
SELECT  KBID, SYMBOLID, FEATURENAME
  from  FEATURE_MASTER
 where  FEATURENAME like IN_FEATURENAME;

/*******************************************************************************    ***
** Compatibility Statement matches
********************************************************************************    ***/
/*
** Load matches to Compatibility table
*/
 INSERT INTO t_compat_match (KBID, CHARID, SYMBOLID)
SELECT  KBID, CHARID, SYMBOLID
  from  FEATURE_COMPAT
 where  INSTR(IN_FEATURENAME, LINE) > 0;

INSERT INTO t_name_match (KBID, SYMBOLID, FEATURE_NAME)
 SELECT cm.KBID, cm.SYMBOLID, NULL
   from (SELECT DISTINCT KBID, SYMBOLID
        from t_compat_match)    cm
  where NOT EXISTS  (SELECT 'X'
               from t_name_match nm
              where nm.KBID     = cm.KBID
                and nm.SYMBOLID = cm.SYMBOLID);


UPDATE  t_name_match

  SET FEATURE_NAME = (SELECT FEATURENAME
  from  FEATURE_MASTER

  where nm.FEATURE_NAME IS NULL
   and  fm.KBID     = nm.KBID
    and fm.SYMBOLID = nm.SYMBOLID);

 OPEN OUT_O_RC FOR
/*******************************************************************************    ***
** Return result set
********************************************************************************     ***/
SELECT  DISTINCT m.MODELNAME, m.VERSION, m.PLANTID, m.FROMDATE, m.TODATE,
     m.BUILD, m.KBID, nm.SYMBOLID, nm.FEATURE_NAME as FEATURENAME
  FROM  t_name_match        nm,
     KB_MASTER      m
  WHERE     m.KBID = nm.KBID 
    AND     m.MODELGROUP    = 'F'
 ORDER BY m.MODELNAME, m.VERSION;

END PROCEDURE1;

might work. The fact that you have the OPEN OUT_O_RC FOR before the comment block and the SELECT after it makes that piece of code somewhat difficult to follow. I also haven't looked for other syntax errors.

Of course, in Oracle, it's pretty uncommon to use temporary tables in the first place-- doing a straight port and using temporary tables in Oracle for every procedure that uses them in SQL Server is going to create some very non-idiomatic Oracle code that is likely to be less efficient than it could be. Ending up with hundreds or thousands of temporary tables in your system may also be annoying particularly if different SQL Server procedures create temporary tables with the same name and a different set of columns. You may be better served with a local collection or simply by not materializing the data first and just querying the underlying permanent tables directly. It depends on why you're using the temporary tables in SQL Server in the first place. See, for example, this thread on alternatives to temporary tables.

Upvotes: 5

Daniel Vukasovich
Daniel Vukasovich

Reputation: 1742

DROP is a DDL statement not valid in PL/SQL code.

Replace it using "execute immediate" 

execute immediate "DROP TABLE t_name_match"

Upvotes: 3

Related Questions