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

    Unanswered: Quick Stored Procedure Syntax Q

    Hey guys...quick question, I'm not real sure what the error message on this is asking me to do!
    Definitely some silly sytnax thing with the single quotes I think...But I've tried everything and i'm about to beat my computer into submission
    Code:
    SQL> CREATE OR REPLACE PACKAGE getreqs
      2  AS
      3   TYPE ref_cur         IS       REF CURSOR;
      4   PROCEDURE getreqs
      5   (
      6   
      7    WH    IN  VARCHAR2,
      8    SRT    IN  VARCHAR2,
      9    RC1               IN  OUT  REF_CUR
     10   );
     11  END;
     12  /
    
    Package created.
    
    SQL> CREATE OR REPLACE PACKAGE BODY getreqs
      2  AS
      3   PROCEDURE getreqs
      4   ( 
      5   
      6    WH    IN  VARCHAR2,
      7    SRT    IN  VARCHAR2,
      8    RC1                     IN  OUT  REF_CUR
      9   )
     10   AS
     11   BEGIN 
     12    
     13    OPEN RC1 FOR 'select * from tbl_OpenWindowsRequests ' || wh || ' '|| srt || '
     14  END;
     15  
     16  
     17  END;
     18  /
    
    Warning: Package Body created with compilation errors.
    
    SQL> 
    SQL> show errors
    Errors for PACKAGE BODY GETREQS:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    13/79    PLS-00103: Encountered the symbol "
             END;
             END;" when expecting one of the following:
             ( - + case mod new null <an identifier>
             <a double-quoted delimited-identifier> <a bind variable> avg
             count current max min prior sql stddev sum variance execute
             forall merge time timestamp interval date
             <a string literal with character set specification>
             <a number> <a single-quoted SQL string> pipe

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    what kind of ending to a select statement are you trying to accomplish there?
    Last edited by The_Duck; 07-23-04 at 12:52.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Dec 2003
    Posts
    148
    Basically, I'm trying to pass in a WHERE clause (wh) and an ORDER BY clause (srt)

    ty for your help

  4. #4
    Join Date
    Apr 2004
    Posts
    246
    line 13 ends with
    || '

    try removing it and putting on a semicolon after srt
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  5. #5
    Join Date
    Dec 2003
    Posts
    148
    Hmm...I tried that, this is the error I receive:

    Line # = 12 Column # = 54 Error Text = PL/SQL: ORA-00933: SQL command not properly ended
    Line # = 12 Column # = 16 Error Text = PL/SQL: SQL Statement ignored
    Line # = 17 Column # = 4 Error Text = PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ;

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    you do have one too many quotes i think

    why not see if this works and add from there?

    try this:
    PHP Code:
    OPEN RC1 FOR 
      
    'select * from tbl_OpenWindowsRequests '
    Last edited by The_Duck; 07-23-04 at 13:08.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  7. #7
    Join Date
    Dec 2003
    Posts
    148

    sweet.

    Good idea Duck. I dont know why I didnt try that in the 1st place!
    this is what it should've looked like. Thank you guys

    Code:
    CREATE OR REPLACE PACKAGE getreqs
    AS
    	TYPE ref_cur         IS      	REF CURSOR;
    	PROCEDURE getreqs
    	(
    		FIRSTREC		IN		NUMBER,
    		LASTREC			IN		NUMBER,
    		WH				IN		VARCHAR2,
    		SRT				IN		VARCHAR2,
    		RC1             		IN  OUT 	REF_CUR
    	);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY getreqs
    AS
    	PROCEDURE getreqs
    	(	
    		FIRSTREC		IN		NUMBER,
    		LASTREC			IN		NUMBER,
    		WH				IN		VARCHAR2,
    		SRT				IN		VARCHAR2,
    		RC1                     IN  OUT 	REF_CUR
    	)
    	AS
    	BEGIN 
    		
    		OPEN RC1 FOR 'select * from tbl_OpenWindowsRequests ' || wh || ' ' || srt || ' ';
    END;
    
    
    END;
    /

  8. #8
    Join Date
    Jul 2003
    Posts
    2,296
    It would be nice if you named your package differently than your procedure:

    PHP Code:
    CREATE OR REPLACE PACKAGE pkg_getreqs
    AS
     
    TYPE ref_cur         IS       REF CURSOR;
     
    PROCEDURE sp_getreqs
     
    (
     
      
    WH    IN  VARCHAR2,
      
    SRT    IN  VARCHAR2,
      
    RC1               IN  OUT  REF_CUR
     
    );
    END pkg_getreqs;
    /

    CREATE OR REPLACE PACKAGE BODY pkg_getreqs
      
    AS
       
    PROCEDURE sp_getreqs
       

       
        
    WH    IN  VARCHAR2,
        
    SRT   IN  VARCHAR2,
        
    RC1   IN  OUT  REF_CUR
       
    )
       AS
       
    BEGIN 
        
        OPEN RC1 
    FOR 'select * from tbl_OpenWindowsRequests ' || wh ||' '|| srt;
      
      
       
    END sp_getreqs;
      
      
    END pkg_getreqs;

    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

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