Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2004
    Posts
    6

    Unanswered: How to create a procedure which returns rowtype?

    Hello,
    I am kind a beginner with oracle, so I need help with one problem,

    I need to create a stored procedure which returns a record as outcoming parameter?

    and also i need a create a stored procedure which returns table as an outcome?
    I got a simple table:

    ID number(4)
    NAME varchar2(10)
    SURNAME varchar2(10)

    maybe some simple samples or somtin'?
    please help
    thanx

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Do you mean PL/SQL record/table or object type? From what language will you be calling the procedure, and what are you going to do with the returned data?

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    did you look at the PL/SQL Oracle Docs?

    http://download-west.oracle.com/docs...a96624/toc.htm
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Dec 2004
    Posts
    6
    yes i mean PL/SQL record and table...
    I work in SQL*PLUS..so I just need to create a simple sample to show how this problem is solved in PL/SQL..

    returned data I could print on screen with DBMS_OUTPUT or place it in some temp table..

    I know that with records it works somtin' like this
    procedure (x IN id.tablename%TYPE, y OUT tablename%ROWTYPE); but what comes next??
    Last edited by gatisv; 12-06-04 at 11:07.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Here is a simple example:
    Code:
    SQL> create or replace procedure getemp (p_empno in number, p_emp out emp%rowtype)
      2  is
      3  begin
      4    select * into p_emp from emp where empno = p_empno;
      5  end;
      6  /
    
    Procedure created.
    ... which you could call like this:
    Code:
    SQL> declare
      2    l_emp emp%rowtype;
      3  begin
      4    getemp(7902,l_emp);
      5    dbms_output.put_line('Name is '||l_emp.ename);
      6  end;
      7  /
    Name is FORD
    
    PL/SQL procedure successfully completed.

  6. #6
    Join Date
    Dec 2004
    Posts
    6
    thanks works fine..
    so with table (return) it will look like similar? or there is some differences?

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    Quote Originally Posted by gatisv
    thanks works fine..
    so with table (return) it will look like similar? or there is some differences?
    what? what is table (return)?

    if you want table info use: USER_TAB_COLUMNS
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Starts to get more complicated...
    Code:
    SQL> create or replace package emp_pkg as
      2    type emptab_type is table of emp%rowtype index by binary_integer;
      3    procedure getemps (p_deptno in number, p_emps out emptab_type);
      4  end;
      5  /
    
    Package created.
    
    SQL> create or replace package body emp_pkg as
      2    procedure getemps (p_deptno in number, p_emps out emptab_type)
      3    is
      4    begin
      5      select * bulk collect into p_emps from emp where deptno = p_deptno;
      6    end;
      7  end;
      8  /
    
    Package body created.
    
    SQL> declare
      2    l_emps emp_pkg.emptab_type;
      3  begin
      4    emp_pkg.getemps(10,l_emps);
      5    for i in 1..l_emps.count loop
      6      dbms_output.put_line('Name is '||l_emps(i).ename);
      7    end loop;
      8  end;
      9  /
    Name is CLARK
    Name is KING
    Name is MILLER
    
    PL/SQL procedure successfully completed.

  9. #9
    Join Date
    Dec 2004
    Posts
    6
    ok big thanx fot the help andrewst,

    PS very good forum...
    Last edited by gatisv; 12-06-04 at 12:11.

  10. #10
    Join Date
    Dec 2004
    Posts
    6
    Hmm..well, when I try to do this package, it shows me this kind of errors, I use oracle 9.0.1..maybe it is a version fault?

    Code:
    SQL> create or replace package body emp_pkg as
      2  procedure getemps (p_deptno in number, p_emps out emptab_type)
      3  is
      4  begin
      5  select * bulk collect into p_emps from emp where deptno = p_deptno;
      6  end;
      7  end;
      8  /
    
    Warning: Package Body created with compilation errors.
    
    SQL> show errors
    Errors for PACKAGE BODY EMP_PKG:
    
    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    5/1      PL/SQL: SQL Statement ignored
    5/28     PLS-00597: expression 'P_EMPS' in the INTO list is of wrong type
    5/35     PL/SQL: ORA-00904: invalid column name

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Maybe, or did you forget to create the package spec first? I tested that code on 8i before I posted it.

  12. #12
    Join Date
    Dec 2004
    Posts
    6
    hmm anyway, I managed to make this one, and the final result look like:

    Code:
    set serveroutput on;
    
    create or replace package tipi is
    type xxx is table of director%rowtype index by binary_integer;
    end tipi;
    
    /
    
    show errors;
    
    create or replace procedure sql_tabula(tabul out tipi.xxx) is
    cursor studi is select * from director;
    begin
    for kursor in studi
    loop
    tabul(kursor.d_id):=kursor;
    end loop;
    end;
    
    /
    
    show errors;
    
    create or replace procedure izpilde is
    b tipi.xxx;
    i number;
    begin
    sql_tabula(b);
    	for i in 5..10 loop
    	if b.Exists(i) then
    dbms_output.put_line ('Rezisors '|| b(i).name || 'un vina alga ir ' || b(i).cash);
    else
    dbms_output.put_line('Rezisors ar ID = '|| i || 'nav');
    end if;
    end loop;
    end;
    /
    show errors;
    
    set linesize 140;
    
    exec izpilde;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •