Is it possible to do this in SQL? If I remove the INSERT statement the SELECT works, if I have the insert Oracle complains that "missing SELECT keyword". WITH tmpdata AS ( //SOME arbitrary select statement ) INSERT INTO myTable (someId, somevalue, someothervalue) SELECT mysequence.nextval, tmpData.somevalue, tmpData.someothervalue,...
There is some construction ALTER USER scott PASSWORD EXPIRE But how can I similair set password to unexpired state? ...
Has anyone else noticed this phenomenon where dbms_output.put_line is unable to print more than 2000 characters at a time? Script is: set serveroutput on size 100000; declare big_str varchar2(2009); begin for i in 1..2009 loop big_str := big_str||'x'; end loop; dbms_output.put_line(length(big_str)); dbms_o...
I have a db table that has > 14M rows. If I attempt to perform: delete from table The connection hangs out. How can I delete all the rows, in "batchs" ( I guess ) so they are deleted eventually? ...
I have a fairly complex query that will be referencing a single date as a start or stop date multiple times throughout. I'm going to have to run this query for 3 different fiscal years, and don't want to have to hunt down the date 17 times in order to change it throughout my query. Is there a way to set a variable at the beginning of my...
Hoping someone can assist with this data update using Oracle. I have a situation where I have data/records that may look like this: Table Name: IPDATA Column Name with these records is called: info Web Proxy (abc):ZZZ Gateway gen1:gen2 ZZZ Gateway gen1:gen2:Web Proxy (abc) ZZZ Gateway gen1:gen2 Web Proxy (abc):ZZZ Gateway gen1:gen2:X...
I am using NHibernate against an Oracle database with the NHibernate.Driver.OracleDataClientDriver driver class. I have an integration test that pulls back expected data properly when executed through the IDE using TestDriven.net. However, when I run the unit test through the NUnit GUI or Console, NHibernate throws an exception saying ...
I use Oracle 10g and I have a table that stores a snapshot of data on a person for a given day. Every night an outside process adds new rows to the table for any person whose had any changes to their core data (stored elsewhere). This allows a query to be written using a date to find out what a person 'looked' like on some past day. A ne...
I want to create a job that would drop a database object at a given date. The job created all right but the procedure is not executed. Tried executing the procedure alone and it works. Here's the code for the create job v_jobnam := v_objnam; v_jobnam := DBMS_SCHEDULER.generate_job_name (v_jobnam); v_startdate := to_tim...
How to use index in a select query in oracle? When creating indexes can enhance performance? How to use indexed explicitly? ...
I have three tables: unmatched_purchases table: unmatched_purchases_id --primary key purchases_id --foreign key to events table location_id --which store purchase_date item_id --item purchased purchases table: purchases_id --primary key location_id --which store customer_id credit_card_transactions: transaction_id --primary key trans_...
So, I often have to load data into holding tables to run some data validation checks and then return the results. Normally, I create the holding table, then a sqlldr control file and load the data into the table, then I run my queries. Is there any reason I should be using external tables for thing instead? In what way will they make my ...
I have a WCF service that uses ODP.NET to read data from an Oracle database. The service also writes to the database, but indirectly, as all updates and inserts are achieved through an older layer of business logic that I access via COM+, which I wrap in a TransactionScope. The older layer connects to Oracle via ODBC, not ODP.NET. The p...
I've got a vb.net codebase using ado to connect to an Oracle database. We have lots of stored procedures that we call, some with multiple out parameters. However, I now need to call a stored function, and it's not clear to me how to get the result of the function back into my VB code. Edit: I'm returning an integer. How do I properly c...
Hi, i'm new to pl/sql and trying to print even numbers up till 100 using the following code: Declare i number; sum number:=0; x number:=2; n number; Begin for i in 1..100 loop if (i%x=0) then n:=i; sum:=sum+n; end if; end loop; dbms_output.put_line(sum); end; i get this error ERROR ...
I get this error message, what does it mean and how to fix it? Warning: oci_parse() expects parameter 1 to be resource, null given in /user_auth_fns.php on line 3 $conn = db_connect(); $result = oci_parse($conn, "select * from user where username='$username' and passwd = sha1('$password')"); if (!$result){ $err = oci_error(); ...
I get the below error message. What other compression function(other than SHA1) should use for oracle? Warning: oci_execute() [function.oci-execute]: ORA-00904: "SHA1": invalid identifier in /user_auth_fns.php on line 2 $result = oci_parse($conn, "select * from user where username='$username' and passwd = sha1('$password')"); $r = oc...
error message: oci_fetch_array() expects parameter 1 to be resource, boolean given in /url_fns.php on line 17 I want to get all bm_URL record and store in $url_array. echo $r is shown to be 1. How to fix this error message? $conn = db_connect(); $result = oci_parse($conn, "select bm_URL from bookmark ...
I was thinking about the syntax of inner joins in Oracle's SQL implementation and here is something that seems a bit inconsistent: Let's say you have two relations loan(loan_number, branch_name, amount) and borrower(customer_name, loan_number). loan_number is the attribute common to both tables. Now, Oracle gives you two ways to express...
1) I want to save case 1 value to an array. Return this array and pass it to a function. The code become case 2, but no result come out, where is the problem? 2) In function display_urls, i want to echo both $url and $category. What should i do in IF condition or add another line of code? function display_urls($url_array) { echo ""; ...