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

    Unanswered: Problem with 2 Variables in a row, in a Stored Proc

    Hi all. I've got a stored proc that works fine - however I added an additional argument/variable to it and it seems like when I have to arguments in a row, I get a "Line # = 15 Column # = 89 Error Text = PL/SQL: ORA-00907: missing right parenthesis" on that line.

    This is the code:
    Code:
    CREATE OR REPLACE PACKAGE te1
    AS
    	TYPE ref_cur         IS      	REF CURSOR;
    	PROCEDURE te1
    	(
    		FIRSTREC		IN		NUMBER,
    		LASTREC			IN		NUMBER,
    		WH			IN		VARCHAR2,
    		SRT			IN		VARCHAR2,
    		RC1                     IN  OUT 	REF_CUR
    	);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY te1
    AS
    	PROCEDURE te1
    	(	
    		FIRSTREC		IN		NUMBER,
    		LASTREC			IN		NUMBER,
    		WH			IN		VARCHAR2,
    		SRT			IN		VARCHAR2,
    		RC1                     IN  OUT 	REF_CUR
    	)
    	AS
    	BEGIN 
    		
    		OPEN RC1 FOR 
    		select * from (select p.*, rownum rnum from (select * from tbl_Reqs wh srt) p where rownum < lastrec) where rnum >= firstrec;
    	END;
    END;
    /
    This is the line it's referring to:

    Code:
    from tbl_Reqs wh srt) p where rownum < lastrec) where rnum >= firstrec;
    specifically, "wh srt"...Wh is a where statement being passed in from an asp page (works fine) and srt is a sort statement doing the same. If I move the variable "srt" to somewhere else, it works fine.

    What's the way around this guys?
    Thanks in advance

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    As far as PL/SQL is concerned, "WH SRT" is just some invalid keywords.

    You will need to use dynamic SQL, i.e. build a string containing the SQL, and concatenate the values of WH and SRT into that:
    Code:
    AS
    BEGIN 
      OPEN RC1 FOR 
        'select * from (select p.*, rownum rnum from (select * from tbl_Reqs ' || wh || srt || ') p where rownum < lastrec) where rnum >= firstrec';
    END;
    Also, if WH or SRT contain any single quotes, you will need to double them up, which you can do like this:
    Code:
    AS
    BEGIN 
      OPEN RC1 FOR 
        'select * from (select p.*, rownum rnum from (select * from tbl_Reqs ' || REPLACE(wh,'''','''''') || REPLACE(srt,'''','''''') || ') p where rownum < lastrec) where rnum >= firstrec';
    END;
    Unless there is a good reason to do all this, it would be much better to just pass in values for bind variables and keep all the SQL logic in the procedure, like this:
    Code:
    PROCEDURE te1
    (	
      FIRSTREC	IN		NUMBER,
      LASTREC	IN		NUMBER,
      P_VAL1	IN		VARCHAR2,
      RC1              IN  OUT 	REF_CUR
    )
    AS
    BEGIN 
      OPEN RC1 FOR 
      select * from (select p.*, rownum rnum from (select * from tbl_Reqs where col1=p_val ORDER BY col2) p where rownum < lastrec) where rnum >= firstrec;
    END;

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Just a note: He will have to do the same (concatenate) for either FIRSTREC and LASTREC input parameters, otherwise he will get a invalid column name error (unless, of course, they exists in table tbl_Reps).

  4. #4
    Join Date
    Dec 2003
    Posts
    148
    hmm..trying this:

    Code:
    select * from (select p.*, rownum rnum from (select * from tbl_OpenWindowRequests  '|| wh || srt ||') p where rownum < lastrec) where rnum >= firstrec;
    I am receiving the PL/SQL: ORA-00907: missing right parenthesis
    on the first '

    Edit: My mistake, I was missing the apostrophes at the beginning and send of the statement. Thank you guys for your help.
    Last edited by RhythmAddict; 07-19-04 at 14:54. Reason: mistake...

  5. #5
    Join Date
    Dec 2003
    Posts
    148

    One last Q..

    Compiles fine with no errors...however running it from the prompt i get this error:

    SQL> variable r refcursor
    SQL> execute gett.gett ('0','15','order by dSubmit DESC','where cReqID = sbob,:r);
    BEGIN gett.gett ('0','15','order by dSubmit DESC','where cReqID = sbob',:r); END;

    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis
    ORA-06512: at "SEC.GETT", line 14
    ORA-06512: at line 1


    SQL>

    the full code:
    Code:
    CREATE OR REPLACE PACKAGE gett
    AS
    	TYPE ref_cur         IS      	REF CURSOR;
    	PROCEDURE gett
    	(
    		FIRSTREC		IN		NUMBER,
    		LASTREC			IN		NUMBER,
    		SRT			IN		VARCHAR2,
    		WH			IN		VARCHAR2,
    		RC1                     IN  OUT 	REF_CUR
    	);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY gett
    AS
    	PROCEDURE gett
    	(	
    		FIRSTREC		IN		NUMBER,
    		LASTREC			IN		NUMBER,
    		SRT		IN		VARCHAR2,
    		WH			IN		VARCHAR2,
    		RC1                     IN  OUT 	REF_CUR
    	)
    	AS
    	BEGIN 
    		
    		OPEN RC1 FOR 
     		'select * from (select p.*, rownum rnum from (select * from tbl_Reqs ' || REPLACE(wh,'''','''''') || REPLACE(srt,'''','''''') || ') p where rownum < ||lastrec||) where rnum >= ||firstrec||';
    
    END;
    
    END;
    /

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You MUST use EXECUTE IMMEDIATE to accomplish what you desire!
    EXECUTE IMMEDIATE is the only way to build & execute SQL dynamically; other than at compile time.
    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.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by anacedent
    You MUST use EXECUTE IMMEDIATE to accomplish what you desire!
    EXECUTE IMMEDIATE is the only way to build & execute SQL dynamically; other than at compile time.
    No, OPEN <ref cursor> FOR <string> is another alternative. The problem is with the string, which should have been constructed like this:

    OPEN RC1 FOR
    'select * from (select p.*, rownum rnum from (select * from tbl_Reqs ' || REPLACE(wh,'''','''''') || REPLACE(srt,'''','''''') || ') p where rownum < '||lastrec||') where rnum >= '||firstrec;

    END;

  8. #8
    Join Date
    Dec 2003
    Posts
    148

    unfortunately..

    I'm still gettin the same error...

    Code:
    SQL> execute gett.gett ('0','15','order by dSubmit DESC','where cReqID = sbob',:r);
    BEGIN gett.gett ('0','15','order by dSubmit DESC','where cReqID = sbob',:r); END;
    
    *
    ERROR at line 1:
    ORA-00907: missing right parenthesis
    ORA-06512: at "SEC.GETT", line 14
    ORA-06512: at line 1
    The full package now reads:

    Code:
    CREATE OR REPLACE PACKAGE gett
    AS
    	TYPE ref_cur         IS      	REF CURSOR;
    	PROCEDURE gett
    	(
    		FIRSTREC		IN		NUMBER,
    		LASTREC			IN		NUMBER,
    		WH				IN		VARCHAR2,
    		SRT				IN		VARCHAR2,
    		RC1             		IN  OUT 	REF_CUR
    	);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY gett
    AS
    	PROCEDURE gett
    	(	
    		FIRSTREC		IN		NUMBER,
    		LASTREC			IN		NUMBER,
    		WH				IN		VARCHAR2,
    		SRT				IN		VARCHAR2,
    		RC1                     IN  OUT 	REF_CUR
    	)
    	AS
    	BEGIN 
    		
    		OPEN RC1 FOR 
    'select * from (select p.*, rownum rnum from (select * from tbl_Reqs|| REPLACE(wh,'''','''''') || REPLACE(srt,'''','''''') || ') p where rownum < '||lastrec||') where rnum >= '||firstrec;
    
    END;
    
    
    END;
    /
    I do apprecaite your help everyone

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    You are not copying and pasting well.

    Also, your where clause (WH) comes first that the order by (srt). So, you must use:

    Code:
    execute gett.gett(0,15,'where cReqID = sbob','order by dSubmit DESC',:r);
    ..
    OR
    ..
    execute gett.gett(FIRSTREC => 0,
                             LASTREC  => 15,
                             WH         => 'where cReqID = sbob',
                             SRT        => 'order by dSubmit DESC',
                             RC1        => :r);
    As for the error, you are missing a space and ' after tbl_Reqs. Try replacing the line with this one (I avoided the REPLACEs, because I dont see any use for them here):

    Code:
    OPEN RC1 FOR 'select * from (select p.*, rownum rnum from (select * from tbl_Reqs ' || wh || ' '|| srt || ') p where rownum < '||lastrec||') where rnum >= '||firstrec;

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

    Talking

    You are executing the procedure with parameters in the wrong order:
    Code:
    SQL> execute gett.gett ('0','15','order by dSubmit DESC'
    ,'where cReqID = sbob',:r);
    Should be:
    Code:
    SQL> execute gett.gett ('0','15','where cReqID = sbob'
    ,'order by dSubmit DESC',:r);
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Dec 2003
    Posts
    148

    ...

    Okay..very close.

    I run
    Code:
    execute gett.gett ('0','15','where cReqID = smitva1','order by dSubmit DESC',:r);
    And I get invalid Identifier. Just FYI cReqID is a column in the table and smitva is a in fact in the table.

    Code:
    SQL> execute gett.gett ('0','15','where cReqID = smitva1','order by dSubmit DESC',:r);
    BEGIN gett.gett ('0','15','where cReqID = smitva1','order by dSubmit DESC',:r); END;
    
    *
    ERROR at line 1:
    ORA-00904: "SMITVA1": invalid identifier
    ORA-06512: at "SEC.GETT", line 14
    ORA-06512: at line 1
    Is this because there are no apostrophes around 'smitva1'?

  12. #12
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713
    Yes, needs to be quoted.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You will need to double up the quotes like this:
    Code:
    'where cReqID = ''smitva1'''
    (Incorrect subsequent comment removed!)
    Last edited by andrewst; 07-20-04 at 09:08.

  14. #14
    Join Date
    Dec 2003
    Posts
    148

    Talking Fantastic...

    This is working just as planned now - thank you to everyone for all the help!

Posting Permissions

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