ID DETAILS
--- --------
100 The ship has left san diego http:/localhost/icons/sandiego.png to okinawa on nov 10, 2011.
I need to update the record(id = 100) by replacing the url "http:/localhost/icons/sandiego.png" with "http:/localhost/icons/okinawa.png".
I need a procedure where I will pass the ID value, replace string(i.e http:/localhost/icons/sandiego.png) and replace with string (ie. http:/localhost/icons/okinawa.png).
I am encountering an error message while updating a xmltype column using dynamic sql statement. I am using dynamic sql here as the table is not placed in the same schema from where the plsql procedure is invoked. The schema name is passed to the procedure as an argument. I am using below pseudo code for this purpose.
Create procedure myproc(p_schemaname varchar2, p_id number) is p_clob clob; p_str varchar2(2000); begin
[code]...
This is throwing an error 'missing expression' at the line of 'exeute immediate'.
But it works if I run a static sql update by hard coding the schema name in the statement.
I have a base table (Table A) block with multiple records displayed. I need to track audits to this underlying table in the following way:
If user updates a field in the block I want the pre-changed record's audit fields to be set and I need to create a copy of the record with the changed values. Basically any changes will result in the record being logically deleted, and a copy record created with the newly changed values.
Tried to implement in the block's pre-update trigger which will call a package to directly update Table A then Insert into Table A, then requery the block. Is there a clean and efficient way to do this?
UPDATE t_tt_hours a SET a.sak_request = ( SELECT b.sak_request FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a SET a.sak_request = ( SELECT b.sak_request FROM t_requests b, co c WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
i want to create a trigger that will update a table when there is an insert or update.i can't across this error that i don't even know what it means "table %s.%s is mutating, trigger/function may not see it".
*Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
CREATE OR REPLACE TRIGGER set_date_end BEFORE INSERT OR UPDATE OF issued ON shares_amount FOR EACH ROW DECLARE BEGIN INSERT INTO shares_amount(date_end) VALUES(SYSDATE); END set_date_end; /
i am reading the columns value from different table but i want to update it with single update statement. such as how to update multiple columns (50 columns) of table with single update statement .. is there any sql statement available i know it how to do with pl/sql.
insert into test_compound values ('user1','1',systimestamp); insert into test_compound values ('user2','2',systimestamp-4); insert into test_compound values ('user3','3',systimestamp-6);
CREATE OR REPLACE TRIGGER trigger_test FOR UPDATE ON test_compound COMPOUND TRIGGER TYPE t_tab IS TABLE OF VARCHAR2(50); l_tab t_tab := t_tab(); [code].......
When I execute :
update test_compound set last_updated_on=systimestamp where userid='user1' and app='1';
The trigger should update the first row and all the data from test_compound table where userid='user1'. Maybe the problem is that updating the same table inside the trigger is firing in a recursive way the trigger.
I have a column "empno" in EMP table and "deptno" in DEPT table . I want to update both the columns with single UPDATE statement. With out a creation of stored procedure or view(updating it through view).
I'm writing a PHP page to display some data from an Oracle database. Unfortunately, I can't copy the code because it's proprietary. One of the columns in the db is of type CLOB. I'm having trouble getting the data from the CLOB column.
The way the code is now, is there is a query string read into a variable, and the query string variable is read into a function that then retrieves the data from the db. Once the result set is returned, it is parsed to get data from all of the columns. The issue is that I've never worked with CLOB data before, so I'm having some difficulty extracting the data for that column. I know I can use the DBMS_LOB.READ function, but I'm not sure how to apply it in this case. The following is a generic form of the query string I'm using with b.clob_col being the column I'm having issues with.
$queryString = <<<EOD SELECT a.col1, a.col2, a.col3, b.col4, b.col5, b.clob_col, from table1 b inner join b.col4 on blah1 inner join a.col2 on blah2 where blah and blah EOD;
insert into topdUIDXML select '<filter name="test" topologyUID="1">' from dual; insert into topdUIDXML select '<filter name="test2" topologyUID="2">' from dual; insert into topdUIDXML select '<filter name="ftest" topologyUID="3">' from dual; insert into topdUIDXML select '<filter name="qtest" topologyUID="4">' from dual;
How can I insert xml to clob ? I'm taking from database clob making it in xml type and when I want to insert xml type into clob field it calls and error:
Error(316,43): PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got -
in our application we are using clob column instead of varchar2 because varchar2 does not allow more that 4000 chars, so Using clob allows to put data of any length, will it cause performance issues ? we have this column in almost in all tables .
I have to generate a big xml. Each part of the xml needs data from different tables. So I have created two clob to hold the portion of the big xml. How can i have the concatenated clob values as an argument for xml element.
Working on building an application which will be in C# (.NET 3.5) and some PL/SQL. However, for the tables which have to store the data, I added a column, comments, to the table and assigned it a data type CLOB. No big deal, except my PL/SQL function is giving me errors if the CLOB built from several varchar2 rows exceeds 4000 characters. I understand the varchar2 field is restricted in 10g, but I was under the impression there wasn't such a restriction on a CLOB (hence the reason we're using it.)
I end up getting:
ORA-6502: PL/SQL: numeric or value error: character string buffer too small ORA-6512: at line 1 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: *Action:The db is 10g (will be upgrading to 11 in October, needs to work on both) on a Windows Server 2003 box (Archive/backup server and db)
I want to execute a procedure and view its output. The procedurename is GET_clnts which has one of the parameters as clob where i am passing xmltype data to it.How can i execute the procedure to view the output. i can execute the SQL statement by itself, but not sure how to call a procedure from a plsql block..
We have oracle 10 g and a table contains a clob field . The table size is getting increased day by day . We have decided to zip all the clob data inside the table except last 1 month records.
I'm trying to import data from a csv file format which is located in a CLOB column in a single record in the database. I want to import the data that is contained in this CLOB into a table. I am having limited success using JH_UTIL. Here's the script that I am running (which works):
set serveroutput on; declare v_lines jh_util.stringlist_t; v_values jh_util.stringlist_t; begin for rec in (select 1 id, ac.clob_content csv [code].......
The problem is when the file gets too big, I get a the following error:
Error report: ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 6 06502. 00000 - "PL/SQL: numeric or value error%s" *Cause: *Action:
I assume this means because the file size is too big. Is there any way to process larger "files" (CLOB data)
I am trying to insert complex HTML into a CLOB value in an Oracle table and keep running into various issues. The ORA errors I get include 0734, 0042 and 0552. The HTML I am inserting includes characters such as &, ', and *.
Here is an example of the SQL I am trying to run:
set DEFINE off' update table set message = '<HTML>TEST!@#$%</HTML>'; commit;
Just wanted to export a clob field to .txt file, the maximum length of the clob field exceeds the limit 32767. So only partial data is exported to flat file. is there any way to export the entire data available in clob field irrespective of the size or lenght.
