Home » SQL & PL/SQL » SQL & PL/SQL » Oracle/PL/SQL runtime errors.
Oracle/PL/SQL runtime errors. [message #335704] Wed, 23 July 2008 06:14 Go to next message
sanramcp
Messages: 1
Registered: July 2008
Location: UK
Junior Member
Hi everybody,
I am new to this forum, i found this interesting and helpful for people like me who have little knowledge of PL/SQL.
I am using Oracle 8i and trying to run the following code to extract the data i need. I tried to understand the errors but i couldnt resolve, can anybody help me please.

SQL> DECLARE
  2   A DATE;
  3   B DATE;
  4  BEGIN
  5   A:= TO_DATE('07-21-2007','MM-DD-YYYY');
  6   B:= TO_DATE('06-23-2007', 'MM-DD-YYYY');
  7  
  8   SELECT gp.org_pas_code GP
  9         ,hos.org_short_name Hos
 10         ,koi.koi_sepe_short_name Wd
 11         ,koi.koi_ccs_pid No
 12         ,koi.koi_start_date ST 
 13         ,koi.koi_end_date ED 
 14        ,sum
 15        (least(trunc
 16             (nvl(koi.koi_end_date,A)),A)
 17        - greatest(trunc
 18             (koi.koi_start_date ),B)) LOS
 19  from   organisations           gp
 20        ,organisations           hos
 21        ,people                  per
 22        ,gp_registrations        gpr
 23        ,addresses               adr
 24        ,address_usages          adu
 25        ,korner_intervals        koi
 26  where  koi.koi_ccs_specialty in ('5','21','31')
 27  and    gpr.gpr_status(+)!= 'X' 
 28  and    adu.adu_status(+) != 'X' 
 29  and    koi.koi_status in ('C','O')
 30  and    hos.org_id = koi.koi_Sepe_located_at
 31  and    gpr.gpr_gp_org_id = gp.org_id(+)
 32  and    adu.adu_address_type(+) = 'HOME'
 33  and    adu.adu_start_date(+) < nvl(koi.koi_end_date,sysdate) 
 34  and    NVL(adu.adu_end_date(+),sysdate) >= trunc(nvl(koi.koi_end_date,sysdate)) 
 35  and    gpr.gpr_registration_type(+) = 'NORMAL'
 36  and    gpr.gpr_start_date(+) < nvl(koi.koi_end_date,sysdate)
 37  and    NVL(gpr.gpr_end_date(+),sysdate) >= trunc(nvl(koi.koi_end_date,sysdate)) 
 38  AND    adu.adu_add_id = adr.add_id(+)
 39  AND    koi.koi_ccs_per  = adu.adu_per_id(+)
 40  and    koi.koi_ccs_per  = gpr.gpr_per_id(+)
 41  AND    adu.adu_add_id = adr.add_id(+)
 42  and    gpr.gpr_per_id(+) = koi.koi_ccs_per 
 43  and    koi.koi_ccs_per = per.per_id
 44  and    trunc(nvl(koi.koi_end_date,A))
 45            >= B
 46  and    trunc(koi.koi_start_date)< A
 47  and    koi.koi_sepe_short_name = 'WD'
 48  GROUP BY NVL(gp.org_pas_code,'NO GP')    
 49         ,hos.org_short_name 
 50         ,koi.koi_sepe_short_name  
 51         ,koi.koi_ccs_pid  
 52         ,koi.koi_start_date 
 53         ,koi.koi_end_date; 
 54  END; 
 55  /
DECLARE
*
ERROR at line 1:
ORA-06550: line 8, column 2:
PLS-00428: an INTO clause is expected in this SELECT statement
ORA-06550: line 8, column 2:
PL/SQL: SQL Statement ignored


[Edit MC: Add code tags]

[Updated on: Wed, 23 July 2008 06:51] by Moderator

Report message to a moderator

Re: Oracle/PL/SQL runtime errors. [message #335707 is a reply to message #335704] Wed, 23 July 2008 06:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Use the "Preview Message" button to verify.

In order to use SELECT in PL/SQL you have to tell Oracle where it should put the result, in which variables.

2 books that might be usefule for you:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel
Re: Oracle/PL/SQL runtime errors. [message #335727 is a reply to message #335704] Wed, 23 July 2008 07:39 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
sanramcp wrote on Wed, 23 July 2008 07:14
Hi everybody,
I am new to this forum, i found this interesting and helpful for people like me who have little knowledge of PL/SQL.
I am using Oracle 8i and trying to run the following code to extract the data i need. I tried to understand the errors but i couldnt resolve, can anybody help me please.



Just a few additional points. this is a fairly complicated code for someone with little knowledge. It's probably someone else's code, right? You say "extract" but a PL/SQL block does not produce output unless you use UTL_FILE or DBMS_OUTPUT directives. Being that you did not give an INTO clause, I am wondering if you meant to use PL/SQL or straight SQL.
Previous Topic: Help can't get this to work
Next Topic: retrieving procedure name
Goto Forum:
  


Current Time: Sun Dec 04 18:58:40 CST 2016

Total time taken to generate the page: 0.08705 seconds