6 Replies Latest reply: Sep 30, 2013 1:45 AM by 1044098 RSS

    Having problem in Plsql table

    1044098

      Hii all ...i want to Know that is it possible to give a plsql table as out mode parameter to another procedure...because when ever i am doing it i am getting an error "PLS-00201: identifier 'EMPTYPE' must be declared"...i have creatred a plsql table of emptype here is my code

       

      create or replace procedure emp_pro(v_data in varchar2,v_empout out varchar2)

      is

      type emprectype is record

      (

      V_empno emp.empno%type,

      v_ename emp.ename%type,

      v_sal emp.sal%type,

      v_job emp.job%type,

      v_hiredate emp.hiredate%type,

      v_comm emp.comm%type,

      v_deptno emp.deptno%type

      );

      type emptype is table of emprectype

      index by binary_integer;

      i binary_integer:=0;

      v_data varchar2(1000):='||7369,SMITH,800,CLERK,17-DEC-80,0,20||7499,ALLEN,1600,SALESMAN,20-FEB-81,300,30||7521,WARD,1250,SALESMAN,22-FEB-81,500,30||7566,JONES,2975,MANAGER,02-APR-81,0,20||7654,MARTIN,1250,SALESMAN,28-SEP-91,100,30||7839,KING,5000,PRESIDENT,17-NOV-81,0,10||7844,TURNER,1500,SALESMAN,08-SEP-81,0,30||7876,ADAMS,1100,CLERK,23-MAY-87,0,20||7900,JAMES,950,CLERK,03-DEC-81,0,30||7902,FORD,3000,ANALYST,03-DEC-81,0,20||7934,MILLER,1300,CLERK,23-JAN-82,0,10||';

      v_record varchar2(100);

      v_num1 number;

      v_num2 number;

      v_num3 number;

      i number;

      j number;

      counter number;

      begin

      counter:=length(v_data)-length(replace(v_data,'||'));

      i:=1;

      loop

      v_num1:=instr(v_data,'||',1,i)+2;

      v_num2:=instr(v_data,'||',1,i+1)-2;

      v_num3:=(instr(v_data,'||',1,i+1))-(instr(v_data,'||',1,i)+2);

      i:=i+1;

      v_record:=substr(v_data,v_num1,v_num3);

      exit when i=counter+1;

      v_empout:=v_record;

      --dbms_output.put_line(v_record);

      end loop;

      end emp_pro;

      /

        • 1. Re: Having problem in Plsql table
          Pleiadian

          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.

          • 2. Re: Having problem in Plsql table
            Frank Kulash

            Hi,

             

            Are you sure that's the error you're getting from the code you posted?  When I try it, I get a different error:

             

            LINE/COL ERROR

            -------- -----------------------------------------------------------------

            0/0      PL/SQL: Compilation unit analysis terminated

            1/1      PLS-00410: duplicate fields in RECORD,TABLE or argument list are

                     not permitted

            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

            )

            IS

            BEGIN

                dbms_output.put_line  (v_data || ' = v_data entering emp_pro');

            END  emp_pro;

            /

            SHOW ERRORS

             

            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.

            • 3. Re: Having problem in Plsql table
              Cherif bh

              Hi, 

               

              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)

              as

              type emprectype is record

              (

              V_empno emp.empno%type,

              v_ename emp.ename%type,

              v_sal emp.sal%type,

              v_job emp.job%type,

              v_hiredate emp.hiredate%type,

              v_comm emp.comm%type,

              v_deptno emp.deptno%type

              );

              type emptype is table of emprectype

              index by binary_integer;

              t emptype;

              --i binary_integer:=0;

              v_data varchar2(1000):='||7369,SMITH,800,CLERK,17-DEC-80,0,20||7499,ALLEN,1600,SALESMAN,20-FEB-81,300,30||7521,WARD,1250,SALESMAN,22-FEB-81,500,30||7566,JONES,2975,MANAGER,02-APR-81,0,20||7654,MARTIN,1250,SALESMAN,28-SEP-91,100,30||7839,KING,5000,PRESIDENT,17-NOV-81,0,10||7844,TURNER,1500,SALESMAN,08-SEP-81,0,30||7876,ADAMS,1100,CLERK,23-MAY-87,0,20||7900,JAMES,950,CLERK,03-DEC-81,0,30||7902,FORD,3000,ANALYST,03-DEC-81,0,20||7934,MILLER,1300,CLERK,23-JAN-82,0,10||';

              v_record varchar2(100);

              v_num1 number;

              v_num2 number;

              v_num3 number;

              i number;

              j number;

              counter number;

              begin

              counter:=length(v_data)-length(replace(v_data,'||'));

              i:=1;

              loop

              v_num1:=instr(v_data,'||',1,i)+2;

              v_num2:=instr(v_data,'||',1,i+1)-2;

              v_num3:=(instr(v_data,'||',1,i+1))-(instr(v_data,'||',1,i)+2);

              i:=i+1;

              v_record:=substr(v_data,v_num1,v_num3);

              exit when i=counter+1;

              v_empout:=v_record;

              --dbms_output.put_line(v_record);

              end loop;

              end emp_pro;

               

              thanks,

              Cherif

              • 4. Re: Having problem in Plsql table
                rp0428
                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?

                • 5. Re: Having problem in Plsql table
                  1044098

                  Thank you...i got that i need declare the plsql table in a procedure and use the instance in my main procedure

                  • 6. Re: Having problem in Plsql table
                    1044098

                    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