SQL & PL/SQL :: Creating Multiple Functions Within A Package?

Apr 16, 2013

I'm trying to create multiple functions, add them then minus 1 in a package. I'm at my wits end as to what I'm not doing correctly as the package gets created fine but the package body is giving me compilation errors.

create or replace package addfunction as
function q1 (num1 IN NUMBER, num2 IN NUMBER) return number;
function q1 (num1 IN NUMBER, num2 IN NUMBER, num3 IN NUMBER) return number;
END;
/

[code].....

Warning: Package body created with compilation errors

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: How Many Procedures And Functions Can Be Written In Package

May 4, 2010

I think there is no limit for number of procedures and functions can be written in a package, but little much confusion.

What is the maximum number of Procedures and Functions can be written in a package? is such type of limit is there or not ?

View 4 Replies View Related

SQL & PL/SQL :: How Many Functions And Procedures Exist In Package

Apr 6, 2011

how many functions and Procedures are exist in Package. Is it possible to identify through query.

Eg: I have one Package:- ABC. This ABC package having 2 functions and 3 procedures. I want, total no.of functions and total no.of procedures through query.

View 1 Replies View Related

PL/SQL :: Get Multiple Records In FUNCTIONS?

Aug 16, 2012

I have created the following function in oracle database 10g then I used it in Oracle reports 6 . It is working fine for single row(one value). But when there is multiple rows then it show error .

Note:ot.po_code is number datatype and clr_name is varchar2 data type.

this is the script

create or replace FUNCTION get_colour_name (POC number)
RETURN VARCHAR2 IS
CLR_NAME VARCHAR2(100);
BEGIN
SELECT DISTINCT BC.COLOUR_NAME INTO CLR_NAME

[Code].....

View 4 Replies View Related

PL/SQL :: Creating A Package Body

Jan 6, 2013

I'm trying to create a package body but i keep getting an error message saying package created with complication.

CREATE OR REPLACE PACKAGE BODY MY_PACK
IS
PROCEDURE MY_PROC(A NUMBER, B NUMBER)
IS
C NUMBER(4);
BEGIN
C:=A+B;
[code].......

View 2 Replies View Related

SQL & PL/SQL :: (error) PLS-00103 / Creating Package

Jul 19, 2012

When trying to create package I receive this error...[Error] PLS-00103 (8: 16): PLS-00103: Encountered the symbol "." when expecting one of the following:;

pointing to "." in the last line "END nondrg_prod.Readmissions;"

CREATE or replace PACKAGE nondrg_prod.Readmissions AS
TYPE Readmits IS REF CURSOR RETURN READMISSIONS_DATA_MH%ROWTYPE;
PROCEDURE open_rdmh (rdmh IN OUT rdmhCurTyp);
END Readmissions;

[code]....

View 14 Replies View Related

PL/SQL :: Creating Collections Inside Package

Nov 23, 2012

I was trying to create types inside package like:

-create or replace package pack1 is
type udtable is table of integer index by binary_integer;
end;

-create or replace package BODY pack1 is
type udtable is table of integer index by binary_integer;
end;

Although this is not exact query what i have doubt in my mind. is it possible to create object types/collections inside/with packages?

View 6 Replies View Related

Application Express :: Creating Procedure Using Database Package

Oct 23, 2012

I have created a procedure within a database package, but when I want to create a form based on procedure but I can't call it. I think that I have to use prefix, I am a beginner in database and I don't know how to do this.

View 4 Replies View Related

SQL & PL/SQL :: Stored Procedure With Multiple Table Join With Aggregate Functions

Jun 15, 2013

I have 2 tables order_items and items.

Order_items Items
Item_id Item_id
Quantity Price

In normal sql statement: select sum(order_items.quantity*items.price) sales_price
from order_items,items
where order.item_id=items.item_id;

I have to put this logic in either a stored procedure or Function just to calculate sum(order_items.quantity*items.price) and store the aggregated value as Sales_price in DB. Then we have to call this from Informatica Stored procedure Transformation where we will have only one output port as Sales_price,this is to load into summary table. All the aggregate calculations and joining of 2 tables should be done on DB side and only one output should be populated when we execute this procedure.

View 2 Replies View Related

SQL & PL/SQL :: Package Returning Multiple Strings

Apr 5, 2011

I have a function in PL/SQL that uses CTE to obtain several strings. I need to return those strings to C#, either as a Return Value or a OUTPUT parameter.

I've managed to do that using PLSQL Associative Array, but it has the unnecessary usage of Array Bind Size, and I do not need to know what will be my Array size. Is that any other way to do it?

My Package is the next one:

create or replace
PACKAGE Pkg1 IS
TYPE listResults is TABLE of VARCHAR2(100) INDEX BY BINARY_INTEGER;
PROCEDURE CalculateResults ( iCode IN VARCHAR2, iAg IN VARCHAR2, resultados OUT listaResultados );
END Pkg1;

create or replace
PACKAGE BODY Pkg1 AS
PROCEDURE CalculateResults(
iCode IN VARCHAR2,
[code]......

View 4 Replies View Related

SQL & PL/SQL :: Multiple Recipients Using UTL_SMTP Package?

Feb 17, 2004

how to send an email to multiple recipients. Is there a delimeter that i have to use to separate the email addresses, or do i have to do the utl_smtp.rcpt(c,email) mulitple number of times.

View 9 Replies View Related

SQL & PL/SQL :: Creating Views - Percentages And Multiple Tables

Oct 10, 2013

I'm having trouble with a little assignment.

"Create a view named vuPassFailRate that will show the pass rate and fail rates of each test."

I have a table named Test_ID containing the following columns:

TEST_ID
TEST_NAME
PASSING_GRADE

And another table named Test_History containing the following columns:

TEST_ID
STUDENT_ID
SCORE

I'm assuming i have to create an inline view, and to work out the pass/fail rates i need to do something along the lines of (For pass rate) Where SCORE is greater than or equal to PASSING_GRADE, and TEST_ID equals TEST_ID, divide by a count of SCORE and multiply by 100. I just cant work out the nested select statements, and work out the formula using two columns in two tables.

I have been staring at this problem for so long now i cant see the wood for the trees.

View 3 Replies View Related

PL/SQL :: Creating Views - Percentages And Multiple Tables

Oct 10, 2013

I'm having trouble with a little assignment. "Create a view named vuPassFailRate that will show the pass rate and fail rates of each test." I have a table named Test_ID containing the following columns:

TEST_IDTEST_NAMEPASSING_GRADE 

And another table named Test_History containing the following columns:

 TEST_IDSTUDENT_IDSCORE 

I'm assuming i have to create an inline view, and to work out the pass/fail rates i need to do something along the lines of (For pass rate) Where SCORE is greater than or equal to PASSING_GRADE, and TEST_ID equals TEST_ID, divide by a count of SCORE and multiply by 100. I just cant work out the nested select statements, and work out the formula using two columns in two tables. I have been staring at this problem for so long now i cant see the wood for the trees. 

View 2 Replies View Related

Application Express :: Creating Tree With Multiple Tables?

Jun 20, 2012

I want to do a tree view based on the following tables in APEX:

create table plattform (
id number,
name varchar2(200))
create table environment (
id number,
plattform_id number,
name varchar2(200))

[code].....

View 4 Replies View Related

PL/SQL :: Find Out In Database Package Where Package Is Installed

May 28, 2013

I need to find out in DB Package where this Package is installed (in which schema). The problem is this DB Package can be installed in various schemas. This means that I can't use select user from dual or system environment SYS_CONTEXT('USERENV', 'OS_USER').

What I would need is something like $$PLSQL_UNIT

View 8 Replies View Related

SQL & PL/SQL :: Global Exception In Package Available Outside Package?

Jan 15, 2012

I have a package with several procedures which raise and catch an error if a foreign key constraint has been violated. I put the the following code in my package body:

e_ouder_niet_gevonden EXCEPTION;
PRAGMA EXCEPTION_INIT(e_ouder_niet_gevonden,-2291);

Now all the procedures inside the package which catch this exception in the EXCEPTION block work fine. I would like to be able to use that exception outside of my package as well though, how would I do this?

View 4 Replies View Related

SQL & PL/SQL :: Min / Max Functions?

Jun 23, 2011

I have a table that looks like this...

APPOINTMENT
APPOINTMENT PATIENT DENTIST PROCEDURE AMOUNT
NUMBER NUMBER ID NUMBER DATE BILLED PAYMENT
20000001 3001 27 33100 11-Feb-2000 405.00 405.00
20000005 3988 23 11201 05-Mar-2000 80.00 0.00

[code]...

and i used this to find which dentist had the least appointment

select min(count(dentist_ID)) as "Minimum Number of Appointments" from appointment group by dentist_ID;

and this shows..
Minimum Number of Appointments
------------------------------
2

if there's a way where I can show the dentist's ID that corresponds to this "min number of appointment" i tried doing

select dentist_ID, min(count(dentist_ID)) as "Minimum Number of Appointments" from appointment group by dentist_ID;

but it gives me this error

ERROR at line 1:
ORA-00937: not a single-group group function

View 9 Replies View Related

SQL & PL/SQL :: Using Character Functions?

May 13, 2010

I would like to use character functions (LPAD, INSTR, SUBSTR, etc) to accomplish what i feel should be rather easy. I would like to take the following character strings:

'ADAM ST #3 S_109640'
'ALBANY AVE #5 & #6 S_AL5&6'
'ALBIN RD #10A S_123318'

And make them

'ADAM ST'
'ALBANY AVE'
'ALBIN RD'

As you can see, it probably boils down to using SUBSTR, INSTR, RTRIM, and LENGTH??

View 10 Replies View Related

SQL & PL/SQL :: Analytic Functions?

Jul 25, 2006

analytic functions regarding the ORDER BY part =)

SQL> ed
Wrote file afiedt.buf
1 select *
2 from (select deptno, ename, sal
3 ,dense_rank() over (partition by deptno order by sal desc) rank
4 from emp)

[code]...

why is that i just added ename on the ORDER BY part of the DENSE_RANK and then
SQL> ed
Wrote file afiedt.buf
1 select *
2 from (select deptno, ename, sal
3 ,dense_rank() over (partition by deptno order by sal desc, ename) ran

[code]...

ADAMS and WARD we're removed from the result, why is it? did it rank it as UNIQUE per sal and ename?

View 9 Replies View Related

SQL & PL/SQL :: Date Functions?

Dec 3, 2012

Trying to understand how Oracle DATE works and how it differs to TO_DATE in the below manner...

SELECT DATE '2012-10-25' FROM DUAL;
SELECT TO_DATE('2012-10-25','YYYY-MM-DD') FROM DUAL;

I've had a look through the Oracle Docs but can't find much on this. Basically I want to know how safe the first option is and if it does the same as the second. Also, where does DATE get the Format String from (NLS setting?). Can it be employed to compare a literal with a date that has a TIME portion set to something other than 12:00:00?

View 2 Replies View Related

SQL & PL/SQL :: Group Functions

Jul 2, 2010

count the no: of emp working under each manager? and instead of manager number display the manager name

View 5 Replies View Related

Example For Conversion Functions

Mar 27, 2012

Again i getting confused with conversion function especially Explicit data type conversions. some cases oracle server automatically converts the data to the required type. This is called IMPLICIT CONVERSION. Explicit conversions are done by using the conversion functions.

Oracle Explicit Data Type Conversions are

1 TO_CHAR
2 TO_DATE
3 TO_NUMBER

View 1 Replies View Related

Server Utilities :: Loading Multiple Input Files Into Multiple Tables

Jul 9, 2012

NGFID;RECTYPE;RECNAME
25;7;POLES
PARENT
CHILD;1401;9845075;2020
817;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 1
AREA_NAME = '3rivieres.export.ngf'

File :-mauri.export.ngf

NGFID;RECTYPE;RECNAME
257;7;POLES
PARENT
CHILD;1401;9845075;2020
8174;8;SUPPORT
PARENT
CHILD

Required output:-

AREA_SRNO = 2
AREA_NAME = 'mauri.export.ngf'....etc

CREATE TABLE NGF_REC_LINK
(
AREA_SRNO NUMBER(2),
AREA_NAME VARCHAR2(40),
NGFID NUMBER(20),
TABLENAME VARCHAR2(40),
PARENT VARCHAR2(200),
[code].......

find the ctl file (ngf_test.ctl) and modify the ctl file as per my requirement.

View 6 Replies View Related

Server Administration :: Multiple Database With Multiple Instance On Same Machine?

Mar 16, 2011

can we have multiple database version running in a single machine with multiple instances provided there are enough resources.Can we do in RAC only?

View 15 Replies View Related

SQL & PL/SQL :: How To Insert Data In Multiple Bases Using Multiple Database Links

Jan 2, 2013

how to insert the data in multiple bases( Same table structure in different bases) using the multiple database links?

View 4 Replies View Related

Extract All Employees Who Have 2 Functions

Feb 24, 2013

I have a table:

create table employee_function
(
id_emloyee number,
id_function number
);
with clients and their functions.

I want to extract all employes who has 2 functions (ex:id_function = 1 and id_function=2)

View 3 Replies View Related

SQL & PL/SQL :: Rewriting Without Analytic Functions

Nov 12, 2011

How can I rewrite this without the analytic functions?

SELECT employee_ID, first_name, salary,
RANK() OVER(ORDER BY salary desc) toprank_desc,
RANK() OVER(ORDER BY salary ASC) toprank_asc
FROM employees
ORDER BY first_name

View 2 Replies View Related

SQL & PL/SQL :: Importance Of Recursion Functions

Aug 31, 2012

we need to use recursion functions in pl/sql?? How can we use them in efficient way?? Also focus on mutual recursion in real world scenario.

View 4 Replies View Related

SQL & PL/SQL :: Restriction On Analytical Functions

Dec 6, 2012

Is there any way to apply the restriction on analytical functions, just like WHERE and HAVING .AS we know that we can apply the restriction on table by using WHERE and grouping functions by using HAVING clause .

For Ex: Departments wise count including all employees record :

SQL> select count(*) over(partition by deptno) dept_Count, ce.*
2 from scott.emp ce
3 order by deptno, job;

DEPT_COUNT EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ----- ---------- --------- ----- ----------- --------- --------- ------
3 7934 MILLER CLERK 7782 1/23/1982 1300.00 10
3 7782 CLARK MANAGER 7839 6/9/1981 2450.00 10
3 7839 KING PRESIDENT 11/17/1981 5000.00 10
5 7788 SCOTT ANALYST 7566 4/19/1987 3000.00 20
[code]....

View 4 Replies View Related

SQL & PL/SQL :: Create View With Functions

Jan 17, 2011

I have created a package with some functions in it.I want to create a view with from 2 tables but also i wanto to use the functions from some fields of the view. (e.g. if the wview has 10 fields the 5 of them will be from the 2 tables and the other from package functions).

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved