1

I use an Oracle SQL Developer script to do a SELECT, displaying results in Query Results window. I then copy/paste the results into an Excel template for reporting.

I would like to replace the script with a PLSQL block, to allow looping etc. The problem is that simple SELECT FROM (without INTO) doesn't seem to work in PLSQL.

Is there any way to use PLSQL to display the results of a select in a window which I can copy/paste from?

Note: I am disallowed from using EXPORT to create text files directly, which would be much better than copy/paste. There is also a standard Oracle package that does output to a file directly from PLSQL, but I am disallowed from using it, too.

This post was marked as a duplicate of another post, one which asked how to get output from a SELECT that was NOT in a PL/SQL block. I do know how to do that and in fact it's what I am doing currently, as I mentioned in the OP. As I said, SELECT without INTO fails in PL/SQL.

user2781942
  • 105
  • 1
  • 10
  • The thread I link to doesn't answer the question above but it answers the question you should have asked: how can I get data from SQL Developer into Excel. I'm not sure how much restrictions you're actually under, but the technique for generating a CSV output which you can cut'n'paste into a text file which you can then open in Excel should work in almost every scenario. – APC May 25 '14 at 08:44

1 Answers1

0

You can create a temporary table:

CREATE GLOBAL TEMPORARY TABLE table_name (
    ( column1 datatype null/not null,
      column2 datatype null/not null,
      ...
) ON COMMIT DELETE ROWS;

Then through each loop, you can insert your data in it:

INSERT INTO table_name
  (SELECT statement);

Finally you can use select statement on temporary table to read data:

SELECT * FROM table_name

and then drop table:

drop table table_name;
Hamid Reza
  • 477
  • 1
  • 4
  • 11
  • 1
    This doesn't answer the question. Also that's not Oracle syntax so while this approach might be valid with whatever database you use. creating permanent database objects is not the best way to solve a interface issue like this. – APC May 25 '14 at 08:40
  • You are right. So I corrected my answer. Thanks for your comment. – Hamid Reza May 26 '14 at 10:52
  • I appreciate the answer but unfortunately it does not answer the question, which I may not have explained clearly. The SELECT * FROM table_name; command cannot be run from PL/SQL, so it would put me at exactly the same place. – user2781942 Jul 16 '14 at 15:39
  • The correct answer to my question, based on my research and the lack of any answers here giving a method, is "No": there is no way to display the contents of a table in a SQL Results window programmatically from PL/SQL. – user2781942 Jul 16 '14 at 15:43