-
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.
-
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;
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|