?
Solved

Stored Procedure not working

Posted on 2010-11-30
19
Medium Priority
?
558 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Technology Partners: 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!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

621 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