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
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.
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
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;
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.
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.
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]......
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.
"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.
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.
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').
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:
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?
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:
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?
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?
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.
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
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]....
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).
