Stored Procedure Problems
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Stored Procedure Problems

  1. #1
    Join Date
    May 2003
    Location
    Miami, FL
    Posts
    22

    Stored Procedure Problems

    Can't seem to figure it out.
    Code:
    CREATE OR REPLACE PROCEDURE get_language_id
    (
    	p_language_code	IN	CHAR(2),
    	p_country_code	IN	CHAR(2),
    	p_language_id	OUT	INTEGER
    )
    IS
    BEGIN
    	SELECT
    		l.id
    	INTO
    		p_language_id
    	FROM
    		languages l
    		INNER JOIN locales nls ON l.locale_id = nls.id
    	WHERE
    		nls.language_code = p_language_code
    		AND nls.country_code = p_country_code;
    	p_language_id := COALESCE(p_language_id, 9);
    END;
    Transio - Home | About | Design | Company

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Would it be because you are tyring to constrain parameters (the char(2)'s) when you aren't supposed to do that? Only pass in the datatype.

    But, by "Can't seem to figure it out," what do you mean? You don't know what this procedure is supposed to do, or you don't know why it doesn't work? (Waiting for some sharp-witted almost unemployed DBA who spends too much time here while at work to ask the third possibility of "Or both?")

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Or both?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    May 2003
    Location
    Miami, FL
    Posts
    22
    Hehe... I know what it's supposed to do... I wrote it

    I was having problems getting it to work. I'm going to try eliminating the constraints on the chars.

    Thx
    Transio - Home | About | Design | Company

  6. #6
    Join Date
    May 2003
    Location
    Miami, FL
    Posts
    22
    Ok, I modified it to this:
    Code:
    CREATE OR REPLACE PROCEDURE transio.get_language_id
    (
    	p_language_code	IN	CHAR,
    	p_country_code	IN	CHAR,
    	p_language_id	OUT	INTEGER
    )
    IS
    BEGIN
    	SELECT
    		l.id
    	INTO
    		p_language_id
    	FROM
    		transio.languages l
    		INNER JOIN transio.locales nls ON l.locale_id = nls.id
    	WHERE
    		nls.language_code = p_language_code
    		AND nls.country_code = p_country_code;
    END;
    Assuming "transio" is the owner of the tables "language" and "locales".

    Still not working :(

    The query itself works, when I run it outside of a procedure.

    What it's doing is retrieving a language_id for the ISO locale identifiers provided (language code and country code as in 'en, 'US').

    This is really getting me down, because I know it should be simple.
    Transio - Home | About | Design | Company

  7. #7
    Join Date
    Jun 2002
    Location
    Denver
    Posts
    54
    LOL@Slimdave.


    If it is giving error, please post error. If not, please change CHAR to VARCHAR2.

  8. #8
    Join Date
    May 2003
    Location
    Miami, FL
    Posts
    22
    The error I'm getting is:
    ORA-00900: invalid SQL statement
    I went a step further than the char thing (which wasn't causing the error anyway) and did this.... still not working:
    Code:
    CREATE OR REPLACE PROCEDURE get_language_id (p_language_id OUT INTEGER) IS
    BEGIN
    	SELECT l.id
    	INTO p_language_id
    	FROM languages l 
    		INNER JOIN locales nls  ON l.locale_id = nls.id
    	WHERE nls.language_code = 'en'
    		AND nls.country_code = 'US';
    END;
    Incidentally, I'm attempting this through a JDBC connection, so my complete error is:
    java.sql.SQLException: ORA-00900: invalid SQL statement
    I have created other stored procedures through the same connection without problems. This one just seems to want to be a pain.
    Transio - Home | About | Design | Company

  9. #9
    Join Date
    May 2003
    Location
    Miami, FL
    Posts
    22
    Incidentally, shibha, I was using CHAR(2)s because that's the datatype of the fields "language_code" and "country_code".

    marist89, I appreciate the resource file, but I certainly wouldn't be posting the problem here if I hadn't done my research before-hand. The fact that you have no positive feedback to offer regarding my problem only shows me that it stumps you, too, and negates the desired effect of your post.
    Transio - Home | About | Design | Company

  10. #10
    Join Date
    May 2002
    Posts
    2,645
    Okay, reading past the first couple of lines of your code, why are you using an inner join? What I mean is this: try re-writing the query the "old" way.
    Last edited by stecal; 05-02-2003 at 06:35 PM.

Posting Permissions

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


Click Here to Expand Forum to Full Width