Why the select statement is not showing me table while executed in oracle?

Question

I am new to oracle.I already have a table tempash. So,I created a procedure to see the data of this table. So,I created procedure as:

create or replace procedure offc.temp_sel(data1 varchar2) is
   var1 varchar2(4000);
BEGIN
 var1:='select * from  offc.temp'||data1;
   EXECUTE IMMEDIATE var1;
 end;

So,I executed the statement but,it is not showing me anything.

 exec offc.temp_sel('ash');

There is no any compilation error in my procedure.But why the select statement is not showing me data of that procedure?

To *see the data of a table* you typically use a `SELECT` statement. Did you try it? `select * from offc.tempASH`. — Marmite Bomber, Oct 23 '19 at 16:29
@KarkiAshwin Procedures don't output anything unless you code them to do that. `execute immediate` doesn't do anything with `select` statements unless they are accompanied by an `into` clause. Maybe you are looking for something like this: https://stackoverflow.com/q/351489/230471 — William Robertson, Oct 23 '19 at 22:50

Marc Asmar · Answer 1 · 2019-10-24T07:38:59.877

1

Try adding out parameter:

create or replace procedure offc.temp_sel(data1 varchar2,result out sys_refcursor) 
is

BEGIN

 open result for 'select * from  offc.temp'||data1;
 end;


SQL> var rc refcursor
SQL> execute offc.temp_sel('ash',:rc)

PL/SQL procedure successfully completed.

SQL> print rc

edited Oct 24 '19 at 07:38

answered Oct 23 '19 at 16:32

Marc Asmar

1,527
1
10
22

Looks like your procedure takes two arguments but you only pass one. – William Robertson Oct 23 '19 at 22:03
Yes my bad, i forgot the first argument, Updated answer – Marc Asmar Oct 24 '19 at 07:17
Also your `result` ref cursor doesn’t seem to be referenced within the procedure. – William Robertson Oct 24 '19 at 07:19

mikcutu · Accepted Answer · 2019-10-24T07:26:34.737

You need to keep the result of the SELECT statement into a variable. As you perform SELECT * ..., you should put the result into a RECORD type but, as the result set contains more than 1 row, your variable needs to be a table of records.

In order to not be prone to error, the tables of records needs to be exactly like your table source structure.

CREATE OR REPLACE PROCEDURE OFFC.TEMP_SEL(DATA1 VARCHAR2) IS

    VAR1 VARCHAR2(4000);

    TYPE T_RESULT IS TABLE OF offc.temp%ROWTYPE;
--  defined the new type based on the structure of table TEMP from schema OFFC

    v_result t_result;
--  define a variable of that type.

BEGIN
    var1:='select * from  offc.temp'||data1;

    EXECUTE IMMEDIATE VAR1 BULK COLLECT INTO V_RESULT;
--  collect he result into the new variable

    FOR I IN 1 ..v_result.count
    LOOP
        dbms_output.put_line(v_result(i).<<column_name from temp offc.table>>);
    end loop;
--  loop through the variable(table of records) and display its content.
--  you need to replace the  << ... >> with the name of your column from source tabel that you want to display.

 end;

To execute the procedure, you should use:

set serveroutput on;
execute temp_sel( 'ash');

Best, Mikcutu

thank you so much.. someone downvotedyou.. i just upvoted now — Random guy, Oct 24 '19 at 16:58

Why the select statement is not showing me table while executed in oracle?

2 Answers2