Home » SQL & PL/SQL » SQL & PL/SQL » Object Types
Object Types [message #187546] Mon, 14 August 2006 07:15 Go to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Hi,

Can you pls guide me as i am little confused.

When we create a Object
as

CREATE OR REPLACE TYPE test_type AS OBJECT(ID NUMBER, NAME VARCHAR2(20))


Can i refer this type into my procedure as an Out parameter??

CREATE OR REPLACE PROCEDURE test_procedure (my_type OUT test_type)
AS
p test_type;
BEGIN
SELECT ID,NAME INTO p.ID,p.NAME FROM AMIT WHERE ID = 1;
my_type := p;
END;


I have created this procedure.
but when i run this procedure

DECLARE
p test_type;
BEGIN
test_procedure (p);
DBMS_OUTPUT.PUT_LINE(p.ID ||' '|| p.NAME);
END;



It gives me an error


DECLARE
*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at "EASYBILL.TEST_PROCEDURE", line 5
ORA-06512: at line 4


Can you pls help me out....
Re: Object Types [message #187557 is a reply to message #187546] Mon, 14 August 2006 07:49 Go to previous message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
Try this:

SQL> CREATE OR REPLACE TYPE test_type AS OBJECT(id NUMBER, name VARCHAR2(20))
  2  /

Type created.


SQL> CREATE OR REPLACE PROCEDURE Test_Procedure(My_Type  OUT TEST_TYPE)
  2  AS
  3    p  TEST_TYPE := Test_Type(NULL,NULL);
  4  BEGIN
  5    SELECT 1, 'Frank'
  6    INTO      p.Id, p.NAME
  7    FROM      Dual;
  8
  9    My_Type := p;
 10  END;
 11  /

Procedure created.


SQL> DECLARE
  2    p  TEST_TYPE := Test_Type(NULL,NULL);
  3  BEGIN
  4    Test_Procedure(p);
  5    dbms_Output.Put_Line(p.Id || ' ' || p.NAME);
  6  END;
  7  /
1 Frank

PL/SQL procedure successfully completed.
Previous Topic: how to view the table is parent or child. and if it is child table.how to find its parent table
Next Topic: TNS ERROR 12154 COULD NOT SOLVE SOMTHING
Goto Forum:
  


Current Time: Wed Dec 07 14:18:14 CST 2016

Total time taken to generate the page: 0.16932 seconds