The type you have created is local to the procedure and is only valid inside that procedure.
To use the same type between procedures, you need to create a type that is visible in both procedures, for instance by creating a type in the header of a package.
1 person found this helpful
Are you sure that's the error you're getting from the code you posted? When I try it, I get a different error:
0/0 PL/SQL: Compilation unit analysis terminated
1/1 PLS-00410: duplicate fields in RECORD,TABLE or argument list are
caused by declaring a local variable with the same name as one of the arguments, v_data.
There are lots of other mistakes, too.
What exactly are you trying to do? Whatever it is, don't try to write the whole procedure at once. Take baby steps. Write as little code as you possibly can, test it, fix the problems, test again, and if it works correctly, add 1 or 2 more lines of code, and test again.
For example, start with something like this:
CREATE OR REPLACE PROCEDURE emp_pro
( v_data IN VARCHAR2
dbms_output.put_line (v_data || ' = v_data entering emp_pro');
If that works as expected, then try adding the second argument.
When you get that much working, try returning something in the 2nd argument.
When you get that much working, try creating a TYPE in the procedure, with just 1 field.
And so on.
If you get stuck, post the version of the code with the problem, and some code that runs it. Explain what that procedure is supposed to do, that is, the output you expect from running it the way you posted. Show what changed since the last time you tested (that is, what 1 or 2 lines you added or changed most recently). If you get an error, post the complete error message, including line numbers.
You have many errors on yours procedures:
v_data and i declared twice,
for your request you should declare t as emptype to use new defined type.
below your procedure modified and compile fine.
create or replace
procedure emp_pro(v_data1 in varchar2,v_empout out varchar2)
type emprectype is record
type emptype is table of emprectype
index by binary_integer;
exit when i=counter+1;
1 person found this helpful
i want to Know that is it possible to give a plsql table as out mode parameter to another procedure
Yes - it is.
But why are you defining your own record if it is nothing but a clone of the emp table rows?
Oracle already does that for you when you use %ROWTYPE. So just use that instead:
type emp_table_type is table of emp%rowtype;
But the fact that you are using collections like this means you are likely using the wrong solution for whatever (unknown to us) problem you are trying to solve.
So why don't you tell us what PROBLEM you are really dealing with so we can suggest a better way to solve it than using collections and PL/SQL if possible?
Thank you...i got that i need declare the plsql table in a procedure and use the instance in my main procedure
thanks a lot for your valuable suggestion
in main procedure i was separating each record of emp delimited by the ' || ' symbol and redirecting the individual record into a separate plsql table associating with a index as out mode parameter from above procedure
means my plsql table should be a outmode parameter so that i could use that in anther procedure