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.
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.
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.