What is wrong?

Posted on 2013-05-10
Medium Priority
Last Modified: 2013-06-02
I have this query:
  type nt_type is table of string(10);
  nt nt_type := nt_type ('08.05.2013','09.05.2013');

for i in 1..nt.count loop

select request_id, request_emode, request_pgm, pp.description, name_id_no, substr(addressee,1,15), policy_no, pr.print_job,request_date 
  from print_request pr, print_program pp
where request_pgm = program_id
  and request_pgm not like ('B8350')
  and request_pgm not like ('BAT%')
  and request_pgm not like ('F%')
  and request_pgm not like ('KX%')
  and request_pgm not like ('269')
  and (pr.printer_id in (1,2) or pr.document_pile is not null)
  and request_emode not like ('X%')
  and trunc(request_date) = to_date(nt(i), 'DD.MM.YYYY')
order by pr.print_job desc, name_id_no;

end loop;

Open in new window

If I run the select statement and replace nt(i) with e.g. '08.05.2013' it works fine, but running it like this gives med the error:
Error report:
ORA-06550: linje 8, kolonne 1:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

Open in new window

What could be the problem? All I want to do is to run the same select several times but with different date values.

Question by:Iver Erling Arva
  • 3
  • 2
  • 2
LVL 74

Expert Comment

ID: 39155544
>>> replace nt(i) with e.g. '08.05.2013'

Ff that works, then you're seeing an oracle bug.
A select without an INTO clause is wrong whether you're using variable or literals.

In other words, the error message is correct for the code you have posted

When you run the select,  what are you expecting to do with the results?
That's why you need an INTO, so the sql output has somewhere to go
LVL 36

Expert Comment

by:Mark Geerlings
ID: 39156592
I agree, an implicit cursor (like yours) in PL\SQL requires an "into" line with either a record variable or discrete variables for each column being selected.

I see another potential problem in your query, that is: "trunc(request_date)".  In a tiny test system, this will not be a problem.  Also, in a new production system with few (or no) records initially, this will not be a problem either.  But, as the number of records in this table increases, and especially if this column is indexed, your "trunc" will prevent the use of the index and force Oracle to read every record that meets the other criteria.  That could add a huge performance penalty as the data in this table increases.

Try to avoid using any operators like: trunc, upper, lower, to_char, to_date, etc. on database columns referenced in your "where" clause.  Do the conversion on the other side of the '=' sign instead.  For date columns that may include a time-of-day, sometimes that means you will have to use "between" instead of "=", something like this:

and request_date between to_date(nt(i), 'DD.MM.YYYY') and to_date(nt(i), 'DD.MM.YYYY') + 86399/86400

1. There are 86,400 seconds in a day (60 x 60 x 24).
2. Your first "to_date" with no time component included, will mean a time of midnight (the first second in the day).
3. The second "to_date" adds enough seconds to get to the last second of the day, so a "request_date" value that includes any time of that day will be included..

Author Comment

by:Iver Erling Arva
ID: 39159318
First you ask what I expect, I'd expect the output of two SELECTs in the output window of Oracle SQL Developer just as I get the output from one select when I enter it and run it.

I will try your suggested solutions tomorrow. Partly this is also since I'm trying to learn.
I have used a lot of relatively straight forward SELECTs for many years, but have used another programming language to manipulate and get the results that I wanted. I thought it would be interresting to go one step further and do it all using sql.

I hear what you say about the INTO, but I have never used it like this. My selects usually are in the form:
SELECT column(s) FROM table(s) WHERE condition(s) ORDER BY column(s).

Open in new window

That's it. It has always worked fine.
But if I have to use INTO in this case, I would like to know what to use to get it to print the output columns in the output window of SQL Developer.
7 new features that'll make your work life better

It’s our mission to create a product that solves the huge challenges you face at work every day. In case you missed it, here are 7 delightful things we've added recently to monday to make it even more awesome.

LVL 74

Expert Comment

ID: 39160179
When you enter a select statement in the editor window of sql developer your execution environment is sql developer and the sql engine returns the output to that.

When you run it as you have shown above your environment is your pl/sql  block and the sql engine returns the output to that and that's why you need an "INTO" to give the output someplace to go.

sql developer has an output grid,  pl/sql does not.

Author Comment

by:Iver Erling Arva
ID: 39201135
Ok, so what should I enter to send the output to SQL developer then? Is that possible?

LVL 36

Accepted Solution

Mark Geerlings earned 500 total points
ID: 39201535
Just use a "select..." statement like the one in your example.  Don't put it in a PL\SQL block in SQL Developer if/when you don't need PL\SQL functionality.

Author Closing Comment

by:Iver Erling Arva
ID: 39215298
Back to where I started, then...

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question