• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2844
  • Last Modified:

Stored Procedure - Status Invalid

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
cdevx
Asked:
cdevx
  • 2
  • 2
  • 2
1 Solution
 
venkotchCommented:
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
 
dash420Commented:
CREATE OR REPLACE PROCEDURE procClientGet
AS
BEGIN
  SELECT * FROM CLIENT;
END;
0
 
venkotchCommented:
Well, dash420 is right that "IS" or "AS" also is missing after "create or replace procedure procClientGet" statement
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
cdevxAuthor Commented:
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
 
dash420Commented:
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
 
cdevxAuthor Commented:
Worked.  Did not know I had to create a recordset in the proc to be returned.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now