?
Solved

Stored Procedure -  Status Invalid

Posted on 2003-03-25
6
Medium Priority
?
2,818 Views
Last Modified: 2012-06-27
Using Enterprise Manager or TOAD to Create Stored Procedure.
CREATE OR REPLACE PROCEDURE procClientGet
BEGIN
   SELECT * FROM CLIENT;
END;

Procedure has invalid status.  No errors when viewing procedure in Enterprise Manager

Oracle 8.1.7 Enterprise Edition (WIN NT 4.0)
* TableSpace Default: USERS, Temporary: TEMP
* USER: DEVELOPER, Roles (DBA, CONNECT, RESOURCE)
* System Priviledges (..., CREATE PROCEDURE, ...)
* Object Priviledges: None Selected.
* Consumer Group: None Selected.


Note: I am running this Oracle database inside a virtual server (NT 4.0) using VMWare.

Is this a permission issue?
Tried; Grant create procedure to developer.

Help!  
0
Comment
Question by:cdevx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 6

Accepted Solution

by:
venkotch earned 100 total points
ID: 8207059
You cannot create procedure like this. You use PL/SLQ engine to compile the procedure, but actually you have a plain SQL statement in there. You should have a variable where to fetch the record set from your select statement and some more stuff to handle errors, etc. Should look like this:

CREATE OR REPLACE PROCEDURE procClientGet
  v_client CLIENT%rowtype
BEGIN
  SELECT *
    into v_client
    FROM CLIENT;

  Exception
    When No_Data_Found then
      -- code here to process the exception
    When Too_Many_Rows
      -- I'm not sure this exception has exactly this name
    When Others then
      -- code here to process the exception
END;
/

Of course you probably need a cursor to do the processing:

CREATE OR REPLACE PROCEDURE procClientGet
BEGIN
  for v_client in (
      SELECT *
        FROM CLIENT) loop
      -- do the processing here
      -- referencing the cursor columns like this:
      -- v_client.field_name
  end loop;
END;

Venko.
0
 
LVL 6

Expert Comment

by:dash420
ID: 8207893
CREATE OR REPLACE PROCEDURE procClientGet
AS
BEGIN
  SELECT * FROM CLIENT;
END;
0
 
LVL 6

Expert Comment

by:venkotch
ID: 8209948
Well, dash420 is right that "IS" or "AS" also is missing after "create or replace procedure procClientGet" statement
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 1

Author Comment

by:cdevx
ID: 8210480
dash420, Status still invalid when using AS or IS.
If I use
CREATE OR REPLACE PROCEDURE "procClientGet" AS
BEGIN
    SELECT * FROM CLIENT;
END;
Compiles fine in TOAD or Enterprise Manager Create Proc Wizard.  But Status still invalid!

-- Gave SCOTT DBA rights,
Connected as SCOTT and got the same status invalid for same
type of procedure.

This should not be this complicated.  Simple and easy in SQL Server.

SELECT * FROM CLIENTS.
0
 
LVL 6

Expert Comment

by:dash420
ID: 8210582
I got your problem

CREATE OR REPLACE PROCEDURE "procClientGet" AS
BEGIN
   --SELECT * FROM CLIENT;
   /* we cann't write this way as it will expect INTO
      PLS-00428: an INTO clause is expected in this SELECT statement */
END;

u can do this way

CREATE OR REPLACE PROCEDURE procClientGet AS
   x   integer;
   y   varchar2(100);
BEGIN
   SELECT project_id, project_name into x, y  FROM ptt_project;
END;

note x and y should be same type as the column.


0
 
LVL 1

Author Comment

by:cdevx
ID: 8211100
Worked.  Did not know I had to create a recordset in the proc to be returned.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question