473,718 Members | 1,949 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

PL/SQL-PACKAGE - 1

debasisdas
8,127 Recognized Expert Expert
The following thread contains some useful tips/sample codes regarding PACKAGES in oracle, that the forum members may find useful.

A package is a collection of procedures,func tions,cursors,g lobal variables etc.

A package is also stored as an Object in the database.

A package ha s two parts
1. Package-------------Specification.
2. Package body---Implimentaton.

Note:-
1.If the package does not include any procedure or functions then the package body is optional.
2.All the variables declared in the package declaration block have global scope.

At the time of execution the package members must be qualified by the package name.

It supports OVERLOADING.

Once any member of the package is called the entire package gets loaded into the memory there ay avoiding additional roundtrips .

SAMPLE PACKAGE EX#1
=============== =====
package specification
-----------------------------------
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PACKAGE PACK1 AS
  2. --The procedure is only declared with parametres.
  3. PROCEDURE PROC1
  4. (
  5. NUM EMP.EMPNO%TYPE,
  6. SS EMP.SAL%TYPE
  7. );
  8. --Function is declared here.
  9. FUNCTION  SPELLDATE
  10. (
  11. DD DATE
  12. )
  13. RETURN VARCHAR2;
  14. END;
  15.  
The body part(implementa tion)
-----------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PACKAGE BODY PACK1 AS
  2. --Here is the implimentation of the previously declared Pocedure.
  3. PROCEDURE PROC1
  4. (
  5. NUM EMP.EMPNO%TYPE,
  6. SS EMP.SAL%TYPE
  7. )
  8. AS
  9. BEGIN
  10. INSERT INTO EMP (EMPNO,SAL)VALUES(NUM,SS);
  11. DBMS_OUTPUT.PUT_LINE('ONE RECORD INSERTED...!');
  12. EXCEPTION
  13. WHEN DUP_VAL_ON_INDEX THEN
  14. RAISE_APPLICATION_ERROR(-20001,'DUPLICATE ENTRY');
  15. END PROC1;
  16. --and the function is implemented here.
  17. FUNCTION SPELLDATE
  18. (
  19. --receives date as input.
  20. DD DATE
  21. ) RETURN VARCHAR2 AS
  22. STR VARCHAR2(500);
  23. BEGIN
  24. SELECT TO_CHAR(DD,'DDSPTH MONTH YEAR') INTO STR FROM DUAL;
  25. --returns the fully spelled out string of the entered date.
  26. RETURN STR;
  27. EXCEPTION
  28. WHEN OTHERS THEN
  29. RAISE_APPLICATION_ERROR(-20001,'SOME ERROR');
  30. --If there is any error returns null.
  31. RETURN NULL;
  32. END SPELLDATE;
  33. END PACK1;
  34.  


SAMPLE PACKAGE EX#2
=============== =====
PACKAGE SPECIFICATION
--------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. create or replace package my_pkg as
  2. procedure my_proc(arg1 in varchar2);
  3. function my_fun(arg1 in number) return varchar2;
  4. end my_pkg;
  5.  
-------------
PACKAGE BODY
----------------------------
Expand|Select|Wrap|Line Numbers
  1. create or replace package body my_pkg as
  2.  
  3. function my_private_fun(arg1 in number) return varchar2
  4. is
  5. return_val varchar2(20);
  6. begin
  7. select col1 into return_val from tab2 where col2=arg1;
  8. return return_val;
  9. exception
  10. when NO_DATA_FOUND THEN
  11. return 'Sorry no data found.....!';
  12. end  my_private_fun;
  13.  
  14. function my_fun(arg1 in number)return varchar2
  15. is
  16. begin
  17. return my_private_fun(arg1);
  18. end my_fun;
  19.  
  20. procedure my_proc(arg1 in varchar2)
  21. is
  22. begin
  23. update tab1 set col1=col1+1 where col2=arg1;
  24. end my_proc;
  25. end my_pkg;
  26.  

Also check PL/SQL-PACKAGE - 2
Jun 7 '07 #1
0 3878

Sign in to post your reply or Sign up for a free account.

Similar topics

3
3352
by: cooldv | last post by:
i am running a website on Windows 2000 server with ASP 3 webpages and Access 2000 database. (with a hosting company) traffic is slow at this time but expect to grow. lately i have been reading about sql database and sql server, specially this article: http://www.aspfaq.com/show.asp?id=2195 will someone help me understand: 1. with *SQL Server*, do i keep my current Access 2000 database and ASP pages?
2
12661
by: Peter | last post by:
I run most of my SQL scripts via kornshell on AIX. I use the "here-document" to run some of the smaller ones. Example: #!/bin/ksh # Analyze the table. sqlplus ${SCHEMA_NM}/${SCHEMA_PASSWD}@${DB_NM} <<-ANALYZE_TABLE SET TERMOUT ON
0
10208
by: Jan | last post by:
I store sql-commands in a database table. In the first step I get the sql command out of the database table with embedded sql. In the second step I try to execute the command, which i got from the database table, using dynamic sql. Executing 'EXEC SQL DESCRIBE SELECT LIST FOR S INTO select_dp;' the error code -2149 is returned That means "Specified partition does not exist". Does anybody know if it is a database problem or a problem of
10
6737
by: Dagwood | last post by:
Good morning: At least it's morning where I am. :) I have a rather newbie question I'm afraid. I have VisualStudio.NET, and have installed it along with SQL server. However I can't seem to find an administration front-end to SQL, other than the VisualStudio, which is ok for creating databases and adding tables/columns. However when I go to generate create script, I'm told that I need client tools.
6
6774
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used the SQL Profile to watch the T-SQL-Command which Access ( who creates the commands?) creates and noticed:
11
4264
by: Mark Yudkin | last post by:
The documentation is unclear (at least to me) on the permissibility of accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003, with separate transactions scope, from separate threads of a multithreaded program using embedded SQL. Since the threads do not need to share transaction scopes, the sqleAttachToCtx family of APIs do not seem to be necessary. <quote> In the default implementation of threaded applications against...
1
6628
by: Peter | last post by:
I've purchased VS.NET 2005 Standard and have tried to install SQL Server 2005 Express, but get the following error in the error log. Please could someone help me.... Microsoft SQL Server 2005 Express Edition x86: Component Microsoft SQL Server 2005 Express Edition x86 returned an unexpected value. ***EndOfSession***? Microsoft SQL Server 2005 Express Edition x86: Component Microsoft SQL Server 2005 Express Edition x86 returned an...
6
2505
by: Fuzzydave | last post by:
I am back developing futher our Python/CGI based web application run by a Postgres DB and as per usual I am having some issues. It Involves a lot of Legacy code. All the actual SQL Querys are stored in the .py files and run in the .cgi files. I have the problem that I need to construct a row from two seprate SQL Querys, I have tried combining the two Querys but all that does is create a Query that returns nothing after a long period...
14
3033
by: Developer | last post by:
Hello All, i have recently installed VS2005 and was trying to install SQL sever 2000. I have Win XP' SP2. But when I tried installing, it only installed client tools and not the database. Can anyone please help me with this as I want to install SQL server and also wouold be grateful, if you can suggest me any workaround to dealwith this problem.(Like should I install any new OS etc). Any help would be appreciated.
5
9288
by: dbrother | last post by:
Access 2003 Win XP Pro SP3 Using SQL /ADO Recordsets in a Do Loop Hello, I'm using a random number generator based on an integer input from a user from a form that will get X number of random records from an external Oracle source using a SQL statement. The SQL statement works as expected when the loop code is commented out, but I receive an error "SQL command not properly ended" when the loop is active. Do Until intLoop = UserNum ...
0
8827
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9206
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9118
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7985
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6652
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5971
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4740
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2550
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2122
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.