DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: decode won't work in proc??

  1. #1
    Join Date
    Mar 2002
    Posts
    22

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492
    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


  3. #3
    Join Date
    Mar 2002
    Posts
    22
    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

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  5. #5
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492


    You forgot the:

    OPEN p_doc_cur FOR

    !!!

  6. #6
    Join Date
    Mar 2002
    Posts
    22
    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
  •  


Click Here to Expand Forum to Full Width