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 :D

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.

[transio]

I'll try it, but like I said, the query was working fine outside the procedure.

[shibha]

Only other thing that comes to mind is that whatever client you are using it in, has Languages or Locales or something in your query specified as a reserved word ?!!

After this I give up.

[transio]

Ok, the latest attempt that hasn't worked:

CREATE OR REPLACE PROCEDURE transio.get_language_id
(p_language_id OUT INTEGER) IS
BEGIN
SELECT l.id
INTO p_language_id
FROM transio.languages l, transio.locales nls
WHERE l.locale_id = nls.id
AND nls.language_code = 'en'
AND nls.country_code = 'US';
END;

Additional information - I'm attempting to execute this through Squirrel SQL Client using the Oracle Thin JDBC Driver. Could that be causing the problem?

[transio]

Originally posted by shibha
Only other thing that comes to mind is that whatever client you are using it in, has Languages or Locales or something in your query specified as a reserved word ?!!
I don't think that's the problem, because I created the tables without any problems:

CREATE TABLE transio.locales
(
id INTEGER NOT NULL,
language_code CHAR(2) NULL,
country_code CHAR(2) NULL,
name VARCHAR2(255) NOT NULL
);
CREATE TABLE transio.languages
(
id INTEGER NOT NULL,
locale_id INTEGER NULL,
internal_string_id INTEGER NOT NULL
);

[hrishy]

Hi

Maybe foolish..bit can you post your JDBC code here..


regards
Hrishy

[transio]

There's no JDBC code. I'm executing the query in Squirrel SQL Client, which uses JDBC to connect. At this point, I'm thinking it's the client that's causing the problems, and working on other ways to connect to the database.

Thanks for your help, everyone :)