Difference Between Return Statuses For Report_object_status

Aug 9, 2010

What is the difference between these return statuses for Report_object_status:

Canceled, Invalid_job, terminated_with_error

View 2 Replies


ADVERTISEMENT

PL/SQL :: Attribute In DB - Return Difference Between Start_date And Current Date

Feb 15, 2013

I have an attribute in my DB called start_date of type date.I want a query that return the difference between the start_date and the current date.for example if start_date = 2/14/2013 *2*:35:00 PM and the current date = 2/14/2013 *1*:35:00 PM the query returns 1.

I want the result in hours.

View 13 Replies View Related

Forms :: 6i - REPORT_OBJECT_STATUS Cannot Be Null

Jan 21, 2010

I have an existing form and report. We recreated our testing environment and now when running any report I first get the following error: REP-3002: Error initializing printer. make sure a printer in installed.

This error pops up when calling RUN_REPORT_OBJECT.

The next line of code checks the report status by using REPORT_OBJECT_STATUS. This check throws out error: FRM-40738: Argument1 to builtin REPORT_OBJECT_STATUS cannot be null.

This indicates that my FIND_REPORT_OBJECT is not returning a value.

All these reports and forms used to work fine on our old testing environment and also works on our live database.

View 4 Replies View Related

Application Express :: 4.2 Item - Validation Return Boolean And Return Error Text Are Switched

Oct 17, 2012

In 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?

View 1 Replies View Related

PL/SQL :: Calculate Difference By Which Day Count Of Difference Is Going?

Oct 17, 2012

Detail table will look like below:

Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....

Need the data like

Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)

10/10/2012 3 4 1 4 2 1 0 0

How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?

View 3 Replies View Related

SQL & PL/SQL :: How To Return In One Row Only

Aug 21, 2012

i 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.

View 10 Replies View Related

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.

View 2 Replies View Related

SQL & PL/SQL :: How To Return Just First Row Which Has Max Date

Sep 1, 2010

This 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]...

View 19 Replies View Related

SQL & PL/SQL :: Min / Max With Multiple Row Return

Apr 22, 2010

Client 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

View 4 Replies View Related

PL/SQL :: Return More Rows Than Specified

Sep 30, 2013

I 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 Related

Varying Return Types

Feb 17, 2012

I 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.

View 1 Replies View Related

Function Return Parameters

Dec 5, 2006

The 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?

View 1 Replies View Related

How To Return Image From Oracle10xe

Aug 8, 2011

i am storing image in label how to return for calling place.

View 1 Replies View Related

Return Minimum Value Of Resultset

Jul 22, 2010

SELECT
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.

View 2 Replies View Related

SQL & PL/SQL :: Clob For Return In Block

Aug 24, 2010

I 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]...

View 2 Replies View Related

SQL & PL/SQL :: Return Only One Row Of Data On Query?

Nov 28, 2012

work_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.

View 3 Replies View Related

SQL & PL/SQL :: Return Only Multiple Rows

Apr 30, 2010

I 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

View 19 Replies View Related

SQL & PL/SQL :: Return Column Name Corresponding To Values

Apr 28, 2011

I want to search a some values in oracle table and then return the corresponding column names.

View 1 Replies View Related

SQL & PL/SQL :: How To Return More Data From Function

Nov 14, 2011

Following 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;

View 3 Replies View Related

SQL & PL/SQL :: Function Return Two Values?

Aug 2, 2010

whether a function can return two values?

View 4 Replies View Related

SQL & PL/SQL :: String Return Type

Feb 17, 2012

I 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.

View 6 Replies View Related

SQL & PL/SQL :: To Return 100 Values By Using The Procedure

Aug 26, 2010

I 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.

View 1 Replies View Related

PL/SQL :: Query To Return Next 7 Dates

Oct 5, 2012

is 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

View 13 Replies View Related

Why Dba_feature_usage_statistics Return No Rows

Aug 30, 2012

I 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 Related

SQL & PL/SQL :: Return A Result Set From One Database To Another?

Sep 4, 2012

Is 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 Related

PL/SQL :: Select Case When With More Than One Return Value?

Nov 6, 2012

I 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.

View 6 Replies View Related

PL/SQL :: Open / Return Cursor?

Oct 31, 2013

Oracle 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?

View 2 Replies View Related

PL/SQL :: Return Values From Procedure

Dec 19, 2012

the 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;

View 14 Replies View Related

PL/SQL :: Return XML Format With Grouping

May 14, 2013

Allow 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?

View 5 Replies View Related

PL/SQL :: Get A Return Value Of 0 Whenever There Is No Matching Record?

Mar 27, 2013

In PL/SQL, the following sql causes an error when there is no record matching the search criteria:

-----
select pin_number from table1 where id = 1;
-----
error: No Data Available

Is there a way to get a return value of 0 whenever there is no matching record?

View 3 Replies View Related







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