| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
07-21-03, 17:44
|
|
Registered User
|
|
Join Date: Feb 2003
Location: In your thoughts
Posts: 195
|
|
|
Stored Procedure Error
|
|
Here is my code to create an SP:
Code:
CREATE OR REPLACE PROCEDURE attendance
(sdate IN DATE,
edate IN DATE)
AS
BEGIN
SELECT ALL EM.EM_KEY, EM.EM_DPKEY, EM.EM_TMKEY, EM.EM_LAST_NAME,
EM.EM_FIRST_NAME, ATX.ATX_CLOCK_IN, ATX.ATX_CLOCK_OUT, ATX.ATX_DATE
FROM ATX, EM
WHERE (ATX.ATX_EMKEY = EM.EM_KEY) AND ATX.ATX_DATE BETWEEN TO_CHAR(sdate) AND TO_CHAR(edate) ORDER BY ATX.ATX_DATE, EM.EM_LAST_NAME;
END attendance;
/
SHOW ERRORS
Now, I get these errors when ran:
Code:
Warning: Procedure created with compilation errors.
Errors for PROCEDURE ATTENDANCE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PLS-00428: an INTO clause is expected in this SELECT statement
6/3 PL/SQL: SQL Statement ignored
Can someone see where I'm going wrong? Thanks, Jeremy
__________________
Nothing better than a good ride.
|
|
07-21-03, 17:51
|
|
Registered User
|
|
Join Date: Jun 2003
Location: West Palm Beach, FL
Posts: 2,456
|
|
<QUOTE>
-----------------------------------------------------------------
6/3 PLS-00428: an INTO clause is expected in this SELECT statement
</QUOTE>
Oracle is trying to tell you that you have not defined any variables to receive the results of the query.
A 'select' statement inside a procedure requires variables defined in order to receive and be able to manipulate the results of the query.
You have to write the query as:
SELECT COLS,... INTO VARS... FROM ....
|
|
07-21-03, 17:54
|
|
Registered User
|
|
Join Date: Feb 2003
Location: In your thoughts
Posts: 195
|
|
|
|
Can you help me out? I thought I did that in my declare block but must had been mistaken. If I were to run the query by itself, and replaced "sdate" and "edate" with dates, it works properly. Thanks, Jermey
__________________
Nothing better than a good ride.
|
|
07-21-03, 17:57
|
|
Registered User
|
|
Join Date: Jul 2003
Posts: 2,292
|
|
aren't you passing in the dates from outside??
PHP Code:
CREATE OR REPLACE PROCEDURE attendance
(sdate IN DATE,
edate IN DATE)
cursor data_cursor is
SELECT
EM.EM_KEY,
EM.EM_DPKEY,
EM.EM_TMKEY,
EM.EM_LAST_NAME,
EM.EM_FIRST_NAME,
ATX.ATX_CLOCK_IN,
ATX.ATX_CLOCK_OUT,
ATX.ATX_DATE
FROM
ATX,
EM
WHERE
(ATX.ATX_EMKEY = EM.EM_KEY) AND
ATX.ATX_DATE BETWEEN
sdate AND edate
ORDER BY
ATX.ATX_DATE,
EM.EM_LAST_NAME;
AS
BEGIN
for v_data IN data_cursor
loop
(update, insert or whatever stuff you want to do)
end loop;
END;
/
Personally, I normally convert dates to character strings when they come in and manipulate then.
if the between gives you errors use the to_date function.
Also, you never say what you want to do with the data.
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
|
Last edited by The_Duck; 07-21-03 at 18:06.
|
07-21-03, 18:01
|
|
Registered User
|
|
Join Date: Feb 2003
Location: In your thoughts
Posts: 195
|
|
Duck...Same error as before. Got any ideas? Thanks, Jeremy
__________________
Nothing better than a good ride.
|
|
07-21-03, 18:09
|
|
Registered User
|
|
Join Date: Jul 2003
Posts: 2,292
|
|
I edited my code above.
state what you are trying to do.
Are you passing in the 2 date variables? I am guessing yes.
What do you want to do with your output?
Throw it in a new table? send it back out the proc?
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
|
|
07-21-03, 18:38
|
|
Registered User
|
|
Join Date: Feb 2003
Location: In your thoughts
Posts: 195
|
|
Well...I'm a developer and I was told I could create a Crystal Report from data coming from a Stored Procedure. All I need to do is create a procedure that will take my two variables, sdate and edate, and run a query and output to the screen as any other query. Can you help? Thanks, Jeremy
__________________
Nothing better than a good ride.
|
|
07-21-03, 18:58
|
|
Registered User
|
|
Join Date: Jul 2003
Posts: 2,292
|
|
sounds good to me.
You need to throw your output into variables.
Try this:
PHP Code:
CREATE OR REPLACE PROCEDURE attendance
( sdate IN DATE,
edate IN DATE,
V_EM_KEY OUT varchar2,
V_EM_DPKEY OUT varchar2,
V_EM_TMKEY OUT varchar2,
V_EM_LAST_NAME OUT varchar2,
V_EM_FIRST_NAME OUT varchar2,
V_ATX_CLOCK_IN OUT varchar2,
V_ATX_CLOCK_OUT OUT varchar2,
V_ATX_DATE OUT varchar2
)
AS
BEGIN
SELECT
EM.EM_KEY,
EM.EM_DPKEY,
EM.EM_TMKEY,
EM.EM_LAST_NAME,
EM.EM_FIRST_NAME,
ATX.ATX_CLOCK_IN,
ATX.ATX_CLOCK_OUT,
ATX.ATX_DATE
INTO
V_EM_KEY,
V_EM_DPKEY,
V_EM_TMKEY,
V_EM_LAST_NAME,
V_EM_FIRST_NAME,
V_ATX_CLOCK_IN,
V_ATX_CLOCK_OUT,
V_ATX_DATE
FROM
ATX,
EM
WHERE
(ATX.ATX_EMKEY = EM.EM_KEY) AND
ATX.ATX_DATE BETWEEN
sdate AND edate
ORDER BY
ATX.ATX_DATE,
EM.EM_LAST_NAME;
END;
/
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
|
|
07-21-03, 19:04
|
|
Registered User
|
|
Join Date: Jul 2003
Location: US
Posts: 314
|
|
|
Re: Stored Procedure Error
Hi
The way the stored procedures work in case of select statement, is that you specify into clause, which have the local variables names declared in the DECLARE section.
Hope that helps.
Thanx and Regards
Aruneesh
Quote:
Originally posted by JCScoobyRS
Here is my code to create an SP:
Code:
CREATE OR REPLACE PROCEDURE attendance
(sdate IN DATE,
edate IN DATE)
AS
BEGIN
SELECT ALL EM.EM_KEY, EM.EM_DPKEY, EM.EM_TMKEY, EM.EM_LAST_NAME,
EM.EM_FIRST_NAME, ATX.ATX_CLOCK_IN, ATX.ATX_CLOCK_OUT, ATX.ATX_DATE
FROM ATX, EM
WHERE (ATX.ATX_EMKEY = EM.EM_KEY) AND ATX.ATX_DATE BETWEEN TO_CHAR(sdate) AND TO_CHAR(edate) ORDER BY ATX.ATX_DATE, EM.EM_LAST_NAME;
END attendance;
/
SHOW ERRORS
Now, I get these errors when ran:
Code:
Warning: Procedure created with compilation errors.
Errors for PROCEDURE ATTENDANCE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
6/3 PLS-00428: an INTO clause is expected in this SELECT statement
6/3 PL/SQL: SQL Statement ignored
Can someone see where I'm going wrong? Thanks, Jeremy
|
|
|
07-21-03, 19:12
|
|
Registered User
|
|
Join Date: Feb 2003
Location: In your thoughts
Posts: 195
|
|
Quote:
Originally posted by The_Duck
sounds good to me.
You need to throw your output into variables.
Try this:
PHP Code:
CREATE OR REPLACE PROCEDURE attendance
( sdate IN DATE,
edate IN DATE,
V_EM_KEY OUT varchar2,
V_EM_DPKEY OUT varchar2,
V_EM_TMKEY OUT varchar2,
V_EM_LAST_NAME OUT varchar2,
V_EM_FIRST_NAME OUT varchar2,
V_ATX_CLOCK_IN OUT varchar2,
V_ATX_CLOCK_OUT OUT varchar2,
V_ATX_DATE OUT varchar2
)
AS
BEGIN
SELECT
EM.EM_KEY,
EM.EM_DPKEY,
EM.EM_TMKEY,
EM.EM_LAST_NAME,
EM.EM_FIRST_NAME,
ATX.ATX_CLOCK_IN,
ATX.ATX_CLOCK_OUT,
ATX.ATX_DATE
INTO
V_EM_KEY,
V_EM_DPKEY,
V_EM_TMKEY,
V_EM_LAST_NAME,
V_EM_FIRST_NAME,
V_ATX_CLOCK_IN,
V_ATX_CLOCK_OUT,
V_ATX_DATE
FROM
ATX,
EM
WHERE
(ATX.ATX_EMKEY = EM.EM_KEY) AND
ATX.ATX_DATE BETWEEN
sdate AND edate
ORDER BY
ATX.ATX_DATE,
EM.EM_LAST_NAME;
END;
/
|
This may sound dumb but how do I call it then? I have tried:
PHP Code:
EXEC attendance('01-JUL-2003','15-JUL-2003')
And I get the following error:
PHP Code:
BEGIN attendance('01-JUL-2003','15-JUL-2003'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'ATTENDANCE'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Thanks, Jeremy
__________________
Nothing better than a good ride.
|
|
07-21-03, 21:03
|
|
Drunkard
|
|
Join Date: Nov 2002
Location: Desk, slightly south of keyboard
Posts: 697
|
|
If you're trying to get the data out to crystal, you need to use a REF CURSOR.
Ie (in a package spec)
TYPE CurType is REF CURSOR;
in a function in the package
PROCEDURE Fred( Parm1 IN DATE, Parm2 IN DATE, curResults OUT CurType ) IS
BEGIn
OPEN curResult FOR SELECT ....
END;
In crystal...
BEGIN
MyPackage.Fred( date1, date2, cursortype );
END;
Google for "package ref cursor" for fuller syntax/details.
Hth
Bill
|
|
07-22-03, 11:37
|
|
Registered User
|
|
Join Date: Jul 2003
Posts: 2,292
|
|
Do what Bill said.
I hate Crystal reports.
I thought Crystal does all this for you at the GUI level as you build the report?
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
|
|
07-22-03, 11:42
|
|
Registered User
|
|
Join Date: Feb 2003
Location: In your thoughts
Posts: 195
|
|
I will but isn't there a way to query the sp we created and get data back instead of that error? Thanks, Jeremy
__________________
Nothing better than a good ride.
|
|
07-22-03, 11:44
|
|
Registered User
|
|
Join Date: Feb 2003
Location: In your thoughts
Posts: 195
|
|
My point here is that I'm learning and I was told that a stored procedure could benefit me in this situation because it allows me to run a query with variables that would be passed when calling the stored procedure. This sp would be ran from an application that I'm writing and it would be easiest if I could do something like:
PHP Code:
EXEC attendance('01-JUL-2003','15-JUL-2003')
and have results returned. Thanks, Jeremy
__________________
Nothing better than a good ride.
|
|
07-22-03, 11:59
|
|
Registered User
|
|
Join Date: Jul 2003
Posts: 2,292
|
|
Quote:
Originally posted by JCScoobyRS
I will but isn't there a way to query the sp we created and get data back instead of that error? Thanks, Jeremy
|
yes there is.
If you use my code you could then do a DBMS_OUTPUT.put line
set serveroutput on
throw this line into your proc after the select statement, like this:
PHP Code:
dbms_output.put_line ('data --> '|| V_EM_KEY||' '||V_EM_DPKEY||' '||V_EM_TMKEY||' '||V_EM_LAST_NAME||' '|| V_EM_FIRST_NAME||' '|| V_ATX_CLOCK_IN||' '|| V_ATX_CLOCK_OUT||' '||V_ATX_DATE);
Otherwise you could add error-handling to your SP.
__________________
- The_Duck
you can lead someone to something but they will never learn anything ...
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Stored Procedure Error
