Creating Oracle PL/SQL Stored procedure

Question

I'm trying to convert the SQL Query to Oracle PL/SQL stored procedure.

Here is the query:

select * from table1 where DATE = " + s1 + " and TYPE='" + ty + "' and  NAME='"+nm+"' Order by TYPE DEsc;

Here is the Stored Procedure:

CREATE PROCEDURE procedure1
    ( 
    s1 IN DATE,
    ty IN CHAR DEFAULT 2,
    nm IN VARCHAR2 DEFAULT 64
    )
IS 
    d table1.DATE%TYPE;
    C table1.TYPE%TYPE;
    S table1.NAME%TYPE;
    CURSOR tb IS select DATE,TYPE,NAME INTO d,c,s from table1;
BEGIN
    FOR i IN tb
    LOOP 
        DBMS_OUTPUT.PUT_LINE('DATE' ||i.DATE);
        DBMS_OUTPUT.PUT_LINE('TYPE' ||i.TYPE);
        DBMS_OUTPUT.PUT_LINE('NAME' ||i.NAME);
    END LOOP;

END procedure1;

I do not see any output after Executing Stored procedure. I'm not sure if I have created the stored procedure correctly.

If you're using sqldeveloper and you highlight the text given and execute it, you should get an ORA-936. If you're using sqlplus, then when you press [Enter] nothing will have been executed because sqlplus does something special when it sees create procedure. It won't execute anything until it sees the / character by itself on a line. Welcome to SO. — Jeff Holt
– Jeff Holt, Commented Jul 16, 2017 at 19:10

APC · Accepted Answer · 2017-07-17 06:48:37Z

"I do not see any output after Executing Stored procedure"

Your "output" is DBMS_OUTPUT which is for displaying text to a screen. However, by default it writes the text to a buffer, and we need to enable the output to see the contents of the buffer.

How to do this varies depending on which client you're using. In SQL*Plus it's

SQL> set serveroutput on

In an IDE like TOAD, PLSQL Developer or Oracle SQL Developer there's a separate DBMS_OUTPUT tab: click on the tab and enable output (there's a button) - or set Preferences to always have it on.

DBMS_OUTPUT is rarely a useful means for returning data in an actual application. The normal approach is to use a Ref Cursor, which maps to JDBC and ODBC ResultSet classes. Something like this:

CREATE OR REPLACE PROCEDURE procedure1
    ( 
        s1 IN DATE,
        ty IN CHAR DEFAULT 2,
        nm IN VARCHAR2 DEFAULT 64,
        rc out sys_refcursor 
    )
IS 
BEGIN
    open rc for 
        select * from table1
        where d  = s1
        and c = ty
        and s = nm;
END procedure1;
/

Incidentally, your parameters are defined with string datatypes but the defaults are numeric values. Please don't get into bad habits. Strong datatyping is a key defence against data corruption and broken code, so always use the correct data type.

hmmftg · Accepted Answer · 2017-07-17 04:45:34Z

2

try this;

 CREATE PROCEDURE PROCEDURE1 (
                              S1   IN DATE,
                              TY   IN CHAR DEFAULT 2,
                              NM   IN VARCHAR2 DEFAULT 64
                             )
 IS
 BEGIN

    FOR I IN (SELECT DATE, TYPE, NAME FROM TABLE1)--Easier way to use cursor
    LOOP
       DBMS_OUTPUT.PUT_LINE ('DATE' || I.DATE);
       DBMS_OUTPUT.PUT_LINE ('TYPE' || I.TYPE);
       DBMS_OUTPUT.PUT_LINE ('NAME' || I.NAME);
    END LOOP;
 END PROCEDURE1;

by executing this you only created the procedure and stored it in db, you need to call it and turn on system output to see the output. like this:

set serveroutput on;

begin
    PROCEDURE1(null, null, null);
end;

answered Jul 17, 2017 at 4:45

hmmftg

1,8501 gold badge25 silver badges38 bronze badges

1 Comment

APC Over a year ago

Your revised code doesn't materially effect the compilation or execution of the procedure.

Steven Feuerstein · Accepted Answer · 2017-07-16 17:37:30Z

2

What environment are using to compile your code? You should certainly be seeing some immediate feedback.

Note that in most environments, though, you need to do a little more than you did before.

The final ";" in your code is part of PL/SQL. It does not trigger execution of your DDL. Generally you should do this:

CREATE OR REPLACE PROCEDURE myproc
IS
BEGIN
  ...
END myproc;
/

And that "/" will submit your statement for execution.

answered Jul 16, 2017 at 17:37

Steven Feuerstein

2,0041 gold badge12 silver badges16 bronze badges

Collectives™ on Stack Overflow

Creating Oracle PL/SQL Stored procedure

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related