Home » SQL & PL/SQL » SQL & PL/SQL » How to use Type to work in a procedure (oracle 9.2.0.3)
How to use Type to work in a procedure [message #428895] Fri, 30 October 2009 09:49 Go to next message
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 #428896 is a reply to message #428895] Fri, 30 October 2009 09:53 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>I want to return the value as type

procedure does not RETURN anything
Re: How to use Type to work in a procedure [message #428897 is a reply to message #428896] Fri, 30 October 2009 09:56 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
How to make the procedure to get 1 or 0 from the above procedure.?
Re: How to use Type to work in a procedure [message #428898 is a reply to message #428895] Fri, 30 October 2009 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
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 #428914 is a reply to message #428898] Fri, 30 October 2009 14:08 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Thanks for the reply.
I did not understand why I need to use IN OUT .Would you please
let me know whats the reason for it.
Many thanks
Re: How to use Type to work in a procedure [message #428916 is a reply to message #428914] Fri, 30 October 2009 14:14 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you need to allocate the parameter outside the procedure and so inside you use it as input to get the object and output to set some (new element and) value in it.

Regards
Michel
Re: How to use Type to work in a procedure [message #429345 is a reply to message #428916] Tue, 03 November 2009 05:21 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir, thank you very much for the reply.


Quote:

You can also extend the object inside the called procedure instead.

I did not understand what you mean here.

Can we use p.extend in main procedure?if yes how?
Re: How to use Type to work in a procedure [message #429349 is a reply to message #429345] Tue, 03 November 2009 05:29 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
p.extend;


Re: How to use Type to work in a procedure [message #429352 is a reply to message #429349] Tue, 03 November 2009 06:16 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Quote:

You can also extend the object inside the called procedure instead.


How can we do this?
Re: How to use Type to work in a procedure [message #429354 is a reply to message #429352] Tue, 03 November 2009 06:20 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can extend an object with

[object].extend;


basically everywhere, as long as it exist and is initiated first before you try to extend it.
Re: How to use Type to work in a procedure [message #429358 is a reply to message #429354] Tue, 03 November 2009 06:26 Go to previous messageGo to next message
prachij593
Messages: 266
Registered: May 2009
Senior Member
Sir,
Thank you so much for your quick response.
What I want is that I want to extend the object in main procedure
Is it possible?
The calling procedure should be
SQL> declare
  2    p type_OutputNumber :=  type_OutputNumber();
  3  begin 
  4    
  5    SP_CHECK(1,p);
  6    dbms_output.put_line(p(1));
  7  end;
  8  /

[Updated on: Tue, 03 November 2009 06:27]

Report message to a moderator

Re: How to use Type to work in a procedure [message #429360 is a reply to message #429358] Tue, 03 November 2009 06:34 Go to previous messageGo to next message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Yes, it is possible.

The one line of code you have to add to your existing two lines is already posted three times.
Re: How to use Type to work in a procedure [message #429364 is a reply to message #429360] Tue, 03 November 2009 07:05 Go to previous messageGo to next message
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

object.extend



Quote:

to your existing two lines

Did not understand what you want to say here

Regards,
Prachi



Re: How to use Type to work in a procedure [message #429368 is a reply to message #429364] Tue, 03 November 2009 07:19 Go to previous message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well. Not much I can do then, when you don't understand.

To clarify: Whether this is homework or something that has to be added to a real-live system, it will have BAD consequences if you just copy/paste a solution you don't understand.

[Updated on: Tue, 03 November 2009 07:22]

Report message to a moderator

Previous Topic: PL/SQL get manager name form employee table
Next Topic: collection and records(basic documents)
Goto Forum:
  


Current Time: Tue Sep 27 21:12:04 CDT 2016

Total time taken to generate the page: 0.07528 seconds