Solved

Stored Procedure not working

Posted on 2010-11-30
19
552 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
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 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
Oracle - SQL Query with Function 3 53
Oracle encryption 12 48
oracle forms question 22 42
subtr returning incorrect value 8 33
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
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 shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

830 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