Home » Other » Client Tools » SQL developer(procedure) (oracle 9i)
SQL developer(procedure) [message #383060] Tue, 27 January 2009 02:37 Go to next message
modhiyakomal
Messages: 49
Registered: November 2008
Location: US
Member
Hi
My code is:

create or replace
PROCEDURE PROCEDURE1
( emp_no IN NUMBER
) AS
a1 NUMBER;
b1 VARCHAR2(20);
c1 NUMBER;
BEGIN
select empno, ename, sal INTO a1, b1, c1
FROM emp
WHERE empno= emp_no;
END PROCEDURE1;


when i run this procedure in SQL Developer it gives me error:

Connecting to the database scott.
Process exited.
Disconnecting from the database scott.


how can i get output data of this procedure in SQL Developer?
Thanks.
Re: SQL developer(procedure) [message #383062 is a reply to message #383060] Tue, 27 January 2009 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous post:
Michel Cadot wrote on Tue, 13 January 2009 10:16
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


Re: SQL developer(procedure) [message #383067 is a reply to message #383062] Tue, 27 January 2009 02:56 Go to previous messageGo to next message
Littlefoot
Messages: 21805
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
The procedure code doesn't matter at all in this case (as far as I can tell); it is
Quote:
Connecting to the database scott.
Process exited.
Disconnecting from the database scott.
that needs attention.
Re: SQL developer(procedure) [message #383072 is a reply to message #383060] Tue, 27 January 2009 03:19 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
modhiyakomal wrote on Tue, 27 January 2009 09:37
when i run this procedure in SQL Developer it gives me error:

Connecting to the database scott.
Process exited.
Disconnecting from the database scott.

You posted the of the procedure definition code. Are you aware, that it does not execute the procedure, it only creates it? If so, I wonder, what "run" means and why you did not post its code. By the way, those messages do not seem to me like "error". Maybe you should specify exactly where you got them.

Quote:
how can i get output data of this procedure in SQL Developer?

As this procedure does not return anything, there is no way to get any data from it except of rewriting it. Maybe it should use OUT parameter(s). Maybe change it to function. Without knowing, where you want to use it, there is nothing more to say.
Re: SQL developer(procedure) [message #520084 is a reply to message #383072] Thu, 18 August 2011 09:14 Go to previous messageGo to next message
OzyM
Messages: 7
Registered: August 2011
Junior Member
create or replace
PROCEDURE PROCEDURE1
( emp_no IN NUMBER
) AS
a1 NUMBER;
b1 VARCHAR2(20);
c1 NUMBER;
BEGIN
select empno, ename, sal INTO a1, b1, c1
FROM emp
WHERE empno= emp_no;
END PROCEDURE1;

[color=red]dbms_output.put_line(a1);[/color] 



What if this line was place here and still recieved the same message?
Re: SQL developer(procedure) [message #520086 is a reply to message #520084] Thu, 18 August 2011 09:17 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
That line would need to go inside the procedure.
Re: SQL developer(procedure) [message #520087 is a reply to message #520086] Thu, 18 August 2011 09:22 Go to previous messageGo to next message
OzyM
Messages: 7
Registered: August 2011
Junior Member
Sorry, meant to put it inside the procedure. But if it WAS inside the procedure, would there be some sort of output?
Re: SQL developer(procedure) [message #520088 is a reply to message #520087] Thu, 18 August 2011 09:26 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
If output is enabled yes. I believe it is by default in SQLDeveloper.
You do realise this thread is 2 years old right?
Re: SQL developer(procedure) [message #520089 is a reply to message #520088] Thu, 18 August 2011 09:31 Go to previous messageGo to next message
OzyM
Messages: 7
Registered: August 2011
Junior Member
Haha...yes. I just started working with this and have been looking for answers. Thanks for the help.
Re: SQL developer(procedure) [message #522167 is a reply to message #520088] Wed, 07 September 2011 01:49 Go to previous messageGo to next message
Baranor
Messages: 83
Registered: September 2011
Location: Netherlands
Member
cookiemonster wrote on Thu, 18 August 2011 09:26
If output is enabled yes. I believe it is by default in SQLDeveloper.
You do realise this thread is 2 years old right?


Alas, not so. Since you are creating a procedure, all the output you're gonna get is whether or not its done. If it were an actual SQL-script like so:
declare
( emp_no IN NUMBER
) AS
a1 NUMBER;
b1 VARCHAR2(20);
c1 NUMBER;
BEGIN

select empno, ename, sal INTO a1, b1, c1
FROM emp
WHERE empno= emp_no;
dbms_output.put_line(a1);
END;



You'd be able to see the output in the output tab.
Re: SQL developer(procedure) [message #522169 is a reply to message #522167] Wed, 07 September 2011 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you read what you quote, especially "You do realise this thread is 2 years old right?"?

Regards
Michel
Re: SQL developer(procedure) [message #522172 is a reply to message #522169] Wed, 07 September 2011 02:08 Go to previous messageGo to next message
Baranor
Messages: 83
Registered: September 2011
Location: Netherlands
Member
Hi Michel,

Yes, I did, and OzyM replied to this thread, ressurecting it, and got a reply from cookiemonster that was not 100% correct, and that needed amending. I hope you don't mind that?

Best wishes,

Barry
Re: SQL developer(procedure) [message #522174 is a reply to message #522172] Wed, 07 September 2011 02:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68617
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't mind, just wanted to point that most likely OP (which didn't come since June 2009) will never read your answer.
So it is better you focus on more recent topics.

Regards
Michel

Re: SQL developer(procedure) [message #522175 is a reply to message #522174] Wed, 07 September 2011 02:15 Go to previous message
Baranor
Messages: 83
Registered: September 2011
Location: Netherlands
Member
Thanks for the tip, I'll keep an eye out. And Good Morning!
Previous Topic: SQLPLUS is not coming out with the exit code if not connected
Next Topic: Toad explain plan on current statement
Goto Forum:
  


Current Time: Mon Mar 18 22:23:12 CDT 2024