Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Stored Proc Execute Problem

    Hey All. I'm sure I'm just doing something silly here, because my stored proc compiles fine and I get an error while I run it. Check it out:

    Code:
    SQL> execute rafeaturecodes.rafeaturecodes(' where iSuperid=''10''',:r)
    BEGIN rafeaturecodes.rafeaturecodes(' where iSuperid=''10''',:r); END;
    
    *
    ERROR at line 1:
    ORA-00911: invalid character
    ORA-06512: at "SOEC.RAFEATURECODES", line 16
    ORA-06512: at line 1
    
    
    SQL>
    Any suggestions would be fantastic, thank you in advance
    The stored proc for reference as I said, compiles fine:

    Code:
    DROP TABLE TempItems
    /
    
    
    CREATE GLOBAL TEMPORARY TABLE TempItems 
    (
    	ReqMkt char (3) , 
    	ReqFeature char (5) 
    )
    /
    
    
    CREATE OR REPLACE PACKAGE RAFeatureCodes
    AS
    	TYPE ref_cur         IS      	REF CURSOR;
    	PROCEDURE RAFeatureCodes
    	(
    		WhereSQL                   IN      	VARCHAR2 DEFAULT NULL,
    		RC1                      IN  OUT 	REF_CUR
    	);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY RAFeatureCodes
    AS
    	PROCEDURE RAFeatureCodes
    	(
    		WhereSQL                   IN      	VARCHAR2 DEFAULT NULL,
    		RC1                      IN  OUT 	REF_CUR
    	)
    	AS
     
    		SearchSQL                  VARCHAR2(5000);
    	BEGIN 
     
    		EXECUTE IMMEDIATE 'TRUNCATE TABLE TempItems';
    	
    		RAFeatureCodes.SearchSQL := 'INSERT INTO #TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCode as Feature ' || 'FROM tbl_OpenWindowRequests WHERE cFeatureCode <> ''N/A'' AND cFeatureCode Is Not Null AND ' || RAFeatureCodes.WhereSQL; 
    			EXECUTE IMMEDIATE SearchSQL;
    		RAFeatureCodes.SearchSQL := 'INSERT INTO #TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCode2 as Feature ' || 'FROM tbl_OpenWindowRequests WHERE cFeatureCode2 <> ''N/A'' AND cFeatureCode2 Is Not Null AND ' || RAFeatureCodes.WhereSQL; 
    			EXECUTE IMMEDIATE SearchSQL;
    		RAFeatureCodes.SearchSQL := 'INSERT INTO #TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCode3 as Feature ' || 'FROM tbl_OpenWindowRequests WHERE cFeatureCode3 <> ''N/A'' AND cFeatureCode3 Is Not Null AND ' || RAFeatureCodes.WhereSQL; 
    			EXECUTE IMMEDIATE SearchSQL;
    		RAFeatureCodes.SearchSQL := 'INSERT INTO #TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCode4 as Feature ' || 'FROM tbl_OpenWindowRequests WHERE cFeatureCode4 <> ''N/A'' AND cFeatureCode4 Is Not Null AND ' || RAFeatureCodes.WhereSQL; 
    			EXECUTE IMMEDIATE SearchSQL;
    		RAFeatureCodes.SearchSQL := 'INSERT INTO #TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCode5 as Feature ' || 'FROM tbl_OpenWindowRequests WHERE cFeatureCode5 <> ''N/A'' AND cFeatureCode5 Is Not Null AND ' || RAFeatureCodes.WhereSQL; 
    			EXECUTE IMMEDIATE SearchSQL;
    		OPEN RC1 FOR 
    		SELECT DISTINCT
    				 T.ReqMkt,
    				 T.ReqFeature
    		FROM  TempItems T; 
     
    	END;
    END;
    /

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You already have a 'WHERE' in the sql you are executing:
    Code:
    ...WHERE cFeatureCode <> ''N/A'' AND cFeatureCode Is Not Null AND ' || RAFeatureCodes.WhereSQL;
    and another in the call to the procedure:
    Code:
    SQL> execute rafeaturecodes.rafeaturecodes(' where iSuperid=''10''',:r)
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    EXACTLY what does "SearchSQL" contain at the time of the error?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Dec 2003
    Posts
    148
    Lwrn, I actually realized that right after I posted and I should've edited.. Unforunately, there is something else going on here as well though...
    Just for reference,I pased the code of a run attempt below.
    Code:
    SQL> execute rafeaturecodes.rafeaturecodes('isuperid=''10''',:r)
    BEGIN rafeaturecodes.rafeaturecodes('isuperid=''10''',:r); END;
    
    *
    ERROR at line 1:
    ORA-00904: "ISUPERID": invalid identifier
    ORA-06512: at "SOEC.RAFEATURECODES", line 16
    ORA-06512: at line 1
    
    
    SQL>
    SearchSQL should contain:
    INSERT INTO TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCode as Feature ' || 'FROM tbl_OpenWindowRequests WHERE cFeatureCode != ''N/A'' AND cFeatureCode Is Not Null AND where iSuperID='10';

    Line 16 is actually the line of the first
    Execute Immediate SearchSQL;

    Also, just for the arguments sake (heh, arguments - get it? sorry) I tried this:
    Code:
    SQL> execute rafeaturecodes.rafeaturecodes(' and isuperid=''10''',:r);
    BEGIN rafeaturecodes.rafeaturecodes(' and isuperid=''10''',:r); END;
    
    *
    ERROR at line 1:
    ORA-00936: missing expression
    ORA-06512: at "SOEC.RAFEATURECODES", line 16
    ORA-06512: at line 1
    
    
    SQL>
    Last edited by RhythmAddict; 07-27-04 at 17:53.

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >SearchSQL should contain:
    I did NOT ask for "should contain" but actually contains!

    >INSERT INTO TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, >cFeatureCode as Feature ' || 'FROM tbl_OpenWindowRequests WHERE >cFeatureCode != ''N/A'' AND cFeatureCode Is Not Null AND where iSuperID='10';
    Since when are [||] valid with SQL?
    Since when is [''N/A'' ] valid within SQL?

    Simply put, you should be able to cut the contents of "SearchSQL",
    paste it into SQL*Plus, & get the desired results.

    IIRC, with EXECUTE IMMEDIATE no terminating semi-colon is needed or desired.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Add the following to your procedure just before the EXECUTE IMMEDIATE:
    Code:
    dbms_output.put_line(SearchSQL);
    And then execute like this:
    Code:
    set serverout on size 1000000;
    execute rafeaturecodes.rafeaturecodes('isuperid=''10''',:r)
    ...and you will see exactly how the sql statement is being constructed, it may be you are missing somethig or other!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    It has more to do with the # at the tablename.

    Code:
    SQL> CREATE GLOBAL TEMPORARY TABLE TempItems
      2  (
      3     ReqMkt char (3) ,
      4     ReqFeature char (5)
      5* )
    SQL> /
    
    Table created.
    
    SQL> CREATE OR REPLACE PACKAGE RAFeatureCodes
      2  AS
      3   TYPE ref_cur         IS       REF CURSOR;
      4   PROCEDURE RAFeatureCodes
      5   (
      6    WhereSQL                   IN       VARCHAR2 DEFAULT NULL,
      7    RC1                      IN  OUT  REF_CUR
      8   );
      9  END;
     10  /
    
    Package created.
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  CREATE OR REPLACE PACKAGE BODY RAFeatureCodes
      2  AS
      3     PROCEDURE RAFeatureCodes
      4     (
      5             WhereSQL                   IN           VARCHAR2 DEFAULT NULL,
      6             RC1                      IN  OUT        REF_CUR
      7     )
      8     AS
      9             SearchSQL                  VARCHAR2(5000);
     10     BEGIN
     11                     EXECUTE IMMEDIATE 'truncate table TempItems';
     12             SearchSQL := 'INSERT INTO #TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCod
     13                     EXECUTE IMMEDIATE SearchSQL;
     14             SearchSQL := 'INSERT INTO #TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCod
     15                     EXECUTE IMMEDIATE SearchSQL;
     16             SearchSQL := 'INSERT INTO #TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCod
     17                     EXECUTE IMMEDIATE SearchSQL;
     18             SearchSQL := 'INSERT INTO #TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCod
     19                     EXECUTE IMMEDIATE SearchSQL;
     20             SearchSQL := 'INSERT INTO #TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCod
     21                     EXECUTE IMMEDIATE SearchSQL;
     22             OPEN RC1 FOR
     23             SELECT DISTINCT
     24                              T.ReqMkt,
     25                              T.ReqFeature
     26             FROM  TempItems T;
     27     END;
     28* END;
    SQL> /
    
    Package body created.
    
    SQL> create table tbl_OpenWindowRequests (
      2  cMarket char(3),
      3  cFeatureCode varchar2(100),
      4  cFeatureCode2 varchar2(100),
      5  cFeatureCode3 varchar2(100),
      6  cFeatureCode4 varchar2(100),
      7  cFeatureCode5 varchar2(100)
      8  )
      9  /
    
    Table created.
    
    SQL> alter table tbl_OpenWindowRequests add isuperid varchar2(100);
    
    Table altered.
    
    SQL> insert into tbl_OpenWindowRequests values ('123','1','2','3','4','5','10');
    
    1 row created.
    
    SQL> set serveroutput on
    SQL> var r refcursor
    SQL> execute rafeaturecodes.rafeaturecodes('isuperid=''10''',:r)
    BEGIN rafeaturecodes.rafeaturecodes('isuperid=''10''',:r); END;
    
    *
    ERROR at line 1:
    ORA-00911: invalid character
    ORA-06512: at "XXXXXX.RAFEATURECODES", line 13
    ORA-06512: at line 1
    
    
    SQL> ed
    Wrote file afiedt.buf
    
      1  CREATE OR REPLACE PACKAGE BODY RAFeatureCodes
      2  AS
      3     PROCEDURE RAFeatureCodes
      4     (
      5             WhereSQL                   IN           VARCHAR2 DEFAULT NULL,
      6             RC1                      IN  OUT        REF_CUR
      7     )
      8     AS
      9             SearchSQL                  VARCHAR2(5000);
     10     BEGIN
     11                     EXECUTE IMMEDIATE 'truncate table TempItems';
     12             SearchSQL := 'INSERT INTO TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCode
     13                     EXECUTE IMMEDIATE SearchSQL;
     14             SearchSQL := 'INSERT INTO TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCode
     15                     EXECUTE IMMEDIATE SearchSQL;
     16             SearchSQL := 'INSERT INTO TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCode
     17                     EXECUTE IMMEDIATE SearchSQL;
     18             SearchSQL := 'INSERT INTO TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCode
     19                     EXECUTE IMMEDIATE SearchSQL;
     20             SearchSQL := 'INSERT INTO TempItems (ReqMkt,ReqFeature) SELECT DISTINCT cMarket, cFeatureCode
     21                     EXECUTE IMMEDIATE SearchSQL;
     22             OPEN RC1 FOR
     23             SELECT DISTINCT
     24                              T.ReqMkt,
     25                              T.ReqFeature
     26             FROM  TempItems T;
     27     END;
     28* END;
    SQL> /
    
    Package body created.
    
    SQL> execute rafeaturecodes.rafeaturecodes('isuperid=''10''',:r)
    
    PL/SQL procedure successfully completed.
    
    SQL> print r
    
    REQ REQFE
    --- -----
    123 1
    123 2
    123 3
    123 4
    123 5
    
    SQL>
    Also, the kind of use you are giving to this temporary table fully undefeats the whole purporse of a temp table.

Posting Permissions

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