SQL & PL/SQL :: Calling Function Which Calculate Sum Of Salary
May 8, 2010
I created a PROCEDURE in that i am calling function which calculate sum of salary...I just want Output in format for that which function i need to use...?
Actual Output:::
DEPt_Name SALARY
ACCOUNTING 8750
RESEARCH 10875
SALES 11600
I want Output in well alignment column...i WANT Output IN column format but my output in not geting in that format...Is there any function to align output...I want Output in well alignment column
I have to write function that receives department name and an aggregation operation (average, maximum, minimum) and apply the operation on the salary of employees working on the given department and return the result.
here is my select statement:
select distinct d.deptno, d.deptname, max(e.salary) from employee e join department d on e.deptno=d.deptno where d.deptname=upper('finance') group by d.deptno, d.deptname; [code]...
how can create function to calculate the vacation example if i takes 10 days vacation and Friday , Saturday only a normal vacation but The rest of the days is Within the vacation
how can calculate the vacation and make this function dynamic such as when takes the vacation 1-1-2011 and number of days vacation 10 not including the Friday, Saturday days
create or replace function getDate(p_joing_date Date,p_sysdate) Return Date; IS v_compltd_mnths; BEGIN SELECT into v_compltd_mnths MONTHS_BETWEEN(TO_DATE('sysdate','MM-DD-YYYY'), TO_DATE('joing_date','MM-DD-YYYY') ) "Months"FROM DUAL; return v_compltd_mnths; END;
I can easily calculate the standard deviation for some rows with the STDDEV function. But that returns me a value expressed in the same units as the things being measured. (In other words, if I have times of 1,12,6,11 and 8 seconds, I will be told that the average time is 7.6 seconds, and the standard deviation is 4.39 seconds).
What I'd like to be able to do is to say that the record with value 1 is (say) 3 std deviations away from the average. Or that the 6 second row is within 1 standard deviation.
I am trying to find records which are more than 3 standard deviations of the mean, because they are the outliers I am interested in.
I cannot see how to convert a STDDEV result into being a "number of standard deviations".
(If I have not expressed myself clearly, I'm using this sort of stuff URL....and I'm trying to find >3-sigma records)
simply to multiply the STDDEV result by 1, 2, 3 and so on, and then compare to the time? That is, is it OK to do the following:
Mean = 7.6 seconds (from my sample data mentioned in the above post) StdDev = 4.29 seconds
So 2sigma would be 4.29*2 = 8.78 seconds, plus or minus the mean?
So any record with a time value between -1.18 (7.6-8.78) and 16.38 (7.6+8.78) seconds would be within 2-sigma, assuming normal distribution?
I am still wondering if there's a nice function or something that returns the sigma value for any given set of records?
-- Create a table type of the table you want TYPE tbl_test IS varray(100) of VARCHAR2(30);
-- Function that will return the table type FUNCTION fnc_test RETURN tbl_test;
-- End package END;
CREATE OR REPLACE PACKAGE BODY pkg_test AS FUNCTION fnc_test RETURN tbl_test IS -- type table_name_va is varray(100) of VARCHAR2(30); -- Variable of the type tbl_test
[code]...
But i am having problem calling this to test it.
declare TYPE tbl_test IS varray(100) of VARCHAR2(30); var_tbl_test tbl_test; begin var_tbl_test:= pkg_test.fnc_test;
For procedures, we need to grant EXECUTE privilege.
For example:
grant execute on scott.process_salary to john;Lets say I have a function SCOTT.GET_EMPIDS and I want another user john to be able to invoke this fuction. So, what is the privilege that needs to be granted to John.
I tried to use external C procedure from the database and i did all required steps as below. 1. create a C program 2. compile and link the C program 3. copy it to the Oracle_home/bin directory 4. configure listener.ora and tnsnames.ora files
I am getting a (PL/SQL: ORA-00903: invalid table name) compile error in a procedure using a merge statement. I have seen many examples using this technique and am at a loss as to why I can't compile.
the pipelined function is:
FUNCTION f_crcli_pipe(pi_source_data IN sys_refcursor, pi_limit_size IN PLS_INTEGER DEFAULT pkg_crcli_variables.c_cursor_limit_def) RETURN CRCLI_AA PIPELINED PARALLEL_ENABLE(PARTITION pi_source_data BY ANY) IS
[code].....
the error is pointing to the TABLE function in the USING clause of the merge statement.
I want Oracle stored function/procedure to calculate number of working days between two dates. We need to exclude Firdays and Saturdays as there are weekend holidays and also exclude official holidasy that lie between two dates.
I'm trying to execute a dynamic sql that calls a function. But that function has inserts and deletes inside and this way it can't be called through a select statement. And to be worst, it has an other problem, my function uses a record type as parameter.
My code (sample): ----------------- DECLARE type r_parameters is record (cd_query cons_query_param.cd_query%type, cd_usuario cons_query_user.cd_usuario%type, nr_param cons_query_param.nr_param%type, vl_param varchar2(2000),
We have a daily batch job executing a oracle-plsql function. Actually the quartz scheduler invokes a java program which makes a call to the oracle-plsql function. This oracle plsql function deletes data (which is more than 6 months) from 4 tables and then commits the transaction.
This batch job was running successfully in the test environment but started failing when new data was dumped to the tables which happened 2 weeks ago (The code is supposed to go into production this week). Earlier the number of rows in each table was not more than 0.1 million. But now it is 1 million in 3 tables and 2.4 million in the other table.
After running for 3 hours, we are getting a error in java (written in the log file) "...Connection reset; nested exception is java.sql.SQLException: Io exception: Connection reset....". When the row-counts on the tables were checked, it was clear that no record was deleted from any of the tables.
Is it possible in oracle database, for the plsql procedure/function to be automatically terminated/killed when the connection is timed out and the invoking session is no longer active?
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:
CREATE OR REPLACE PROCEDURE TEST ( V_COUNT OUT NUMBER ) AS V_FOO NUMBER(2,0); BEGIN
[Code]...
There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................
I'm trying to call a Java function from within a PL/SQL aggregation function with a (array of) complex data type.
a) basic definitions (work and are ok)
CREATE OR REPLACE TYPE eventtype AS OBJECT ( relevance NUMBER, bid1 NUMBER,tid1 NUMBER,prob1 NUMBER, bid2 NUMBER,tid2 NUMBER,prob2 NUMBER, bid3 NUMBER,tid3 NUMBER,prob3 NUMBER, bid4 NUMBER,tid4 NUMBER,prob4 NUMBER, bid5 NUMBER,tid5 NUMBER,prob5 NUMBER, bid6 NUMBER,tid6 NUMBER,prob6 NUMBER ); CREATE OR REPLACE TYPE eventtuple IS TABLE OF eventtype; /
b) The following call works (but is not exactly that what I want):
public class pcompDP { public static NUMBER probComputation(oracle.sql.STRUCT pin) throws java.sql.SQLException { ... } } create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT) return BigDecimal'; / SELECT pcompDPFunc(eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)) from dual;
c) The following call doesn't work (but should have the intended semantics):
public class pcompDP { public static NUMBER probComputation(oracle.sql.STRUCT[] pin) throws java.sql.SQLException { ... } } create or replace FUNCTION pcompDPFunc(cin IN eventtype) RETURN NUMBER PARALLEL_ENABLE DETERMINISTIC AS LANGUAGE JAVA NAME 'pcompDP.probComputation(oracle.sql.STRUCT[]) return BigDecimal'; / SELECT pcompDPFunc(eventtuple(eventtype(2, 3, 0.234, 1, 1, 0.123, 0, 0, 0, 0, 0, 0, 0, 0, 0), eventtype(5, 6, 0.3, 10, 20, 0.45, 0, 0, 0, 0, 0, 0, 0, 0, 0))) from dual;
d) What's the error? Do I have to use JPublisher or is it possible without it? I guess an array should be possible without it.
The type of the parameter seems to be wrong. Question is whether in PL/SQL, Java or SQL.
A.Create object to get the salary hike of an employee on the basis of completion of years in an organization.
Conditions:- Create two tables with name as "Employee_yourname" and "Employee_Hike_Yourname". "Employee_yourname" can have columns like EMPID, NAME, SALARY, DATE_OF_JOIN. "Employee_Hike_Yourname" will have 3 more columns for new salary, Current salary and completed period in organization. Consider the employees who have completed minimum one year in organization. Salary hike for employee with tenure >= 1yr and < 2yrs should be 10% of current salary. If tenure is greater than or equal to two years and less than 3 years then salary hike should be 20% of current salary. For employees having tenure more than 3 years hike should be 30% % of current salary
I have a created data block using employees table so am trying to validate item salary using max_salary and min_salary from table jobs how to do this kind of validation.
[How can i validate input number into a Field of type char in oracle form?]
The above result give me minimum salary but total number of JOB holders, You can see only one SALEMAN getting 1000 but count show total number of SALESMAN. Similarly 3 MANAGERS are getting minimum and same salary but count show total number of MANAGERS.
My question is how can i get number of person on min salary?
