Reputation:
I am new to oracle and write a complex stored procedure to select different data on the basis of different conditions. I am not able to run my stored procedure, it gives following error:
ORA-06550: line2, column 3:
PLS-00905: object SAURAV.LOG_DETAIL is invalid
ORA-06550: line 2, column 3:
PL/SQL: Statement Ignored
ORA-06512: at line 58
My code is given below:
CREATE OR REPLACE
PROCEDURE LOG_DETAIL(
startIndex IN INT
,pageSize IN INT
,branchId IN varchar2
,customerId IN varchar2
,fromDate IN DATE
,toDate IN DATE
,withDate IN INT
,p_cursor OUT sys_refcursor
) AS
BEGIN
IF withDate = 1 then
IF branchId = NULL then
IF customerId = 'All' then
OPEN p_cursor
FOR SELECT * FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY id
) AS MyRows
,log_info.branch_id
,branch_name
,customer_id
,in_time
,out_time
,date_time
,ip
,(
SELECT COUNT(id)
FROM log_info
WHERE date_time BETWEEN fromDate || '%' AND toDate || '%'
) AS RowNumber FROM log_info,branch_info
WHERE date_time BETWEEN fromDate || '%' AND toDate || '%'
AND branch_info.branch_id = log_info.branch_id
)
WHERE MyRows BETWEEN startIndex AND startIndex + pageSize - 1;
ELSE
OPEN p_cursor
FOR SELECT * FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY id
) AS MyRows
,log_info.branch_id
,branch_name
,customer_id
,in_time
,out_time
,date_time
,ip
,(
SELECT COUNT(id)
FROM log_info
WHERE customer_id = customerId
AND date_time BETWEEN fromDate || '%'
AND toDate || '%'
) AS RowNumber
FROM log_info,branch_info
WHERE customer_id = customerId
AND date_time BETWEEN fromDate || '%'
AND toDate || '%'
AND log_info.branch_id = branch_info.branch_id
)
WHERE MyRows BETWEEN startIndex
AND startIndex + pageSize - 1;
END IF;
ELSE
IF customerId = 'All' then
OPEN p_cursor
FOR SELECT * FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY id
) AS MyRows
,log_info.branch_id
,branch_name
,customer_id
,in_time
,out_time
,date_time
,ip
,(
SELECT COUNT(id)
FROM log_info
WHERE branch_id = branchId
AND date_time BETWEEN fromDate || '%'
AND toDate || '%'
) AS RowNumber
FROM log_info
,branch_info
WHERE log_info.branch_id = branchId
AND date_time BETWEEN fromDate || '%'
AND toDate || '%'
AND branch_info.branch_id = log_info.branch_id
)
WHERE MyRows BETWEEN startIndex
AND startIndex + pageSize - 1;
ELSE
OPEN p_cursor
FOR SELECT * FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY id
) AS MyRows
,log_info.branch_id
,branch_name
,customer_id
,in_time
,out_time
,date_time
,ip
,(
SELECT COUNT(id)
FROM log_info
WHERE branch_id = branchId
AND customer_id = customerId
AND date_time BETWEEN fromDate || '%'
AND toDate || '%'
) AS RowNumber
FROM log_info
,branch_info
WHERE log_info.branch_id = branchId
AND customer_id = customerId
AND date_time BETWEEN fromDate || '%'
AND toDate || '%'
AND log_info.branch_id = branch_info.branch_id
)
WHERE MyRows BETWEEN startIndex
AND startIndex + pageSize - 1;
END IF ;
END IF ;
ELSE
IF customerId = 'All' then
IF branchId = NULL then
OPEN p_cursor
FOR SELECT * FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY id
) AS MyRows
,log_info.branch_id
,branch_name
,customer_id
,in_time
,out_time
,date_time
,ip
,(
SELECT COUNT(id)
FROM log_info
) AS RowNumber
FROM log_info
,branch_info
WHERE branch_ingo.branch_id = log_info.branch_id
)
WHERE MyRows BETWEEN startIndex
AND startIndex + pageSize - 1;
ELSE
OPEN p_cursor
FOR SELECT * FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY id
) AS MyRows
,log_info.branch_id
,branch_name
,customer_id
,in_time
,out_time
,date_time
,ip
,(
SELECT COUNT(id)
FROM log_info
WHERE branch_id = branchId
) AS RowNumber
FROM log_info
,branch_info
WHERE log_info.branch_id = branchId
AND log_info.branch_id = branch_info.branch_id
)
WHERE MyRows BETWEEN startIndex
AND startIndex + pageSize - 1;
END IF ;
ELSE
IF branchId = NULL then
OPEN p_cursor
FOR SELECT * FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY id
) AS MyRows
,log_info.branch_id
,branch_name
,customer_id
,in_time
,out_time
,date_time
,ip
,(
SELECT COUNT(id)
FROM log_info
WHERE customer_id = customerId
) AS RowNumber
FROM log_info
,branch_info
WHERE customer_id = customerId
AND branch_info.branch_id = log_info.branch_id
)
WHERE MyRows BETWEEN startIndex
AND startIndex + pageSize - 1;
ELSE
OPEN p_cursor
FOR SELECT * FROM (
SELECT ROW_NUMBER() OVER (
ORDER BY id
) AS MyRows
,log_info.branch_id
,branch_name
,customer_id
,in_time
,out_time
,date_time
,ip
,(
SELECT COUNT(id)
FROM log_info
WHERE customer_id = customerId
AND branch_id = branchId
) AS RowNumber
FROM log_info
,branch_info
WHERE customer_id = customerId
AND log_info.branch_id = branchId
AND log_info.branch_id = branch_info.branch_id
)
WHERE MyRows BETWEEN startIndex
AND startIndex || pageSize - 1;
END IF ;
END IF ;
END IF ;
END LOG_DETAIL;
I am executing procedure using following command:
variable z refcursor;
LOG_DETAIL(1,20,null,'All',null,null,0,z);
After Executing following query:
select * from all_errors where name='LOG_DETAIL' order by sequence
I got following Result:
OWNER NAME TYPE SEQUENCE LINE POSITION TEXT ATTRIBUTE MESSAGE_NUMBER
SAURAV LOG_DETAIL PROCEDURE 1 30 49 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 2 30 57 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 3 30 29 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 4 30 39 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 5 32 47 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 6 32 55 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 7 32 27 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 8 32 37 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 9 54 11 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 10 54 19 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 11 53 28 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 12 53 38 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 13 59 9 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 14 59 17 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 15 58 26 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 16 58 36 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 17 84 10 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 18 84 18 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 19 83 27 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
SAURAV LOG_DETAIL PROCEDURE 20 83 37 PLW-07204: conversion away from column type may result in sub-optimal query plan WARNING 7204
Please help me in solving this problem.
Edit: Stack trace is added.
Upvotes: 1
Views: 967
Reputation: 50077
At least one problem is the lines which read
ELSE
IF customerId = 'All' then
This should be
ELSIF customerId = 'All' then
I didn't go through to see if there are any more occurrences of ELSE IF
in this code. If there are, correct them and recompile.
Share and enjoy.
Upvotes: 1
Reputation: 23767
Warning messages are caused by strings containing such expressions:
date_time BETWEEN fromDate || '%' AND toDate || '%'
Why appending percent signs? Try to remove them and to recreate your procedure to make it valid.
Upvotes: 1