Re: Execute Immediate : Can't run ...Please help

From: Chet Justice <chet.justice_at_pfsf.org>
Date: 22 Apr 2005 10:29:06 -0700
Message-ID: <1114190946.707715.148420@l41g2000cwc.googlegroups.com>

Error message would certainly help.

First thing I would guess is that you need double quotes around your variables like this:

strSql := strSql || ' WHERE SLOOKUP_GROUP = '''|| pSLOOKUP_GROUP ||''; strSql := strSql || ' AND ILOOKUP_LANGUAGECODE = ''' || pILOOKUP_LANGUAGECODE || '';

Second, why do you even need the EXECUTE IMMEDIATE? OPEN io_cursor FOR strSql will work just fine.

your reworked code:
AS
  PROCEDURE GET_LOOKUP
    (pILOOKUP_LANGUAGECODE IN NUMBER,

     pSLOOKUP_GROUP IN VARCHAR2,
     IO_CURSOR IN OUT T_CURSOR)

  IS
    strSql VARCHAR2(1000);
  BEGIN     strSql := 'SELECT

                 DISTINCT LOOKUP.SLOOKUP_VALUE,
                 LOOKUP.SLOOKUP_LABEL
               FROM SFIS.LOOKUP';

    strSql := strSql || ' WHERE SLOOKUP_GROUP = ''' || pSLOOKUP_GROUP || '';

    strSql := strSql || ' AND ILOOKUP_LANGUAGECODE = ''' || pILOOKUP_LANGUAGECODE || '';

    IF (pSLOOKUP_GROUP='FacStatusEntryType') OR (pSLOOKUP_GROUP='FacOwner') THEN

      OPEN IO_CURSOR FOR
        strSql := strSql || ' ORDER BY LOOKUP.SLOOKUP_VALUE';
    ELSE
      OPEN IO_CURSOR FOR
        strSql;

    END IF;
  END GET_LOOKUP_VALUE_LABEL;
END PAC_LOOKUP; hope this helps. if not, post the error message.

chet Received on Fri Apr 22 2005 - 12:29:06 CDT