Solved

Stored Procedure not working

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

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
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: 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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SSN Format in Oracle 2 60
Oracle Database Upgrade 13 41
Oracle SQL Select within a Where Clause 9 42
null value 15 67
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…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now