We have table with number as datatype which starts with number 10 like 1056723. Here we need to update the first 2 digits which is 10 to 04.Digit 10 will be always constant across all the data.
select * from temp;
id
----------
1056723
After update, it should be
id
----------
0456723
What function in pl/sql should be used for achieve the desired results.
I have a table containing BLOB column which stores scanned images. Due to an application error, few extra data was padded with BLOB data and now we want to remove it.
The table count will be near to 10 million rows.
We need to remove data from 161byte to 167byte of the blob data. I tried to do with DBMS_LOB.ERASE.But it will create blank spaces for the removed data. Here we need to reduce the size of BLOB data by 6 bytes by removing data from 161bytes to 167 bytes.
I have made an application in forms 6i. Now i want if user can update, or modify the data it will required automatic authentication. Administrator will gives his password then the updation will continue otherwise rollback the transaction.
There is a customer_account table. It is partitioned by Range. In one of the partition, the range value is given wrongly, and irrelevant data sits in that partition.
How can i modify the range partition value and re-arrange the data ?
I am quite a beginner One of trigerred jobs is freezing and I have to reproduce the issue. Thing is about extracion and export data from one table to another (from Rating to Xtexport_table_1).
I want to build query which will fill xtexport_table_1 with values, partialy declared and partialy fetched from Rating tabble.
I figured out that that I have to get rid of all condition statements(IF, WHEN etc ), and just declare variables for some columns and then use a SELECT statement to fetch data.
create or replace TRIGGER TRG_RATING_EXP_DLS BEFORE DELETE OR UPDATE OR INSERT OF RATING_ID,EXTERNAL_ID_1,DN_RATING_ATTRIBUTE_ID,ALI GNMENT_ID,TEAM_ID,CUSTOMER_ID,AFFILIATION_ID,PRODU CT_ID,PERIOD_ID,VALUE_MIN,VALUE_MAX,TENANT_ID,USER _ACCOUNT_ID ON RATING REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE
We are adding support for barcode scanner in one part of our information system. So that the mechanics can add parts to an work orders bill of material themselves using barcode scanner to scan the part and enter the qty.
I can via SQL add a part to the bill of materials and reserve the part. But then the part needs to be issued to make it disappear from the stock.
I have found a table that contains information about the part and the bill of material, it has a column named "QTY ISSUED" I have tried via an update command to set the qty issued = 1 for the reserved part. The table is updated, and via SQL everything looks fine, but the part doesn't disappear from the stock. So my guess is that there is a trigger or function somewhere that I need for this.
I have a database in which a user xxxx is assigned a password 'bbbbb'.I want to change the password to the one which was used before which was 'aaaaa'.But when I change the password it was saying "Password cannot be Reused".So I checked in user profile and found out that password_reuse_time=unlimited and password_reuse_max=5.
So what I did was change the password 6 times to something else(Since it is 5) and then tried changing it to 'aaaaa' but still it is saying "Old password cannot be reused".
I am working on Oracle reports 6i. Just for R&D purpose I have opened REP file in notepad. In the notepad file I could find the queries which are there in the data model.
I did a small addition to the query and saved it in the same location. I tried to open the modified REP file from RWRUN60, but it is throwing me error saying "REP-0110- Unable to open file".
Original Query in data model: SELECT empno, sal, job, hiredate FROM emp;
Modified Query: SELECT empno, sal, job, hiredate FROM emp WHERE deptno in (10,30);
I am using 'Novell Sentinel Log Manager' to collect/fetch logs from my Oracle 11g R2.To enable auditing, first I did following:
login as sys, then SQL> create user testuser identified by "testuser"; SQL> grant connect to testuser SQL> grant dba to sharf SQL> grant CREATE SESSION to testuser; SQL> grant select on v_$session to testuser; SQL> grant select on v_$version to testuser; SQL> grant select on SYS.DBA_AUDIT_TRAIL to testuser; SQL> grant select_catalog_role to testuser; SQL> grant select any dictionary to testuser;
Now logon/logof of user 'testuser' are logged , as well as if testuser drops a table or creates a table, its also logged . but when 'testuser' insert a new record, this information does not logged ;( while I need to know exactly what was added SQL> insert into emp (empid, name, salary) values (10002, 'Ron', 6000)
likewise if 'testuser' modify/update an existing record it also does not logged. SQL> update emp set salary=700 where empid=10001;
which sql statements I have to execute to start auditing 'insert' and 'update', so that I know what was added/inserted and exactly what was updated/ changed/modify by user 'testuser'.
I am using the client system to change the setting and the time zone differs from that of production. Even though the job is scheduled to run at 5 AM it show the start date as 6:30 PM which is the client system time.
We have a large customer table so first thought was to partition.Also we see two union alls in the plan - can we introduce parallelism? Below is the plan - have attached a text file if difficult to read
I am trying to create a custom package that creates users as well as modify user information in OID using DBMS_LDAP package. I was able to create the package but I am stuck on specifying the orcladmin password for the procedure DBMS_LDAP.simple_bind_s(my_session,'orcladmin','orcladminpwd');
I need to get the orcladmin password dynamically. Is there a way to achieve this?
We are planning to provide our application users the ability to add new users / modify their own OID information.
i have a problem in my view. when i try to update a column in my view it prompts me with an error. i consulted the database error messages, and the description is as below:
ORA-01779: cannot modify a column which maps to a non key-preserved tableORA-01500 to ORA-02098 4-25 Cause: An attempt was made to insert or update columns of a join view which map to a non-key-preserved table. Action: Modify the underlying base tables directly
i dont understand this "non-key-preserved table". is there any way out from this????. why cant i perform update opearation???. parent tables and the view are as follows:
( create view donations_n_tests as select dn_regno, dn_blood_gp, bagno, sc_test_date, hbs, hcv, hiv, vdrl, mp, dn_base, camp_name, camp.branch_id camp_branch, screening_tests.branch_id as testing_branch from screening_tests left outer join camp using (campno) order by bagno )
I'm trying to modify my dbca template to include two control files. One on ASM +RECO and we we're thinking of adding the second one on the database filesystem $ORACLE_BASE/something/something Within the template there are references to controlfiles two places:
With this configuration there is only one controlfile created and that is on +RECO. What am I doing wrong and also should I add the second controlfile to +DATA instead of fielsystem $ORACLE_BASE?
We have some tables in our database in which for loading data we have the setup in place to do Exchange partition after data load into staging. Today we did changes to column length to one pair of main and staging table. Post that Exchange partition stop working.
I need to modify an existing form, thus I have to resize a canvas and a window for it. In windows, I run it and I got the right display. When I transfer it to dev server running in linux, then I compile and run it, it display the previous/unmodified size. I'm sure my form is the one that running, I placed some text there and it was displayed.
I have my database structured to where i have my tables, constraints, and Rows populated in each table which is 4 tables in particular. for one I want to be able to List all four tables with its content and constraints all at once. What i want to with the PL/SQL is to be able to program a prompt to where the user can just enter the individual order information and the data goes to what ever appropriate column or Table it needs to go to. Now should I be mostly be using procedures, functions or triggers to make this possible.
I tried BRK and COMPUTE commands myself after reading the documentation but its not working...The output of my script is correct but What I want is the information to be displayed on a different way:
Original script for the report:
set serveroutput on size 1000000 set pages 10000 set lines 1000 set arraysize 1 set trimspool on set heading off [code]....
The output should be exactly as above as the requirement is this should be in this particular order and how many is the count in each thoroughfare or locality, as shown in the final output, above.
Data from table 1 is printed first and after every X number of rows another set of data either from the same table or a different table needs to be printed.
I am working on an application that pulls data from an Oracle view into Microsoft Excel (Oracle 11g, MS Excel 2003). It is an automated pivot table created through vba. The problem I'm having is that the decimal points from number columns are being truncated - they don't make it to the pivot table.
When I use the pivot table wizard and set the external data source using a SQL string (select * from view), the precision is not lost. When I create the pivot table automatically in vba, the precision is lost. The pivot table settings, regardless of how the data is brought in, shows the format of the number column as general. This tells me that Excel is trying to figure out what the data type is, and can't (not smart enough).
When looking at the description of the view, the data type is NUMBER (no precision). The table that it is pulling from has the precision set (NUMBER(11,3)). I have tried using the following, but it doesn't work:
select to_number(field_name, '99999999.999') field_name from view
In my Project, there are many queries have join conditions between Number and Vrahchar2. Will it give any wrong results or Invalid Number exception even the varchar2 column always contains the number data?
Example: Select * from Table_t1 t1, table_t2 t2 where t1.num_col = t2.var_col -- Here var_col always hold only numaric data.
I need to read a huge number of rows, say in lakhs and then need to populate it in data block. Since it is having huge data am never able to run the form. it hangs after some time. when i test with few rows it is working. so no problem in coding.