Running several SQL one after each other

[JKK]

Hi,

I am quite new to PL/SQL Developer although I got quite big experience in writing SQL in SAS.

I am wondering whether it is possible to use PL/SQL Dev in a way similar to SAS. What I mean:
- is it possible to create temporary tables
- build sql on temporary tables
- write a procedure to execute 2 or more sql queries
- run the procedure from command line and export the results to excel/csv file

Many thanks for help,
JK

 

[Marco Kalter]

- is it possible to create temporary tables

File > New > Table, define the table and set the "Duration" properties to make it temporary.

- build sql on temporary tables

Use the SQL Window to develop and run your SQL statements.

- write a procedure to execute 2 or more sql queries

Use the Program Window to develop your procedure.

- run the procedure from command line and export the results to excel/csv file

I'm not quite clear on this one, but I don't think you can do this from within PL/SQL Developer.

 

[JKK]

Hi,

Thank you for your answer. Could you please give me some examples or tutorials how to do the first three?I know how build and execute sql in Sql window, but don't know how to create temporary table from sql window.

The last one - I found info that it is possible to run 'Query reporter' from command line and export results directly to excel/csv. I was wondering if the same is possible within Pl/Sql Dev. but not to run 1 query but a procedure.

Many thanks,
Jakub

 

[Marco Kalter]

Thank you for your answer. Could you please give me some examples or tutorials how to do the first three?I know how build and execute sql in Sql window, but don't know how to create temporary table from sql window.

Creating a temporary table is easy. Just go to File > New > Table and set the correct properties. See chapter 8.1 in the User's Guide for more information.

Creating a temporary table from the SQL Window is not that easy, because you need to know the exact SQL syntax. However, if you define it as described in chapter 8.1 and press the "View SQL" button, you can copy the this SQL into the SQL Window.

The last one - I found info that it is possible to run 'Query reporter' from command line and export results directly to excel/csv. I was wondering if the same is possible within Pl/Sql Dev. but not to run 1 query but a procedure.

The Report Window cannot execute a procedure and display a result set.

 

[ScottMattes]

Could you use the Test Window instead and open your queries as cursors? then you could open the cursors in a query result grid and then export to csv/excel.

The query cursor names take the form of :cursor_name, then in the variable grid you right click and select scan to have the TW recognize the name. Remember that you must change the variable type to cursor! Execute the TW and then click the ... button to the right of the cursor variable row.

If I was at my PC I could supply a small example TW, sorry.