Jump to content

Search the Community

Showing results for tags 'sql'.

More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type

Find results in...

Find results that contain...

Date Created

  • Start


Last Updated

  • Start


Filter by number of...


  • Start





Website URL







Found 5 results

  1. My function does not return the date. I will publish the package body and call the function Can you tell me where the error is? My function in package body looks like this: SQL> CREATE OR REPLACE PACKAGE BODY account_api AS 2 PROCEDURE add_new_account 3 ( p_acc_id accounts.acc_id%type 4 , p_acc_name accounts.acc_name%type 5 , p_acc_amount accounts.acc_amount%type 6 , p_acc_date accounts.acc_date%type) 7 IS 8 BEGIN 9 INSERT INTO accounts(acc_id, acc_name, acc_amount, acc_date) 10 VALUES (p_acc_id, p_acc_name, p_acc_amount, p_acc_date); 11 COMMIT; 12 EXCEPTION 13 WHEN OTHERS THEN 14 ROLLBACK; 15 RAISE; 16 END; 17 PROCEDURE upd_account 18 (p_acc_id accounts.acc_id%type 19 , p_acc_name accounts.acc_name%type 20 , p_acc_amount accounts.acc_amount%type 21 , p_acc_date accounts.acc_date%type 22 ) 23 IS 24 BEGIN 25 UPDATE accounts 26 set acc_name = p_acc_name 27 , acc_amount = p_acc_amount 28 , acc_date = p_acc_date 29 WHERE acc_id = p_acc_id; 30 COMMIT; 31 END; 32 PROCEDURE del_accounts 33 (p_acc_id accounts.acc_id%type) 34 IS 35 BEGIN 36 DELETE FROM accounts WHERE acc_id = p_acc_id; 37 COMMIT; 38 EXCEPTION 39 WHEN OTHERS THEN 40 ROLLBACK; 41 RAISE; 42 END; 43 FUNCTION get_amount 44 (p_acc_id accounts.acc_id%type) 45 return Number is res number; 46 begin 47 select acc_amount into res 48 from accounts where acc_id =p_acc_id; 49 return res; 50 end; 51 FUNCTION get_date 52 (p_acc_id accounts.acc_id%type) 53 RETURN date IS res1 date; 54 BEGIN 55 SELECT acc_date INTO res1 56 FROM accounts WHERE acc_id = p_acc_id; 57 RETURN res1; 58 end; 59 end account_api; 60 / The function is executed but does not return the data. SQL> set serveroutput on SQL> declare 2 res1 date; 3 begin 4 res1 := account_api.get_date(1); 5 end; 6 / PL/SQL procedure successfully completed.
  2. Hello... I want to change "stale_percent" to 3 and "concurrent" to true, but I need to test it first. I need to improve statistics gathering on very large fast changing tables.What is the best way to check before and after it will be set to check if this make any difference?I read that I need to check parameter JOB_QUEUE_PROCESSES and parallel_adaptive_multi_user before I will set "concurrent".Could you advise what values are save when setting it?Thank you in advance.
  3. sundara

    Huge SQL data Insert

    I have couple of tables that I need to use to load a 3rd table. The total number of rows to load comes around 30 million. My Insert SQL query is a INSERT-INTO-<TABLE>-SELECT. My Insert SQL query hangs (Waited for 30 mins before killing the session). But when I used ROWNUM in the SELECT query with the ROWNUM value equal to the total number of result rows, the INSERT query ran in 43 seconds. And, both the results are consistent. I am completely lost here. Can you please throw your expert opinion on why the INSERT query without ROWNUM hangs and the INSERT query with ROWNUM runs much faster ? Details : TABLE - A : has a total of 50,000,000 rows; No index TABLE - B : has a total of 200,000 rows; Index present in COLUMN1 (Relation with TABLE-A is one to maasny) TABLE - C : Output table Original INSERT query: INSERT INTO C SELECT COLUMN1 FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B); INSERT query with ROWNUM : SELECT COUNT(*) FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B); -- No issues with this query INSERT INTO C SELECT COLUMN1 FROM A WHERE COLUMN2 IN (SELECT COLUMN1 FROM B) AND ROWNUM <=30,000,000;
  4. A+++500+read++ I want the occurence of last '+' or in other words,total count of '+' before last value 'read' through PL/SQL at which value occurs.First value is 'A',Second value is '500' , third value is 'read'.Here last value is 'read' and position of '+' at which last value 'read' occurs is 4.I want this output 4 through query.Just want occurence of Highlighted '+' or in other words,total count of '+' before last value 'read'.Max position of '+' at which last value is there.Here output should be 4. Second i want total number of values that occurs between '+'..Here three values namely A,500,read is there.I want count of values means how many values exist between '+'.Output should be 3.
  5. Hello Everyone. Request you to please suggest some way on this. I have a csv file which i am uploading to a table using UTL_FILE. This activity runs on daily basis where file name remains same but data in there changes (insertion or update). I need to record that particular row with required columns which was changed in the csv file. I created a procedure and used below as condition which works for upload part. ## merge into test using dual on (Id = v_Id) when not matched then insert (Id, name, address) values (v_Id, v_name, v_address) when matched then update set name = v_name, address = v_address; For recording that update i used a trigger on this table (test) ## create or replace TRIGGER test_trg after update on test for EACH ROW begin If Updating Then insert into test_log (name, address ) values (:new.name, :new.address ); The trouble i am facing is that instead of recording that single record which is changed in table test using utl_file upload it records whole table values of required columns. Trigger works fine if i manually edit or update the test table but in case of UTL_FILE upload procedure trigger records whole table data of required columns.