Home » SQL & PL/SQL » SQL & PL/SQL » Simple procedure (select into procedure)
Simple procedure [message #642851] Mon, 21 September 2015 13:34 Go to next message
jclouser
Messages: 2
Registered: September 2015
Location: Tampa
Junior Member
Compiles without errors.

CREATE OR REPLACE PROCEDURE VBS
(inusername in dba_users.username%TYPE,
outusername out dba_users.username%TYPE )
is
begin

select username
into outusername
from dba_users
where username = inusername;


END VBS;
/

But when I:

EXECUTE VBS ARUNION


Get:

ERROR at line 1:
ORA-06550: line 1, column 11:
PLS-00103: Encountered the symbol "ARUNION" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "ARUNION" to continue.

New to this, please help.

Report message to a moderator

Re: Simple procedure [message #642852 is a reply to message #642851] Mon, 21 September 2015 13:45 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
 
declare
  l_out dba_users.username%type;
begin
  vbs('ARUNION', l_out);
  dbms_output.put_line(l_out);
end;
/


[EDIT: added L_OUT to procedure call]

[Updated on: Mon, 21 September 2015 14:55]

Report message to a moderator

Re: Simple procedure [message #642853 is a reply to message #642852] Mon, 21 September 2015 13:52 Go to previous messageGo to next message
jclouser
Messages: 2
Registered: September 2015
Location: Tampa
Junior Member
Well, this script is a stepping stone for a bigger one I want to do.
I want to get this small one working so I can add to/expand it.

Report message to a moderator

Re: Simple procedure [message #642854 is a reply to message #642852] Mon, 21 September 2015 14:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You meant:
vbs('ARUNION',l_out)


[Edit: Yes, you meant it. Smile ]

[Updated on: Mon, 21 September 2015 14:56]

Report message to a moderator

Re: Simple procedure [message #642855 is a reply to message #642853] Mon, 21 September 2015 14:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jclouser wrote on Mon, 21 September 2015 20:52
Well, this script is a stepping stone for a bigger one I want to do.
I want to get this small one working so I can add to/expand it.


Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals.
And use SQL*Plus and copy and paste your session when you post a question.

Report message to a moderator

Re: Simple procedure [message #642856 is a reply to message #642853] Mon, 21 September 2015 15:02 Go to previous message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, the only thing you can omit is the DBMS_OUTPUT.PUT_LINE call (it just displays the result).

As the procedure you created has two parameters - one IN and one OUT, you have to pass them when you call the procedure. (Ooops! I just noticed that I missed the OUT parameter - I'll fix it now!) In order to be able to accept the result, you have to declare a variable which will do that job - it is the L_OUT in my example.

Being a PL/SQL piece of code, you need to use all parts that its syntax requires: declaration section along with executable code embedded into BEGIN-END keywords.

So, there's nothing smaller in this case.

However: perhaps you should have used a function (instead of a procedure) as it returns a value anyway.

Finally, consider visiting this page: Oracle 10g documentation. You didn't mention which version you use. 12c is the latest one, and 10g is now obsolete but the documentation page is neatly arranged so that newbies - like you - can easily spot what's interesting. Start with "Getting started", and then move to PL/SQL in "PL/SQL User's Guide and Reference" and "Application Developer's Guide - Fundamentals".

If you feel that 10g doesn't suit your needs, feel free to read documentation related to version you use. It can be found on the Oracle Technology Network.

Report message to a moderator

Previous Topic: How To Trunc Date In Oracle
Next Topic: How to create named DEFAULT constraint
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 03:49:57 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.