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:
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.
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.
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?
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.
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..
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
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.
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.
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
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?
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'.
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.
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?
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;
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:
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?
I was just wondering that do we have any function available in oracle which returns the last Friday of the month.
In our company we close our monthly books on last Friday of the month and there are few activities that we have to do on the following Monday as a part of month-end activity. Now the following Monday can fall on the same month or at times it falls on the following month.
I have to schedule a report to be sent to a user on the following Monday after the month-end. I need to schedule it using the cron job. We are using Oracle 9i on Linux platform.
I wonder if there is any way to return the columns of an select with its letters lowercase?
I have a piece of code that creates an script wich returns an SQL result to be confronted with some templates. My template have the column names in lowercase and because It is case sensitive the Uppercase returned by Oracle,
ID Product Color Time-In 1 Apple Green May 2 Apple Red April 3 Pear Green May 4 Pear Green April 5 Plum Blue June
In SQL I want to return all 4 fields of the records except those records where Product and Color are identical - in that case it should return the latest (by name of month - preferred) or just the first it finds
So I should get these
1 Apple Green May 2 Apple Red April 3 Pear Green May 5 Plum Blue June
If I do a select distinct then I will only get those fields I test on (product and color), not the rest.
