2

I have written a Stored proc which calculates size of a table.

create or replace PROCEDURE RETRIEVE_TABLE_SIZE (
p_segment_type in VARCHAR2, 
p_segment_name in VARCHAR2, 
P_table_size out INTEGER )

AS

BEGIN
SELECT bytes/(1048576*1024) as GB FROM DBA_SEGMENTS WHERE SEGMENT_NAME = p_segment_type AND         SEGMENT_TYPE = p_segment_name ;
    END ;

I am calling this Stored proc from my java class and my code is below

String sqlQuery = "{call RETRIEVE_TABLE_SIZE(?,?,?)}";
CallableStatement callableStatement = connection.prepareCall(sqlQuery);
callableStatement.setString("p_segment_type","TABLE");
callableStatement.setString("p_segment_name","SIM_HEADER");
callableStatement.registerOutParameter("P_table_size",java.sql.Types.INTEGER);
callableStatement.executeUpdate();
Integer size = callableStatement.getInt(1);
System.out.println("size is: "+size);

But I am getting error given below:

Exception in thread "main" java.sql.SQLException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'RETRIEVE_TABLE_SIZE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

This is first time I am writing Stored Proc. Please rectify me if I am doing something wrong.

Vaibhav
  • 3,035
  • 7
  • 24
  • 27
  • As a general note, after back and forth discussion I strongly believe this question now duplicates [this StackOverflow question](http://stackoverflow.com/questions/968857/dba-jobs-running-table-or-view-does-not-exist-when-trying-to-access-from-proced) which exhibits the same problems. – ProgrammerDan Mar 05 '14 at 21:36

1 Answers1

0

A few notes, first check out this stackoverflow question that is essentially the same. Basically, from sqldeveloper command console you will be able to run the query, but the owner of the procedure probably doesn't have the proper permissions, or they are via a Role and not direct.

Second, from the Oracle documentation on the topic, you need to indicate within your stored procedure the value of the output parameter P_table_size, like so:

SELECT bytes/(1048576*1024) INTO P_table_size FROM DBA_SEGMENTS WHERE SEGMENT_NAME = p_segment_name AND SEGMENT_TYPE = p_segment_type ;

Also note that your use of p_segment_name and p_segment_type are reversed; you had SEGMENT_NAME = p_segment_type and SEGMENT_TYPE = p_segment_name which will cause no end of confusion when you resolve the permissions problem and get your query running.

One final note, although this is speculative: you may be able to use FROM USER_SEGMENTS instead of FROM DBA_SEGMENTS. According to the docs it has the same layout and data, but only returns data for the current user, which might bypass the permissions problem.

Community
  • 1
  • 1
ProgrammerDan
  • 871
  • 7
  • 17
  • After doing what you said above, I got syntax error : Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00911: invalid character – Vaibhav Mar 05 '14 at 20:54
  • Ah, my comment applied to your earlier version, not to the new edited version where you have `(???)` as the call param list. I'll look at your new exception and see if I can take a stab at it. – ProgrammerDan Mar 05 '14 at 20:56
  • The exception you are getting indicates it is not correct. My Oracle is very rusty, however, trying to see if I can find a better way for you write it. – ProgrammerDan Mar 05 '14 at 21:02
  • Try out this new revision. Make sure you issue your procedure create statement in the database before running your application code. – ProgrammerDan Mar 05 '14 at 21:09
  • DBA_SEGMENT is not a table but like a keyword use to calculate table size. Actually initially I executed "SELECT bytes/(1048576*1024) as GB FROM DBA_SEGMENTS WHERE SEGMENT_NAME = p_segment_type AND SEGMENT_TYPE = p_segment_name ;" as a sql query and it worked. But as part of stored proc , it is not working. – Vaibhav Mar 05 '14 at 21:20
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/49097/discussion-between-programmerdan-and-vaibhav) – ProgrammerDan Mar 05 '14 at 21:21