I have created a amortization SELECT statement that amortizes loans based upon a table of loan information. The problem is this: when I run an ITERATION on numbers, the select statement returns a value but the ITERATION seems to be reading zero. This leads to a bunch of zeroes in the resultant table.
P C_USER1 C_USER9 C_USER11 C_USER10 C_B1 C_B2 C_BUDGET C_USER12
0 Jones 131828.81 3.348 72 0 0 131828.81 2023.55
0 Jones 131828.81 3.348 1 367.8 -367.8 132196.61 2023.55
As you can see, in column C-B2, despite referencing the column C_USER12 which should have 2023.55 as a value in it, it is returning the mirror opposite of C_B1 which leads me to believe that the system is reading a ZERO in the column despite displaying 2023.55.
I am trying to use model clause to get comma separate single row for multiple rows. My scenario is like this:
SQL> desc test1 Name Null? Type ----------------------------------------------------- -------- ------------------------------------ ID NUMBER VALUE CHAR(6)
SQL> select * from test1 order by id;
ID VALUE ---------- ------ 1 Value1 2 Value2 3 Value3 4 Value4 5 Value4 6 7 value5 8
The query that I have is: SQL> with t as 2 ( select distinct substr(value,2) value 3 from test1 4 model 5 ignore nav 6 dimension by (id) 7 measures (cast(value as varchar2(100)) value) 8 rules 9 ( value[any] order by id = value[cv()-1] || ',' || value[cv()] 10 ) 11 ) 12 select max(value) oneline 13 from t;
I have been testing some functionalist, I have been trying to get the updated dept no values into an array and then print the count. But i am getting the following error.
I have implemented, the whole example is to know about 'dynamic sql returning clause into collection'
s@ORCL> select * from t_dept;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 comp NEW YORK 20 Compt DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 60 Comp Ahaaa 80 data ab 80 data ab 80 data ab 80 data ab
9 rows selected.
s@ORCL> ed Wrote file afiedt.buf
1 declare 2 type tp_dept is table of number; 3 arr_dept tp_dept; 4 begin 5 execute immediate q'['update t_dept set dname = 'Pointers' where deptno = 80 returning deptno into :out]' 6 returning into arr_dept; 7 dbms_output.put_line('The count is '||arr_dept.count); 8* end; s@ORCL> / returning into arr_dept; * ERROR at line 6: ORA-06550: line 6, column 19: PLS-00597: expression 'ARR_DEPT' in the INTO list is of wrong type ORA-06550: line 5, column 4: PL/SQL: Statement ignored
DECLARE cnt number(10); BEGIN SELECT COUNT(*) INTO CNT FROM TBL_ADDRESS WHERE ADDRESS_ZIP IN (SELECT * FROM TABLE(MY_PACK.STR2TBL('46227'))); DBMS_OUTPUT.PUT_LINE (cnt); END;
MY_PACK.STR2TBL() is a function which takes '|' delimited string, extracts values and returns a table of zipcodes. The function works fine and returns 46227 but the count returned is 0 instead of 280(count returned by replacing inner select with '46227').
We use apex and my professor wants us to import a table model into apex. everytime i try and import my table it says "your export file is not supported". i asked my professor what to do and he said "You should gen a ddl file from designer. Then use that to upload into apex and then run.
I have one report consist of two user parameters like FROMDATAE and TODATE and two queries in data model..
The 1st query is..
SELECT WONO,MCV_DATE,QTY FROM MCSHOP1 WHERE MCV_DATE BETWEEN :FROMDATE AND :TODATE;
It created two user parameters.ie FROMDATE and TODATE.
And 2nd query like this
SELECT MCVN FROM MCSHOP1 WHERE WONO=:WONO OR WONO LIKE 'RW%'||:WONO;
I dont know how to make 2nd query in data model. becoz the WONO will come from 1st query and LIKE command is there..But I tried in formula Column....but it returns more than one row...
URL....Topic: The Execution Model for Triggers and Integrity Constraint Checking
Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:
1.Run all BEFORE statement triggers that apply to the statement. 2.Loop for each row affected by the SQL statement. a.Run all BEFORE row triggers that apply to the statement. b.Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.) c.Run all AFTER row triggers that apply to the statement. 3.Complete deferred integrity constraint checking. 4.Run all AFTER statement triggers that apply to the statement.
We would like to used Oracle secure backup using SAS interface on an IBM Autoloader Model TS-2900. Would it be possible to configure oracle secure backup using SAS?
For applications and Timesten databases on the same server we can use direct model to gain the base performance. But I want to know that how big heap size of JVM to be set for my java application when enabling direct model?
Does my application need more head memory when direct model than other local communication protocols, such as Unix domain socket or IPC? Supposing my Timesten database takes 12GB memory from OS, does it mean I need specify the same size for JVM heap(-Xmx12G)?
I'm trying to add edmx file in my project for first time. I want to choose the oracle provider ODP.net but cannot find Oracle in the data source list. I have oracle 11g installed , odp and odt installed and can access it from the solution as well. I saw the Oracle listed under data source when I tried to connect the solution to the database through server explorer. The solution is connected to Oracle database through ODP.
In a pl/sql procedure, when I am doing an update, I need the old value to be returned and stored in a local variable, so that the same can be used for future purpose.
Note : I know the "OLD:" option is present when we use TRIGGER, but in my case , the table I am updating is a old table and I am not permitted to create a trigger for it.
create table testing ( id number (10), key number (10) ) insert into testing values (1,10) insert into testing values (1,10) insert into testing values (2,10) insert into testing values (2,20) insert into testing values (3,10)
i got a table(deptid, deptname, address,city,zip, state, other columns) i want to write a query to determine any error(records with different values) because i expect all records grouped-by(deptid, deptname, address,city,zip, state) to have the same deptid(pls note that deptid isnt unique),
or a specific deptid should have only one record based on the grouping (deptid, deptname, address,city,zip, state),
I have a table which lists previous jobs, whcih has monthto,yearto, monthfrom and year from fields. I am trying to find the most recent job (of which they might be multiple ending on the same month) Initially, i did the following:
ran a sub query to find the most recent (employee id, max (yearto * 12 + monthto), then another query where I got a list of the most recent jobs, and returned the first one using rownum = 1 (note this may be inconsistent between runs as thee rownum is calculcated before nay ordering clause. This worked, but i figured using the first function might be better as it gives an order
select employee_id from (select employee_id ,max(NVL(yearto,0) *12 + NVL(monthto,1)) KEEP (DENSE_RANK FIRST ORDER BY emloyer) as latest from employees group by employee_id;
but this seems to return more than one entrty:
table date below: drop table employee_list; drop table employee_historic; [code]...
What is the best way to return a single row, if the primary key is not available / applicable.
I am working on a script in which I want to retrieve multiple rows but I get error ORA-1422.I tried solving it using the following script , but it still gives error.
CREATE OR REPLACE PROCEDURE proc_query DECLARE TYPE all_dest IS TABLE OF NUMBER; destIds all_dest; BEGIN SELECT dest_id from sb_packet WHERE src_id = 32; RETURNING dest_id bulk collect into destIds; END;
For the customer ID 123 I want to return Z1, for customer 678 I want to return S2 and for customer ID 345 I want 11
Problem is that I'm new to the concept of looping. I know how to write a function that accepts customer_id as a value write a cursor and then check IF hierarchy = 1 the return FUNCTION_CODE IF hierarchy - 2 THEN ...
but I need something more universal as some of the customers may have hierarchy function 1 and that would be the top one for him but others might have function of hierarchy 10 as top and checking all of the possibilities using if would be just stupid. So how to write something universal ? And of course if function did not find any customer_id then return null.
I need to return which hour for a given date range had the most calls. I have a query that works but it is inelegant and I'm pretty sure I could do better. I'm pretty new to analytic queries so go easy...
select hour, calls from ( select hour, calls, rank() over (ORDER BY calls desc) as ranking from (
I'm trying to query an array where we will have mathnames that have the follow format: variablemathname[00000]
have been digging around for a few days to try and simplify my existing query. As of right now i'm hacking it together to bring back the bracketed array value by using INSTR and SUBSTR. This works and gets me the correct results but I want to clean the code up by using regexp_SUBSTR.
In my reading up on regular expression I've tried to create my pattern by using [.] which I believe to be [any character]. I want it to start at the beginning of the string so I've used [^ and I only want the one occurrence so I've ended my expression with ]
I tried using the escape before my pattern as I know that [ is a metacharacter but I receive the same results.i'm trying to use to get the expression correct.
SELECT REGEXP_SUBSTR('variablemathname[00000]', '[.],[^,],') RESULT FROM DUAL;
My expectation is it will bring back the following [00000] but the way it is written now is bringing back nothing.
I've just started with the Oracle SQL and come from a heavy MS SQL background and I understand that here are some natural differences in the syntax but I'm stumped as to why the following sql represents a problem:
update MASTERMICODES t1 set t1.TEMPTA = ( select t2.TAFCODE from TA_FEATURES t2 where t2.FCODE = t1.FCODE )
It returns Error report: SQL Error: ORA-01427: single-row subquery returns more than one row 01427. 00000 - "single-row subquery returns more than one row" *Cause: *Action:
I want it to return more than one row...in fact I want it to make on all rows that have the same fcode between tables.
I understand what the message "subquery returning multiple rows" means but I have a case where I'm not 100% sure why it's happening to my update query (which in turn probably means I don't fully understand what's going on behind the scenes)
Here is my query:
Update A set (A.id, A.alt_name, A.min_rank)= (SELECT B.id, B.fullname, MIN(B.nm_rankval) FROM B, A WHERE A.id = B.id AND A.name <> B.fullname AND B.nametyp = 'ON' GROUP BY B.id, B.fullname) ;
The subquery returns 6 rows but they are all unique in terms of the id, name, rankval, etc...I naturally thought that the update statement wouldn't have a problem with this since the subquery is returning rows that are not duplicates and match one for one between table A and B. I used the group by to ensure I return unique values from table B (which does have duplicate id values)
Each of those 6 rows from the subquery of table B can be matched 1-1 with table A...so what am I missing.
I work on a client-server application, where users need to be able to run rather complex queries.
We currently have most of the queries defined in views on the Oracle database server and the client application simply downloads the data (i.e. SELECT * from example_view). This is good for us as we can maintain these queries without releasing new versions of the client tool.
However we have some queries implemented by a colleague that have caused a lot of trouble (efficiency and quality) and these are stored client-side.
The issue I have is that these client side queries can return records in different units (i.e. in standard cubic metres, or barrels of oil etc), as the SQL is defined at runtime on the client, and I want to know the best way to replicate this with SQL stored server-side.
The client-side SQL has column definitions such as: SELECT oil_production * decode(&unit,'Nm3',.948,'Sm3',1,'MMBOE',6.0924,1) ... The &unit parameter is then replaced by the appropriate text (i.e. 'Sm3') before the query is sent to Oracle.
Is there anyway to pass variables to server-side SQL and get a recordset back? I don't think PL/SQL procedures can do this? and views can't contain bind variables.
I would like to create some PL/SQL procedures that return XML as CLOB parameters. I want to just do this (which works fine with simple tests):
create or replace procedure p_xml_test_1( p_xml out nocopy clob ) is begin p_xml := '<?xml version="1.0" encoding="utf8" ?>' || '<test><something>some value</something></test>'; end p_xml_test_1;
But I have access to some other source code that basically does this:
create or replace procedure p_xml_test_2( p_xml out nocopy clob ) is lv_xml clob; begin dbms_lob.createtemporary( [code]......
I'm wondering if the first method will cause any problems for me down the road. Is it ok to do it that way? What is the advantage, if any, to the second method?
I'm creating a stored procedure where i get to return (OUT parameter) a cursor that points to a custom table. If I create an object, I could just do something like:
Quote: CREATE OR REPLACE TYPE TmpObjType AS OBJECT (...); CREATE OR REPLACE TYPE TmpObjTblType AS TABLE OF TmpObjType; PROCEDURE tmp_proc (..., out_param_resultset OUT g_cursor_type ) .... OPEN out_param_resultset FOR SELECT * FROM TABLE(CAST(tmpObjTbl AS TmpObjTblType)); ....
How do I return the table (referenced by a cursor) without creating objects?
