Create and execute an Oracle Stored Procedure for a select query in SQL Developer

Question

I am using Oracle SQL Developer with Oracle 11g.

I face a strange issue creating a simple stored procedure for a Select query that doesn't need any input parameters as such. It just selects from a user defined function from the "dual" table.

These are the issues I face:

I am not able to create a procedure with no input parameters (because I don't need to use any parameter value in the select!). But the syntax does not allow me to have zero parameters, it demands a REF_CURSOR out parameter. Is the REF_CURSOR a compulsory thing in SQL Developer procedures? Is it anything to do with procedures involving a Select query?
The select query demands an INTO clause (a variable to copy the query result) in SQL developer. Is it mandatory?
Even if I used an INTO clause, I can't figure out the syntax to declare a temporary variable to copy the query result into this variable. So that I can use this out variable in my program snippet.

This is my procedure block:

Create or Replace PROCEDURE Getmarketdetails
AS
DECLARE temp varchar;
BEGIN
  SELECT *
  INTO temp from  dual;
END Getmarketdetails;

I get these errors on compiling the procedure:

PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: begin function pragma procedure subtype type current cursor delete exists prior external language The symbol "begin" was substituted for "DECLARE" to continue.
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge .

All I need is the perfect script syntax to create the stored procedure for this and also execute it using the exec command. And some clarifications to questions raised above. Appreciate if someone can oblige ! :)

if it's a simple select why do you need a procedure? also you stire the result in a variable but don't do anything with it, it doesn't make sense. — Cyrille MODIANO
– Cyrille MODIANO, Commented Sep 27, 2017 at 14:02
I need to run this in an Nhibernate C# code. My actual query involves selecting from a Function from dual. Something like "Select <function()> from dual" !! This was proving hard in a direct approach in Nhibernate, hence the Stored proc approach! — sanrnsam7
– sanrnsam7, Commented Sep 27, 2017 at 14:13
Regarding the unused variable, it is just to check the syntax. I just need the basic syntax!! Hence used a simple query for clarifications! — sanrnsam7
– sanrnsam7, Commented Sep 27, 2017 at 14:16

sanrnsam7 · Accepted Answer · 2017-09-28 06:49:22Z

4

Your syntax is incorrect - you need to declare a length for your varchar and you don't need the declare.

Create or Replace PROCEDURE Getmarketdetails
AS
temp varchar(100);
BEGIN
  SELECT *
  INTO temp from  dual;
END Getmarketdetails;

edited Sep 28, 2017 at 6:49

sanrnsam7

3011 gold badge5 silver badges12 bronze badges

answered Sep 27, 2017 at 14:32

RLOG

6406 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Boneist · Accepted Answer · 2017-09-27 14:40:02Z

0

Create or Replace PROCEDURE Getmarketdetails 
AS 
  temp varchar2(20);
BEGIN 
  SELECT 'stack overflow' INTO temp from dual;
  Dbms_output.put_line(temp);
END Getmarketdetails;

Some modification done in your procedure. Don't write declare and mention variables as per your need.

edited Sep 27, 2017 at 14:40

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

answered Sep 27, 2017 at 14:32

Mangesh

12 bronze badges

Collectives™ on Stack Overflow

Create and execute an Oracle Stored Procedure for a select query in SQL Developer

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related