Solved

Stored Procedure not working

Posted on 2010-11-30
19
553 Views
Last Modified: 2012-05-10
This stored procedure is not working

create or replace procedure prod(dt date, p_MEMBER_ID number, p_EMPLOYEE_ID number, p_CLUB_ID number)
res number;
begin
select id into re into
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (trunc(EFFECTIVEDATEFROM) <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID);

--if you want only one of the IDs, just make the others NULL in the calling sql
end prod;


error:
ERROR line 2, col 1, ending_line 2, ending_col 3, Found 'res', Expecting: AS  IS   -or-   AUTHID  
DETERMINISTIC  PARALLEL_ENABLE
Also, I want pass sysdate as input parameter

0
Comment
Question by:GRChandrashekar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
19 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34237779
please try this:
create or replace procedure prod(dt date, p_MEMBER_ID number, p_EMPLOYEE_ID number, p_CLUB_ID number)
returns number
as
declare re number;
begin
select id 
  into re 
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (trunc(EFFECTIVEDATEFROM) <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID);

--if you want only one of the IDs, just make the others NULL in the calling sql
return re
end prod;

Open in new window

0
 

Author Comment

by:GRChandrashekar
ID: 34237798
error
ERROR line 2, col 1, ending_line 2, ending_col 7, Found 'returns', Expecting: AS  IS   -or-   AUTHID  DETERMINISTIC  PARALLEL_ENABLE
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34237807
sorry, this is a procedure... so no return:
create or replace procedure prod(dt date, p_MEMBER_ID number, p_EMPLOYEE_ID number, p_CLUB_ID number)
as
declare re number;
begin
select id 
  into re 
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (trunc(EFFECTIVEDATEFROM) <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID);

--if you want only one of the IDs, just make the others NULL in the calling sql
end;

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GRChandrashekar
ID: 34237818
ERROR line 3, col 12, ending_line 3, ending_col 17, Found 'number', Expecting: ;   -or-   ..  :=  DEFAULT  NOT  NULL   -or-   %   -or-   (  .  @
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34237826
i'm working too much with MS SQL server :)
create or replace procedure prod(dt date, p_MEMBER_ID number, p_EMPLOYEE_ID number, p_CLUB_ID number)
as
  re number;
begin
select id 
  into re 
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (trunc(EFFECTIVEDATEFROM) <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID);

--if you want only one of the IDs, just make the others NULL in the calling sql
end;

Open in new window

0
 

Author Comment

by:GRChandrashekar
ID: 34237827
Here is the query which I want to convert to stored proc
select *
from BLOCKMEMBER
where
(
trunc(sysdate) between EFFECTIVEDATEFROM and EFFECTIVEDATETO
or (trunc(EFFECTIVEDATEFROM) <= trunc(sysdate) and EFFECTIVEDATETO is null)
)
and (p_MEMBER_ID is null or p_MEMBER_ID = MEMBER_ID )
and (p_EMPLOYEE_ID is null or p_EMPLOYEE_ID = EMPLOYEE_ID)
and (p_CLUB_ID is null or p_CLUB_ID = CLUB_ID)

with input sysdate, member_id, employee_id, club_id
0
 

Author Comment

by:GRChandrashekar
ID: 34237923
STILL NOT WORKING

PL/SQL: ORA-00904: "ID": invalid identifier
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34237943
note: a query cannot run in a procedure to return data, UNLESS the procedure returns an output ref cursor.
it's not working like in mssql where a procedure simply can return a dataset !

so, please review the functional limitations and requirements ...
0
 

Author Comment

by:GRChandrashekar
ID: 34237983
This is what I need actually. but it says not enough values
/* Formatted on 30/11/2010 05:35:54 PM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE BCLUB1868.SP_BLOCKMEMBERDATA (
   DT                      DATE,
   P_MEMBER_ID             NUMBER,
   P_USERID                VARCHAR2,
   P_EMPLOYEE_ID           NUMBER,
   P_CORPORATE_ID          NUMBER,
   P_AFFILIATEMEMBER_ID    NUMBER,
   P_AFFILIATECLUB_ID      NUMBER
)
AS
   BLOCKDATA   NUMBER;
BEGIN
   SELECT   BLOCKMEMBER.BLOCKMEMBER_ID, REASON.DESCRIPTION
     INTO   BLOCKDATA
     FROM   BLOCKMEMBER, REASON
    WHERE   (REASON.REASON_ID = BLOCKMEMBER.REASON_ID(+))
            AND (TRUNC (SYSDATE) BETWEEN EFFECTIVEDATEFROM
                                     AND  EFFECTIVEDATETO
                 OR (TRUNC (EFFECTIVEDATEFROM) <= TRUNC (SYSDATE)
                     AND EFFECTIVEDATETO IS NULL))
            AND (P_MEMBER_ID IS NULL OR P_MEMBER_ID = MEMBER_ID)
            AND (P_USERID IS NULL OR P_USERID = USERID)
            AND (P_EMPLOYEE_ID IS NULL OR P_EMPLOYEE_ID = EMPLOYEE_ID)
            AND (P_CORPORATE_ID IS NULL OR P_CORPORATE_ID = CORPORATE_ID)
            AND (P_AFFILIATEMEMBER_ID IS NULL
                 OR P_AFFILIATEMEMBER_ID = AFFILIATEMEMBER_ID)
            AND (P_AFFILIATECLUB_ID IS NULL
                 OR P_AFFILIATECLUB_ID = AFFILIATECLUB_ID);
--IF YOU WANT ONLY ONE OF THE IDS, JUST MAKE THE OTHERS NULL IN THE CALLING SQL
END;
/

Open in new window

0
 

Author Comment

by:GRChandrashekar
ID: 34237986
also sysdate should take parameter dt
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34238091
SELECT   BLOCKMEMBER.BLOCKMEMBER_ID, REASON.DESCRIPTION
     INTO   BLOCKDATA

Open in new window


should be obvious. you have 2 columns in the SELECT, but only 1 in INTO clause.

>also sysdate should take parameter dt
can you please clarify? I presume you want to take sysdate as default, or don't want a parameter or ,...
0
 

Author Comment

by:GRChandrashekar
ID: 34238166
i dont want sysdate as default, I will pass the date as input. Would be of great help if you can give me the full procedure once again as per requirement
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34238182
so, you want to replace sysdate by dt in the query???

/* Formatted on 30/11/2010 05:35:54 PM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE BCLUB1868.SP_BLOCKMEMBERDATA (
   DT                      DATE,
   P_MEMBER_ID             NUMBER,
   P_USERID                VARCHAR2,
   P_EMPLOYEE_ID           NUMBER,
   P_CORPORATE_ID          NUMBER,
   P_AFFILIATEMEMBER_ID    NUMBER,
   P_AFFILIATECLUB_ID      NUMBER
)
AS
   BLOCKDATA   NUMBER;
BEGIN
   SELECT   BLOCKMEMBER.BLOCKMEMBER_ID, REASON.DESCRIPTION
     INTO   BLOCKDATA
     FROM   BLOCKMEMBER, REASON
    WHERE   (REASON.REASON_ID = BLOCKMEMBER.REASON_ID(+))
            AND (TRUNC (DT  ) BETWEEN EFFECTIVEDATEFROM
                                     AND  EFFECTIVEDATETO
                 OR (TRUNC (EFFECTIVEDATEFROM) <= TRUNC (DT)
                     AND EFFECTIVEDATETO IS NULL))
            AND (P_MEMBER_ID IS NULL OR P_MEMBER_ID = MEMBER_ID)
            AND (P_USERID IS NULL OR P_USERID = USERID)
            AND (P_EMPLOYEE_ID IS NULL OR P_EMPLOYEE_ID = EMPLOYEE_ID)
            AND (P_CORPORATE_ID IS NULL OR P_CORPORATE_ID = CORPORATE_ID)
            AND (P_AFFILIATEMEMBER_ID IS NULL
                 OR P_AFFILIATEMEMBER_ID = AFFILIATEMEMBER_ID)
            AND (P_AFFILIATECLUB_ID IS NULL
                 OR P_AFFILIATECLUB_ID = AFFILIATECLUB_ID);
--IF YOU WANT ONLY ONE OF THE IDS, JUST MAKE THE OTHERS NULL IN THE CALLING SQL
END;
/

Open in new window

0
 

Author Comment

by:GRChandrashekar
ID: 34238192
I am getting not enough values
and cursor hilighted  in  FROM   BLOCKMEMBER, REASON
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34238237
I repeat my about comment:

you have:
SELECT   BLOCKMEMBER.BLOCKMEMBER_ID, REASON.DESCRIPTION
     INTO   BLOCKDATA 

Open in new window

so, you query for 2 columns, but put only 1 destination variable.

either, you put a second destination variable, or, if you want to concatenate, for example:
SELECT   BLOCKMEMBER.BLOCKMEMBER_ID || ':' || REASON.DESCRIPTION
     INTO   BLOCKDATA

Open in new window

0
 

Author Comment

by:GRChandrashekar
ID: 34238311
how to put a second destination variable
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34238327

/* Formatted on 30/11/2010 05:35:54 PM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE BCLUB1868.SP_BLOCKMEMBERDATA (
   DT                      DATE,
   P_MEMBER_ID             NUMBER,
   P_USERID                VARCHAR2,
   P_EMPLOYEE_ID           NUMBER,
   P_CORPORATE_ID          NUMBER,
   P_AFFILIATEMEMBER_ID    NUMBER,
   P_AFFILIATECLUB_ID      NUMBER
)
AS
   BLOCKDATA   VARCHAR2(1000);
 , BLOCKID NUMBER   
BEGIN
   SELECT   BLOCKMEMBER.BLOCKMEMBER_ID, REASON.DESCRIPTION
     INTO   BLOCKID , BLOCKDATA
     FROM   BLOCKMEMBER, REASON
    WHERE   (REASON.REASON_ID = BLOCKMEMBER.REASON_ID(+))
            AND (TRUNC (DT  ) BETWEEN EFFECTIVEDATEFROM
                                     AND  EFFECTIVEDATETO
                 OR (TRUNC (EFFECTIVEDATEFROM) <= TRUNC (DT)
                     AND EFFECTIVEDATETO IS NULL))
            AND (P_MEMBER_ID IS NULL OR P_MEMBER_ID = MEMBER_ID)
            AND (P_USERID IS NULL OR P_USERID = USERID)
            AND (P_EMPLOYEE_ID IS NULL OR P_EMPLOYEE_ID = EMPLOYEE_ID)
            AND (P_CORPORATE_ID IS NULL OR P_CORPORATE_ID = CORPORATE_ID)
            AND (P_AFFILIATEMEMBER_ID IS NULL
                 OR P_AFFILIATEMEMBER_ID = AFFILIATEMEMBER_ID)
            AND (P_AFFILIATECLUB_ID IS NULL
                 OR P_AFFILIATECLUB_ID = AFFILIATECLUB_ID);
--IF YOU WANT ONLY ONE OF THE IDS, JUST MAKE THE OTHERS NULL IN THE CALLING SQL
END;
/

Open in new window

0
 

Author Comment

by:GRChandrashekar
ID: 34238339
ERROR line 12, col 2, ending_line 12, ending_col 2, Found ',', Invalid identifier
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 34238424

/* Formatted on 30/11/2010 05:35:54 PM (QP5 v5.114.809.3010) */
CREATE OR REPLACE PROCEDURE BCLUB1868.SP_BLOCKMEMBERDATA (
   DT                      DATE,
   P_MEMBER_ID             NUMBER,
   P_USERID                VARCHAR2,
   P_EMPLOYEE_ID           NUMBER,
   P_CORPORATE_ID          NUMBER,
   P_AFFILIATEMEMBER_ID    NUMBER,
   P_AFFILIATECLUB_ID      NUMBER
)
AS
   BLOCKDATA   VARCHAR2(1000);
   BLOCKID NUMBER;
BEGIN
   SELECT   BLOCKMEMBER.BLOCKMEMBER_ID, REASON.DESCRIPTION
     INTO   BLOCKID , BLOCKDATA
     FROM   BLOCKMEMBER, REASON
    WHERE   (REASON.REASON_ID = BLOCKMEMBER.REASON_ID(+))
            AND (TRUNC (DT  ) BETWEEN EFFECTIVEDATEFROM
                                     AND  EFFECTIVEDATETO
                 OR (TRUNC (EFFECTIVEDATEFROM) <= TRUNC (DT)
                     AND EFFECTIVEDATETO IS NULL))
            AND (P_MEMBER_ID IS NULL OR P_MEMBER_ID = MEMBER_ID)
            AND (P_USERID IS NULL OR P_USERID = USERID)
            AND (P_EMPLOYEE_ID IS NULL OR P_EMPLOYEE_ID = EMPLOYEE_ID)
            AND (P_CORPORATE_ID IS NULL OR P_CORPORATE_ID = CORPORATE_ID)
            AND (P_AFFILIATEMEMBER_ID IS NULL
                 OR P_AFFILIATEMEMBER_ID = AFFILIATEMEMBER_ID)
            AND (P_AFFILIATECLUB_ID IS NULL
                 OR P_AFFILIATECLUB_ID = AFFILIATECLUB_ID);
--IF YOU WANT ONLY ONE OF THE IDS, JUST MAKE THE OTHERS NULL IN THE CALLING SQL
END;
/

Open in new window

0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

737 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