PL/SQL :: Find If Ename Is MILLER Then It Return Dname?
Feb 23, 2013create or replace function akk(p_empno number) return varchar2
is
cursor c1 is
select ename from emp where empno=p_empno;
v varchar2(10);
[code]...
ADVERTISEMENT
PL/SQL :: N.b-suppose Dname Is Not A Mandatory Column For Selection
Jun 20, 2012in one query i am selecting 3 columns from emp table
1)select ename,empno,dname from emp where ename='ABC'
in other query i am selecting 2 columns
2)select ename,empno from emp where ename='ABC'
my question is - if i added dname with the above two columns will it effect any performance of the query.
n.b-suppose dname is not a mandatory column for selection
JDeveloper, Java & XML :: Insert All 20,000 Ename In Table?
Mar 23, 2011inserting data in the table.
We are getting more than 20,000 rows (example: ename) from java and they want to insert all the 20,000 enames together in a table having single column ename. For ex:
create table emp(ename varchar2(10));
Currently they are doing
insert into emp values('raj');
insert into emp values('raja');
.
.
.
so on for 20,000.
They are saying that it hits the performance as we are doing insert for 20,000.
Do we have any way where we can insert all the 20,000 ename in one go.
SQL & PL/SQL :: Display Ename / Contact_no / Location Joining The Two Tables?
Apr 13, 2010i have two tables
create table tab1(
ename varchar2(10),
empid number(3) primary key);
SQL> insert into tab1 values('Ram', 101);
1 row created.
SQL> insert into tab1 values('Hari', 102);
1 row created.
SQL> insert into tab1 values('Shyam', 103);
[code]....
but i want like this
EMPID CONTACT_NO(PHONENO, MOBILENO)LOCATION
---------- -----------------------------------------
101 80123456 Banglore
101 9986234567 Banglore
102 809863728 Banglore
102 9032456578 Banglore
103 409863728 Hyderabad
[code]....
i want to display the ename,contact_no,location joining the two tables.
Application Express :: 4.2 Item - Validation Return Boolean And Return Error Text Are Switched
Oct 17, 2012In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.
Is there a simple statement that can be used to fix this in the apex dictionary?
SQL & PL/SQL :: Print Ename And Salary Of Employee Using Employee Number As Input
Aug 11, 2013/* Formatted on 2013/08/11 18:46 (Formatter Plus v4.8.8) */
CREATE PROCEDURE p_get_name (
p_empno IN OUT NUMBER,
p_name OUT VARCHAR2,
p_err OUT NUMBER
[code].......
Note:- I want to print ename and salary of emp using empno as a input but i dont want to declare extra variable for salary , i want to print salary using empno but when i execute this procedure. It gives value of empno in salary. Don't Know Why , how can i print salary of emp using empno as input without declaring extra variable for salary.
SQL & PL/SQL :: How To Return In One Row Only
Aug 21, 2012i need to have an SQL statement that will return only in one row.
in this SELECT statement,
select NVL(a.jj_crdr,'CR'),
nvl(a.CR_cnt,0) CR_cnt,
NVL(a.CR_amt,0) CR_amt,
NVL(b.jj_crdr,'DR'),
NVL(b.DR_cnt,0) DR_cnt,
NVL(b.DR_amt,0) DR_amt
[Code]...
it returned
Tot_cr tot_cr_amt tot_dr tot_dr_amt
1 100 0 0
because there is record for CR and there is no DR.
but when there's record in DR and no in CR, there's no record returned.
because the outer join is in the 2nd Select. i need to change the condition to: 'where a.jj_creator(+) = b.jj_creator' in order to return a record.
what i need is to be flexible so that it will return records even if one of the source doesn't have record.
SQL & PL/SQL :: XML - Return New Row For Each Variable?
Oct 31, 2013 I have some XML being returned from a web service, and it returns almost 900 variables. Whilst I am familiar with how to return these in a single row, do I can return a row for each variable? My DBA is very uncomfortable with creating a table with almost 900 columns, for obvious reasons. However, we already have plenty of tables with tens of millions of rows, so he's fine with that. I'll try and expand on the requirement. Below is some XML from the data returned to us:
<APPLICANT app_no="1">
<APPLSUMMARY>
<MAIN W="ZZ" X="{ND}"/>
<COUNTS Z="3" AB="0" BB="3" CB="0" DB="3" EB="3" FB="3" GB="0"/>
</APPLSUMMARY>
</APPLICANT>
I would like to be able to return a new row for each variable, For example:
VARIABLE | VALUE
----------------
W | ZZ
X | {ND}
Z | 3
And so on.
SQL & PL/SQL :: How To Return Just First Row Which Has Max Date
Sep 1, 2010This query returns 2 rows and the output is displayed as well. how I can return just the first row where the max end_date is 4/30/2011?
select
pt.customer_number,
pt.customer_name,
lease.lease_number,
lease.lease_name,
lease.property_name_disp,
lease.location_code_disp,
MAX(pt.end_date) end_date,
pt.attribute1 Disabled
[code]...
SQL & PL/SQL :: Min / Max With Multiple Row Return
Apr 22, 2010Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0930-Jun-09
abcman11-Jul-0931-Dec-09
abcman21-Jan-1031-Mar-10
abcman21-Apr-1031-Aug-10
I need to code a SQL statement (Not PL/SQL) to display following records:
Client managerCont. Start DateCont. End Date
abcman11-Jan-0830-Jun-08
abcman21-Jul-0831-Dec-08
abcman11-Jan-0931-Dec-09
abcman21-Jan-1031-Aug-10
PL/SQL :: Return More Rows Than Specified
Sep 30, 2013I am fetching records from many voluminous tables having multiple joins based on filter criteria filled from frond end application. As per the selected criteria, I want to have a pre-check if query would return more than 1000 rows then I have to show user a message saying that he should refine the search. Is there any performant way to query db and dynamically find record count and stops executions if it is going return more than the specified no of rows.
View 5 Replies View RelatedVarying Return Types
Feb 17, 2012I have two Oracle instances, both are 10.2.0.3..Executing the following results in two different results:
select N'Test" "result" from dual;
On one the output is:
resu
----
Test
and on the other the output is
result
----------------
Test
This seems to suggest that one is returning "Test" as a char(16) and the other as a varchar2 or nvarchar2. In both cases, NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16.
Not being an Oracle DBA, I am not sure where to look.
Function Return Parameters
Dec 5, 2006The function definition in PL/SQL has IN OUT parameter as well as return statement. Using both we can return the values. Basic definition of a function is function can return only one value at a time.
The question is, Can we return a number thru return statement and a char value thru INOUT parameter. Is it possible to return two different values using these?
How To Return Image From Oracle10xe
Aug 8, 2011i am storing image in label how to return for calling place.
View 1 Replies View RelatedReturn Minimum Value Of Resultset
Jul 22, 2010SELECT
REF.REFERRAL_TO,
REF.LOCAL_PATIENT_NUM,
REF.REFERRAL_REFERENCE_NUM,
TO_CHAR (REF.REFERRAL_DTE, 'DD/MM/YYYY') REFDATE,
TO_CHAR (OPC.CONTACT_DATE, 'DD/MM/YYYY') CONTACTDATE,
TO_DATE (OPC.CONTACT_DATE, 'DD/MM/YYYY') - TO_DATE (REF.REFERRAL_DTE, 'DD/MM/YYYY')
FROM
PAS.REFERRAL_DETAILS REF
FULL JOIN
PAS.OUTPATIENT_CONTACT OPC
[Code]...
This calculates the difference between a set date and a contact date, I only want it to return the minimum of the results set for each patient, how can this be achieved? At the moment it pulls off several records for each patient as there are several contacts.
SQL & PL/SQL :: Clob For Return In Block
Aug 24, 2010I want to execute a procedure and view its output. The procedurename is GET_clnts which has one of the parameters as clob where i am passing xmltype data to it.How can i execute the procedure to view the output. i can execute the SQL statement by itself, but not sure how to call a procedure from a plsql block..
DECLARE
clientid NUMBER;
OUT_RESULTSET clob;
BEGIN
GET_clnts ( clientid, OUT_RESULTSET);
DBMS_OUTPUT.Put_Line('');
END;
[code]...
SQL & PL/SQL :: Return Only One Row Of Data On Query?
Nov 28, 2012work_order unitid frommi tomi frompm topm
2666054111 06-154 77.000 85.000 77.370 null
2666054111 06-154 77.000 85.000 null 85.370
2666054111 06-154 77.000 85.000 null null
I used select distinct(work_order) to come up with the three different possible scenarios the problem is that i need all this information on a single row
work_order unitid frommi tomi frompm topm
2666054111 06-154 77.000 85.000 77.370 85.370
this is a conversion for distance. when i get this to work properly, it will generate reports on thousands of work orders with their converted distance markers.
SQL & PL/SQL :: Return Only Multiple Rows
Apr 30, 2010I need to return results for the following query only when there are two or more rows found by the following:
SELECT DISTINCT D.PUBLICATION,
D.ADI,
D.DISTRICT,
D.ACCOUNT,
D.ROUTE,
[code]......
Expected results would be:
1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 5 23-APR-10 48623
1 145 CRF TUANR000 VM BILLETING 16-APR-10 5 4 23-APR-10 48629
SQL & PL/SQL :: Return Column Name Corresponding To Values
Apr 28, 2011I want to search a some values in oracle table and then return the corresponding column names.
View 1 Replies View RelatedSQL & PL/SQL :: How To Return More Data From Function
Nov 14, 2011Following is the scenario:
CREATE OR REPLACE TYPE OBJ IS OBJECT
(
TEST_ID NUMBER(9),
TEST_DESC VARCHAR(30)
)
/
[Code]..
I WANT USED VALUE TEST_ID AND TEST_DESC THE EXISTING IN FUNCTION FN_MY_DATA WITH A VARIABLES :
DECLARE
X NUMBER(9);
Y VARCHAR(30);
BEGIN
X := -- VALUE TEST_ID EXISTING IN FN_MY_DATA;
Y := -- VALUE TEST_DESC EXISTING IN FN_MY_DATA;
END;
SQL & PL/SQL :: Function Return Two Values?
Aug 2, 2010whether a function can return two values?
View 4 Replies View RelatedSQL & PL/SQL :: String Return Type
Feb 17, 2012I have two Oracle instances, both are 10.2.0.3
Executing the following results in two different results:
select N'Test" "result" from dual;
On one the output is:
resu
----
Test
and on the other the output is
result
----------------
Test
This seems to suggest that one is returning "Test" as a char(16) and the other as a varchar2 or nvarchar2. In both cases, NLS_CHARACTERSET is WE8ISO8859P1 and NLS_NCHAR_CHARACTERSET is AL16UTF16.
Not being an Oracle DBA, I am not sure where to look.
SQL & PL/SQL :: To Return 100 Values By Using The Procedure
Aug 26, 2010I want to return 100 values by using the procedure. In that we have to declare 100 OUT parameters. So it's a time consuming process.
Instead of declaring 100 OUT parameters. How can we return 100 values in a single shot.
PL/SQL :: Query To Return Next 7 Dates
Oct 5, 2012is there a way to return the next 7 dates just using a query... for example, I need a query that returns:
select (I don't know that put here) from dual
Date
2012-10-05
2012-10-06
2012-10-07
2012-10-08
2012-10-09
2012-10-10
2012-10-11
If possible, I would like to know if there's a way to pass a date and based on it, the query returns the next 7 dates based on the passed date... for example:
select (I don't know that put here) from dual where date > '2012-10-15'
Date
2012-10-16
2012-10-17
2012-10-18
2012-10-19
2012-10-20
2012-10-21
2012-10-22
Why Dba_feature_usage_statistics Return No Rows
Aug 30, 2012I have a database in 10g in entreprise edition but i don't understand why the dba_feature_usage_statistics return no rows ? On some database when i look at dba_ feature_usage_statistics Protection Mode - Maximum Performance is used and active why?
View 4 Replies View RelatedSQL & PL/SQL :: Return A Result Set From One Database To Another?
Sep 4, 2012Is there any way to return a result set (using a Database Link) from one Database to another Database? Database A needs to call Database B and Database B needs to return a result set via a stored package/function. The stored procedure/function on Database B also accepts 1 input parameter. Something like "select test.test('some input parameter')@database_b from dual'.
View 39 Replies View RelatedPL/SQL :: Select Case When With More Than One Return Value?
Nov 6, 2012I would like to know, if there is a solution under 10g for something like this
select case when "C" = 1
then
(select 1,2,3,4,5 from dual)
else
(select 6,7,8,9,0 from dual)
end
from dual,
(select 1 "C" from dual)
What I really need is the full result of (select 1,2,3,4,5 from dual) or (select 6,7,8,9,0 from dual) if a condition as a result of a different sql-statement is true or false. It doesn't matter if the solution uses "case when" or anything else.
PL/SQL :: Open / Return Cursor?
Oct 31, 2013Oracle 11g For a procedure, if I use the hard-code procedure
sp_test( p_rc out sys_refcursor, p_1... p_2 ... ) is begin open p_rc for select * from TEST_TABLE... But now, I want to use the dynamically constructed sql, such as if condition1 then v_sql := ... elsif condition2 then v_sql := ... execute immedite v_sql
How can I return the the cursor p_rc in this case?
PL/SQL :: Return Values From Procedure
Dec 19, 2012the following procedure successfully compiled and executed. How to access the values returning from this procedure in another procedure or PLSQL block.
CREATE OR REPLACE PROCEDURE test IS
cursor c1 is SELECT a.idnumber, a.idp FROM holdings a;
r1 c1%rowtype;
BEGIN
open c1;
loop
fetch c1 into r1;
exit when c1%notfound;
DBMS_OUTPUT.put_line (r1.idnumber||'--'||r1.idp);
end loop;
close c1;
END;
PL/SQL :: Return XML Format With Grouping
May 14, 2013Allow me to preface this with the notice that I am not familiar with XML outside of its hierarchical structure, and am not familiar with what you can do with it using formatting.
As an example, let us say you have the following table:
Object_Type | Object_Name | Descriptor |
------------------------------------------------------------
Fruit | Apple | Crunchy |
Fruit | Orange | Sour |
Utensil | Pencil | Wooden |
Now let's say you want to query this table to return an XML format, which will be used in a web site to display the information, and you want to group the display by Object_Type, so that you want an XML format like this:
<Object Group>
<Object Type>Fruit</Object Type>
<Object>
<Object Name>Apple</Object Name>
<Descriptor>Crunchy</Descriptor>
[code]........
However, from what I can tell, using the XMLELEMENT function, it appears the closest I can get is following:
SELECT XMLELEMENT("Object Group",
XMLELEMENT("Object Type", object_type),
XMLELEMENT("Object",
XMLELEMENT("Object Name", object_name),
XMLELEMENT("Descriptor", descriptor)
)
)
FROM object_tbl;
<Object Group>
<Object Type>Fruit</Object Type>
<Object>
<Object Name>Apple</Object Name>
[code].........
Is it possible to group it in a way so that Apple and Orange end up in the the same <Object Group>? Or is that meaningless and such grouping can be done on the web site itself by formatting the XML?