-2

I have a stored procedure proc1 without parameters. I want to extract data from this stored procedure. How can I get that? Could you help me?

Stored procedure:

create procedure proc1 
as
begin
    select e_id, e_nm, e_sal 
    from emp 
    where e_id like 'e%';
end proc1;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • You should be more specific - to get some answers... what is the purpose of the procedure? are you the one writing the stored procedure? what kind of data are you trying to extract? – PKey Jul 12 '17 at 08:04
  • @Plirkee in this procedure some SQL statement is there but without parameter. How can i get or extract data from this store procedure or how can i get data from store procedure having parameter –  Jul 12 '17 at 08:09
  • 1
    It will not be fruitful to discuss this without you posting your procedure code in your question. – Keyur Panchal Jul 12 '17 at 08:12
  • @Shahin post the code and explain what do you want to extract (desired output)... Otherwise I doubt that you will get any useful answers. – PKey Jul 12 '17 at 08:14
  • it looks like you're trying to port something from MS SQL. Oracle's concepts are different. You should read about cursors first. – ibre5041 Jul 12 '17 at 08:19
  • 1
    You cannot get data out of a procedure without having parameters. You could either return a cursor as an out parameter, or return some scalar values as an out parameter, but at the end you need parameters. – KFx Jul 12 '17 at 09:20
  • The sample code won't compile because a plain `select` needs an `into` to put the results in. You need to define it as a ref cursor. See my reply. – William Robertson Jul 12 '17 at 11:26

3 Answers3

1

You can do this in Oracle 12.1 or above:

create or replace procedure demo
as
    rc sys_refcursor;
begin
    open rc for select * from dual;
    dbms_sql.return_result(rc);
end demo;

This requires an Oracle 12.1 or later client/driver to handle the implicit result set.

For more details, see Implicit Result Sets in the Oracle 12.1 New Features Guide, Tom Kyte's Blog, Oracle Base etc.

William Robertson
  • 15,273
  • 4
  • 38
  • 44
0

Here's one possible solution:

Declaration:

create procedure proc1 (emp_row IN OUT emp%rowtype)
as
begin
  select * --e_id, e_nm, e_sal
    into emp_row
    from emp
   where e_id like 'e%';
end proc1;

Use case:

DECLARE
  l_emp_row emp%rowtype;
BEGIN
  proc1(l_emp_row);
  -- Here you can access every column of table "emp", like so:
    -- dbms_output.put_line('e_id:  ' || to_char(l_emp_row.e_id));
    -- dbms_output.put_line('e_nm:  ' || to_char(l_emp_row.e_nm));
    -- dbms_output.put_line('e_sal: ' || to_char(l_emp_row.e_sal));
END;

Is there anything special that you need to get out of the Procedure?

Cheers

g00dy
  • 6,752
  • 2
  • 30
  • 43
0
  1. you create a view this query. (recomended)

  2. Oracle procedure is not return any data. So, you do not see any result. Your result get buffer but not print screen. if You need a procedure, insert all data another table. 

    create procedure proc1 
as
begin
insert into new_table select e_id, e_nm, e_sal from emp where e_id like 'e%';
end proc1;

Another way; You create a function. Because function outputs and inputs. This function;

for example Create an Oracle function that returns a table

CEA
  • 1
  • 2