SQL & PL/SQL :: Importance Of Recursion Functions
Aug 31, 2012we 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
ADVERTISEMENT
Importance Of Public And Virtual IPs In Etc / Hosts File When Dealing With Oracle RAC
Mar 12, 2011When working against the Oracle 10G/11G RAC database it's important to have all the relevant entries in the etc/hosts file.
Thus, when running the following code against the database sometimes it finishes with "Ok!" and sometimes getting the error below:
CODEimport java.sql.*;
public class TestDBOracle {
public static void main(String[] args)
[Code].....
The reason is missing RAC-related entries in the etc/hosts.
When dealing with Oracle RAC it should be both entries for the public and for the virtual IPs of the nodes in the etc/hosts file.
CODEA.A.A.A NODEA
A.A.A.B NODEB
...
B.B.B.X NODEA-VIP
B.B.B.Y NODEB-VIP
...
Recursion - Concatenation On Database?
Nov 27, 2009We are using Oracle 11g (11.1.0).I'm not all that prolific when it comes to writing queriesI have a table...
Table1
-----------------------------------------
oid narr parent
-----------------------------------------
1 some narrative null
2 more narrative 1
3 a bit of test narrative 2
Simply put, I need a query that will recurse up through each rows parent and return the concatination of all parents and itself narrative.
Expected output
------------------------------------
some narrative
more narrative some narrative
a bit of test narrative more narrative some narrative
The requirement is that this is one single query as it will be called from a third party application we are using. We need this recursion and concatenation to be done on the database, as while we have control over the database queries that get executed we have no control over the internal source of this third party applications.I have been digging around for a bit, I have tried using a combination of JOINS and UNIONS but keep hitting a brick wall.
The best I could come up with is...
SELECT concat(n1.narr, n2.narr) FROM table1 n1
JOIN table1 n2 ON n1.oid = n2.parent
But this only returns two lines, for oid's 2 and 3, and only concatinates with the immediate parent.
SQL & PL/SQL :: Find All Data Items In Hierarchy Recursion?
Jan 21, 2003I need to find all "report to" supervisors in a hierarchical chain. A binary search won't work, as each level may contain > 2 supervisors. Example:
1
2 3 4
5 6 7 8 9
10
10 reports to 9
9 reports to 4
5 and 6 report to 2
7 and 8 report to 3
2, 3 and 4 report to 1
Result for all reporting to 1 would be a table containing 2, 3, 4, 5, 6, 7, 8, 9, 10 (order is unimportant!).
I think I need to use recursion. I can do this in C; need code for PL/SQL??? (I can't get the "tree" to display well!)
SQL & PL/SQL :: Min / Max Functions?
Jun 23, 2011I 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
SQL & PL/SQL :: Using Character Functions?
May 13, 2010I 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??
SQL & PL/SQL :: Analytic Functions?
Jul 25, 2006analytic 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?
SQL & PL/SQL :: Date Functions?
Dec 3, 2012Trying 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?
SQL & PL/SQL :: Group Functions
Jul 2, 2010count the no: of emp working under each manager? and instead of manager number display the manager name
View 5 Replies View RelatedExample For Conversion Functions
Mar 27, 2012Again 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
Extract All Employees Who Have 2 Functions
Feb 24, 2013I 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)
SQL & PL/SQL :: Rewriting Without Analytic Functions
Nov 12, 2011How 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
SQL & PL/SQL :: Restriction On Analytical Functions
Dec 6, 2012Is 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]....
SQL & PL/SQL :: Create View With Functions
Jan 17, 2011I 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 RelatedSQL & PL/SQL :: Aggregate Functions On A Join
Nov 7, 2012I am trying to Show all Customers (custId, last name, first name) who bought cars at the Arctic AND Pacific branches and Order it by CustId.
These are the tables:
VIN CARTYP BRNAME BUYERID SALEDATE
------- ------ ---------- ---------- -----------
1JJ0001 CT9015 Arctic
1NN0001 CT9014 Arctic 10010 18-mar-2012
1PP0001 CT7013 Arctic 10007 25-sep-2012
1QQ0001 CT7012 Atlantic 10014 22-dec-2009
1RR0001 CT7011 College 10013 30-sep-2012
2JJ0001 CT9015 Pacific
2NX0001 CT9014 Pacific
2PP0001 CT7013 Arctic
2QQ0001 CT7012 Arctic
[Code]...
CUSTID LNAME FNAME BALANCE_DUE
---------- ------------ ------- -----------
10001 Disney Daisy 9000.5
10010 Dupont Caryn 40000
10006 Gordon Diane 75000.5
10005 Gordon Tracy 3000.25
10013 Gross Henry 50000
10015 Hilton Kate 40000
10011 Lea Diane 12000
10003 Lopez Athena
[Code]...
No matter how I write the query, I get the message that no rows have been selected.
SQL & PL/SQL :: Decode / Case Functions?
Aug 19, 2010I am having some records in the table. If the record num is
1--It should show the month as "Apr"
2--"May"
3--"Jun"
4--"July"
5--"Aug"
6--"Sept"
if it is having other than these 6 should show "0" for the remaining months.
SQL & PL/SQL :: Group Functions In Joins
Jul 29, 2013i want to get SUM(salary) by combining both employee and employees table.Look my table structure below:
SQL> select * from employee;
EMPNO ENAME HIREDATE ORIG_SALARY SALARY R MGR DEPTNO
---------- --------------- --------- ----------- ---------- - ---------- ----------
1 Jason 25-JUL-96 1234 8767 E 2 10
2 John 15-JUL-97 2341 3456 W 3 20
3 Joe 25-JAN-86 4321 5654 E 3 10
4 Tom 13-SEP-06 2413 6787 W 4 20
5 Jane 17-APR-05 7654 4345 E 4 10
6 James 18-JUL-04 5679 6546 W 5 20
7 Jodd 20-JUL-03 5438 7658 E 6 10
8 Joke 01-JAN-02 8765 4543 W 20
9 Jack 29-AUG-01 7896 1232 E 10
[code]....
Above, i used separate queries to get the result of SUM(salary) by deptno.Here, I want a single query to get SUM(salary) with deptno.
deptno Sum(salary)
----------------------------
10 30056
20 27132
30 6300
40 4300
SQL & PL/SQL :: How To Use Analytic Functions In Case
Mar 18, 2010DATA is like this;i want to insert to same set of rows again with increasing the MAIN_SEQ with 4,5,6.
DOCUMENT_ID MAIN_SEQ
VSISLG401 1
VSISLG401 2
VSISLG401 3
DATA is like this;i want to insert to same set of rows again with increasing the MAIN_SEQ with 4,5,6.
DOCUMENT_ID MAIN_SEQ
VSISLG401 1
VSISLG401 2
VSISLG401 3
INSERT INTO TEMP_TEST
[code]...
SQL & PL/SQL :: Reference List In Functions
Jun 6, 2011I use some quantity of functions with list of account id like in example below. Some functions use the same account id list, some use another. Everything works fine except those days when changes come and lists should be updated. Then I should edit each function... I think about creating new table for reference list like
CREATE TABLE MYREFERENCELIST
AS
SELECT XXXX AS ACCOUNTID, YYYY AS LIST1 FROM DUAL
UNION ALL
SELECT ZZZZ AS ACCOUNTID, UUUU AS LIST2 FROM DUAL
FUNCTION ACCOUNTID
(arc_date date,
cid number )
RETURN NUMBER
AS
[code]..........
SQL & PL/SQL :: FUNCTIONS - Bind Variables?
Nov 20, 2012Create and invoke the GET_JOB function to return a job title.
a.Create and compile a function called GET_JOB to return a job title.
b.Create a VARCHAR2 host variable called b_title, allowing a length of 35 characters. Invoke the function with job ID SA_REP to return the value in the host variable, and then print the host variable to view the result.
This is my FUNCTION:
CREATE OR REPLACE
FUNCTION GET_JOB(
p_jobid IN jobs.job_id%TYPE)
RETURN VARCHAR2
[code]...
This is how I invoked the FUNCTION but WHILE DECLARING THE BIND VARIABLE IT IS GIVING ME AN ERROR!!!!!
VARIABLE b_title VARCHAR2(35)
set serveroutput on
DECLARE
P_JOBID VARCHAR2(10);
v_jobtitle VARCHAR2(200);
[code]...
PL/SQL :: Get Multiple Records In FUNCTIONS?
Aug 16, 2012I 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].....
XML DB :: Xpath Functions In Oracle
Aug 24, 2012Is there any function like fn:local-name(.) in oracle XPATH functions. I am new to Oracle & migrating procedure from SQL server to oracle where procedure taken XML script as input.
I need to convert below SQL server select statement to Oracle.
SELECT converseUID,
'Distributed',
cast(ServiceCalls.query('fn:local-name(.)') as varchar(100)),
1,
WIP.ServiceCalls.value('(TotalTime)[1]','int'),
WIP.ServiceCalls.value('(Status)[1]','varchar(100)'),
[code].......
PL/SQL :: Analytical Functions In Group By
Sep 27, 2012I have simplified this for ease of understanding. I have a Data column and a Month_ID column like this:
Values Month_ID
--------- -------------------------------------------------------
AAA 1
BBB 2
I split this out to values per year like this
Value_2011 Value_2012 Month_ID
-------------------------------------------------------------------------
AAA 1
BBB 2
Now i am trying to get the max(Value_2011) keep (dense_rank Last order by Month_ID) but i get a NULL. I can understand its because the Month_ID accomodates all years but i only need it to look at Month_ID for 2011 and return me the last dense_rank value, how can i achieve this?
I tried a couple of different methods like Last_Value() but i have group by in my original statement and i think analytical functions dont like GROUP by if they are not part of it. How can i achieve this?
Analytic Functions To The Rescue
Oct 21, 2010I have a table (events) with this structure: customer_id, event_id, ... For each customer_id there can be several rows in the table. I need to run a query of the format: select customer_id, expensive_function(customer_id),... from events.
The expensive_function to be applied to customer_id in the query is really expensive (a Java class calculating a check sum) and the events table has billions of rows.
Rows in events table have same customer_id for a few rows, then continue with a different customer_id ang again coming back to the first, etc.
I was thinking that it should be a way to trigger calculation of expensive_function only when customer_id changes, in order to reduce the number of calls. Only my knowledge about SQL is not going that far and I cannot use PL/SQL or any other procedural language, need to stick to standard SQL (or Oracle version of it).
PL/SQL :: Aggregated Functions Moving Value One Row Down
Feb 7, 2013this query
SELECT
shrtgpa_pidm,
shrtgpa_term_code,
sum(shrtgpa_hours_earned) over (
partition by shrtgpa_pidm
order by shrtgpa_term_code
[Code]....
retrurns this rsult
82724 201010 4.5 201010
82724 201020 9 201020
82724 201110 13.5 201110
82724 201120 17.5 201120
82724 201310 20.5 201310
I need to be able to displayu the 4.5 in 201020 9 in 201110 in another words I need to move the results one term down
how I can do this?
I am thinking in a sequence? so 201010 will be seq 1
201020 will be seq 2
201110 will be seq 3
etc..etc..the I can do an update
Can this be done with aggr sql func? If not how I can add a sequence in the query It needs to break by shrtgpa_pidm
I need to end with these results
201020 4.50
201110 9.0
201120 13.50
201210 17.50
201220 21.50
here is the code to withe a table with data
create table testgpa
(
testgpa_pidm NUMBER(8) NOT NULL,
testgpa_term_code VARCHAR2(6 CHAR) NOT NULL,
testgpa_hours_earned NUMBER(9,3) NOT NULL
)
[Code]....
Doesn't Calculate Any Excel Functions
Jul 26, 2010My applications is integrate with Excel 2003 by Ole2 package. Some of them excel books are using functions activates with Excel Complements (activate with men options: Tools-Complements-Analyisis Complements and Analysis complements VBA).
The problem is when I invoke this excel book in Oracle Forms, this functions don't work.
Analytic Functions / TEMP Usage?
Jan 26, 2011I am building a reporting table using the count analytic function in order to count up several different attributes in one statement.What I find is that this method quickly eats up my TEMP space. This is 10gR2. I have attempted to use MANUAL workarea policy with as large ofsort_area_size as possible (2G) but that does not seem to have any effect on performance or TEMP usage. The RAW table is about 12G with 75 million rows. I am not that concerned about execution time, but rather TEMP usage.
--INSERT into <object>...
select distinct
file_sid,filename,control_numb,processing_date,file_class,
vendor_id,vendor_desc,
c_status_id,c_status_desc,
[code]...
I am not seeing any increase in onepass or multipass executions on the PGA during execution of this statement using...
SELECT CASE WHEN low_optimal_size < 1024*1024
THEN to_char(low_optimal_size/1024,'999999') ||
'kb <= PGA < ' ||
(high_optimal_size+1)/1024|| 'kb'
ELSE to_char(low_optimal_size/1024/1024,'999999') ||
[code]...
I'd like to get a better explaination of how analytics use the instance resources and TEMP space. For example if I add
a count with a different window (such as the last two columns commented in the above query) I blow out my temp space (70G).
Is the critcal factor the use of distinct? or multiple windows? or something else?
Forms :: 10g - Using Form Only For Query-functions?
Aug 17, 2011I want to use my form only for query-functions.
When one enters the form, he can look up records either on Companyname or on Projectname. Therefor I have provided 2 buttons who pop-up a LOV. After eitherone is selected, the query has to be executed. There is a Master-Detail relationship between Company and Project.
My plsql for the company button:
declare
v_show_lov boolean ;
begin
enter_query;
v_show_lov := show_lov('LOVFIRMA');
if not v_show_lov then
[code]....
Plsql for the project button:
declare
v_show_lov boolean;
v_get_value number;
begin
v_show_lov := show_lov('LOVPROJECT');
if not v_show_lov then
[code]....
The first button only works when I go manually in Querymode first (by pressing F11). So I reckon my enter_query doesn't work? The property 'Fire in Enter-Query Mode' is Yes.
When I press it in non-query mode. It just fills in the LOV-values and the CCODE from company. It doesn't execute the query (probably because there is no enter_query).
When I enter query mode, the focus changes automatically to Company. And the LOV doesn't appear.
I have tried placing the enter_query on different places, just as the go_block and clear_block things, but there is always something wrong.
SQL & PL/SQL :: Overriding Pre-defined Functions In Oracle
Apr 11, 2012Can we override the pre defined functions in oracle. I am able to do this in my schema. But if DB server contains more than one schema do I need to write the same procedure in all schemes.
we can call the same procedure with schema name from different schemes also . But is there any way to over ride the actual function provided by oracle (just like over riding the equals method of object class in java).
create or replace function to_number(var varchar2) return number
is
no number := var;
begin
return 99999;
end;
SQL> select to_number(' 234 ') oracle_function , dbo.to_number(234) user_function from dual;
ORACLE_FUNCTION USER_FUNCTION
--------------- -------------
234 99999
SQL & PL/SQL :: How To Pass Dynamic Columns In Functions
Aug 16, 2010I am facing with one problem while creation of function.
CREATE OR REPLACE FUNCTION fun1(
v_finyear VARCHAR2,
v_yrno NUMBER,
[Code].....
The function created successfully...
But it return the wrong values, like
SQL> SELECT fun1('2004-05', 8, 'FEB')
2 FROM DUAL
3 ;
TB('2004-05',8,'FEB')
--------------------------------------------------------------------------------
FEB
SQL> spool off;
2004-05 is the value of TABLE1
08 is also value of TABLE1
FEB is the Column_name of TABLE1
Actually, i have a value in FEB month, but it return FEB. FEB is the column_name of 'TABLE1'