Help with using variable in Oracle SQL select statement?

Question

I'm a complete Oracle Noob, but I'm trying to run the following simple sql select statement to start familiarizing myself with how to use variables in my select statements. But when I run it I get an Oracle 06550 Error - "Encountered the symbol END when expecting one of the following:...." Here is the simple code I'm trying to run:

DECLARE stu_id number;

Begin
stu_id :=403984

END;
/
SELECT s.*

FROM students s

WHERE s.student_id = stu_id;

Answer 1

you have to write the query in your pl/sql block

also the results returned by the query should be stored in a variable



DECLARE stu_id number;

v_name varchar(10);



Begin

stu_id :=403984





SELECT s.name

INTO v_name

FROM students s



WHERE s.student_id = stu_id;



END;

/





if you use a simple datatype like string, you can store only the name

to store the complete record, you will require a record set



Its not that difficult once you get used to it





Also the above program wont display anything because there is no print statement

if you want to print, write

DBMS_OUTPUT.PUT_LINE(v_name);

before the END;



and if it still doesnt print, use the SQL * plus commad : - SET SERVEROUTPUT ON

before you run the program

Answer 2

What precisely do you prefer to attain ? The ORDER via syntax is there to reserve the consequences of your go with according to a number of of of the columns you come back. on your case you purely return CREATOR_ID, so it is the sole column you are able to order on, as an occasion: go with creator_id from table1 order via creator_id The order via clause needs column names (or sq. expressions), no longer PL/sq. variables.

Answer 3

Go and have a look at http://www.w3schools.com/sql/default.asp great tuts there



Also I would so suggest you go and download something a little more light weight for your IDE



Have a look at



http://www.oracle.com/technetwork/developer-tools/sql-developer/overview/index.html



Good luck !