Stored Procedure Problems

[transio]

Can't seem to figure it out.

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;

[stecal]

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?")

[slimdave]

Or both?

[marist89]

http://www.catb.org/~esr/faqs/smart-questions.html

[transio]

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]

Ok, I modified it to this:

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.

[shibha]

LOL@Slimdave.


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

[transio]

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:

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]

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.

[stecal]

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.