How to use Type to work in a procedure [message #428895] |
Fri, 30 October 2009 09:49 |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
create or replace TYPE type_OutputNumber is table of number;
/
create or replace
PROCEDURE SP_CHECK(
p_orderno IN NUMBER,
pFlag OUT type_OutputNumber ) IS
BEGIN
SELECT 1
INTO pFlag(1)
FROM orders
WHERE order_no=p_orderno
and rownum < 2;
EXCEPTION
WHEN OTHERS THEN
pFlag(1) := 0;
END;
/
I want to return the value as type
|
|
|
|
|
Re: How to use Type to work in a procedure [message #428898 is a reply to message #428895] |
Fri, 30 October 2009 10:01 |
|
Michel Cadot
Messages: 68711 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Your first idea does not work:
SQL> create or replace TYPE type_OutputNumber is table of number;
2 /
Type created.
SQL> create or replace
2 PROCEDURE SP_CHECK(
3 p_orderno IN NUMBER,
4 pFlag OUT type_OutputNumber ) IS
5 BEGIN
6 SELECT 1
7 INTO pFlag(1)
8 from dual;
9 EXCEPTION
10 WHEN OTHERS THEN
11 pFlag(1) := 0;
12 END;
13 /
Procedure created.
SQL> declare
2 p type_OutputNumber;
3 begin
4 SP_CHECK(1,p);
5 dbms_output.put_line(p(1));
6 end;
7 /
declare
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "MICHEL.SP_CHECK", line 10
ORA-06531: Reference to uninitialized collection
ORA-06512: at line 4
You have to allocate the object in the caller and define the parameter as IN OUT:
SQL> create or replace
2 PROCEDURE SP_CHECK(
3 p_orderno IN NUMBER,
4 pFlag IN OUT type_OutputNumber ) IS
5 BEGIN
6 SELECT 1
7 INTO pFlag(1)
8 from dual;
9 EXCEPTION
10 WHEN OTHERS THEN
11 pFlag(1) := 0;
12 END;
13 /
Procedure created.
SQL> declare
2 p type_OutputNumber := type_OutputNumber();
3 begin
4 p.extend;
5 SP_CHECK(1,p);
6 dbms_output.put_line(p(1));
7 end;
8 /
1
PL/SQL procedure successfully completed.
You can also extend the object inside the called procedure instead.
Regards
Michel
[Updated on: Fri, 30 October 2009 10:02] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
Re: How to use Type to work in a procedure [message #429364 is a reply to message #429360] |
Tue, 03 November 2009 07:05 |
prachij593
Messages: 266 Registered: May 2009
|
Senior Member |
|
|
Quote:
The one line of code you have to add to your existing two lines is already posted three times.
Quote:
The one line of code you have to add
Quote:
to your existing two lines
Did not understand what you want to say here
Regards,
Prachi
|
|
|
|