How To Right Justify And Zero Fill In Oracle
Dec 9, 2008How to right justify and zero fill in oracle ?
as example input data is '1' and i want to insert '0' in front of the input data and the output will be 01 .
ADVERTISEMENT
SQL & PL/SQL :: How Does Oracle Fill Missing Date Parts
Oct 10, 2010What happens when I enter date with missing parts ? for example if there is a field of type Date and I inserted the value To_Date ('12/3/2005', 'dd/mm /yyyy') what will the values of the missing date parts be ? hh and mi and ss .so how does Oracle completes the missing parts of the date.
View 6 Replies View RelatedSQL & PL/SQL :: Assigning ID (Fill SID Column)
Feb 4, 2013I have a table with 200k entries, which has ~12k unique combination of columns that I wish to assign an ID to. My real world problem is with addresses and street components (of which nulls are allowed); but this should be a reasonable example.
DROP TABLE tt;
CREATE TABLE tt AS
SELECT ROWNUM id,
CASE
WHEN MOD(ROWNUM, 107) = 0 THEN NULL
ELSE MOD(ROWNUM, 101)
END
c1,
CASE
[Code]...
Now I have 3 ways to assigned an ID (fill the SID column).
The first is congruent with the method most commonly used, but seems especially slow in this case. I think this is due to the COALESCE/TO_CHAR usage. Can this be optimized?
DECLARE
CURSOR c1 IS
SELECT a.*, sid_seq.NEXTVAL sid
FROM (SELECT DISTINCT c1, c2 FROM tt) a;
BEGIN
FOR s IN c1 LOOP
UPDATE tt d
[Code]...
The second I've made relies on the usage of a temp table, which I really don't like, but it is faster; so that is something at least.
CREATE TABLE ttt AS
SELECT a.*, sid_seq.NEXTVAL sid
FROM (SELECT DISTINCT c1, c2 FROM tt) a;
[Code]...
The third discards the sequence, and uses dense_rank. This allows us to use the ID (which might have an index, whereas c1 and c2 will not).
MERGE INTO tt d
USING (SELECT id, (SELECT COALESCE(MAX(sid), 317) FROM tt) + DENSE_RANK() OVER (ORDER BY c1, c2) sid FROM tt) s
ON (d.id = s.id)
WHEN MATCHED THEN
UPDATE SET d.sid = s.sid
The ideal solution would use the SEQUENCE, not use a temporary table and match/merge/update based on ID, and be 'fast'; but I cannot conceive of such a solution; does one exist? What would you do?
SQL & PL/SQL :: Query To Fill Data From Top Records
Mar 5, 2010I want to fill data from the above records in the same column. How to do that by use SQL command ?
from
Invoice_No.Line_Item
000011
2
3
000021
2
000031
000041
2
000051
to
Invoice_No.Line_Item
000011
000012
000013
000021
000022
000031
000041
000042
000051
SQL & PL/SQL :: Using Optional Parameters To Fill Update Statement?
Jul 17, 2011I need to use funtion optional parameters to construct an update statement in the funtion body. I have two scenarios, either both parameters are not null or the 2nd is null. Do I have to use
IF
(param2 is null)
THEN
udpate using param1 only
ELSE
update using both param1 and param2
END IF;
or is there a shortcut to do this?
SQL & PL/SQL :: Fill VARRAY Using Table Fields And While Loop?
Jun 13, 2011I am using arrays on this procedure but would like to use a WHILE LOOP to fill the array. The first element uses a balance forward amount unlike the next 11 elements so I would think I have no other choice for the first element.
jde_account_balances is the table name that stores the amounts.
Here is what I currently have coded:
X INTEGER;
Y INTEGER;
Z INTEGER;
[code]...
SQL & PL/SQL :: Procedure Containing Select Statement To Fill Page Items
Oct 24, 2011I would like to fill items :P200_A and :P200_B and so on with the result of a SELECT which depends on the different values of many select lists.
E.G. :P200_list_alpha with the list of values
STATIC:less than 10;less,equal than 10;equal,above 10;above,indifferent;indiff
:P200_list_beta with the list of values
STATIC:active;active,passiv;passiv,excluded;excluded
How do I write the select statement ? I think it has to be executed in an anonymous PLSQL Procedure (after submit).
What is a convenient way to write the select statement ? I could imagine to use lots of IF , ELSIF, ELSE statements and in each branch (here 12 ) the whole/complet SELECT statement is written.
How to solve this problem in an elegant way ? In my opinion the CASE statement could be useful, but how to use it in the WHERE clause with this nested conditions ?
Application Express :: On Button Click To Fill Out Form On Same Page As Report
Jun 27, 2012I have a report that contains a name that I want to pass in to a field in a form on a different region on the same page. My button located on the report is has its own column called ADD_LINK. Here is the button.
<button id="apexir_btn_ADD" class="apexir-button" type="button" value="Add" onclick=""><span>Add</span></button>
Once that button is clicked, I have the form region fade in. The region is called Add Tag to Employee, which has P4_ROWID, P4_EMPLOYEE_ID, and P4_TAG_ID. Once the region is displayed, I want the #NAME# to show in the P4_EMPLOYEE_ID.
I did copy this form from another page, where I had it working before. It made more sense to move it to this page for interface purposes, so maybe that tidbit will work as well.
Security :: How To Configure Oracle EM With Newly Created Oracle Instance On 10g DB
Dec 11, 2012How to configure Oracle EM with newly created Oracle Instance on Oracle 10g DB,which is Single Instance DB but not RAC ,when I start the Oracle EM it is starting the default DB which created during Oracle Server Installation.
View 0 Replies View RelatedNetworking And Gateways :: Oracle Database Gateway Installing On Oracle 8?
May 17, 2011But i do a lot of work-arounds to make sure the installation does not make corrupt my OracleAgent,OracleService and OracleDataGatherer. If i just let the installation do its thing i have problems with my libraries, and can't start anything.
The errors are : Procedure entry point 'BlaBla' could not be located in the Dynamic Link library 'AnyName'.
I notice that i have 2 versions of the libraries in OracleHomeBIN (one version 8, one version 11). The programs that start the OracleAgent,Service and DataGatherer call the old libraries but expect to find values that can be found only in new libraries.
PL/SQL :: Oracle SQL Template To Create Re-usable DDL / DML Scripts For Oracle Database
Jun 13, 2012I have a requirement to put together a Oracle SQL template to create re-usable DDL/DML Scripts for Oracle databases.Only the Oracle DBA will be running the scripts so permissions is not an issue.
The workflow for any DDL is as follows:-
1) New Table
a. Check if the table exists from the system/admin views.
b. If table exists then give message "Table Exists"
c. If table does not exist then execute DDL code
2) Add Column
a. Check if Column exists for a given table from system/admin views
b. If column exists in the specified table,
b1. backup table.
b2. alter table to make changes to the column
b3. verify data or execute dml script convert from backup to the new change.
c. If Column does not exist
c1. backup table
c2. alter table to add column
c3. execute dml to populate column with default value.
The DML scripts are for populating base tables with data required for business operations.
3) Add new row
a. check if row exists by comparing old values of each column with new values to be added for the new record.
b. If exists, give message row exists
c. If not exists, add new record.
4) Update existing record (We have createtime columns in these tables so changes can be tracked)
a. check if row exists using primary key.
b. If exists,
b1. deactivate the record using the "active" column of the table
b2. Add new record with the changes required.
c. If does not exist, add new record with the changes required.
Moving Oracle 11g UNIX Sun Solaries To Oracle 11g Linux Readhat OS
Aug 8, 2012We would be moving oracle 11g unix sun solaries to oracle 11g Linux readhat OS. what would be the disadvantage and what are the item needs to be verified. Basically advantage of oracle 11g Linux readhat OS.
View 2 Replies View RelatedJDeveloper, Java & XML :: Update Xml Element In Oracle 10gr1 Without Using Oracle XDB
Jun 27, 2013I would like to update an XML element without using the function APPENDCHILDXML or INSERTCHILDXML because they are not available in Oracle 10GR1.
In my database, Oracle XDB is not installed.
The following query fail with the following error : ORA-00904: "INSERTCHILDXML" : identificateur non valide
update scl_profile
set profile_data =
insertChildXML(profile_data,'/exportImportMarcheCriteria','colonnesExport',
XMLType('<colonnesExport>ENTETE_GESTIONNAIRES_AUTORISES</colonnesExport>'))
where profile_xmltype =
'fr.mipih.marches.marche.criteres.ExportImportMarcheCriteria'
and profile_type =
'eMagh2.MRGS.AccesMarche.ListeMarche.Export.OptionsExportImport';
[code]........
If i try to use the package DBMS_XMLDOM, i have the following error :
ORA-06550: Ligne 3, colonne 11 :
PLS-00201: l'identificateur 'DBMS_XMLDOM.DOMDOCUMENT' doit etre declare
ORA-06550: Ligne 3, colonne 11 :
PL/SQL: Item ignored
I think it's because ORACLE XDB component is not installed in my database.
Application Express :: Downloading Oracle 11g And Then Oracle APEX
Aug 7, 2012This time i am downloading the Oracle 11g and then the Oracle APEX...(they are not in one package...right ? i mean they both have to download separately)i really new in oracle / PL/SQL. I'm not a programmer but i interest about database programming.For now i still use Ms. Access to develop an aplication and i really suprised i found this Oracle APEX because it an RAD tool too same as Access but more powerfull i think.
View 2 Replies View RelatedConnect To Oracle From Another Server That Does Not Have Any Oracle Applications On It
Mar 7, 2007I am trying to connect to Oracle from another server that does not have any Oracle applications on it.
I tried Perl, PHP, Java but each require parts of Oracle installed.
Oracle Forms 6i And Oracle DB 10gR2 Or 11
Jul 8, 2011I am working on an application which is on Oracle forms and Reports 6i.It connects to a database which is Oracle 9i. My client wants to upgrade the Database to Oracle 10gR2 or Oracle 11g. I searched in many forums and also saw in Interoperability table which says it is not compatible. But some users are using it fine.
View 1 Replies View RelatedServer Administration :: Upgrade Oracle 9.2.0.1.0 From RHEL4 Platform To Oracle 11g Rel 2 On RHEL5 Platform
Feb 4, 2012Can I directly upgrade Oracle 9.2.0.1.0 From RHEL-4 platform to Oracle 11g Rel 2 on RHEL -5 platform?I have the export dump and cold backup of Oracle 9.2.0.1.0 database.
View 11 Replies View RelatedRAC/ASM Clusterware Installation :: ORA-03114 / Not Connected To ORACLE Error With Oracle DB Installation
Dec 3, 2012I am getting "ORA-03114: not connected to ORACLE" while installing "Oracle Database 11GR2" on "Oracle RAC 11R2" cluster setup. I am getting this error at the time of "Oracle Database Configuration Assistant" configuration reaches till 39% on HP-UX 11.31 IA64 platform.
View 25 Replies View RelatedCannot Log In To Oracle?
Jul 12, 2013I have problems to log in to Oracle?What name should I use for log in?
View 1 Replies View RelatedOEM In Oracle 10 G
May 29, 2008I want to manage database (create table ,insert data ,delete table etc ) by using tool in oracle 10 g similar to a OEM in oracle 9 i in order to manage the database simply and not using SQL command
I used to open OEM in browser but I don't know to manage my database method by using OEM . In a screen I find many function for example Host CPU Active session etc but I can't find function are about create database create table etc . And this is my question
How I did open OEM in order to manage database (create table ,insert data ,delete table etc )?
How To Know Row Id In Oracle 10g
Jun 20, 2013Is there anyway to know rowid in oracle10g when i know block number?
View 7 Replies View RelatedXE :: Oracle 11g XE 64 Bit?
Jan 29, 2013, does oracle 11g XE 64 bit with database and client for Windows 8pro is available?
View 2 Replies View RelatedInstalling Oracle 9i On XP
Nov 23, 2006While installing oracle 9i on windows XP. Oracle database configuration agent fails.
And it throws following error message.
java.lang.NoClassDefFoundError: oracle/sysman/emSDK/common/nls/MessageBundle
at oracle.sysman.assistants.dbca.backend.InitParamAttributes.loadAllInitParams(Compiled Code)
at oracle.sysman.assistants.dbca.backend.InitParamAttributes.initialize(InitParamAttributes.java:313)
at oracle.sysman.assistants.dbca.backend.InitParamAttributes.<init>(InitParamAttributes.java:255)
[code]........
Indexes In Oracle
May 9, 2011this is just a generic question. If a table has multiple Indexes on it say a table of 20 columns where 8 columns have indexes on it (each columns out of the 8 has an index on it and out of which three columns contain unique indexes on it).
If i run a query on this table where only two columns are specified in the where clause are the remaining 6 indexes on the table scanned during query execution.
Query In 10g Oracle
Apr 18, 2008I am facing problem with a select query in oracle 10g database from vb.net.It was working for oracle 9. The select statement I have written is as follows
Str=" select UCC.table_name, UCC.constraint_name, UCC.column_name, UCC.position, UC.constraint_type " & "from USER_CONS_COLUMNS UCC,USER_CONSTRAINTS UC " & "where (UCC.constraint_name = UC.constraint_name) " & "and UC.constraint_type = 'P' " & "and UCC.table_name = " & " '" & TableName & "'"
Oracle 9i To 10g Upgrade
Jul 3, 2010I am trying to upgrade database from 9.2.0.7 to 10.2.0 in my test server. Here are the steps i am planning to do.
1. Export user data of production, Import in test db(ABC) using Destroy=Y Fromuser,touser parameter( this step just to refresh old test data)
2. Install 10g db, Use DBUA Utility of 10g to start upgrade>select 9i database which i want to upgrade
3. follow DBUA instruction ..create datafile paths, init.ora file, tnsnames,listener... same as the existing setup i have of 9i(so i can make replica of 9i in 10g)
question: I have another database(xyz) in test server its also in 9i, I don't want to upgrade that for now. so do i need to use one listener /tnasnames file of 10g and include setting of 9i in that 10g file ?
How to manage init,tnasnames,listener files setting in 2 db versions . Is there anything else i need to do to perform upgrade 9i-10g ? or its all ?
I will install just 10g database will import full export of 9i db. if i can do full import in 10g or should i just go for fromuser, tuser option to import? i believe for that i need to create datafiles, user,grant in new 10g db. I am not sure which option is best to perform 10g upgrade?
How To Patch Oracle 11g
Apr 19, 2012I've been told by management this weekend I will be required to perform a complete patch of our Oracle 11g database.
Problem is I really have never installed / patched Oracle. I'm able to access pretty much any database here and interact with it from a SQL stand point so since I'm the only person in my entire office who knows SQL, they decided I can become their full blow DBA.
Currently the database is running a unpatched version:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
Once I download the latest patch (11.2.0.2.0) files from Oracle & perform a complete backup using RMAN:
RMAN> backup full as compressed backupset database plus archivelog;
How exactly does one properly patch / update Oracle 11g to the latest version?
GUI Application For Oracle 10g
Apr 5, 2010What is the best GUI application i can used to access oracle database, run SQL query and etc in window 7 ?.
I have installed toad for oracle, but i do not know how to configure it and connect to oracle 10g.
To Deploy Oracle R12 EBS
Dec 11, 2010We plan to deploy oracle EBS R12. what are all the components needed to build this infrastructure. ex. Oracle 11g database, oracle 10g application server.
also which solution should be deployed to ensure the Enterprise single-Sign On (ESSO).
How To Upgrade Oracle 9.2.0.1 To 9.2.0.4
Feb 3, 2011I want to know the detailed steps for the Upgradation of database from Oracle 9.2.0.1 to 9.2.0.4?
View 4 Replies View Related