OraFAQ Forum: SQL & PL/SQL » Easy PL/SQL question... I'm new to this.

Home » SQL & PL/SQL » SQL & PL/SQL » Easy PL/SQL question... I'm new to this.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Easy PL/SQL question... I'm new to this. [message #35849]

Fri, 19 October 2001 07:01

George Larry
Messages: 10
Registered: October 2001

Junior Member

This is my first attempt at PL/SQL so this is probably a very stupid question, but I'm asking it anyway...
I'm trying to create a procedure... here's my code:
CREATE OR REPLACE PROCEDURE blah(
sNum SERVICES.SERVICEID%TYPE,
aNum SERVICES.ACCOUNTNUMBER%TYPE
) AS
BEGIN
SELECT ACCOUNTNUMBER INTO aNum FROM SERVICES WHERE SERVICEID = sNum;
END blah;

I want to call the procedure with sNum as a parameter and have it return aNum...
But I'm getting an error:
6/2 PL/SQL: SQL Statement ignored
6/28 PLS-00403: expression 'ANUM' cannot be used as an INTO-target of a SELECT/FETCH statement

Why? Am I way off target? Please explain this to me.
Thanks.

----------------------------------------------------------------------

Report message to a moderator

Re: Easy PL/SQL question... I'm new to this. [message #35850 is a reply to message #35849]

Fri, 19 October 2001 07:25

arun
Messages: 67
Registered: January 2000

Member

Instead of giving aNum as a parameter give it as a variable declaration.And read the value of variable by using Dbms_output.Put_line().

----------------------------------------------------------------------

Report message to a moderator

Re: Easy PL/SQL question... I'm new to this. [message #35852 is a reply to message #35849]

Fri, 19 October 2001 07:35

George Larry
Messages: 10
Registered: October 2001

Junior Member

Excuse my inexperience... I have created the procedure:
CREATE OR REPLACE PROCEDURE blah(
sNum SERVICES.SERVICEID%TYPE
) AS
aNum SERVICES.ACCOUNTNUMBER%TYPE;
BEGIN
SELECT ACCOUNTNUMBER INTO aNum FROM SERVICES WHERE SERVICEID = sNum;
END blah;

But I don't understand how to use 'Dbms_output.Put_line();'
Could you enlighten me? With examples?
Thanks.

----------------------------------------------------------------------

Report message to a moderator

Re: Easy PL/SQL question... I'm new to this. [message #35856 is a reply to message #35849]

Fri, 19 October 2001 08:12

arun
Messages: 67
Registered: January 2000

Member

Before calling this procedure type
'set server output on'
at the start of your calling program.

Then if you give
Dbms_output.put_line(aNUM);
in your procrdure it will show up the value of aNUM.

----------------------------------------------------------------------

Report message to a moderator

Re: Easy PL/SQL question... I'm new to this. [message #35858 is a reply to message #35849]

Fri, 19 October 2001 08:26

George Larry
Messages: 10
Registered: October 2001

Junior Member

I'm trying to call the PL/SQL procedure using JSP:
CallableStatement cs = con.prepareCall ("begin blah (:57260); end;")
I'm getting an error:
ORA-01008: not all variables bound
Which variable am I missing?

----------------------------------------------------------------------

Report message to a moderator

Re: Easy PL/SQL question... I'm new to this. [message #35861 is a reply to message #35849]

Fri, 19 October 2001 10:44

arun
Messages: 67
Registered: January 2000

Member

why did you give a : before the number?

----------------------------------------------------------------------

Report message to a moderator

Re: Easy PL/SQL question... I'm new to this. [message #35894 is a reply to message #35849]

Mon, 22 October 2001 15:05

Don
Messages: 24
Registered: June 2001

Junior Member

CREATE OR REPLACE PROCEDURE blah(
sNum SERVICES.SERVICEID%TYPE,
aNum IN OUT SERVICES.ACCOUNTNUMBER%TYPE
) AS
BEGIN
SELECT ACCOUNTNUMBER INTO aNum FROM SERVICES WHERE SERVICEID = sNum;
END blah;

(Notice the IN OUT for the parameter)

----------------------------------------------------------------------

Report message to a moderator

Previous Topic:	dbms_sql
Next Topic:	how to lock a particupar column?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 30 15:07:17 CDT 2026