PL/SQL - Wrote Code Successfully But Cannot Bulk Update It?
Dec 15, 2008
Has 2 tables with the following columns
1.abc_y (notes,frmt)
sample data:
notes:
i live in texas and work in AIG.
2.abc_z(tags_name)
sample data:
tags_name:
live in work.We shoud look for tags_name in notes field of abc_y. If we encounter any tags_name in notes field they should be removed and inserted in frmt field Now the column format should be updated to 'i texas and AIG'.abc_y has 2 million rows and abc_z has 120 rows.wrote the code sucessfuly but cannot bulk update it, which is really needed for me.
I am havin some problm in FOR LOOP after FORALL in the folowing.
DECLARE
l_sql_strng VARCHAR2 (20000);
TYPE var_tab IS TABLE OF VARCHAR2 (20000)
INDEX BY BINARY_INTEGER;
l_text_arry var_tab;
CURSOR c1
running a jquery code after submit button has successfully completed, how do i integrate these 2 events ? clicking the submit button and if submits successfully execute the small jquery code based on the same submit button
I have performance issue with the bulk update. I have 2 tables one with 21 millions of data and the other table with 2 millions of data.here the requirement is to update the table which is having 21 millions with the other tables data(which is having 2 million records) based on some matching criteria.
The procedure is taking 9 hours to update the table(which is having 21 millions data). I have created required indexes on table also.But the performance is not good.
Here my procedure:
CREATE OR REPLACE PROCEDURE AHM_GKPR.CLAIMS_WITHOUT_PARTITIONS IS TYPE T_PL_CO IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER; L_PL_CO T_PL_CO; TYPE T_PL_CD IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER; L_PL_CD T_PL_CD; TYPE T_PL_NB IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER; L_PL_NB T_PL_NB; TYPE T_SE_CD IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER; L_SE_CD T_SE_CD; TYPE T_BIR_DT IS TABLE OF DATE INDEX BY BINARY_INTEGER; [code].........
I am trying to write an update using two tables, one table contains the list of updates to be applied, and the second contains the old data. I have a working solution but I was wondering if there might be some other better way to accomplish this task.
CREATE TABLE foo ( foo_id NUMBER, foo_val NUMBER ); CREATE TABLE foo_change ( foo_id NUMBER, foo_val NUMBER [code]...
The update works, but I thought there may be a better way I just don't know about.
My oracle version is 10g.I want to update a table which has 2 million rows and 20 columns. I want to update 3 columns in my table based on a query output.
Say:- select decode(col1,'a',col2,col3) up_col1, decode(col5,'Y',20,30)* col6 up_col2, col7 up_col3 from base_tab a, update_tab b where a.key = b.key
I tried the simple update SQL, it hangs and never comeback even after 2 hrs.So I resort to PLSQL to complete my job using bulk collect with limits. Now I am stumbled upon with the Bulk update step.I referred to the BULK Update example given in [URL]...
I gone by 9i approach, I got invalid rowid error. Since the example doesn't uses a join.
I am little bit worried to go by 10g approach given in the example. I have to fetch entire row from my update table. My update table has 5 varchar(1000) fields. Anyway I am not going to affect them in my update. Whether it will occupy memory and again leads to hanging of my procedure.
optimize this code. Scenario have to update about 40 million rows to static value, I'm committing 1Million rows in one loop. The first 1 millions rows are getting updated very fast probably in a 2 minute, after that the code just hungs and i don't see increase in committed rows.
Declare cursor c1 is select rowid from t1 where c1 is not null;
I have more than 10 lakhs records in the table for which i am going to update two columns without any filtration. i have pasted my query in it..it's taking more time to update..is there any way to fine tune this block.
I am trying to update a table column values if any change occurs using bulk collect and for all update not able to get idea. below is the proc working out.it is for insert and update using the cursors.
CREATE OR REPLACE PROCEDURE PRC_INS(P_ID IN NUMBER,P_STAT OUT NUMBER) IS TYPE T_TEST_TAB IS TABLE OF T_DTLS%ROWTYPE; V_PARAM T_TEST_TAB; V_STATUS NUMBER; V_BUS VARCHAR2(20); V_UP VARCHAR2(1); V_Q VARCHAR2(50);
Snapshot_date is last working date of that month, and is recorded only once in the table for a matrl_num & plant combination.
Inserting records in the table: Refer to insert_extended_values.sql Note: This is just a sample table , having records for only two matrl_num.
after inserting records , you can see the records are for two different matrl_num's. For each matrl_num , we can have multiple values of plant and for each plant we can have different value of std_Cost(eg:matrl_num='0023173556').
Moreover for each matrl_num , I have some records in each month , based on the number of plants(with std_cost). Std_cost for a plant is updated in the table whenever there is a change in it from the previous value.In case of no change in value of std_cost over previous month's value , we have 'Null' std_cost for that matrl_num, plant & month(next month).
I want to update these 'Null' values with latest(last month's 'not null' - std_cost) std_cost for that matrl_num and plant. Such change in std_cost can occur multiple times , so we need to update the 'Null' std_cost till the time we have encountered a change in std_cost and so on.
Have 3 tables table1 (campaignid,promoflag) table2 (campaignid,projectid,campaigndesc) table3 (projectid,promoflag,projectstart,projectend)
I am to update table 1 promoflag with value from promoflag in table3
Update table1 set promoflag = table3.promoflag
I would like to make sure only appropriate record is updated therefore want to use where clause condition but the primary key for table1 and table3 are different, the only link can be found on table2.
I want to use condition where table1.campaignid=table2.campaignid and table2.projectid=table1.projectid
Have used the following without success:
Scenario 1
Update table1 SET promoflag = table3.promoflag FROM table1 inner join table2 on table1.campaignid = table2.camapaignid inner join table3 on table2.projectid = table3.projectID;
Error at line 1 ORA-00933: SQL command not properly ended
Scenario 2 with real table/column names
Update UA_CAMPAIGNEXTATTR SET CFPROMOTABLE = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUD_CFPROMOTABLE FROM UA_CAMPAIGNEXTATTR,UA_CAMPAIGN,LMUK_PROJECT_AUDIENCE_GRID WHERE UA_CAMPAIGNEXTATTR.CAMPAIGNID = UA_CAMPAIGN.CAMPAIGNID AND UA_CAMPAIGN.PROJECTID = LMUK_PROJECT_AUDIENCE_GRID.GRID_AUDIENCE_ID; Error at line 2 ORA-00933: SQL command not properly ended
It appears to get block with the 'FROM' statement (was underlined in red)
I have no knowledge about Barcode. The problem is an issue of Loyalty Cards of a Hotel and Restaurant to various customers and then these cards will be presented by the customers time to time in the Hotel as well as Restaurant. The Owner of the Hotel and Restaurant wants to generate separate barcode for each card and when this card will be presented then the bar code reader will readout the code and the system will calculate the amount of discount/rebate. Because if the data entry operator enter the code of the card through key board the it will be a chance of leakage or misuse of that card.
I have to compare my SVN source code (packages, views etc) with the production code in the database like views etc (actually we are not sure that what we have in the svn is the final version of production code, we have objects created in the production database, but we don't have latest scripts for that. we have to deploy the svn code in the UNIX box).
So here the comparison is between the OS files and the database objects.
I thought I would get scripts of all the packages, views etc from the production database by using DBMS_METADATA or some utility and save the code in OS files then compare one svn file with OS file manually by using some comparison tools e.g toad provide one comparison tool.
I downloaded oracle sql developer, i type my code into a worksheet but if i use the run statement option, it asks me to make a connection. I dont want to make a connection, just test the data locally.However, even if I do try and make a connection, i get ora-12560 error (local connection).
I just want to type up some data to make some table and test to retrieve or manipulate the data. I'll use any program, command line or gui.
PROCEDURE MaxNo IS A Number; BEGIN Select Max(Supplier_ID)+1 into A from supplier ; END; SQL> Select Max(Supplier_ID)+1 from supplier; MAX(SUPPLIER_ID)+1 ------------------ 6
I have one problem. i am using client_to_db function to store the file to database table.During upload of file,System showing me message "File successfully transferred to database". but when i am downloading the file from database, its not working.
I am running this statement in a Java DAO and it results in an SQLException. I'm trying to get the DB schema. If I run this statement separate in SQLPlus, it successfully returns the schema. Why this is failing in my Java program?
ResultSet r = s.executeQuery("SELECT sys_context('USERENV', 'CURRENT_SCHEMA') FROM S_DUAL;");
I have a ORACLE 10g(10.2.0.1.0) database that I am running on windows. I have export backups batch file running every day through windows scheduler. I would like to know If there is some way that sends me an email after the backup is finished and also send me if it does not finish with error.
I have successfully done this om my SQL Server machine but don't know for ORACLE as I have not purchased it and have license
Export: Release 10.2.0.3.0 - Production on Thu Dec 6 15:06:21 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Release 10.2.0.3.0 - Production Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set server uses AL32UTF8 character set (possible charset conversion)
About to export specified users ...
EXP-00010: DISTESTA is not a valid username Export terminated successfully with warnings.
C:Documents and SettingsAdministrator>
When i connect from sqlplus, it doesn't give any erro. What could be the reason?
How to make the automatic deletion of all the archivelogs when it is applied successfully on the standby database? We have 1 primary database and 1 standby database using oracle physical dataguard.
In the primary database, we have daily backup job so that the applied archivelogs are deleted automatically, no problem
But in the standby database ( physical standby) I have to go there and delete manually because I don't issue backup on this database. Is there any solution for this problem.
when I am trying to send report for prints then I received this error.
" REP-57054: In-Process Job terminated: The report generated successfully but distribution to destination failed. REP-50159:The report generated Successfully but distribution to destination failed. REP-50151: Destination ID Currency failed with error An error occured while sending the file to the printer :HPofficejetpro7700Series- Cannot run program "COrcaleMiddlewareasinst_5Config eportsin wlpr":
create process error=2, The system cannot fine the file specified."
I am using Oracle 9i and Unix on my system and trying to execute a UNIX shell command through external procedure in C.I created a shared lib (libextproc.so) for the following function.
int sysrun(char *command) { return system(command); }
This function runs fine when caled through a driver function in C, meaning that the shared lib is fine.In PL/SQL, I have used the following method to invoke a UNIX command:- create or replace library shell_lib as '/home/ECETRAonsite/oracle/OraHome1/lib/libextproc.so'; / create or replace function sysrun (syscomm in varchar2) return binary_integer as language C name "sysrun" library shell_lib parameters(syscomm string); /
Now when I call this PL/SQL function to invoke the command, it is run succesfully but does not create the file.
PL/SQL procedure successfully completed.I have verified that the path for 'touch' is correct.Following are my configuration files. listener.ora ------------- LISTENER = (DESCRIPTION_LIST = (DESCRIPTION =
what is the error in this procedure.I am getting error where s.msg_id = Tbl_Repo_Salo (i.msg_id));
Create or replace Procedure Proc_Stg_Clear(P_Err_Code OUT NOCOPY number, P_err_msg OUT NOCOPY varchar2) as v_count number; Type Repo_Salo is table of Tbl_Tml_msg_Salo_Stg%ROWTYPE; Type Repo_Smb is table of Tbl_Tml_msg_SMB_Stg%ROWTYPE;
