Solved

Stored Procedure not working

Posted on 2010-11-30
19
551 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 142

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 142

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 142

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 142

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 142

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 142

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 142

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 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best RAID for a BDD Oracle 4 69
Read only access to a Procedure in oracle? 4 67
sort a spool into file output in oracle 1 30
oracle- set role and grant privileges 6 29
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

821 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