Select in Oracle Stored Procedure raises PLS-00428

I am very new to Oracle, and I have been put on a project that requires me to use it. I am trying to create a new stored procedure, the select statement works, but when I run the entire thing, it will say:

[Error] PLS-00428 (8: 9): PLS-00428: an INTO clause is expected in this SELECT statement

Here is the code:

CREATE PROCEDURE SSACHDEV.CheckServiceProviderDownload
as

tempInt number;

BEGIN

    Select 
        distinct(trunc(dateStamp)), 
        SVCProviderSTTSSEQID, 
        CaseNbr 
        into 
        "Date",
        PrividerId,
        CaseNumber
    from SVCProviderSTTS 
    Where (trunc(datestamp) between trunc(sysdate - 1) and trunc(sysdate));




   -- tskmgr.task_priority_download (CaseNumber, Id, tempInt);

END;

I figured I have to create a temporary table to store the results into then do a cursor to call the next procedure which I have commented out called tskmgr.task_priority_download(CaseNumber, Id, tempInt);

Any help would be greatly appreciated. Thank you very much.

Answers


First, I'd recommend against using a local temp variable called date. Something like myDate or Date1 or anything else would be preferable, in my opinion.

Second, you have to declare those variables -- Date, ProviderId, and Casenumber.

For example,

CREATE PROCEDURE...
as
   tempInt NUMBER;
   myDate NUMBER;
   caseNumber VARCHAR2(100);
   ProviderId NUMBER;
BEGIN
...

Lastly, even though it makes things a little verbose, I tend to prefix my variables so that I can tell they are variables. For example, I might have done this instead, but some folks would tell me it is wasteful and unnecessary.

CREATE PROCEDURE...
as
   tempInt NUMBER;
   l_Date NUMBER;
   l_caseNumber VARCHAR2(100);
   l_ProviderId NUMBER;
BEGIN
...

Naming my variables this way makes it easier to understand when I am doing a join or where clause and have column names the same as my variables -- which you do.

I am a little confused as to the error message though. I'll take one more look and update my answer if necessary.


Need Your Help

batch code to run ccleaner

batch-file

How do I start CCleaner with batch commmand. I am using this.

When to use Golang's default MUX versus doing your own

http go mux

I have seen a lot of posts talk about building your own MUX in Go, one of the many examples is here (http://thenewstack.io/building-a-web-server-in-go/).