Home » SQL & PL/SQL » SQL & PL/SQL » Easy PL/SQL question... I'm new to this.
Easy PL/SQL question... I'm new to this. [message #35849] Fri, 19 October 2001 07:01 Go to next message
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.

----------------------------------------------------------------------
Re: Easy PL/SQL question... I'm new to this. [message #35850 is a reply to message #35849] Fri, 19 October 2001 07:25 Go to previous messageGo to next message
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().

----------------------------------------------------------------------
Re: Easy PL/SQL question... I'm new to this. [message #35852 is a reply to message #35849] Fri, 19 October 2001 07:35 Go to previous messageGo to next message
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.

----------------------------------------------------------------------
Re: Easy PL/SQL question... I'm new to this. [message #35856 is a reply to message #35849] Fri, 19 October 2001 08:12 Go to previous messageGo to next message
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.

----------------------------------------------------------------------
Re: Easy PL/SQL question... I'm new to this. [message #35858 is a reply to message #35849] Fri, 19 October 2001 08:26 Go to previous messageGo to next message
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?

----------------------------------------------------------------------
Re: Easy PL/SQL question... I'm new to this. [message #35861 is a reply to message #35849] Fri, 19 October 2001 10:44 Go to previous messageGo to next message
arun
Messages: 67
Registered: January 2000
Member
why did you give a : before the number?

----------------------------------------------------------------------
Re: Easy PL/SQL question... I'm new to this. [message #35894 is a reply to message #35849] Mon, 22 October 2001 15:05 Go to previous message
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)

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


Current Time: Thu Mar 28 06:19:03 CDT 2024