Pages

▼

Monday, 17 September 2012




Plsql Subprograms:

A subprogram is a named block of PL/SQL.
There are two types of subprograms in PL/SQL namely Procedures and Functions.
Every subprogram will have a declarative part, an executable part or body, and an exception handling part, which is optional.
A procedure may or may not return any value.

A stored procedure, function, or package is a PL/SQL program unit that:
  • Has a name.                    A subprogram is a named block of PL/SQL.
  • Can take parameters, and can return values.
  • Is stored in the data dictionary.
  • Can be called by many users.



Note:

The term stored procedure is sometimes used generically for both stored procedures and stored functions.
The only difference between procedures and functions is that functions always return a single value to the caller, while procedures do not return a value to the caller.

 Naming Procedures and Functions :

Because a procedure or function is stored in the database, it must be named.
This distinguishes it from other stored procedures and makes it possible for applications to call it.
Each publicly-visible procedure or function in a schema must have a unique name, and the name must be a legal PL/SQL identifier.

Note:
If you plan to call a stored procedure using a stub generated by SQL*Module, then the stored procedure name must also be a legal identifier in the calling host 3GL language, such as Ada or C.

Your software checklist:

  • A text editor. The code examples were written with Microsoft ® Notepad version 5.1, supplied with the operating system called Microsoft ® Windows XP Professional.

  • A tool to submit Oracle SQL and PL/SQL statements to an Oracle database. The code examples were tested in Oracle's SQL*Plus tool, version 3.3.4.0.0 for Windows.

  • An Oracle database you can connect to with a user name and password. Your user name should be set up with the permission to write procedures: the CREATE PROCEDURE system privilege. The code examples were tested on an Oracle9i database, version 9.2.0.

Creating Procedures:

In Oracle, you can create your own procedures.

The syntax for a procedure is:
CREATE [OR REPLACE] PROCEDURE procedure_name
    [ (parameter [,parameter]) ]
IS
    [declaration_section]
BEGIN
    executable_section
[EXCEPTION
    exception_section]
END [procedure_name];

In the above given syntax things enclosed in between angular brackets (“< > “) are user defined
enclosed in between square brackets (“[      ]”) are optional.
AUTHID clause is used to decide whether the procedure should execute with invoker (current-user or person who executes it) or with definer (owner or person created) rights
When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
  1. IN - The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
  2. OUT - The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
  3. IN OUT - The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
PL/SQL procedures behave very much like procedures in other programming language.

Here Is An Example Of A Pl/Sql Procedure:


addtuple1 that, given an integer i, inserts the tuple (i, 'xxx') into the following example relation:

CREATE TABLE T2 (

    a INTEGER,

    b CHAR(10)

);





CREATE PROCEDURE addtuple1(i IN NUMBER) AS

BEGIN

    INSERT INTO T2 VALUES(i, 'xxx');

END addtuple1;


run;

Procedure is a subprogram used to perform a specific action. A procedure contains two parts specification and the body.
Procedure specification begins with CREATE and ends with procedure name or parameters list. Procedures that do not take parameters are written without a parenthesis.
The body of the procedure starts after the keyword IS or AS and ends with keyword END.

Explanation of every keyword in procedure:


CREATE [OR REPLACE] :

A procedure is introduced by the keywords CREATE PROCEDURE followed by the procedure name and its parameters.

An option is to follow CREATE by OR REPLACE. The advantage of doing so is that should you have already made the definition, you will not get an error.

On the other hand, should the previous definition be a different procedure of the same name, you will not be warned, and the old procedure will be lost.
 
Q1:              //why create or replace why not drop and create  

PARAMETERS:
There can be any number of parameters, each followed by a mode and a type.
The possible modes are
                                               IN (read-only),  
An IN parameter is used an input only. An IN parameter cannot be changed by the called program.
                                               OUT (write-only), and
An OUT parameter is initially NULL. The program assigns the parameter a value and that value is returned to the calling program.
                                               INOUT (read and write).
An IN OUT parameter may or may not have an initial value. That initial value may or may not be modified by the called program. Any changes made to the parameter are returned to the calling program.
Parameters are declared with data types but without data type length or precision. That means that a parameter may be declared as VARCHAR2 but it will not be declared with a length component (VARCHAR2(30) would not be valid).
Note: Unlike the type specifier in a PL/SQL variable declaration, the type specifier in a parameter declaration must be unconstrained.
For example, CHAR(10) and VARCHAR(20) are illegal; CHAR or VARCHAR should be used instead. The actual length of a parameter depends on the corresponding argument that is passed in when the procedure is invoked.

Note that assigning values to parameters declared as OUT or INOUT causes the corresponding input arguments to be written.

Because of this, the input argument for an OUT or INOUT parameter should be something with an "lvalue", such as a variable like v in the example above.

A constant or a literal argument should not be passed in for an OUT/INOUT parameter.
Parameters may also be assigned a default value. You can either use the assignment operator (:=) or use the DEFAULT keyword. When a parameter has a default value, you do not need to include that parameter in the call. You MUST always include IN OUT and OUT parameters.

( parameter_1 IN VARCHAR2 := 'ABC',

 parameter_2 IN VARCHAR2 DEFAULT 'ABC',
Q2:    //why CHAR(10) and VARCHAR(20) are illegal and why we use CHAR or VARCHAR should be used instead
AS   OR     IS :
Following the arguments is the keyword AS (IS is a synonym).
Then comes the body, which is essentially a PL/SQL block. We have repeated the name of the procedure after the END, but this is optional.
However, the DECLARE section should not start with the keyword DECLARE. Rather, following AS we have:
... AS

<local_var_declarations>

BEGIN

    <procedure_body>

END;

RUN:

The run at the end runs the statement that creates the procedure; it does not execute the procedure.
NOTE: In the examples given above, we are using backward slash ‘/’ at the end of the program. This indicates the oracle engine that the PL/SQL program has ended and it can begin processing the statements.

 

Actual Versus Formal Subprogram Parameters

Subprograms pass information using parameters. The variables or expressions referenced in the parameter list of a subprogram call are actual parameters. For example, the following procedure call lists two actual parameters named emp_num and amount:
raise_salary(emp_num, amount);
 
The next procedure call shows that expressions can be used as actual parameters:
raise_salary(emp_num, merit + cola);
 
The variables declared in a subprogram spec and referenced in the subprogram body are formal parameters. For example, the following procedure declares two formal parameters named emp_id and amount:
 
 
PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS
BEGIN
   UPDATE emp SET sal = sal + amount WHERE empno = emp_id;
END raise_salary;
 
A good programming practice is to use different names for actual and formal parameters.
When you call procedure raise_salary, the actual parameters are evaluated and the results are assigned to the corresponding formal parameters.
If necessary, before assigning the value of an actual parameter to a formal parameter, PL/SQL converts the datatype of the value. For example, the following call to raise_salary is valid:
raise_salary(emp_num, '2500');
The actual parameter and its corresponding formal parameter must have compatible datatypes.
For instance, PL/SQL cannot convert between the DATE and REAL datatypes.
Also, the result must be convertible to the new datatype. The following procedure call raises the predefined exception VALUE_ERROR because PL/SQL cannot convert the second actual parameter to a number:
raise_salary(emp_num, '$2500');  -- note the dollar sign

 

Execution:

Positional Versus Named Notation for Subprogram Parameters:

When calling a subprogram, you can write the actual parameters using either positional or named notation.

That is, you can indicate the association between an actual and formal parameter by position or name. So, given the declarations

DECLARE
   acct INTEGER;
   amt  REAL;
   PROCEDURE credit_acct (acct_no INTEGER, amount REAL) IS ...

you can call the procedure credit_acct in four logically equivalent ways:
BEGIN
   credit_acct(acct, amt);                  -- positional notation
   credit_acct(amount => amt, acct_no => acct);  -- named notation
   credit_acct(acct_no => acct, amount => amt);  -- named notation
   credit_acct(acct, amount => amt);             -- mixed notation


Using Positional Notation

The first procedure call uses positional notation.
The PL/SQL compiler associates the first actual parameter, acct, with the first formal parameter, acct_no. And, the compiler associates the second actual parameter, amt, with the second formal parameter, amount.

Using Named Notation

The second procedure call uses named notation. An arrow (=>) serves as the association operator, which associates the formal parameter to the left of the arrow with the actual parameter to the right of the arrow.

The third procedure call also uses named notation and shows that you can list the parameter pairs in any order. So, you need not know the order in which the formal parameters are listed.

Using Mixed Notation

The fourth procedure call shows that you can mix positional and named notation. In this case, the first parameter uses positional notation, and the second parameter uses named notation. Positional notation must precede named notation. The reverse is not allowed. For example, the following procedure call is illegal:

credit_acct(acct_no => acct, amt);  -- illegal


To execute the procedure, use another PL/SQL statement, in which the procedure is invoked as an executable statement. For example:

General way of Execution :


BEGIN

addtuple1(99);

END;

run;

With Execute statement :

execute    addtuple1(99);

With Exec Statement :

exec  addtuple1(99);


The following procedure also inserts a tuple into T2, but it takes both components as arguments:

Example Procedues which involves IN mode parameters::


Using the IN Mode

An IN parameter lets you pass values to the subprogram being called.
Inside the subprogram, an IN parameter acts like a constant.
Therefore, it cannot be assigned a value.

Let’s create a procedure which gets the name of the employee when the employee id is passed.

1> CREATE OR REPLACE PROCEDURE emp_name (id IN NUMBER, emp_name OUT NUMBER)
2> IS
3> BEGIN
4>    SELECT first_name INTO emp_name
5>    FROM emp_tbl WHERE empID = id;
6>dbms_output.put_line(‘the name of the employee is ‘||emp_name);
7> END;
8> /



For example, the following assignment statement causes a compilation error:

PROCEDURE debit_account (acct_id IN INTEGER, amount IN REAL) IS
   minimum_purchase CONSTANT REAL DEFAULT 10.0;
   service_charge   CONSTANT REAL DEFAULT 0.50;
BEGIN
   IF amount < minimum_purchase THEN
   amount := amount + service_charge; -- causes compilation error
   END IF;
   ...
END debit_account;



The actual parameter that corresponds to an IN formal parameter can be a constant, literal, initialized variable, or expression.

Unlike OUT and IN OUT parameters, IN parameters can be initialized to default values. For more information, see "Using Default Values for Subprogram Parameters".

Execution of Procedues which involves IN mode parameters:


Examples:

Constant as a actual parameter:   exec tes(100);

Initialised Variable as a actual parameter:  exec tes(:acct);

Variable decaration:
                                var account_number number;

Exec  : account_number    :=40000000;

Procedure Execution:  exec tes (account_number);

Expression as a actual parameter:   exec tes (: acct+10-10);

Literal as a actual parameter :  exec tes(100);

Knowledge for variable declaration and initialization in sql * plus:


In SQL*Plus, a bind variable is declared with variable: 
var num_var number
var txt_var varchar2(15)

After the declaration, a value can be assigned to the variable 

begin
  select 44, 'fourty-four' into :num_var, :txt_var from dual;
end;
/

The value of the bind variable can then be printed with print: 

print num_var

Assigning value to the bind variable:

Since an execute is basically a wrapper around a begin .. end PL/SQL block, a variable can be assigned a value like so: 

exec :num_var := 42
exec :txt_var := 'fourty-two'


Example illustrating out parameter:


The following illustrates the use of an OUT parameter:

CREATE TABLE T3 (

    a INTEGER,

    b INTEGER

);

CREATE PROCEDURE addtuple3(a NUMBER, b OUT NUMBER)

AS

BEGIN

    b := 4;                           //out variable can be overwritten

    INSERT INTO T3 VALUES(a, b);

END;

run;

DECLARE

    v NUMBER;

BEGIN

    addtuple3(10, v);                 //procedure call   

END;

.

run;

Using Invoker's Rights Versus Definer's Rights (AUTHID Clause):

Oracle8i Database introduced the AUTHID clause for procedures, functions and packages.

By default,stored procedures and SQL methods execute with the privileges of their owner, not their current user.

Such definer's rights subprograms are bound to the schema in which they reside, allowing you to refer to objects in the same schema without qualifying their names.

When set to AUTHID DEFINER (the default), then your program runs under "definer rights." This means that any references to data objects (such as tables and views) are resolved at compile time, based on the directly granted privileges of the definer or owner of the program. Roles are ignored.

If, on the other hand, you set the clause to AUTHID CURRENT_USER, then any references to data objects are resolved at run time, based on the privileges of the currently-connected schema. And (the DBAs have got to love this) role-based privileges are now applied.

A stored procedure runs either with the rights of the caller (authid current_user) or with the rights of the procedure's owner (authid definer). This behaviour is specified with the authid clause. This authid clause immediatly follows the create procedure, create function, create package or create type statement. It can be ommited, in which case the default authid definer is taken.
By default,stored procedures and SQL methods execute with the privileges of their owner, not their current user.Such definer's rights subprograms are bound to the schema in which they reside, allowing you to refer to objects in the same schema without qualifying their names.
For example, if schemas HR and OE both have a table called departments,
A procedure owned by HR can refer to departments rather than HR.departments. If user OE calls HR's procedure, the procedure still accesses the departments table owned by HR.
If you compile the same procedure in both schemas, you can define the schema name as a variable in SQL*Plus and refer to the table like &schema..departments. The code is portable, but if you change it, you must recompile it in each schema.
A more maintainable way is to use the AUTHID clause, which makes stored procedures and SQL methods execute with the privileges and schema context of the calling user. You can create one instance of the procedure, and many users can call it to access their own data.
Such invoker's rights subprograms are not bound to a particular schema. The following version of procedure create_dept executes with the privileges of the calling user and inserts rows into that user's departments table:

Example 8-13 Specifying Invoker's Rights With a Procedure:

CREATE OR REPLACE PROCEDURE create_dept (
   v_deptno NUMBER,
   v_dname  VARCHAR2,
   v_mgr    NUMBER,
   v_loc    NUMBER) 
AUTHID CURRENT_USER AS
BEGIN
   INSERT INTO departments VALUES (v_deptno, v_dname, v_mgr, v_loc);
END;
/
CALL create_dept(44, 'Information Technology', 200, 1700);
 

My Example  without  AUTHID CURRENT_USER CLAUSE:

Now I am connected to the scott\tiger@upsssub66 account  and I create an procedure with emp table
create or replace procedure addename(empnumber number) is
begin
insert into emp columns(ename,empno) values('UPSS',empnumber);
end;

SQL> /
Procedure created
SQL> select * from emp;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
SQL>
There is no rows in the emp table..
SQL> exec addename(125);
PL/SQL procedure successfully completed
SQL> select * from emp;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
  125 UPSS                                                      
SQL>
Now I am login into the simulator/simulator@upsssub66
SIMULATOR>> SELECT * FROM EMP;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
there are no rows in the emp table
now I am executing the addename() procedure from simulator schema
SIMULATOR>> exec addename(145);
begin addename(145); end;
ORA-06550: line 1, column 7:
PLS-00201: identifier 'ADDENAME' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SIMULATOR>> exec scott.addename(145);
PL/SQL procedure successfully completed
SIMULATOR>> commit;
Commit complete
SIMULATOR>> select * from emp;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
SIMULATOR>>
Now I am connected to the scott\tiger@upsssub66 account 
SQL> select * from emp;
EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
  125 UPSS                                                       
  145 UPSS                                                      
The table updated in scott schema table because of without AUTHID CURRENT_USER CLAUSE

 

 

 

My Example  with  AUTHID CURRENT_USER CLAUSE:

Note: if you use AUTHID CURRENT_USER CLAUSE in your procedure then the row inserted into emp table in the SIMULATOR SCHEMA 
Then the procedure with authid current_user clause
create or replace procedure addename(empnumber number)
authid current_user is
begin
insert into emp columns(ename,empno) values('UPSS',empnumber);
end;

Complete Demonstration About Authid Clause:

The authid clause has implications, for example, on how objects are resolved within a procedure. This is demonstrated in the following.
First, two users are created, one named a and one named b. Some needed privileges are granted to these users as well.
create user a identified by a default tablespace data quota unlimited on data;
create user b identified by b default tablespace data quota unlimited on data;
grant create session, create procedure, create table to a;
grant create session, create procedure, create table to b;
Now, user a creates tow tables named t and u and fills them with this is a's table. User u is allowed to read t.
connect a/a;
create table t (d varchar2(40));
insert into  t values ('this is a''s table');
commit;
create table u (d varchar2(40));
insert into  u values ('this is a''s table');
grant select on t to b;
-- no grant on u
User a also creates a procedure (named pt) with authid current_user that just reads what was filled into table t:
create procedure pt
 authid current_user
as
  v_d t.d%type;
begin
  select d into v_d from t;
  dbms_output.put_line(v_d);
end;
/
User a then creates a procedure (pu) that reads from table u:
create procedure pu
 authid definer
as
  v_d t.d%type;
begin
  select d into v_d from u;
  dbms_output.put_line(v_d);
end;
/
User b should be allowed to execute both procedures:
grant execute on pt to b;
grant execute on pu to b;
Now, it's user b's turn to do something. He, as well, creates two tables t and u and fills them with this is b's table:
connect b/b;
create table t (d varchar2(40));
insert into  t values ('this is b''s table');

create table u (d varchar2(40));
insert into  u values ('this is b''s table');
commit;
Now, he executes user a's procedure pt:
set serveroutput on
begin a.pt; end;
/
The output is:
this is b's table
Then, he executes a.pu:
begin a.pu; end;
/
This time, the output is:
this is a's table
This demonstrates that a procedure is run in the context of the caller if has authid current_user and in the context of the owner if the procedure has authid definer

Effects on roles:

A procedure with authid definer disables all roles that are granted to the caller.
This can lead to ora-00942 errors.
If the procedure is created with authid current_user, the roles of the caller are enabled when the procedure is executed. However, in order to compile the procedures, the privileges still need to be granted directly.

Advantages of Invoker's Rights:

Invoker's rights subprograms let you reuse code and centralize application logic. They are especially useful in applications that store data using identical tables in different schemas.
All the schemas in one instance can call procedures owned by a central schema. You can even have schemas in different instances call centralized procedures using a database link.
Consider a company that uses a stored procedure to analyze sales. If the company has several schemas, each with a similar SALES table, normally it would also need several copies of the stored procedure, one in each schema.
To solve the problem, the company installs an invoker's rights version of the stored procedure in a central schema. Now, all the other schemas can call the same procedure, which queries the appropriate to SALES table in each case.
You can restrict access to sensitive data by calling from an invoker's rights subprogram to a definer's rights subprogram that queries or updates the table containing the sensitive data. Although multiple users can call the invoker's rights subprogram, they do not have direct access to the sensitive data.

 

Data dictionary views for subprograms

SQL> desc user_source;
Name      Type                                        Nullable                Default   Comments                                                                                                     
---- -------------- -------- ------- -------------------------------------------------------------------------------------------------------------
NAME VARCHAR2(30)                         Y                                                              Name of the object                                                                                           
TYPE VARCHAR2(12)                            Y                                                              Type of the object: "TYPE", "TYPE BODY", "PROCEDURE",                                                                                                                                             "FUNCTION","PACKAGE", "PACKAGE BODY" or "JAVA SOURCE"
LINE NUMBER                                         Y                                                             Line number of this line of source                                                                            
TEXT VARCHAR2(4000)                  Y                                                             Source text                                                                                                  


DBA_OBJECTS
Describes all objects in the database.

Column
Not NULL
Datatypes
Description
OWNER
NOT NULL
VARCHAR2(30)
Owner of the object
OBJECT_NAME
NOT NULL
VARCHAR2(128)
Name of the object
SUBOBJECT_NAME

VARCHAR2(30)
Name of the subobject (for example, partition)
OBJECT_ID
NOT NULL
NUMBER
Dictionary object number of the object
DATA_OBJECT_ID

NUMBER
Dictionary object number of the segment that contains the object. Not the same as unique 16-byte object identifier (object ID) that Oracle assigns to row objects in object tables in the system
OBJECT_TYPE

VARCHAR2(18)
Type of the object (such as TABLE, INDEX)
CREATED
NOT NULL
DATE
Timestamp for the creation of the object
LAST_DDL_TIME
NOT NULL
DATE
Timestamp for the last modification of the object resulting from a DDL command (including grants and revokes)
TIMESTAMP

VARCHAR2(19)
Timestamp for the specification of the object (character data)
STATUS

VARCHAR2(7)
Status of the object (VALID, INVALID, or N/A)
TEMPORARY

VARCHAR2(1)
Whether the object is temporary (the current session can see only data that it placed in this object itself)
GENERATED

VARCHAR2(1)
Was the name of this object system generated? (Y|N)
SECONDARY

VARCHAR2(1)
Whether this is a secondary object created by the ODCIIndexCreate method of the Oracle9i Data Cartridge (Y|N)


.


DBA_PROCEDURES

Oracle 11gR1
DBA_PROCEDURES lists all functions and procedures along with their associated properties. Its columns are the same as those in "ALL_PROCEDURES".

Column
Datatype
NULL
Description
OWNER
VARCHAR2(30)
NOT NULL
Owner of the procedure
OBJECT_NAME
VARCHAR2(30)
NOT NULL
Name of the object: top-level function, procedure, or package name
PROCEDURE_NAME
VARCHAR2(30)

Name of the procedure
OBJECT_ID
NUMBER

Object number of the object
SUBPROGRAM_ID
NUMBER

Unique subprogram identifier
OVERLOAD
VARCHAR2(40)

Overload unique identifier
OBJECT_TYPE
VARCHAR2(19)

The typename of the object
AGGREGATE
VARCHAR2(3)

Indicates whether the procedure is an aggregate function (YES) or not (NO)
PIPELINED
VARCHAR2(3)

Indicates whether the procedure is a pipelined table function (YES) or not (NO)
IMPLTYPEOWNER
VARCHAR2(30)

Name of the owner of the implementation type, if any
IMPLTYPENAME
VARCHAR2(30)

Name of the implementation type, if any
PARALLEL
VARCHAR2(3)

Indicates whether the procedure or function is parallel-enabled (YES) or not (NO)
INTERFACE
VARCHAR2(3)

YES, if the procedure/function is a table function implemented using the ODCI interface; otherwise NO
DETERMINISTIC
VARCHAR2(3)

YES, if the procedure/function is declared to be deterministic; otherwise NO
AUTHID
VARCHAR2(12)

Indicates whether the procedure/function is declared to execute as DEFINER or CURRENT_USER (invoker)

 

To drop a stored procedure/function


drop procedure <procedure_name>;

drop function <function_name>;

When client executes a procedure are function, the processing is done in the server.
This reduces network traffic. The subprograms are compiled and stored in the Oracle database as stored programs and can be invoked whenever required.
As they are stored in compiled form when called they only need to be executed. Hence they save time needed for compilation.

 

Subprograms provide the following advantages 

1.       They allow you to write PL/SQL program that meet our need
2.       They allow you to break the program into manageable modules. 
3.       They provide reusability and maintainability for the code.

 

Discovering Errors


PL/SQL does not always tell you about compilation errors. Instead, it gives you a cryptic message such as "procedure created with compilation errors". If you don't see what is wrong immediately, try issuing the command

show errors  procedure <procedure_name>;

Alternatively, you can type, SHO ERR (short for SHOW ERRORS) to see the most recent compilation error.
Note that the location of the error given as part of the error message is not always accurate!

The following is a simple example of a procedure with  in parameter :  
CREATE OR REPLACE Procedure UpdateCourse
   ( name_in IN varchar2 )
IS
    cnumber number;
    cursor c1 is
    select course_number
      from courses_tbl
      where course_name = name_in;


BEGIN
open c1;
fetch c1 into cnumber;

if c1%notfound then
     cnumber := 9999;
end if;
insert into student_courses
( course_name,
  course_number)
values ( name_in,
                cnumber );
commit;
close c1;
EXCEPTION
WHEN OTHERS THEN
 raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;
This procedure is called UpdateCourse. It has one parameter called name_in. The procedures will lookup the course_number based on course name. If it does not find a match, it defaults the course number to 99999. It then inserts a new record into the student_courses table.

Oracle Stored Procedures Tips


http://www.dba-oracle.com/art_proc.htm

Faqs


What is the difference between "IS" and "AS" while creating procedure. Ex:- create procedure IS or AS


How one call DDL statement using stored procedures in Oracle?


Explain the benefits of running stored procedure on a database engine?

Stored Procedure are of two type
1-user define stored procedure
2- System define Stored procedure




Q) Is it possible to know from the select statement if it is INVOKER(CURRENT_USER) or DEFINER

A) Yes, It is possible to get this information from the select statement. Use

SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = "Your Package Name"
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS'

Example:-
-----------

SELECT dbo.object_name,
(DECODE(SIGN(bitand(options,16)),1,'INVOKER','DEFINER')) "authid"
FROM dba_objects dbo,
sys.PROCEDURE$ p
WHERE p.obj# = dbo.object_id
AND dbo.object_name = 'ASO_APR_WF_INT'
AND dbo.object_type = 'PACKAGE'
AND dbo.owner = 'APPS'


Faqs related to data dictionary views:


Faq1
To find out what procedures and functions you have created, use the following SQL query:
select object_type, object_name

from user_objects

where object_type = 'PROCEDURE'

   or object_type = 'FUNCTION';


Faq2
SQL to display all stored procedures in my database
Question: 
I want the SQL to display all stored procedures in my database. I also want to know how to show the source for Oracle stored procedures. Where do I find stored procedure information in Oracle dictionary?
Answer:

The dba_source view contains the details on Oracle stored procedures and it’s easy to query dba_source to see stored procedure details.

For a roadmap to the dictionary views, get the free Oracle poster, indispensible for these types of SQL queries.

The DBA_SOURCE view is based on the SOURCE$ table that holds all of the source code for the stored objects (other than triggers and views) in the database.

Name                                       Null?    Type
------------------------------------------------------------------
OWNER                                               VARCHAR2 (30)
NAME                                                  VARCHAR2(30)
TYPE                                                    VARCHAR2(12)
LINE                                                     NUMBER
TEXT                                                    VARCHAR2(4000)
 

Notice that the TEXT column is a VARCHAR2(4000) data type. This 4000 length indicates that to properly view any text lines you may need to set the LONG variable in SQL*Plus to 4000.

A simple select will return all of the lines for a given stored object in order as shown in Figure 6.

select 'create or replace '||text,line
from
 dba_source
where
 owner = upper('&&owner') and
 type = upper('&&obj_type') and
 name = upper('&&obj_name') and
 line = 1
UNION
select text,line
from
 dba_source s2
where
 s2.name = upper('&&3') and
 s2.owner = upper('&&1') and
 s2.type = upper('&&2') and
 s2.line > 1
order by 2;

Q1: How do you invoke a stored procedure in a SELECT statement?

Expert Brian Peasland:
You cannot invoke a stored procedure in a SELECT statement.
However, you can call a function in a SELECT statement.
The function can be a wrapper for the stored procedure. For instance, assume that I have a stored procedure called FOO. Let's create a wrapper function BAR that calls the stored proc:
CREATE FUNCTION bar ()
RETURN number
AS
BEGIN
   foo();
   RETURN 1;
END;
/
Now I can use the function above to execute the stored proc in my SELECT statement:
SELECT bar() FROM dual;




Q2: I have a situation to call the same stored procedure from inside the same procedure. For example, let's say I have a procedure called ConverSubPlans with some parameters. I am doing some validations inside that procedure and based on those, I need to call ConverSubPlans once again within ConverSubPlans. Is it advisable?


Expert Brian Peasland: You sure can call a procedure from within that same procedure. Such an operation is called "recursion". One procedure recursively calls itself. The big thing to keep in mind is that you need an ending point otherwise the recursive calls will be infinite and never stop. A common recursive procedure or function is to compute the factorial of a number. N factorial (written N!) is defined as N*(N-1)*(N-2)*...*2*1. This also means that N! = N*(N-1)!. So a recursive function is born. Such a recursive function would appear as follows:

CREATE FUNCTION fact (n NUMBER)
 RETURN NUMBER IS
    returnVal NUMBER;
 BEGIN
    IF (n = 1) THEN
       returnVal := 1;
    ELSE
       returnVal := n*fact(n-1);
    END IF;
    RETURN returnVal;
 END;
 /
Notice that in the above example, I have a terminating condition (n=1). If this condition is not met, then the function is called again. The same concept applies to procedures. But ensure that you have a terminating condition.


Q3.Oracle: how can one search plsql code for a string or key value


The text source of the stored objects (including stored procedures) are made available in Oracle by three views:

  1. USER_SOURCE describes the text source of the stored objects owned by the current user. This view does not display the OWNER column.
  2. ALL_SOURCE describes the text source of the stored objects accessible to the current user.
  3. DBA_SOURCE describes the text source of all stored objects in the database.


These are the columns:
Column Name
Description
OWNER
Owner of the object
NAME
Name of the object
TYPE
Type of object: FUNCTION, JAVA SOURCE, PACKAGE, PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY
LINE
Line number of this line of source
TEXT
Text source of the stored object



If you want to search for a certain keyword in the source, this query may be helpful:

SELECT type, name, line
FROM   user_source
WHERE UPPER(text) LIKE UPPER ('%&SEARCH_STRING%');

Enter the string you are looking for when prompted for the value of SEARCH_STRING.


Q4:How can one see if somebody modified any code?


The source code for stored procedures, functions and packages are stored in the Oracle Data Dictionary. One can detect code changes by looking at the TIMESTAMP and LAST_DDL_TIME column in the USER_OBJECTS dictionary view. Example:

SELECT OBJECT_NAME,
       TO_CHAR(CREATED,       'DD-Mon-RR HH24:MI') CREATE_TIME,
       TO_CHAR(LAST_DDL_TIME, 'DD-Mon-RR HH24:MI') MOD_TIME,
       STATUS
FROM   USER_OBJECTS
WHERE  LAST_DDL_TIME > '&CHECK_FROM_DATE';

Note: If you recompile an object, the LAST_DDL_TIME column is updated, but the TIMESTAMP column is not updated.
If you modified the code, both the TIMESTAMP and LAST_DDL_TIME columns are updated.

Q5: I am encountering a very strange problem....when I create the following query in SQL Plus


SELECT * FROM USER_OBJECTS;

then only the records under the OBJECT_NAME column get displayed; the rest of the columns simply get ignored!!! Why is this happening? If I change the query to

SELECT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE FROM USER_OBJECTS;

then again only the records under the OBJECT_NAME column get displayed but if I change the query to

SELECT OBJECT_ID,OBJECT_TYPE,OBJECT_NAME FROM USER_OBJECTS;

then the records of all the 3 columns get displayed!! If the query is changed to

SELECT OBJECT_ID,OBJECT_NAME,OBJECT_TYPE FROM USER_OBJECTS;

then only the records under the OBJECT_ID & OBJECT_NAME columns get displayed!!! Can someone throw some light on this erratic behaviour?

Thanks,

Arpan

Solution for the above problem:

 CRoberts (MIS) 23 Feb 03 21:20 
I may have a solution to your problem.  It is simply the way SQL*Plus displays columns. 

It has to do with what the SET LINESIZE n is set to. 
Numbers are limited to NUMBER(38) so your Object_ID will be displayed. 
However, if a text column, like Object_Name is displayed, you can get '--- ...' forevery.  Two ways to cure this.  One is use a column command like COLUMN OBJECT_NAME FORMAT A30 TRUNC and so on for each Text column. 

The other is to SET LINESIZE 80 or whatever.

Try this test:

DBSBE> set linesize 300
DBSBE> desc user_tables

Name                                                        
----------------------------------------  . . .
TABLE_NAME                                                  
TABLESPACE_NAME        

DBSBE> set linesize 80
DBSBE> desc user_obects

DBSBE> desc user_objects
 Name                         Null?    Type
 ---------------------------- -------- -------------
 OBJECT_NAME                            VARCHAR2(128)
 SUBOBJECT_NAME                         VARCHAR2(30)                                     


arpan (Programmer) 24 Feb 03 2:48 
Hi,

Thanks for your response. Before posting my question, I had thought that the LINESIZE could be the culprit since when the LINESIZE is set to 80 (which is the default value)

SELECT * FROM USER_OBJECTS;

says 'rows will be truncated'. When the LINESIZE is increased to 260, then the message 'rows will be truncated' was not shown but at the same time, only the records under the OBJECT_NAME column were displayed!! So I don't think LINESIZE is the reason behind this erratic behavior.

I tried out using COLUMN OBJECT_NAME FORMAT A30 TRUNC & then all the records under the columns other than the OBJECT_NAME column were displayed. But I couldn't exactly follow what does COLUMN OBJECT_NAME FORMAT A30 TRUNC do? What does 'A30' mean or what it does? Could you please just explain me this? Also I have noticed that if A30 is changed to A1, still it doesn't make any difference to the output. Why so?

sem (Programmer) 24 Feb 03 4:31 

In fact sql*plus has some problems with increasing linesize. In some cases resizing(maximaizing) its window may help.
Regards, Dima







at

No comments:

Post a Comment