Anonymous Blocks?

[Netherbeast]

Im trying to make an anonymous block and it is not working right. I keep getting errors or some other error.
Im using sqlplus.

DECLARE
amount NUMBER:=0;
BEGIN
SELECT COUNT(*)
INTO amount
FROM PERSON
WHERE PERSON.PERSONID = 1016;
DBMS_OUTPUT.PUT_LINE(amount);

does this look correct?

 

[SantaMufasa]

Beast,

When doing third-person error-message troubleshooting, it's pretty important to include the error message(s) along with the code in question.

Your code looks fine except for a missing "END;" statment and a "/" after that. As far as run-time errors are concerned, if the SELECT cannot successfully find EXACTLY one row in the PERSON table WHERE PERSON.PERSONID = 1016, then that will cause a run-time error.

So, include the error message(s), and we can give you more help.

Mufasa
(aka Dave of Sandy, Utah, USA @ 02:24 (08Dec03) GMT, 19:24 (07Dec03) Mountain Time)

 

[Netherbeast]

Sorry, I just realized I forgot to include the error.
When I write:

CREATE OR REPLACE PROCEDURE TESTING
AS
BEGIN
SELECT P.PERSONID
FROM PERSON P;
END;

WARNING: Procedure created with compilation errors.

When I try and run the procedure I type "exec testing" and I get an error at line 1 saying the object beast.testing is invalid.

Thanks in advance

 

[SantaMufasa]

Hmmmm, Beast. Slightly different code from before, right?

This time, the syntax problem is that in PL/SQL, you MUST have a target "INTO" variable to receive the expressions you are selecting unless you use an explicit CURSOR (which you are not using here).

Fix that problem, then re-invoke the block. If you encounter the message, "WARNING: Procedure created with compilation errors," just type "show errors" at the SQL> prompt. If the error message(s) are not intuitive, then re-post here: code and results of "show errors".

Mufasa
(aka Dave of Sandy, Utah, USA @ 04:39 (08Dec03) GMT, 21:39 (07Dec03) Mountain Time)

 

[Netherbeast]

Thanks Mufasa,
do you think you could write a small example on how to use the 'INTO' and the 'CURSOR' that you were talking about. I dont know what the two keywords do.

thanks very much.

 

[carp]

The problem with your first block is that you forgot "END;" at the "end"!

Try
DECLARE
amount NUMBER:=0;
BEGIN
SELECT COUNT(*)
INTO amount
FROM PERSON
WHERE PERSON.PERSONID = 1016;
DBMS_OUTPUT.PUT_LINE(amount);
END;

Elbert, CO
0948 MDT

 

[Netherbeast]

Having problems still. Im trying to use a loop to print the first names of everyone in the table.
I dont know how to use loops yet so Im just trying to make a procedure that will print the number of the first person in the table.

CREATE OR REPLACE PROCEDURE TESTING
AS
theName NUMBER:=0;
CURSOR NAME IS
SELECT PERSON.PERSONID FROM PERSON
INTO theName
WHERE PERSON.PERSONID = 1016;
BEGIN
DBMS_OUTPUT.PUT_LINE(theName);
END;
/

the error is saying "SQL command not properly ended"

This code is probably totally wrong since I dont know what the CURSOR does.

Thanks Guys.

 

[Netherbeast]

I just figured it out...

Thanks

 

[SantaMufasa]

Beast,

Your last post is not "cricket". Just saying, "I just figured it out..." is not a valid reply. In fairness to your contributors, you need to tell us what the problem/solution was.

Mufasa
(aka Dave of Sandy, Utah, USA @ 03:41 (09Dec03) GMT, 20:41 (08Dec03) Mountain Time)

 

[Netherbeast]

oh...sorry,
the first problem I found was that I had the CURSOR and an INTO statement and that was not working well together.
So I just used the CURSOR to make the for loop that would select all the first names in my PERSON table.

CREATE OR REPLACE PROCEDURE TESTING
AS
CURSOR LINE IS
SELECT PERSON.FIRST AS FIRSTNAME FROM PERSON;
BEGIN
FOR RECORD IN LINE
LOOP
DBMS_OUTPUT.PUT_LINE(RECORD.FIRSTNAME);
END LOOP;
END;
/

I just realized what CURSOR did and figured out how to use it.

Hope that helps