Home » SQL & PL/SQL » Client Tools » execute a procedure with parameter of object type (10204)
execute a procedure with parameter of object type [message #449469] Mon, 29 March 2010 14:25 Go to next message
bella13
Messages: 90
Registered: July 2005
Member
I am trying to execute the procedure from toad, where the type is like an object.How can I do that?

CREATE OR REPLACE TYPE list_t AS TABLE OF list_objtype;

CREATE OR REPLACE TYPE list_objtype AS OBJECT
( Emp_ID NUMBER(5),
EMp_Name VARCHAR2(32 BYTE)
);

In the following procedure I am calling list_t, but how would i pass the values to it?

PROCEDURE modify_settings
( p_List in list_t,
p_info out varchar2)



DECLARE
p_List TABLE;
p_info VARCHAR2(200);

BEGIN
-- p_List := NULL; Modify the code to initialize this parameter
p_info := NULL;

modify_settings ( p_List , p_info );

DBMS_OUTPUT.Put_Line(' p_info = ' || p_info);

DBMS_OUTPUT.Put_Line('');

COMMIT;
END;


Any help, is appreciated..thanks
Re: execute a procedure with parameter of object type [message #449577 is a reply to message #449469] Tue, 30 March 2010 10:34 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
YOu'll need to have your CREATE TYPE statements the other way round - they'll generate an error the way you've got them.

Try This:
DECLARE 
p_List    list_t := p_list();
p_info    VARCHAR2(200);

BEGIN 
p_list.extend(2);
p_list(1) := list_objtype(1,'Name 1');
p_list(1) := list_objtype(34,'Algernon');

modify_settings ( p_List , p_info );

DBMS_OUTPUT.Put_Line(' p_info = ' || p_info);

DBMS_OUTPUT.Put_Line('');

COMMIT; 
END; 
Previous Topic: different charactersets show line-break in select result
Next Topic: PL SQL Developer Program Help
Goto Forum:
  


Current Time: Fri Aug 23 13:00:53 CDT 2019