CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle-sqldeveloper
sanrnsam7
sanrnsam7

Reputation: 161

How to write an Oracle procedure with a select statement (Specifically on SQL Developer)?

I want to create a simple Oracle Stored procedure on SQL Developer that will return some records on a simple select query. I do not want to pass in any parameter, but I just want the Records to be returned back from the procedure into a result set -> a suitable variable.

I have been trying to use the following syntax : 

create or replace PROCEDURE Getmarketdetails2(data OUT varchar2)
IS
BEGIN

  SELECT *
  into data
  from  dual;

END Getmarketdetails2;

But it gives me an error while I try to execute with the following exec statement --> 

Declare a Varchar2;
exec Getmarketdetails2(a);

Error: PLS-00103: Encountered the symbol "end-of-file" when expecting "something else".

Cause: Usually a PL/SQL compilation error.

Appreciate if anyone can help me out of this long pending situation! I have tried enough to find a basic guide to create a simple Oracle stored procedure and execute it in SQL Developer, but none of them answer to the point!!

Upvotes: 1

Views: 21617

Answers (5)

Dineshkumar Achari
Dineshkumar Achari

Reputation: 1

Please Try this execute one by one statement is working on my end.

create or replace procedure sp_test(c1 out SYS_REFCURSOR)    
as    
begin  
open c1 for  
select * from user_details;  
end sp_test; 

SET SERVEROUTPUT ON;  
variable mycursor refcursor;  
exec sp_test (:mycursor);  
print mycursor;

output:

 USER_ID   USER_NAME                                         
--------- ----------
       1   anita                                             
       2   dinesh                                            
       3   sagar

Upvotes: 0

Mitch3091
Mitch3091

Reputation: 4688

This is how I return a cursor in Oracle

PROCEDURE GetAllData (P_CURSOR OUT SYS_REFCURSOR)
IS
BEGIN

  OPEN P_CURSOR FOR
    SELECT *
    FROM TABLE ;

END GetAllData ;

Upvotes: 1

Mangesh
Mangesh

Reputation: 1

Declare a Varchar2; 
exec Getmarketdetails2(a);

Your procedure is ok; Instead of above query, use below query to run sp:

Declare 
  a Varchar2(10); 
Begin
  Getmarketdetails2(a);
End;

Upvotes: 0

user330315
user330315

Reputation:

Assuming an up-to-date Oracle version, you can use dbms_sql.return_result()

create or replace PROCEDURE Getmarketdetails2
IS
  c1 SYS_REFCURSOR;  
BEGIN

  OPEN c1 FOR 
  SELECT *
  from  dual;
  DBMS_SQL.RETURN_RESULT(c1);

END Getmarketdetails2;
/

Then simply run 

exec Getmarketdetails2

The only drawback is that SQL Developer only displays the result as text, not as a proper result grid.

Upvotes: 1

MT0
MT0

Reputation: 167991

You want:

DECLARE
  a VARCHAR2(4000);          -- Give it a size
BEGIN                        -- Begin the anonymous PL/SQL block
  Getmarketdetails2(a);      -- Call the procedure
  DBMS_OUTPUT.PUT_LINE( a ); -- Output the value
END;                         -- End the anonymous PL/SQL block
/                            -- End the PL/SQL statement

or:

VARIABLE a VARCHAR2(4000);   -- Create a bind variable
EXEC Getmarketdetails2(:a);  -- Execute the procedure using the bind variable
PRINT a                      -- Print the bind variable

Upvotes: 2

Related Questions