Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    Ontario
    Posts
    8

    Question Unanswered: Return a set of values from a procedure?

    Hi, I try to return a set of values from a procedure. The procedure seems fine on sqlplus. The question is that I can't get returened values using Coldfusion 5. Can anyone take a look for the code created(package,...)

    CREATE OR REPLACE PACKAGE PACK_keyword AS
    TYPE temp_contig_type IS RECORD
    (contigid contiginfo.contigid%TYPE,
    length contiginfo.length%TYPE,
    num_sequences contiginfo.num_sequences%TYPE,
    e_value contiginfo.e_value%TYPE,
    description contiginfo.description%TYPE,
    organism contiginfo.organism%TYPE,
    accession contiginfo.accession%TYPE,
    mnum mbcinfo.mgo_num%TYPE,
    mlink mbcinfo.mlink%TYPE,
    mdesc mbcinfo.mgodesc%TYPE,
    bnum mbcinfo.bgo_num%TYPE,
    blink mbcinfo.blink%TYPE,
    bdesc mbcinfo.bgodesc%TYPE,
    cnum mbcinfo.cgo_num%TYPE,
    clink mbcinfo.clink%TYPE,
    cdesc mbcinfo.cgodesc%TYPE);

    TYPE CUR_contig IS REF CURSOR
    RETURN temp_contig_type;

    PROCEDURE PROC_keywordsearch
    (arg_keyword IN contiginfo.description%type,
    arg_cursor OUT CUR_contig);
    END PACK_keyword;
    /

    SET SERVEROUTPUT ON;

    --create package body

    CREATE OR REPLACE PACKAGE BODY PACK_keyword AS
    PROCEDURE PROC_keywordsearch
    (arg_keyword IN contiginfo.description%type,
    arg_cursor OUT CUR_contig) IS
    CURSOR CUR_contig IS
    select c.contigid, c.length, c.num_sequences, c.e_value, c.description, c.organism, c.accession,
    g.mgo_num, g.mlink, g.mgodesc, g.bgo_num, g.blink, g.bgodesc, g.cgo_num, g.clink, g.cgodesc
    from contiginfo c, mbcinfo g
    where c.contigid = g.contigid (+);
    CUR_result CUR_contig%ROWTYPE;
    BEGIN
    OPEN CUR_contig;
    FETCH arg_result INTO CUR_result;
    RETURN;
    CLOSE CUR_contig;
    IF (CUR_result.contigid is NULL) THEN
    dbms_output.put_line('Your requirement is not found.');
    END IF;
    END PROC_keywordsearch;
    END PACK_keyword;
    /


    This code suppose return a result as CUR_result, isn't it?

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Exclamation

    1) You are not using the arg_keyword in your query!
    2) Not necesary to use 'CURSOR CUR_Cursor' declaration.
    3) FETCH is incorrect.
    4) RETURN will exit procedure BEFORE the statements that follow.
    5) arg_cursor is the 'cursor' you have to populate with a query in order to return some values.
    6) It is bad programming to assign the same name to different objects like:
    TYPE CUR_Cursor ...
    and CURSOR CUR_CURSOR IS ...

    Better try something like this:

    --create package body

    CREATE OR REPLACE PACKAGE BODY PACK_keyword AS
    PROCEDURE PROC_keywordsearch
    (arg_keyword IN contiginfo.description%type,
    arg_cursor OUT CUR_contig) IS
    BEGIN
    OPEN arg_result FOR
    select c.contigid, c.length, c.num_sequences, c.e_value, c.description, c.organism, c.accession,
    g.mgo_num, g.mlink, g.mgodesc, g.bgo_num, g.blink, g.bgodesc, g.cgo_num, g.clink, g.cgodesc
    from contiginfo c, mbcinfo g
    where c.contigid = g.contigid (+);
    IF SQL%ROWCOUNT = 0 THEN
    dbms_output.put_line('Your requirement is not found.');
    END IF;
    END PROC_keywordsearch;
    END PACK_keyword;
    /
    NOTE: still needs to use the 'arg_keyword' in the query.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Aug 2003
    Location
    Ontario
    Posts
    8

    Talking

    Thanks a lot. I got it.

Posting Permissions

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