0

I have been asked to create an SP which creates temporary table and insert some records.

I am preparing some sample code for the same as mentioned below but the output is not displayed.

create or replace procedure Test
is 
   stmt varchar2(1000);
   stmt2 varchar2(1000);
begin
    stmt := 'create global temporary table temp_1(id number(10))';
    execute immediate stmt;
    insert into temp_1(id) values (10);
    execute immediate 'Select * from temp_1';
    execute immediate 'Drop table temp_1';
    commit;
end;

When i am executing the SP by (Exec Test) desired O/P is not displayed.

I am expecting O/P of "Select * from temp_1" to be displayed. But it is not happening. Please suggest where i am doing wrong.

Improve this question
7
  • 6
    Creating a temporary table, inserting some records and dropping the table afterwards is a rather uncommon thing to do in PL/SQL. Usually there are ways to avoid the temporary table completely. What's the ultimate goal of your procedure? Commented Mar 10, 2016 at 11:59
  • 5
    PL/SQL won't magically display the result of a SELECT statement. You need to process the result and display it e.g. using dbms_output. Or make this a table function and return the result of the select directly. But I agree with ammoQ: the use of the temporary table seems rather dubious. Commented Mar 10, 2016 at 12:02
  • @ammoQ, actually i have to perform this task using temp table to support some old codes. But i am interesting in knowing why ( execute immediate 'Select * from temp_1';) do not yield any result. Commented Mar 10, 2016 at 12:04
  • Thanks @ a_horse_with_no_name, @ammoQ for suggestions. Could you please help me with the alternate concept of GTT. My requirement is: I have 10 Millions of record in a View. I have to extract the View result, the perform some business logic like to perform mapping and then insert into final table. Insertion will still require additional logics on data. Kindly suggest. Commented Mar 10, 2016 at 12:11
  • Your latest comment is the question you posted here: stackoverflow.com/questions/35898642/… - so may be best if any futher suggestions are added as answers there. Commented Mar 10, 2016 at 12:28

1 Answer 1

Reset to default
3

But i am interesting in knowing why ( execute immediate 'Select * from temp_1';) do not yield any result

For two reasons. Firstly because as @a_horse_with_no_name said PL/SQL won't display the result of a query. But more importantly here, perhaps, the query is never actually executed. This behaviour is stated in the documentation:

If dynamic_sql_statement is a SELECT statement, and you omit both into_clause and bulk_collect_into_clause, then *execute_immediate_statement( never executes.

You would have to execute immediate into a variable, or more likely a collection if your real scenario has more than one row, and then process that data - iterating over the collection in the bulk case.

There is not really a reliable way to display anything from PL/SQL; you can use dbms_output but that's more suited for debugging than real output, and you usually have no guarantee that the client will be configured to show whatever you put into its buffer.

This is all rather academic since creating and dropping a GTT on the fly is not a good idea and there are better ways to accomplish whatever it is you're trying to do.

The block you showed shouldn't actually run at all; as you're creating temp_1 dynamically, the static SQL insert into temp_1 will error as that table does not yet exist when the block is compiled. The insert would have to be dynamic too. Any dynamic SQL is a bit of a warning sign you're maybe doing something wrong, though it is sometimes necessary; having to do everything dynamically suggests the whole approach needs a rethink, as does creating objects at runtime.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.