CODEWITHSUNDEEP
oracle-databasestored-procedures
behzad razzaqi
behzad razzaqi

Reputation: 1511

How can i use oracle object type in stored procedure?

I'm beginner in Oracle, and I declare this object type:

create or replace 
TYPE behzadtype AS OBJECT 
( /* TODO enter attribute and method declarations here */ 
  SESSIONID Number

)

and I want that object use in my stored procedure:

CREATE OR REPLACE PROCEDURE PROCEDURE1 AS 
  behzadtype t1;
BEGIN
  t1.SESSIONID:=12;
   DBMS_OUTPUT.PUT_LINE('THE VALUES OF P_K ARE' || t1.SESSIONID);

END PROCEDURE1;

but when compile up procedure i get this error:

Error(2,14): PLS-00201: identifier 'T1' must be declared

How can I write correct procedure? Thanks for all.

Upvotes: 1

Views: 3117

Answers (1)

Boneist
Boneist

Reputation: 23578

When you declare variables in PL/SQL, you state the name of the variable first, followed by its datatype.

In your example, you have declared a variable of behzadtype as being of type t1, and then tried to use a variable called t1 in your code body. It looks like you wanted to declare a variable t1 as being of type behzadtype.

As well as that, you can't assign a value to the object like that - you have to initialise it as an object first.

I think what you're after is:

create or replace type behzadtype as object 
(sessionid number);
/

create or replace procedure procedure1 as 
  t1 behzadtype;
begin
  t1 := behzadtype(12);
  dbms_output.put_line('THE VALUES OF P_K ARE' || t1.sessionid);
end procedure1;
/

begin
  procedure1;
end;
/

THE VALUES OF P_K ARE12

drop type behzadtype;

drop procedure procedure1;

Upvotes: 3

Related Questions