Systems Engineering and RDBMS

Back to the basics: Declaring procedures and functions in anonymous PL/SQL block

Posted by decipherinfosys on August 28, 2008

Last week at client site, one of the developers had a question whether we can use function or procedure in anonymous PL/SQL block or not. And the answer is yes we can very well declare and use the function and procedure in the PL/SQL block. In this blog post, we will show how to do it using small code snippet. But first of all, create the table for our test purpose.

CREATE TABLE TEST
(
ID NUMBER(9),
OBJ_TYPE VARCHAR(5),
OBJ_NAME VARCHAR(30),
NUM_ROWS NUMBER(9),
CREATE_DATE DATE
);

Now let us write small PL/SQL block, which contains both function and stored procedure along with other declarations.

DECLARE
/*– variable*/
v_date DATE;
v_tableName VARCHAR(30);
v_objectID NUMBER(9);
v_numRows NUMBER(9);
/*– Cursor*/
CURSOR C1 IS
SELECT object_name, object_id
FROM user_objects
WHERE object_Type = ‘TABLE’;
/*– Function declaration*/
FUNCTION getDate(num_days NUMBER)
RETURN DATE IS
BEGIN
RETURN(SYSDATE – num_days);
END getDate;
/*– Procedure declaration*/
PROCEDURE testproc(p_name IN VARCHAR, p_num_rows OUT NUMBER) IS
BEGIN
SELECT num_rows INTO p_num_rows FROM user_tables
WHERE table_name = p_name;
END testproc;

BEGIN
OPEN C1;
FETCH C1 INTO v_tablename,v_objectID;
LOOP
EXIT WHEN C1%NOTFOUND;
/*– Call to function*/
v_date := getDate(1);
/*– Call to procedure*/
testproc(v_tableName,v_numRows);

INSERT INTO TEST(ID,obj_Type,obj_name,num_rows,create_date)
VALUES(v_objectID,’TABLE’,v_tableName,v_numrows,v_Date);
/*– Fetch new record*/
FETCH C1 INTO v_tablename,v_objectID;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE_APPLICATION_ERROR(-20001,SQLERRM);
END;
/

If we run this block as is, it will run perfectly fine. Scope of declared subprograms will remain until the execution of the anonymous block. If we query the USER_OBJECTS table, you will not find subprograms used in this PL/SQL block.

SELECT object_id, object_Type, object_name
FROM user_objects
WHERE object_name IN (‘GETDATE’,’TESTPROC’)

no rows selected

SQL>

One very important thing to remember when dealing with subprograms in anonymous PL/SQL block is that, declaration of subprograms should be always in the end, after all the variable, cursor and other declarations are done. If subprograms are not declared in the end, it will result into an error. Let us assume that in our example above, if cursor is declared in the last after function and procedure declaration, it will result into following error.

ERROR at line 20:
ORA-06550: line 20, column 4:
PLS-00103: Encountered the symbol “CURSOR” when expecting one of the following:
begin function package pragma procedure form

6 Responses to “Back to the basics: Declaring procedures and functions in anonymous PL/SQL block”

  1. Pakua 2004 said

    Very, very interesting! It seems a very powerful approach for temporary scripts, in which we need some code structure and abstraction, but still don´t need to store the structures permanently.

  2. Anonymous said

    Apparently, these functions cannot be used in SQL statements inside the block.

  3. rekha said

    HI,
    Thanks for giving code.
    Oracle Training in Chennai

  4. vani said

    Can i get few more examples??

    Oracle Training

  5. Amol Gite said

    HOW TO FIRE A MULTIPLE QUERY IN PL/SQ?

  6. Amol Gite said

    how fire a queries in pl/sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: