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 don't understand why the custid is the same for each customer, and why it's selecting every customer and not just those with more than 150 gallons ordered.
For this one use the oil tables that you set up and use a subquery. Select the minimum average fall use from the house table. Then show all customers whose number of gallons delivered times two is greater than the minimum.
What is the Best option for SQL/sybase server Temp table to use/for data manipulation (Insert/update/delete) inside the procedure in oracle Other then global temporary table.
Since we are porting from SQL/Sybase to Oracle we don't want to Create too many global temporary table.
I got ORA-14074 error says that if you have a MAXVALUE already you will need to either drop the partition that encompasses the MAXVALUE or split partition.So I want to use split partition option through the below statement,
alter table tablename split partition partmax at (XXX) into (partition partXXX, partition partmax);
I am concerned about the existing data in the table will it by any change gets deleted, and what about indexes, do I have to rebuild, etc.I have to do this on produciton. I cannot test this as I don't have a test environment and moreover the table is having 70 million records and we don't have time/hardware resources to recreate this by export import in another database.
Just a general query on parallel query. My customer having 4 cpus and running the database in 11.2.0.3 in AIX 5.3(One is in AIX 6.1). Under which circumstances, we can propose to user parallel query options.
I want to know what syntax I should use for encoding option with loadjava command?
here is the scenario--
Our oracle database is already compatible with UTF-8 characters i.e. charset encoding is set as 'AL32UTF8'. I am able to save Chinese characters in the database however when I trigger a stored procedure which eventually use a loaded java class I get an error.
I am suggested to provide -encoding option while running 'loadjava' command but I don't know what syntax I should provide for 'UTF8' encoding.
create or replace p_aggop(dno number, maxi number,mini number,avgi number,tot number,cnt number) is cursor c1 is select * from emp1 WHERE DEPTNO=dno GROUP BY DEPTNO; v_emp1 c1%rowtype; begin open c1; loop fetch c1 into v_emp1; select min(v_emp1.sal),max(v_emp1.sal),avg(v_emp1.sal),sum(v_emp1.sal),count(*) into mini,maxi,avgi,tot,cnt from v_emp1; exit when c1%notfound; Dbms_output.put_line('EXECUTED '); end loop; dbms_output.put_line('The minimum salaray in dept is :'||v_emp1.mini); dbms_output.put_line('The maximum salaray in dept is :'||v_emp1.maxi); dbms_output.put_line('The average salaray in dept is :'||v_emp1.avgi); dbms_output.put_line('The total salaray in dept is :'||v_emp1.tot); dbms_output.put_line('The total members in dept is :'||v_emp1.cnt); CLOSE C1; end;
I am creating a stored procedure to create a table during run time using 3 passed in parameters to build the table name. Oracle is giving me the ORA-00922 missing or invalid operation error message on the EXECUTE IMMEDIATE statement of my stored procedure. I am new to writing stored procedures in Oracle. I am pasting a copy of the stored procedures. I have also changed the names of the field names to be generic so I can post the code in this forum.
CREATE OR REPLACE PROCEDURE createTable(PARAMETER1 in string, PARAMETER2 in string, PARAMETER3 in string) IS
TABLE_NAME NVARCHAR2(50); QUERY_STRING LONG; BEGIN
I am trying out the COMPRESS option along with CREATE TABLE. just wanted to understand if we need the "Advanced Compression" option enabled. Does this require extra license?
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.
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.
How can i use refresh option as 1 hr in creation of materialized view, Can i use unoin all in the materialized view and other clause like CONNECT BY PRIOR .
I m currently written a sqlplus query and executing as below
sqlplus -s << ! set head off pages off trimspool on termout off spool sysdate.txt select sysdate from dual; spool off !
With this query the sysdate is getting written in the sysdate.txt but it is also getting printed in the screen/terminal., which I don't need to. Is their any options available for that.
Note: I came to know that "Termout off" will work only when we execute the query from a file and as a result I can still see the sysdate getting displayed in the screen.
The database is 11.2.0.3 on a linux machine. I issued the following command, but the session was a little slow. The table size is about 50 GB and has 3 indexes. I specified "degree=8" for parallel processing.
When gathering statistics on the table , parallel slaves were invoked and gathering statistics on the table has finished fast enough. However, when it goes to gathering statistics on the indexes, only one active session was invoked, and thus "degree=8" option was ignored.
My question is :
Do I need to use dbms_stats.gahter_index_stats instead of "cascade" option in order to gather statistic on the indexes with parallelism?
exec dbms_stats.gather_table_stats(ownname=>'SDPSTGOUT',tabname=>'OUT_SDP_CONTACT_HIS',estimate_percent=>10, degree=>8 , method_opt=>'FOR ALL COLUMNS SIZE 1',Granularity=>'ALL',cascade=>TRUE)
SQL> ALTER SYSTEM SET NLS_LANG='AMERICAN_AMERICA.AR8MSWIN1256'SCOPE=SPFILE; ALTER SYSTEM SET NLS_LANG=' PORTUGUESE_PORTUGAL.WE8MSWIN1252'SCOPE=SPFILE * ERROR at line 1: ORA-02065: illegal option for ALTER SYSTEM
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;
