I need to insert Data into a table based on particular condition, So I'm using an Insert select as follows:
INSERT INTO error_table
(error_code,cust_id,customer_name,error_message )
SELECT '34', cust_id,customer_name, 'Missing xxx'
FROM customers
WHERE cust_id = cur_cust.cust.id
AND ( NOT EXISTS ( Select .....) -- control 1
OR (EXISTS ( Select .....) --control 2
OR (NOT EXISTS ( Select .....) -- control 3
I have around 10 controls. I want these controls to run conditionally..I was wondering if this can be done using a decode based on some variable outside the query.
I want the query to run like this:
if A then execute control 1: AND ( NOT EXISTS ( Select .....)
If B then execute control 2: AND ( OR EXISTS ( Select .....)
I'm trying to optimize an application running heavy updates/inserts/deletes, by having it using bind variables instead of "string queries".
The columns to be updated can vary (possibly from one column to all columns of a table), thus I have made som logic to build the query accordingly. My problem is now that I cant get the EXECUTE below to handle the VARRAY passed in the USING clause, it fails with "PLS-00457: expressions have to be of SQL types"
Environment:
create table table_x (a varchar2(10),b varchar2(10),c varchar2(10),d varchar2(10));
insert into table_x values('a','b','c','d'); commit;
Code, simplified with static number of columns:
declare type v is varray(10) of varchar2(20); v_values v:=v('A','B','c'); myupdate varchar2(2000); begin myupdate:='update table_x set a=:a, b=:b where c=:c'; dbms_output.put_line(myupdate); execute immediate myupdate using v_values; end;
I've understood that I cannot send TABLE type variables by USING, but this should be an VARRAY.
I have 3 pages: - Classes: a Classic Report that shows a list of classes and summarises attendance- Class Attendance Report: An Interactive Report that shows a row for each student that attended a class- Attendance Add / Edit Form On the Classes page, the Date value is a Link which passes a filter in the URL to the Class Attendance Report, showing only students who attended the class held on this date On the Class Attendance Report, I want to have a Region Button that, when clicked, goes to the Attendance Add / Edit Form and the Class Date field on this form is automatically populated with the same date value as was being used for the filter on the Interactive Report on the Class Attendance Report Page. So, I click on the Class Date link for 2013-11-01, this takes me to the Class Attendance Report and filters for students who have been already entered as attending on 2013-11-01. If I click on the "Add Attendance" button, it goes to the Attendance Add / Edit form and the Class Date is automatically set to 2013-11-01. I assume I need to do this via a Dynamic Action that runs when the "Add Attendance" button on the Class Attendance Report page is clicked?
I have a table of addresses where the indexed column consists of the city, an optional area name, the street name and the street number. For example 'Stockholm Drottninggatan 2'.
The users must enter the full city name and the beginning of the street name. So if the user wants to find all the addresses of both the streets Stockrosvägen and Stockbergsvägen which are in Stockholm, the query would look something like this:
Select * From AddressSearch Where Contains(AddressSearch.Address, 'Stockholm AND Stock%') > 0;
But this will select all the addresses of Stockholm. Is there a way to make the part after the AND not match the already matched first part?
I want to conditionally re-arrange the serial. Sample test case is as below.
DROP TABLE t1; CREATE TABLE t1 (
[Code].....
SQL> Select * from t1 order by no;
NO T ---------- - 1245 R 1246 N 1247 N 1248 R 1249 R 1250 R 1251 N 1252 N 1253 R 1254 R 1255 N
11 rows selected.
Required output is
OLD_NO T NEW_NO ---------- - -------- 1245 R 1245 1246 N 1247 1247 N 1251 1248 R 1248 1249 R 1249 1250 R 1250 1251 N 1252 1252 N 1255 1253 R 1253 1254 R 1254 1255 N 1256
Scnario is to add 1 in no. field but no.s having type 'R' will remain unchanged. e.g. Record no. 1247 is changed to 1251 because 1248,1249,1250 have typ 'R'.
I'm working with old code that uses dbms_sql.execute to build/execute dynamic sql. In our case, the user can select varying columns(I think up to 20) with different where conditions as needed.
After building the sql, here's an example
WITH ph AS (SELECT ph.* FROM po_header ph WHERE 1 = 2), pf AS (SELECT DISTINCT pf.order_id, pf.fund FROM po_fau pf, ph WHERE 1 = 1 AND ph.order_id = pf.order_id
[code]....
Where table records for
po_header = ~567746 po_fau = ~2153570
and PK "order_id" is a NUMBER(10) not null and a snippet of the code looks like
nDDL_Cursor := dbms_sql.open_cursor; dbms_sql.parse(nDDL_Cursor, sSQLStr, 2); FOR x IN 1 .. nCols LOOP sCols(x) := ''; dbms_sql.define_column(nDDL_Cursor, x, sCols(x), 100); END LOOP; nError := dbms_sql.execute(nDDL_cursor);
why when the "execute" statement is fired off the elapsed time takes ~4.5 seconds but If I change "1 = 1" above to "1 = 2" it takes ~.2 seconds. If I run the above query interactively it takes ~.2 seconds. Shouldn't the above query when joining
ph.order_id = pf.order_id
return zero rows back instantly or does the "dbms_sql_execute" do some other type of parsing internally that takes cpu time.
We can execute dynamic sql using both execute immediate and ref cursor..But what is the difference between the two and performance-wise which is better?
I want to convert the time part of each rows to number of second in different column. so for the 1st rows it is 09:18:00.000 that means 9hour and 18 min = 9*60*60+18*60.
I started one packaged procedure in plsql developer yesterday..thats being run from yesterday...I wanted to know what part of the query that is being run now from the procedure.
I have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.
i am working on sql server developer in on of the organization. i got requirement from client that migration part from oracle 6i forms to oracle 10g and i got existing forms backup from client that is have .fmb, .mmb, .rdf and some of libraries having .pll files, but am new to oracle which form need to open from form builder first?
i tried to open .fmb file given database connection also but it is showing some libraries are missing. what should be the solution for my problem.
i'm trying to import data to QUANTUM "oracle database" from Oracle database import assistant using OCDia.exe with SQL statement below and i'm getting this error message "Part Number and manufacturer combination must be unique"
------------------------------------------------------------------------------------------------------------------------------------- PROCEDURE ACE_LISTPRICE_LEADTIME (P_IMP NUMBER) IS C DIA_RL_PKG.CURSOR_TYPE ;
select * from testing Quote: ID NAME REDATA -------------------- -------------------- -------------------- 1 ABC,DEF,GHI,LKJ 000001 2 MNC,GHI,CTF 000002
2 rows selected
select * from testing where name like ('GHI,TCF');
now my output should be as above because GHI is common in both the names.if any of the where condition string matches the Name field then I need that record to be retrieved.
SQL> SELECT distinct a.legacy_product_code FROM im_polprdmap_pukka a where a.new_product_code like '%GIFT%'; LEGACY_PRODUCT_CODE ------------------- GIFT25 GFTPF21NP GIFT21/H GIFT21//
[code]...
I need to calculate a field taking the numeric part of the above filed.How can i write this?
I have a column where I have time stamp with date and time(8-May-10 10:20:55 Am) from that i will get only time(10:20:55 Am)[query for this mentioned below] Now i need to append the time to current date and insert into a Timestamps column only .
so my result should be 17-May-11 10:20:55 Am(Assuming current Date is 17-May-11) . I tried search it by i found a way in sql server by using DataAdd and DataDiff .
query to get the time is:select to_char(to_date('11-MAY-11 05.00.00 PM', 'DD-Mon-YY HH:MI:SSAM'),'HH24:MI:SS') "Time Now" from dual
There is a readonly user on our reporting server. Developers want to use global temporary tables with this user. I don't want the user to have permissions other than readonly.I can grant the user CREATE TABLE privilege and did not grant quota on any permanent tablespace, therefore user would not be unable to create permanent tables but still should be able to create global temporary tables.
Question is would a user with such permissions still be able to utilize temp tables as part of a scheduled job?
I am trying to create a silent install as part of a batch file that does other things after. I'm running into the following issues
1) I need the batch file to wait until the install is done to continue. Trouble is that running setup with a response file opens up a new windows to run the install. I tried the wait command but doesn't work right either 2) Same issue with DBCA and a response file.
Any way to run the silent installs and have the batch file wait until the install is done and then continue? The less user intervention required, the better.
For our customer we have build a custom login page. The form we currently made has de following items:
- Username - Password - Select list for selecting the task type
When the customer enters his username, automatically the select list of the task type will be refreshed to get only the task type where the customer has the rights to do it. This part works...It can occur that a customer has only one task type (which is default) then we want to hide the select list. This part doesn't work
We used APEX version 4.1.1.What we did is:
- One action item to refresh the selection list (works) - One action item to hide the selection list by starting the application (works) - One action item to show the selection list only when the selection list has more than one result. For this part we used the conditional part of the action item (Function returning a boolean).
In the query (inside the function) we want to use the entered username, looking into the application in the session state I saw the correct username, but for some reason the function cannot use this entered username, it becomes empty. When I use a hardcoded username the code works.
Why I cannot use a username into the conditional part of the Action Item.
When I see the apex dashboard in the IE or google chrome, part of screen goes missing.Last part of dashboard doesn't come but it works fine in Mozilla Firefox.What should I do? Is there some settings I need to do in IE.I am using the IE version 9.0 for 64 bit.
if it is possible to include 'data' files as part of the Application Export process. Up till now I've only been able to include supporting object files which will recreate a table structure but not the data records - I've been creating the latter outside of APEX.
