CODEWITHSUNDEEP
oracle-databaseselectstored-proceduresoracle-sqldeveloper
sanrnsam7
sanrnsam7

Reputation: 161

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

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:

  1. 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?

  2. The select query demands an INTO clause (a variable to copy the query result) in SQL developer. Is it mandatory?

  3. 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:

  1. 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.
  2. 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 ! :)

Upvotes: 1

Views: 5372

Answers (2)

RLOG
RLOG

Reputation: 640

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;

Upvotes: 4

Mangesh
Mangesh

Reputation: 1

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.

Upvotes: 0

Related Questions