-
decode won't work in proc??
Hi all,
I am running into a problem here that has me figuratively pounding my head against the wall.
I am trying to do a certain type of select and the select work when run in SQL*Plus Worksheet:
Code:
SELECT u.usr_name AS "created_usr",
DECODE(i.hist_modified_usr,0,' ',(SELECT usr_name FROM sec_usrs WHERE usr_id = i.hist_modified_usr)) as "modified_usr",
i.hist_id AS "hist_id",
i.doc_md5_id AS "doc_id",
i.hist_desc AS "hist_desc",
i.hist_created_dt AS "created_dt",
i.hist_created_usr,
i.hist_modified_dt AS "modified_dt"
i.hist_modified_usr
FROM inf_cat_docs_history i,
sec_usrs u
WHERE i.doc_md5_id = '32_Char_MD5_Hash_Goes_Here'
AND i.hist_created_usr = u.usr_id
However, when I try to compile the same select in a package, I get errors telling me it doesn't like the SELECT statement in the DECODE.
Code:
CREATE OR REPLACE PACKAGE misPkg AS
PROCEDURE getDocHistory(p_doc_md5_id IN CHAR,p_doc_cur IN OUT return_cur);
END misPkg;
/
CREATE OR REPLACE PACKAGE BODY misPkg AS
PROCEDURE getDocHistory(p_doc_md5_id IN CHAR,
p_doc_cur IN OUT return_cur) IS
BEGIN
OPEN p_doc_cur FOR
SELECT u.usr_name AS "created_usr",
DECODE(i.hist_modified_usr,0,' ',(SELECT usr_name FROM sec_usrs WHERE usr_id = i.hist_modified_usr)) as "modified_usr",
i.hist_id AS "hist_id",
i.doc_md5_id AS "doc_id",
i.hist_desc AS "hist_desc",
i.hist_created_dt AS "created_dt",
i.hist_created_usr,
i.hist_modified_dt AS "modified_dt"
i.hist_modified_usr
FROM inf_cat_docs_history i,
sec_usrs u
WHERE i.doc_md5_id = p_doc_md5_id
AND i.hist_created_usr = u.usr_id
ORDER BY i.hist_created_dt desc;
END getDocHistory;
END misPkg;
Can anyone offer some insight into what is going on here, and how I can get this to work?
Thanks in advance for any answers!
Cheers,
Keith.
[edited to remove the comments in the query from debugging - sorry]
Last edited by Taoism; 01-22-2003 at 04:34 PM.
-
Try this:
SELECT u.usr_name AS "created_usr",
NVL(m.usr_name,' ') as "modified_usr",
i.hist_id AS "hist_id",
i.doc_md5_id AS "doc_id",
i.hist_desc AS "hist_desc",
i.hist_created_dt AS "created_dt",
i.hist_created_usr,
i.hist_modified_dt AS "modified_dt"
i.hist_modified_usr
FROM inf_cat_docs_history i,
sec_usrs u,
sec_usrs m
WHERE i.doc_md5_id = p_doc_md5_id
AND i.hist_created_usr = u.usr_id
AND m.usr_id(+) = i.hist_modified_usr
ORDER BY i.hist_created_dt desc
-
Hi LKBrwn_DBA!
Thanks for the response. I took your suggestion and am basically encountering a similar issue (see code below). In the code block below I also included the response from Oracle when trying to compile.
The strange thing (in my mind) is that as straight SQL in the Worksheet, both your query and mine work (and thus, I assume are valid ;p). However they won't compile into a package.
Even the line number in the error message doesn't line up with the line numbers from my text editor.
Is there a way for me to get oracle to display the exact code line it is choking on? The must be a table or view that lists the package code in an uncompiled state, no?
Any feedback on the below code/error is appreciated!
Cheers,
Keith.
Code:
1 CREATE OR REPLACE PACKAGE misPkg AS
2 TYPE return_cur IS REF CURSOR;
3 PROCEDURE getDocHistory(p_doc_md5_id IN CHAR,p_doc_cur IN OUT return_cur);
4 END misPkg;
5 /
6 CREATE OR REPLACE PACKAGE BODY misPkg AS
7 PROCEDURE getDocHistory(p_doc_md5_id IN CHAR,
8 p_doc_cur IN OUT return_cur) IS
9 BEGIN
10 SELECT u.usr_name AS "created_usr",
11 NVL(m.usr_name,' ') AS "modified_usr",
12 i.hist_id AS "hist_id",
13 i.doc_md5_id AS "doc_id",
14 i.hist_desc AS "hist_desc",
15 i.hist_created_dt AS "created_dt",
16 i.hist_created_usr,
17 i.hist_modified_dt AS "modified_dt",
18 i.hist_modified_usr
19 FROM inf_cat_docs_history i,
20 sec_usrs u,
21 sec_usrs m
22 WHERE i.doc_md5_id = p_doc_md5_id
23 AND i.hist_created_usr = u.usr_id
24 AND m.usr_id(+) = i.hist_modified_usr
25 ORDER BY i.hist_created_dt DESC;
26 END getDocHistory;
27 END misPkg;
Package created.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY MISPKG:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3 PLS-00428: an INTO clause is expected in this SELECT statement
5/3 PL/SQL: SQL Statement ignored
-
The line number reported in an error message is perfectly correct. However you must reed *the whole* error message!
It is telling you that the error occured at linenumber 5 (column 3) of the *package body*! If you count the lines starting from the line which says "CREATE OR REPLACE PACKAGE BODY ..." you'll see that the line number 5, column 3 is exactly the posittion of the beginning of your SELECT statement.
It is also telling you what is missing in your SELECT statement. In PL/SQL you have to select INTO something, eg into some variable, record etc. After your column list in the select statement you need INTO clause.
As far as you initial question about DECODE in PL/SQL is concerned, you doesn't mention (yet you always should!) your database release. I suppose it is 8i, as you probably wouldn't get an error in 9i. It is because in pre-9i releases there are two SQL parsers for SQL and PL/SQL. And the on in PL/SQL doesn't like the select inside the DECODE, it's as simple as that.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
You forgot the:
OPEN p_doc_cur FOR
!!!
-
jmodic & LKBrwn_DBA,
Thanks for the replies!
Man, I tell ya, sometimes it is so hard to find such a simple (and dumb) error like that.
In the future I will post the version (for reference it is 8.1.7.0 I think - I use 8i Personal edition for initial development).
Cheers,
Keith.
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
|