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

Thread: CURSORS --- URGENT , is this POSSIBLE

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi
    i want to concat my IN variable inval to a CURSOR

    the value of the inval will be ' select * from emp where deptno = 10 '

    i will this inval to concat with the CURSOR @ the declaration part

    create or replace procedure test_formstr(inval in varchar2,outval out varchar2) as
    a varcahr2(100);
    cursor c1 is inval;
    begin
    dbms_output.put_line(a);
    valoutab := 1;
    end;

    is this POSSIBLE?
    plsss
    write back @ balajiyes@yahoo.com or jegannathans@yahoo.com
    jegan
    Cheers!
    OraKid.

  2. #2
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    It sounds as if you're trying to open a cursor based on a piece of SQL held in a string variable. You can do this using the "open cursor ...for..." command. Here's an example:

    declare
    type cv is ref cursor;
    my_curs cv;
    dummy varchar2(1);
    procedure open_cursor (curs_text in varchar2,
    curs_out in out cv) is
    begin
    open curs_out for curs_text;
    end;
    begin
    open_cursor('select * from dual', my_curs);
    fetch my_curs into dummy;
    dbms_output.put_line(dummy);
    close my_curs;
    end;

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598

    How to fetch multiple values

    Hi,

    This is possible if the cursor returns a single value..how to get the cursor which returns multiple values and displayit..
    say SELECT EMPNO FROM EMP WHERE DEPARTMENT_NO=10;
    THIS WILL RETURN FIVE ROWS..HOW TO RETREIVE IT..

    balaji
    Cheers!
    OraKid.

  4. #4
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    You simply put it in a loop as you would with any other cursor:

    loop
    fetch my_curs into xxxx;
    if my_curs%notfound then
    exit;
    end if;
    end loop;

    I don't think you can do a cursor for loop, though, which is a pity.

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    hi
    thanQ i did as u said but we have to do some small changes its working

    CREATE OR REPLACE PROCEDURE sp_bal_transfer_insert (aa IN VARCHAR2,bb IN VARCHAR2,
    cc OUT NUMBER,sessionid in varchar2) AS
    TYPE c1 IS REF CURSOR;
    y c1;
    y1 c1;
    x1 ACCOUNT_STATEMENT.NO_BT%type;
    x2 ACCOUNT_STATEMENT.AMT_BT%type;
    x3 NUMBER(38);
    x4 NUMBER(38);
    session_id_val VARCHAR2(250);
    lv_seq NUMBER;
    lv_inc NUMBER;
    lv_err_cd VARCHAR2(10);
    lv_err_msg VARCHAR2(250);
    PROCEDURE oc (ct IN VARCHAR2, ct1 IN VARCHAR2,co IN OUT c1,co1 IN OUT c1) IS
    BEGIN
    OPEN co FOR ct;
    OPEN co1 FOR ct1;
    END;
    BEGIN
    SELECT seq_process_id.nextval INTO lv_seq FROM dual;
    session_id_val := sessionid;
    oc(aa,bb,y,y1);
    FETCH y1 INTO x4;
    LOOP
    FETCH y INTO x1,x2,x3;
    EXIT WHEN y%notfound;
    INSERT INTO WRK_BAL_TRNSFR_IN(month_year,session_id,bt_in_no,bt_in_amt,avg_no_card_hold,avg_amt_card_hold)
    VALUES(x3,session_id_val,x1,x2,x1/x4,x2/x4);
    lv_inc :=lv_inc+1;
    END LOOP;
    CLOSE y;
    sp_proc_stat(1201,5,1,lv_inc ,lv_inc ,'no error',0,lv_seq,199801);
    cc := lv_inc;
    EXCEPTION
    WHEN OTHERS THEN
    lv_err_cd:=to_char(SQLCODE);
    lv_err_msg:=SQLERRM;
    ROLLBACK;
    sp_proc_stat(1201,5,0,lv_inc ,0,lv_err_cd||lv_err_msg,1,lv_seq,199801);
    COMMIT;
    END sp_bal_transfer_insert;


    thanx again
    jegna
    Cheers!
    OraKid.

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