| CASE statement in a Stored Procedure [message #19461] |
Wed, 20 March 2002 07:21  |
uma
Messages: 67
Registered: May 2001
|
Member |
|
|
I want to use CASE function in a PROCEDURE but I am getting an error whenever I am compiling the stored procedure and complainting about the CASE. The error as
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
Any Ideas? How to create a PACKAGE if PROCEDURE can't help?
Thanks
Uma
|
|
|
|
|
|
| Re: CASE statement in a Stored Procedure [message #19465 is a reply to message #19461] |
Wed, 20 March 2002 08:57  |
uma
Messages: 67
Registered: May 2001
|
Member |
|
|
I found the way to make my procedure to work. But I am getting this runtime error.
ORA-06539: target of OPEN must be a query
ORA-06512: at UMAMA.PRODCUTTYPE", line 81
ORA-06512: at line 3
My procedure is like this
CREATE OR REPLACE PROCEDURE PRODUCTTYPE
AS
dbms_cursor_id INTEGER;
dbms_cursor_exe INTEGER;
BEGIN
dbms_cursor_id := dbms_sql.open_cursor;
dbms_sql.parse (dbms_cursor_id, 'TRUNCATE TABLE UMAMA.PRODUCTTYPE',
dbms_sql.native);
dbms_cursor_exe := dbms_sql.execute(dbms_cursor_id);
COMMIT;
dbms_cursor_id := dbms_sql.open_cursor;
dbms_sql.parse (dbms_cursor_id, 'ALTER TABLESPACE APP_D COALESCE',
dbms_sql.native);
dbms_cursor_exe := dbms_sql.execute(dbms_cursor_id);
COMMIT;
DECLARE
TYPE c1type IS REF CURSOR;
c1 c1type;
sqlstr VARCHAR2( 10000 );
result VARCHAR2( 10000 );
BEGIN
sqlstr:= 'INSERT INTO UMAMA.PRODUCTTYPE
( SOURCE_SEGMENTATION_ID,
INVOICE_ID,
PYMT_NUMBER,
DATE_DUE_DATE_ID,
INVPS_GROSS_AMT,
INVOICE_PYMT_USA_AMT,
INVOICE_PYMT_ORIG_AMT,
DISCOUNT_AMT_TAKEN,
EXCHANGE_DATE,
EXCHANGE_RATE,
EXCHANGE_RATE_TYPE,
DATE_PAID_DATE_ID,
CHECK_ID,
CHECK_NUMBER,
DM_LOAD_DATE,
DM_LOAD_TIME)
SELECT
13,
IPH.INVOICE_HEADER_ID,
1,
IPH.DATE_DUE,
IPH.AMT_TOTAL_INVOICE,
IPH.AMT_PD * NVL(CEREDR.CURRENCY_XCHNG_RATE,1),
IPH.AMT_PD,
(CASE WHEN (IPH.AMT_DISCOUNT > 0 and IPH.DATE_PAID <= IPH.DATE_DISCOUNT)
then IPH.AMT_DISCOUNT else NULL end),
IPH.DATE_PAID,
NVL(CEREDR.CURRENCY_XCHNG_RATE,1),
NULL,
IPH.DATE_PAID,
IPH.CHECK_NUMBER,
IPH.CHECK_NUMBER,
TRUNC(SYSDATE),
TO_CHAR(SYSDATE)
FROM
APPL.INV_HEADER IPH,
APPL.CURR_RATE_RANGE CEREDR
WHERE
IPH.CURRENCY_USAGE_ID = CEREDR.CURRENCY_USAGE_ID (+) and
IPH.DATE_PAID >= CEREDR.XCHNG_RATE_EFF_START_DATE (+) and
IPH.DATE_PAID <= CEREDR.XCHNG_RATE_EFF_END_DATE (+)';
OPEN c1 FOR sqlstr ;
LOOP
FETCH c1 INTO result;
EXIT WHEN c1%NOTFOUND; (line 81 is this)
DBMS_OUTPUT.PUT_LINE( result );
END LOOP;
CLOSE c1;
END;
COMMIT;
END PRODUCTTYPE;
/
Thanks
Uma
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
