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;

---

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

Or both?

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

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

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.

LOL@Slimdave.


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

The error I'm getting is:

Quote:

---

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:

Quote:

---

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.

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.

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.

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

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.

Ok, the latest attempt that hasn't worked:

Code:

---

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?

Quote:

---

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:

Code:

---

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
);

---

Hi

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


regards
Hrishy

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