query in pl/sql

Question

im trying to write a query in pl/sql i know it should be in this order

DECLARE . . BEGIN . . END . RUN;

i do this but i get a weird problem after the end . the oracle mark a red line under the dot and the end of the word "end" and when i put my mouse over the line it say "syntax error, expected: identifier name name_wo_function_call"

this is my code

DECLARE
          temp_m model.model%TYPE;
          temp_mn model.modelnum%TYPE;
          temp_cap model.capacity%TYPE;
          temp_we model.weight%TYPE;
          CURSOR temp IS SELECT *  FROM model WHERE (weight>40) FOR UPDATE;
          CURSOR temp2 IS  SELECT model_num,COUNT(id)
                                            FROM TECH_MO
                                            GROUP BY model_num
                                            HAVING COUNT(id)>1;
BEGIN
          OPEN temp;
          OPEN temp2;
          LOOP
              FETCH temp INTO temp_m,temp_mn,temp_cap,temp_we;
              dbms_output.put_line(temp_m);
               EXIT WHEN temp%notfound;
          END LOOP;
          CLOSE temp;
          CLOSE temp2;
END;
.
RUN;

thank you for your answers.

Where did you find you need " . RUN" ? General syntax for Oracle anonymous block is `[declare] begin ... [exception] ... end;`. — a1ex07, Jan 15 '13 at 16:02
what ide are you using? You probably want to run an anonymous block as a script, and remove the . and RUN; parts. In Toad, run as script is an icon of a page with a lightning bolt. — tbone, Jan 15 '13 at 16:04
when i remove the ".run;" its saying "anonymous block completed" but i want to see the result of "dbms_output.put_line(temp_m);" why cant i see that? and where do i use ".run;"? — asaf, Jan 15 '13 at 16:08
if you're using a GUI , there's usually a panel for output. in sqlplus run `set serveroutput on size 1000000` first. — DazzaL, Jan 15 '13 at 16:13

score 1 · Accepted Answer · answered Jan 15 '13 at 16:14

1

No idea where you have got the .RUN from as that isn't part of the syntax. The way to do this in a script is like so.

DECLARE
  ...
BEGIN
  ...
EXCEPTION
  ...
END;
/

(The exception part is optional)

The issue you have with regard to not seeing the output I suspect is because you have not set server output on. You do this like so in SQL*Plus.

SET SERVEROUTPUT ON

answered Jan 15 '13 at 16:14

A. Gilfrin

302
1
8

when i put the "/" its showing me input without using the "set serveroutput on" , but when i put "set serveroutput on" its mark a red line under the "ser" in "serveroutput" and when i put my mouse over it say "syntax error, expected: . ; (" – asaf Jan 15 '13 at 16:25
You need to provide more details in the original question with regard to how you are running this. What development environment are you using? – A. Gilfrin Jan 15 '13 at 16:31
Oracle Sql developer Version 3.2.10.09 . i wrote this query in a .sql file – asaf Jan 15 '13 at 16:33
1

The accepted answer on this question has the details... http://stackoverflow.com/questions/7887413/printing-the-value-of-a-variable-in-sql-developer – A. Gilfrin Jan 15 '13 at 16:38
you great! its works. so last question is if i dont have to use ".run;" in annonymus block, then where should i use it?in procedures and functions? and what is that "/" in the end of the block? – asaf Jan 15 '13 at 16:41
Nowhere, it's not part of Oracle. Where did you find that? Are you misreading instructions that say click Run in SQL developer. – A. Gilfrin Jan 15 '13 at 17:24
i saw an example that using that , and i learn at the class that when i want to run procedures/functions i have to use that. so i dont have to use this at all? – asaf Jan 15 '13 at 18:06

query in pl/sql

1 Answers1