Procedure Return two values

[orajit]

I have Created one oracle store procedure .There are total 3 parameters 2 in and 1 out parameter ,

I need to insert the out parameter into the table . Put when I am firing insert statemet that will insert only one value .

Could you tell me how to insert more than one values if your procedure returning two values .

My code was little lengthy so could not able to paste here .

I am giving you emp talble exam ..suppose following procedure returning two values

[code=oracle]

Create or replace procedure empty (v_ename varchar2,v_empn o varchar2,v_chil dname out varchar2) as
TYPE childname IS REF CURSOR;
v_childname childname;
begin
open childname for select childname from emp where ename=v_ename and empno= v_empno
FETCH v_childname INTO v_childname ;
EXIT WHEN v_childname %NOTFOUND;
end loop;
end ;
[/code]

if this procedure retruns two value then how to handle these two values ... Please reply

Thanks

[amitpatel66]

Could you please post your insert statement here.

[amitpatel66]

Orajit,


Please make use of [CODE] TAGS when ever you post any source code in this forum. Using CODE TAGS increases the readability and clarity of the source code. For more on posting guidelines, please check http://www.thescripts. com/forum/faq.php?faq=pos ting_guidelines

MODERATOR

[orajit]

[CODE=oracle]
Declare
v_sonname varchar2(100);
begin
empty ('Dev',101,v_so nname);
insert into Emp_family_info ('001',v_sonnam e,999);
end ;
/
[/CODE]
This will insert only one value in Emp_family_info table.

could please guide me if i insert more than one value then i could i do this .

Thanks

Orajit: Please note that the CODE TAGS should end with [/code]

[debasisdas]

What do you mean by two values ?

Two out parameters or two rows ?

[orajit]

two values...one output parameter returns two values ..


how to do that ..hope u clear my query ..Thanks

[amitpatel66]

Check this out:

[code=oracle]

SQL> SELECT * FROM EMP;

ENAME EMPNO DAT
-------------------- ---------- ---------
a 2 17-MAR-08
a 1 17-MAR-08

SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PACKAGE pkg AS
2 TYPE emp_name IS TABLE OF emp.ename%TYPE;
3* END pkg;
SQL> /

Package created.

SQL> ed
Wrote file afiedt.buf

1 CREATE OR REPLACE PROCEDURE get_data(emp_no IN NUMBER, e_name OUT pkg.emp_name) AS
2 BEGIN
3 SELECT ename BULK COLLECT INTO e_name FROM emp WHERE empno = emp_no;
4* END get_data;
SQL> /

Procedure created.

SQL> SET SERVEROUTPUT ON

SQL> DECLARE
2 enam pkg.emp_name;
3 BEGIN
4 get_data(1,enam );
5 FOR I IN enam.FIRST..ena m.LAST LOOP
6 INSERT INTO EMP VALUES(enam(i), 1,sysdate);
7 COMMIT;
8 DBMS_OUTPUT.PUT _LINE(enam(i));
9 END LOOP;
10 end;
11 /

a

PL/SQL procedure successfully completed.

SQL> select * from emp;

ENAME EMPNO DAT
-------------------- ---------- ---------
a 2 17-MAR-08
a 1 17-MAR-08
a 1 19-MAR-08

SQL> DECLARE
2 enam pkg.emp_name;
3 BEGIN
4 get_data(1,enam );
5 FOR I IN enam.FIRST..ena m.LAST LOOP
6 INSERT INTO EMP VALUES(enam(i), 1,sysdate);
7 COMMIT;
8 DBMS_OUTPUT.PUT _LINE(enam(i));
9 END LOOP;
10 end;
11 /

a
a

PL/SQL procedure successfully completed.

SQL> select * from emp;

ENAME EMPNO DAT
-------------------- ---------- ---------
a 2 17-MAR-08
a 1 17-MAR-08
a 1 19-MAR-08
a 1 19-MAR-08
a 1 19-MAR-08

SQL>

[/code]

Hope this is helpful

[orajit]

[code=oracle]
Declare
v_sonname pkg.emp_name;
begin
empty ('Dev',101,v_so nname);
FOR I IN v_sonname.FIRST ..v_sonname.LAS T
LOOP
insert into Emp_family_info ('001',v_sonnam e,999);
end loop;
end ;
/
[/code]


Please find my above calling code .. Its working fine for values more than 1 ..
But now i am sending another input parameter that will give me one value...but this code gives me duplicate values ...how to avoid this ..

Input parameter -----output values
dev ,101 sen,sun
leo,102 kun
jeo,103 set,mat,jen

etc

how to achieve this ... hope u got my question ...

need ur help ffor above ,,thanks ....

[amitpatel66]

I do not understand your question. In your code, there needs to be a small change. In the insert statement, for second value, it should be v_sonname(i)

[orajit]

[CODE=oracle]
Declare
v_sonname pkg.emp_name;
begin
empty ('Dev',101,v_so nname);
FOR I IN v_sonname.FIRST ..v_sonname.LAS T
LOOP
insert into Emp_family_info ('101',v_sonnam e(i),999);
end loop;
end ;
/
[/CODE]

okay ,, done the change ...

Let me give u clear idea...The output parameter of above code will give me two values . Now I have sent another input parameters and the out put suppose to give me single value ..but this code give me two values (duplicate ) ..

means select son_info from emp_info where ename='leo' and empno=102;
out put is
Kun-----single value

but this above code give me two values ie
value is :Kun
value is :Kun



I wanted my code in a such away that if out put parameter return two values then it should give me two values and if it return one value then it should return a single value ... please let me know whether u have understood my question or not ..
please advice ...thanks

[amitpatel66]

Declare 
v_sonname pkg.emp_name;
begin 
empty ('Dev',101,v_sonname);
FOR I IN v_sonname.FIRST..v_sonname.LAST 
LOOP
insert into Emp_family_info('101',v_sonname(i),999);
end loop;
end ;
/

okay ,, done the change ...

Let me give u clear idea...The output parameter of above code will give me two values . Now I have sent another input parameters and the out put suppose to give me single value ..but this code give me two values (duplicate ) ..

means select son_info from emp_info where ename='leo' and empno=102;
out put is
Kun-----single value

but this above code give me two values ie
value is :Kun
value is :Kun



I wanted my code in a such away that if out put parameter return two values then it should give me two values and if it return one value then it should return a single value ... please let me know whether u have understood my question or not ..
please advice ...thanks

Check if your table contains duplicate value. It tested and it worked fine for me. It gave me only one value

[orajit]

CREATE OR REPLACE procedure IST.GET_CALLCLASS_TES_PRO( p_event_id number,
p_acc_num varchar2,
p_st_date VARCHAR2 ,
p_end_date VARCHAR2 ,
p_callclass out rental_spend_test.t1) as
 
TYPE callclass IS REF CURSOR;
v_callclass callclass;
sql_stmt VARCHAR2(1000);
v_st VARCHAR2(1000) ;
v_st1 VARCHAR2(1000);
sql_stmt1 VARCHAR2(1000);
BEGIN
sql_stmt := 'select call_class
from rest_callclass_info where EVENT_TYPE_ID= :p_event_id';
OPEN v_callclass FOR sql_stmt USING p_event_id ; LOOP
FETCH v_callclass INTO v_st;
EXIT WHEN v_callclass %NOTFOUND;
dbms_output.put_line( v_st); 
sql_stmt:='SELECT '||v_st|| ' FROM rest_event where EVENT_TYPE_ID= :p_event_id and ACCOUNT_NUM=:p_acc_num and 
TRUNC(CREATED_DTM) BETWEEN (:p_st_date)
AND (:p_end_date)';
OPEN v_callclass FOR sql_stmt USING p_event_id, p_acc_num,p_st_date,p_end_date;
LOOP
FETCH v_callclass bulk collect INTO p_callclass;
EXIT WHEN v_callclass %NOTFOUND;
--dbms_output.put_line( p_callclass);
-- process record
end loop;
END LOOP;
CLOSE v_callclass ;
END;
/

calling progm

DECLARE
P_EVENT_ID NUMBER;
P_ACC_NUM VARCHAR2(200);
P_ST_DATE VARCHAR2(200);
P_END_DATE VARCHAR2(200);
P_CALLCLASS rental_spend_test.t1;
BEGIN
IST.GET_CALLCLASS_TES_PRO ( 514, 'GP00000239', TO_DATE('01022008','dd-mm-yyyy'), TO_DATE('01022008','dd-mm-yyyy'), P_CALLCLASS );
FOR I IN 1..P_CALLCLASS.count
LOOP
DBMS_OUTPUT.PUT_LINE(P_CALLCLASS(i));
end loop;
END;
/

it gives me two values


SQL> /

Ringtone
Ringtone

PL/SQL procedure successfully completed.


It suppose to give me one values ...


I wanted my code in a such away that if out put parameter return two values then it should give me two values and if it return one value then it should return a single value

cud u please tell me where I am doing mistake

I am sending you the original code ,,,please advice

[amitpatel66]

Could you provide me your sample data. There is some duplicate in your table that a query is fetching becuase of that.Can you check your data properly.

[orajit]

cud u please send me ur mail id i will send that data tru mail ...

[amitpatel66]

cud u please send me ur mail id i will send that data tru mail ...

Just Run your sql query in sql plus for the input parameters that you have passed to procedure and post only that data alone. Do not post all the table data here.