-
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?")
-
-
-
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
-
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:
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.
-
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.
Last edited by stecal; 05-02-2003 at 05: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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|