CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
user1465587
user1465587

Reputation:

unable to run oracle stored procedure

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

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

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

Egor Skriptunoff
Egor Skriptunoff

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

Related Questions