Databases
StackOverflow2013
Postsdbo
POunable to run oracle stored procedure
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POunable to run oracle stored procedure
    primarykey
    data
    text
    <p>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:</p> <pre><code>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 </code></pre> <p>My code is given below:</p> <pre><code>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; </code></pre> <p>I am executing procedure using following command:</p> <pre><code> variable z refcursor; LOG_DETAIL(1,20,null,'All',null,null,0,z); </code></pre> <p>After Executing following query:</p> <pre><code>select * from all_errors where name='LOG_DETAIL' order by sequence </code></pre> <p>I got following Result:</p> <pre><code>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 </code></pre> <p>Please help me in solving this problem.</p> <p><strong>Edit:</strong> Stack trace is added.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POunable to run oracle stored procedure
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POunable to run oracle stored procedure
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COAre you sure you created the procedure under the user SAURAV? Check with: `select object_name, owner from all_objects where object_name = 'LOG_DETAIL'`. If yes, then please post your execute statement
      singulars
      1. POunable to run oracle stored procedure
      1. USJWK
    2. COyes and I am trying to run it via sql developer ide provided by oracle under same connection
      singulars
      1. POunable to run oracle stored procedure
      1. This table or related slice is empty.
    3. CO@JWK when I fire your query froom command line I got following result: OBJECT_NAME OWNER ------------------------------ ------------------------------ LOG_DETAIL SAURAV
      singulars
      1. POunable to run oracle stored procedure
      1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload