Home » SQL & PL/SQL » Client Tools » Unable to Print The Query Output On TOAD (TOAD 10 & Oracle 10 G)
Unable to Print The Query Output On TOAD [message #540540] Tue, 24 January 2012 06:51 Go to next message
bagiri
Messages: 5
Registered: January 2012
Location: Bangalore
Junior Member
[MERGED by LF]


Hi

When i run the below mentioned query(similar query)on TOAD
It will run without any errors and i can see "PL/SQL Procedure sucessfully completed" in the taskbar.....

However i am unable to see the output for the query.

Could you please help me to print the the query output on TOAD ....


**** Sample Query Starts *****
DECLARE
i PLS_INTEGER;
BEGIN
SELECT NVL(i, 93)
INTO i
FROM DUAL;

--print i;
--dbms_output.put_line('i1: ' || i);
END;

**** Sample Query Ends*****


**** Actual Query Starts *****
-----------------------------------------------------------------
DECLARE
UnxDate number(6);
MyResult number(6);
Todaysdate date := TO_DATE('17-01-2012 00:00:00','dd-mm-yyyy hh24:mi:ss');
BEGIN

SELECT ROUND (Todaysdate - TO_DATE('01-jan-1970','dd-mon-yyyy') ) INTO UnxDate FROM dual;

SELECT sum(decode(TableA.Col1,null,1,TableA.Col1))
INTO MyResult
FROM TableA
WHERE Col_Num2 > 123
AND
Col_Num2 >= (SELECT min(Col_Num2) FROM TableA WHERE Col_Date = UnxDate)
AND
Col_Num2 <= (SELECT max(Col_Num2) FROM TableA WHERE Col_Date = UnxDate);
end;
-----------------------------------------------------------------
**** Actual Query Starts *****

Thanks
-Giridhar


[Updated on: Fri, 27 January 2012 05:20] by Moderator

Report message to a moderator

Re: Unable to Print The Query Output On TOAD [message #540542 is a reply to message #540540] Tue, 24 January 2012 07:05 Go to previous messageGo to next message
Littlefoot
Messages: 19815
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Editor window contains several tabs in its bottom part: Data Grid (which displays output of a SELECT statement), Auto Trace, DBMS output, Query Viewer, ...

So, navigate to DBMS Output tab, enable server output (click the red button to turn it green), set polling frequency (if you want to) and run the procedure again.

./fa/9739/0/

[Updated on: Tue, 24 January 2012 07:07]

Report message to a moderator

Re: Unable to Print The Query Output On TOAD [message #540545 is a reply to message #540542] Tue, 24 January 2012 07:25 Go to previous messageGo to next message
bagiri
Messages: 5
Registered: January 2012
Location: Bangalore
Junior Member
Hey It works!!!
Thanks a lot...
How to display output without using DBMS_OUTPUT.PUT_LINE [message #541035 is a reply to message #540540] Fri, 27 January 2012 03:41 Go to previous messageGo to next message
bagiri
Messages: 5
Registered: January 2012
Location: Bangalore
Junior Member
Hi

what are the alterntive commands for dbms_output.put_line to display the output in pl/sql.

Example:

Declare
g_result date;
BEGIN
SELECT sysdate into g_result FROM dual;
--DBMS_OUTPUT.PUT_LINE(g_Result);
-- How to Display the value of g_result without using DBMS_OUTPUT.PUT_LINE
above command??

END;

Thanks and Regards
GIRIDHAR
Re: How to display output without using DBMS_OUTPUT.PUT_LINE [message #541036 is a reply to message #541035] Fri, 27 January 2012 03:45 Go to previous messageGo to next message
cookiemonster
Messages: 11202
Registered: September 2008
Location: Rainy Manchester
Senior Member
In TOAD? None.

PL/SQL runs on the server. It is not designed to interact with the client.
TOAD will print the results of selects that are run directly, and past that it'll only display the results of dbms_ouput.
Re: How to display output without using DBMS_OUTPUT.PUT_LINE [message #541038 is a reply to message #541036] Fri, 27 January 2012 04:01 Go to previous messageGo to next message
bagiri
Messages: 5
Registered: January 2012
Location: Bangalore
Junior Member
Okey,

So we dont have option in pl/sql to display the value of the variable?
how about PRINT,Select commands?
Re: How to display output without using DBMS_OUTPUT.PUT_LINE [message #541039 is a reply to message #541038] Fri, 27 January 2012 04:18 Go to previous messageGo to next message
cookiemonster
Messages: 11202
Registered: September 2008
Location: Rainy Manchester
Senior Member
There are debugging packages if that's what you're after, I assume TOAD can interface with them.
Otherwise dbms_output is your only option (well you could use utl_file but I doubt that'd be an improvement).
There is no print command in PL/SQL.
As for selects - if run directly in TOAD it'll give you the output, otherwise it's just another bit of PL/SQL.
Re: How to display output without using DBMS_OUTPUT.PUT_LINE [message #541052 is a reply to message #541039] Fri, 27 January 2012 05:30 Go to previous messageGo to next message
bagiri
Messages: 5
Registered: January 2012
Location: Bangalore
Junior Member
Okey, thanks
However i tried to avoid declaring & assigning results in to variable , Please find the below query where in i commented the variable declaration and tried to display result using select statement itself howevre below error recieved when i ran the query... could you please tell me why i got this error
Thank you


ORA-06550: line 6, column 9:
PLS-00428: an INTO clause is expected in this SELECT statement


DECLARE
Todaysdate date := TO_DATE('26-01-2012 00:00:00','dd-mm-yyyy hh24:mi:ss');
--sResult number(10); (COMMENTED) BEGIN
--(COMMENTED)
--SELECT sum(decode(TableA.Column_pages,null,1,TableA.Column_pages))
--INTO sResult FROM TableA

--Above line commented to display the output without using variable
SELECT sum(decode(TableA.Column_pages,null,1,TableA.Column_pages))
FROM TableA
WHERE Column_number > 123
AND
Column_number >=(SELECT max(Column_number) FROM TABLEA
WHERE ddate = Todaysdate)
AND
Column_number <= (SELECT max(Column_number) FROM TABLEA
WHERE ddate = Todaysdate);
--DBMS_OUTPUT.PUT_LINE(sResult); END;
Re: How to display output without using DBMS_OUTPUT.PUT_LINE [message #541053 is a reply to message #541052] Fri, 27 January 2012 05:46 Go to previous messageGo to next message
Littlefoot
Messages: 19815
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Because SELECT, when used in PL/SQL, requires INTO (where would you put the result, otherwise)?
Re: How to display output without using DBMS_OUTPUT.PUT_LINE [message #541059 is a reply to message #541053] Fri, 27 January 2012 06:22 Go to previous message
cookiemonster
Messages: 11202
Registered: September 2008
Location: Rainy Manchester
Senior Member
When I said run select on its own, I literally meant that. As soon as you put a BEGIN/END round it it's PL/SQL and you have use dbms_output to see the data in TOAD.
Previous Topic: can we force a paramter value to change the filename for every execution of sql script
Next Topic: Not getting Editor from sqlplus*
Goto Forum:
  


Current Time: Thu Nov 27 03:38:07 CST 2014

Total time taken to generate the page: 0.06824 seconds