Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2006
    Posts
    140

    Unhappy Unanswered: Oracle Parsing and fixing code

    Hello,

    I was hoping someone could help me on parsing a delimited string from crystal reports to oracle. What I have is I have created a stored procedure/package in oracle and I use crystal reports 9 to query from the procedure that has 3 parameters. This all works fine and great however, currently users only have the option of enter a single value in each parameter. I need to give them the ability to enter multiple values, single values or even use the word ALL to select all data from the dataset. The multiple value string should take the format cat, dog, mouse and pass/parse this information to the oracle stored procedure which would them find the results and display into crystal reports. I have no idea how to do this, where to begin and where to start putting my code in. I am completely new to oracle but think I have managed with the help of experts to get on my way. Here is a sample of my code that I will need to add parsing to. A function would probably be very useful as I will be using this code in all my reports. Thanks experts.

    CREATE OR REPLACE PACKAGE GroupsPackage
    AS
    TYPE CURSOR_TYPE IS REF CURSOR;
    PROCEDURE test(CategorySelection IN VARCHAR2,
    GroupSelection IN VARCHAR2,
    TypeSelection IN VARCHAR2,
    results_cursor IN OUT CURSOR_TYPE);
    END;
    /
    CREATE OR REPLACE PACKAGE BODY GroupsPackage
    AS
    PROCEDURE test(CategorySelection IN VARCHAR2,
    GroupSelection IN VARCHAR2,
    TypeSelection IN VARCHAR2,
    results_cursor IN OUT CURSOR_TYPE)
    IS
    BEGIN
    OPEN results_cursor FOR
    SELECT Task_id_,
    Category,
    Type,
    Status,
    (CASE when status = 4 and convert_utc_date(Actual_End_date,'MDT') between trunc(sysdate,'DAY')-7
    and trunc(sysdate,'DAY')-(1/24/60/60) then 1 else 0
    End)CurrentWeek

    FROM chg_task
    WHERE Category = CategorySelection AND
    Type = TypeSelection AND
    Implementor_group_ = GroupSelection
    ORDER BY Task_id_;
    END;
    END;

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Do a search for str2tbl on google, this could be called from your stored proc.

    Alan

  3. #3
    Join Date
    Apr 2006
    Posts
    140

    Red face Still working on this...confused

    Thanks so much AlanP for replying.

    I found a function from this guys Tom's site and is listed below
    create or replace type myTableType as table of number;
    /

    create or replace function str2tbl( p_str in varchar2 ) return myTableType
    as
    l_str long default p_str || ',';
    l_n number;
    l_data myTableType := myTabletype();
    begin
    loop
    l_n := instr( l_str, ',' );
    exit when (nvl(l_n,0) = 0);
    l_data.extend;
    l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
    l_str := substr( l_str, l_n+1 );
    end loop;
    return l_data;
    end;
    /
    show errors;

    Now my I tried to apply this function in the where clause as such

    WHERE Category IN (select category from chg_task(cast(str2tbl(CategorySelection) as myTableType)))
    AND
    Type IN (select Type from chg_task(cast(str2tbl(TypeSelection) as myTableType)))
    AND
    Implementor_group_ IN (select Implementor_group from chg_task(cast(str2tbl(GroupSelection) as myTableType)))
    ORDER BY Task_id_;


    Sorry I am new to oracle and trying to learn. My categorySelection, TypeSelection and GroupSelection, they are parameters. I'm not sure if they are placed in the right area. Something is not right cause I am getting errors. Any idea Alan or any other experts. Thanks for search tip Alan.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    whats the error.

    Alan

  5. #5
    Join Date
    Apr 2006
    Posts
    140
    Missing right Paranthesis. But I don't see anything wrong with the paranthesis so I'm assuming the syntax is wrong.

  6. #6
    Join Date
    Apr 2006
    Posts
    140
    Sorry AlanP,

    I got it to work. Where you chg_task that should be table and it compiled fine. However, I still have an issue. ORA-01741: illegal zero length-identifier. Any ideas.

  7. #7
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Find the first line number for the error and then look it up in the procedure which fell over.

    Also what were the three parameter values?

    Alan

  8. #8
    Join Date
    Apr 2006
    Posts
    140
    Well I try to run this in SQL Developer and it says finished. Really I am not sure how to run it in SQL developer that is why I run it using crystal.


    My 3 parameter values are as follows:

    categorySelection = Reports, Supplies
    Type Selection = Computers, keyboards
    GroupSelection = IT Department

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •