I know my way around “regular” SQL pretty good. But I am starting to need to use PL/SQL for some of my analysis. I am having trouble figuring out how to get the output from these PL/SQL scripts. I know about the clock icon and connection output buttons, and I can get DBMS_OUTPUT.PUT_LINE to send output, but how do I get that output into a table of some sort? When I am sending to a text file, I usually do something like this:
BEGIN
DECLARE
CURSOR c_cur IS
SELECT
EMPNO as “EMPNO”,
ENAME as “ENAME”,
JOB as “JOB”,
MGR as “MGR”
FROM EMP;
BEGIN
FOR r_cur IN c_cur LOOP
DBMS_OUTPUT.PUT_LINE(r_cur.EMPNO||CHR(9)||r_cur.ENAME||CHR(9)||r_cur.JOB||r_cur.MGR);
END LOOP;
END;
END;
What I am looking for is a way to output to a table in Toad that I can easily review and then export to Excel as needed. You know, just like with regular SQL???
RETURN SYS_REFCURSOR
AS
c SYS_REFCURSOR;
BEGIN
OPEN c FOR select * from dual;
RETURN c;
END;
Debbie
The way to do this is using a fucntion that returns a ref cursor. When you have this, use the "Execute Function" action from the toolbar and you will get the cursor displayed as a result set in the grid.
Here is sample function. Also, see screenshot.
CREATE OR REPLACE FUNCTION myRefCursor
RETURN SYS_REFCURSOR
AS
c SYS_REFCURSOR;
BEGIN
OPEN c FOR select * from dual;
RETURN c;
END;
Debbie
The way to do this is using a fucntion that returns a ref cursor. When you have this, use the "Execute Function" action from the toolbar and you will get the cursor displayed as a result set in the grid.
Here is sample function. Also, see screenshot.
CREATE OR REPLACE FUNCTION myRefCursor
Hmm… it’s not working for me.
-
Open the connection to my Employee database
-
In the SQL editor panel, I entered the code below:
CREATE OR REPLACE FUNCTION myRefCursor
RETURN SYS_REFCURSOR
AS
c SYS_REFCURSOR;
BEGIN
OPEN c FOR select * from dual;
RETURN c;
END;
-
I executed the code. Observed that a new function appears in the Object explorer.
-
Select the function MYREFCURSOR in the object explorer
-
Execute the function using the lighting bolt in the toolbar. Observed that the result sets tab in the Results pane gets focused, but no output appears in there.
Did I miss a step?
I am using TDA 3.0.1.1734
Check to see if the function created is in a valid state.
Also, post a full screetn shot of what it looks like when you try to execute.
Debbie
I don't know what is meant by valid state, and unfortunately, the screen shots aren't going to be very helpful other than to verify the steps, but here goes:
The first screenshot shows the contents of the function (I right-clicked the MYREFCURSOR in the object explorer and chose Operations->Alter Function...)
I then executed the function (just to be sure) by clicking the Execute icon (ctrl+shift+return, but I think I changed that from the default).
Screenshot 2 shows the screen after I click the lighting bolt in the object explorer. I see a small difference from the screenshot that you posted in that there is not a checkbox in front of that "RESULT-CURSOR" line. Is that important? Anyway, I just clicked OK here.
Screenshot 3 shows the result set is empty.
Skip
I don't know what is meant by valid state, and unfortunately, the screen shots aren't going to be very helpful other than to verify the steps, but here goes:
The first screenshot shows the contents of the function (I right-clicked the MYREFCURSOR in the object explorer and chose Operations->Alter Function...)
I then executed the function (just to be sure) by clicking the Execute icon (ctrl+shift+return, but I think I changed that from the default).
Screenshot 2 shows the screen after I click the lighting bolt in the object explorer. I see a small difference from the screenshot that you posted in that there is not a checkbox in front of that "RESULT-CURSOR" line. Is that important? Anyway, I just clicked OK here.
Screenshot 3 shows the result set is empty.
Skip
I don't know what is meant by valid state, and unfortunately, the screen shots aren't going to be very helpful other than to verify the steps, but here goes:
The first screenshot shows the contents of the function (I right-clicked the MYREFCURSOR in the object explorer and chose Operations->Alter Function...)
I then executed the function (just to be sure) by clicking the Execute icon (ctrl+shift+return, but I think I changed that from the default).
Screenshot 2 shows the screen after I click the lighting bolt in the object explorer. I see a small difference from the screenshot that you posted in that there is not a checkbox in front of that "RESULT-CURSOR" line. Is that important? Anyway, I just clicked OK here.
Screenshot 3 shows the result set is empty.
Skip
Let's check to see if the function is in a valid state. Right click and under options choose 'Status' as an extended column. It will tell you if the function was created and compiled ok.
Debbie
It's valid.
By the way, thanks for all the help and sticking with me.
As I look into this I find that I made a change in the code for TDA 3.1. It works in the Beta build and will work in the next release. But 3.0 displays as you show it.
Woops . Is it possible to download the Beta and try?
Debbie
Yes, I can download it. Is it stable enough to use on a daily basis? Or can I install it alongside my “real” installation and use both of them as needed? And will they share the same settings in that case?
They are side by sdie installs so you can use both. When you first run the Beta it will ask you if you want to import your settings from your other application. If it doesn’t do this just copy over the settings.xml and connecitons.xml from the Data Directory.
Debbie
