Home » Other » Client Tools » Problem creating procedure using SQL Developer (Mac, SQL Developer for Mac, Oracle 10g on XP virtual machine)
Problem creating procedure using SQL Developer [message #540454] Mon, 23 January 2012 14:38 Go to next message
mallethead@verizon.net
Messages: 10
Registered: January 2012
Location: New Jersey
Junior Member
Hello,

I think this is probably a simple answer but I'm not seeing it. I am trying to get my stored procedures together again after many years of working on MS SQL server. I'm alternating between using SQL-Plus and Oracle SQL Developer for Mac. In SQL Developer - which I like to use - I keep getting errors at the point where the END command for the package header occurs and the create command for the package body starts.

I've found that if I run the following in SQL-Plus I'm OK but if I try it from SQL Developer I get a compile error such as following or else an error telling me that it expects function, or pragma or something to that effect:

Error(8,1):PLS-00103:Encountered the symbol "/"

The code is as follows:
--------------------------------------
CREATE OR REPLACE PACKAGE MACR_SAMPLE_PROC_4 AS

PROCEDURE sampleproc_4(
pParam1 in integer,
pParam2 out varchar2);
END MACR_SAMPLE_PROC_4;

CREATE OR REPLACE PACKAGE BODY MACR_SAMPLE_PROC_4 AS
PROCEDURE SampleProc_4(
pParam1 in integer,
pParam2 out varchar2) IS
BEGIN
IF pParam1 = 1
THEN
pParam2 := 'Hello !!';
ELSE
pParam2 := 'Goodbye !!';
END IF;
END SampleProc_4;

END MACR_SAMPLE_PROC_4;
--------------------------------

Can someone tell me what I need to do differently in SQL Developer?

Many thanks,
Mallethead
Re: Problem creating procedure using SQL Developer [message #540455 is a reply to message #540454] Mon, 23 January 2012 15:04 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
I don't like gui tools like SQL Developer, so I don't know how it handles things, but if you were doing this in SQL*Plus, you would have to put a slash (/) after the header and body sections. I do not see any in yours, but maybe SQLDeveloper "implies" them?
Re: Problem creating procedure using SQL Developer [message #540456 is a reply to message #540455] Mon, 23 January 2012 15:10 Go to previous messageGo to next message
mallethead@verizon.net
Messages: 10
Registered: January 2012
Location: New Jersey
Junior Member
joy_divison,

After reading your reply I see that I wasn't completely clear. I should have said I used the / after the header and body sections in SQL-Plus and it works. However, I'm using SQL Developer and it throws an error with them or without them.

So, there must be some other way in SQL Developer to transition from the head to the body that is not clear to me.

thanks, mallethead
Re: Problem creating procedure using SQL Developer [message #540457 is a reply to message #540456] Mon, 23 January 2012 15:19 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Could it be that this "tool" does not allow creation of header and body in the same command?

Or, if it works in SQL*Plus, why continue to use SQLDeveloper to actually develop. It seems like it would be sufficient tool to "browse" your structure and metadata, but not to do any development.

[Updated on: Mon, 23 January 2012 15:21]

Report message to a moderator

Re: Problem creating procedure using SQL Developer [message #540458 is a reply to message #540454] Mon, 23 January 2012 15:45 Go to previous messageGo to next message
wvu1999
Messages: 40
Registered: April 2007
Member

This is two commands, so you need to execute as script or F5 in the worksheet.
Re: Problem creating procedure using SQL Developer [message #540461 is a reply to message #540458] Mon, 23 January 2012 16:06 Go to previous messageGo to next message
mallethead@verizon.net
Messages: 10
Registered: January 2012
Location: New Jersey
Junior Member
wvu1999,

thanks I think you are on to it. I've just seen that there is a difference between where you paste the code into a SQL Developer window and how you choose to run it. I'm trying to learn SQL Developer and re-learn stored procedures at the same time.

I just tried clicking on the "New" button and selecting "SQL File" from the dialogue box. Gave it a name, pasted in my code to build the package and clicked on the "Run Script" button. I also included the forward slashes (/) at the end of the package header create code and the package body create code.

This WORKED and I am glad it did. I was trying to right click on Packages and select "New Package", paste in the code, then click on the "Compile" button and this always seems to throw the error.

However, I'm still perplexed about the whole process. Can I NOT create a header and package together by right-clicking on "Packages" and choosing "New Package". Do I have to do them separately in SQL Developer??

Thanks a bunch, mallethead
Re: Problem creating procedure using SQL Developer [message #540462 is a reply to message #540461] Mon, 23 January 2012 16:15 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_twelve004.htm#sthref1103

the slash character is an "operator" within sqlplus Utility & NOT part of SQL standard.
Re: Problem creating procedure using SQL Developer [message #540464 is a reply to message #540462] Mon, 23 January 2012 16:26 Go to previous messageGo to next message
mallethead@verizon.net
Messages: 10
Registered: January 2012
Location: New Jersey
Junior Member
Thanks All,

wvu1999 I don't see a way to mark the correct answer or award points but I consider your answer as correct.

BlackSwan - thanks for the link - I see now. However, I was successful just now running the the entire script (in SQL Developer) including the slashes - I'll have to try it again without them after dinner.

Regards, mallethead
Re: Problem creating procedure using SQL Developer [message #540465 is a reply to message #540464] Mon, 23 January 2012 17:42 Go to previous messageGo to next message
wvu1999
Messages: 40
Registered: April 2007
Member

SQL Developer has a SQL*Plus emulator, when you use the 'Execute as Script' button, that invokes the SQL*Plus 'engine.'

While we don't support 100% of SQL*Plus commands - for instance you can't issue a shutdown - we should have the heavy hitters you depend on for scripting.

If you find one lacking, please let us know!

Also, you can find a slew of SQL Developer tutorials and tricks on my blog
http://www.thatjeffsmith.com/archive/category/general/pedia/sqldev/
Re: Problem creating procedure using SQL Developer [message #540467 is a reply to message #540465] Mon, 23 January 2012 19:28 Go to previous message
mallethead@verizon.net
Messages: 10
Registered: January 2012
Location: New Jersey
Junior Member
wvu1999,

Thanks for the link. I had looked at an article on that site this weekend while desparately searching for some answers. I didn't see the Sql Developer Category link in the left nav then but I do now. Looking forward to checking them out.

I understand about the emulator - that is helpful.

mallethead
Previous Topic: sql developer online data move
Next Topic: can we force a paramter value to change the filename for every execution of sql script
Goto Forum:
  


Current Time: Fri Mar 29 11:02:50 CDT 2024