CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqloracle12c
Kirtan Patel
Kirtan Patel

Reputation: 23

Creating Oracle PL/SQL Stored procedure

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.

Upvotes: 2

Views: 2911

Answers (3)

APC
APC

Reputation: 146339

"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.

Upvotes: 4

hmmftg
hmmftg

Reputation: 1754

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;

Upvotes: 2

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

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.

Upvotes: 2

Related Questions