computer technician - Online Tech Support
  Home
 

This Online Tech Support page is written based on examples to be easier to follow. After logging on to Oracle database you may see error ORA-28002: the password will expire within X days and the reason for that is Oracle databases have set by default on users’ password expiration limit. To change it you would need to amend the Oracle Profile. You can change the password expiring time longer or shorter or turn it off. The syntax to amend password life time is following:

ALTER PROFILE <PROFILE_NAME> LIMIT PASSWORD_LIFE_TIME UNLIMITED;

The first thing to do is to find out your user profile name and the SQL syntax below will help you out replace the “<username>” with your own one as  on the example below the syntax.

SELECT PROFILE FROM DBA_USERS WHERE USERNAME = '<username>';

This Oracle query below finds user SYSTEM profile name:

SELECT PROFILE FROM DBA_USERS WHERE USERNAME = 'SYSTEM';

online tech support computer help computer technician computer problems ORACLE PROFILE in ORACLE PROFILE ORACLE DBA_USERS in ORACLE DBA_USERS ORACLE DBA_USERS in ORACLE PASSWORD_LIFE_TIME ORACLE PASSWORD_LIFE_TIME in ORACLE database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql ORA-28002: the password will expire within X days

The output above shows that the SYSTEM user belongs to group named “DEFAULT“.

Now to set the profile password limit to never expire you can use the following example. Change the profile name (DEFAULT) to your user profile and run the statement as SYSDBA.

alter profile DEFAULT limit password_life_time UNLIMITED;

online tech support computer help computer technician computer problems ORACLE PROFILE in ORACLE PROFILE ORACLE DBA_USERS in ORACLE DBA_USERS ORACLE DBA_USERS in ORACLE PASSWORD_LIFE_TIME ORACLE PASSWORD_LIFE_TIME in ORACLE database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql ORA-28002: the password will expire within X days

Now to active the profile change for username you would need to change the password for one more time.

ALTER USER <USERNAME> IDENTIFIED BY <PASSWORD>;

The statement below changes user SYSTEM password to word “SYSTEM“.

alter user SYSTEM identified by SYSTEM;

online tech support computer help computer technician computer problems ORACLE PROFILE in ORACLE PROFILE ORACLE DBA_USERS in ORACLE DBA_USERS ORACLE DBA_USERS in ORACLE PASSWORD_LIFE_TIME ORACLE PASSWORD_LIFE_TIME in ORACLE database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql ORA-28002: the password will expire within X days

Also you can check your user expiry date using the next select statement where you should replace the “<username>” to your user:

SELECT EXPIRY_DATE FROM DBA_USERS WHERE USERNAME = '<username>';

The Oracle SQL returns expiry date for user SYSTEM and it shows the date is empty and that does mean it is turned off.

SELECT EXPIRY_DATE FROM DBA_USERS WHERE USERNAME = 'SYSTEM';

online tech support computer help computer technician computer problems ORACLE PROFILE in ORACLE PROFILE ORACLE DBA_USERS in ORACLE DBA_USERS ORACLE DBA_USERS in ORACLE PASSWORD_LIFE_TIME ORACLE PASSWORD_LIFE_TIME in ORACLE database software database oracle sql database programming database oracle retirement planning retirement sql database oracle database sql ORA-28002: the password will expire within X days



See Also:
Home Oracle Create User

 

This page is based on examples to be easier to follow. The Oracle SELECT statement can be written in the way as it is regulated by the ANSI standard. The general Select syntax is:

SELECT <columns> 
  FROM <tables>;

The first example is the most basic Oracle Select statement with using Oracle DUAL table.

SELECT * 
  FROM DUAL;

online tech support computer help computer technician computer problems oracle select oracle select in oracle database software database oracle listagg oracle cursor oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

About the SQL statement above we are using table Dual that is an one-row table available in all Oracle database versions.

To see how looks an Oracle Select statement with a real table that you for example just created, we will use a table named CLIENTS and the Select statement will look follow:

SELECT * 
  FROM CLIENTS;

To write a query using the Oracle Dual table and to making it to return more than one row, we wrote the following example. The Oracle Select statement will return 10 rows. So what did we exactly write here? The query is using pseudo-column named Oracle ROWNUM and it is combined with CONNECT BY operator. The Connect By operator is taken from Oracle hierarchical queries and it creates and join with the table itself this is called as self-join. Using Connect By operator the query is smaller and easier to use.

 SELECT ROWNUM 
   FROM DUAL 
CONNECT BY ROWNUM < 11;

online tech support computer help computer technician computer problems oracle select oracle select in oracle database software database oracle listagg oracle cursor oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

If you are using Oracle 11g database then we got useful build-in function Oracle LISTAGG. It helps you to group the values on the same line and separate with some character defined by you. In this computer help example we are going to separate the values using coma. The following Oracle Select statement will show you how to use Oracle Listagg function and it sets all Oracle Rownum values on the same line, so the last picture will be turned to the next picture. Compare them both to see the differences.

SELECT LISTAGG (ROWNUM, ',') WITHIN GROUP (ORDER BY ROWNUM) AS ROW_NUMBERS
  FROM DUAL CONNECT BY ROWNUM < 11;

online tech support computer help computer technician computer problems oracle select oracle select in oracle database software database oracle listagg oracle cursor oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Important To Know:
While you tried the query and it raised error ORA-00923: FROM keyword not found where expected then your Oracle database version does NOT support the Oracle Listagg function and you would not be able to use it. Function Listagg Oracle has been included to version 11g. See the ORA-00923: FROM keyword not found where expected exception on the following picture:

online tech support computer help computer technician computer problems oracle select oracle select in oracle database software database oracle listagg oracle cursor oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Oracle Select has quite many different options and ways to write it, so the next example will be Oracle Select with Oracle Partition table. The query with Oracle partition can be written as well many ways and will bring out following two most common styles. It is important to know that the partition oracle method will give much better performance than a usual table with the same amount of rows. Read more about oracle partition and table partitioning from here.

In this Oracle partition example we do have table “old_users” that has column “ACTIVE_YEAR” as “Number(4) Not NULL”. All users in that table have the active_year column filled with a year number i.e. “2012”. The table is partitioned by using column “ACTIVE_YEAR” and since we do have data for only years 202 and 2011 the Oracle partition names are “YEAR_2012” and “YEAR_2011”. To find all users for year 2012 use of the following queries:

SELECT *
  FROM old_users PARTITION (YEAR_2012);

or

SELECT *
  FROM old_users
 WHERE ACTIVE_YEAR = 2012;

P.S. When you are using an Oracle partition table do not forget to add the partition condition (“ACTIVE_YEAR = 2012”) as the first condition into your Select Oracle statement to make sure it always uses the Oracle Partition By condition.

You can also write an Oracle Select statement using an Oracle Cursor function. To see the result you need to use some visual PL/SQL development tool like Toad, SQL Navigator or Oracle SQL Developer. Another option would be to insert the Oracle cursor result into declared Oracle collection type and see the result using a PL/SQL procedure or anonymous block. Still you should be able to execute without declaring anything the following example what is Oracle Cursor in Oracle Select query.

SELECT CURSOR (SELECT ROWNUM 
                 FROM DUAL 
              CONNECT BY ROWNUM < 11) AS cursor_lines
FROM DUAL;

online tech support computer help computer technician computer problems oracle select oracle select in oracle database software database oracle listagg oracle cursor oracle sql database programming database oracle retirement planning retirement sql database oracle database sql



See Also:
Home

 

Your Ubuntu or Linux doesn’t work with website uk.advfn.com and with Firefox. The ADVFN website says that they don’t support Linux. For this type of problem Ubuntu support doesn’t give more answers either. The Online Tech Support have still work around to have Ubuntu access ADVFN .

We are guessing you already tried to log on Advfn.com web and everything seems to be working fine beside the Java applet. The problem is in Java’s Ubuntu (Linux) version that is available by default in your Linux downloads. The following guide should get the Advfn access with Ubuntu to work.

server linux server online tech support computer help computer technician computer problems computer ubuntu support java ubuntu java java.lang.SecurityException: Changing the SecurityManager is not allowed unix server unix sql database programming database oracle retirement planning retirement sql database oracle database sql

The first thing to do is to download a working version of Java package. Go to www.java.com and click on the “Downloads” link. For Linux system the web page should offer Linux downloads. Click on the link where is written only “Linux” and the file should look like something as jre-<version>-linux-i586.tar.gz where the <version> string would be the latest one available.

Move the Java package somewhere among of the other Java versions. You may need to used user root permissions to do it. The folder could be something as the Online Tech Support is using in this guide for example /usr/lib/java and the command

# sudo mv jre-7u21-linux-i586.tar.gz /usr/lib/java

server linux server online tech support computer help computer technician computer problems computer ubuntu support java ubuntu java java.lang.SecurityException: Changing the SecurityManager is not allowed unix server unix sql database programming database oracle retirement planning retirement sql database oracle database sql

Make sure the file has executable rights.

# sudo chmod a+x jre-7u21-linux-i586.tar.gz

server linux server online tech support computer help computer technician computer problems computer ubuntu support java ubuntu java java.lang.SecurityException: Changing the SecurityManager is not allowed unix server unix sql database programming database oracle retirement planning retirement sql database oracle database sql

To unpack the file use the following command.

# sudo tar zxvf jre-7u21-linux-i586.tar.gz

server linux server online tech support computer help computer technician computer problems computer ubuntu support java ubuntu java java.lang.SecurityException: Changing the SecurityManager is not allowed unix server unix sql database programming database oracle retirement planning retirement sql database oracle database sql

Since we are using Firefox in the example then the next things would be to link the Firefox library to the new Java version file that you just downloaded. To do it you need to create a new symbolic link under Firefox plug-ins and to point to libnpjp2.so file.

Go to your Firefox plugins folder it should be something like /usr/lib/firefox/plugins

# cd /usr/lib/firefox/plugins

Create a link to file libnpjp2.so.

# ln -s /usr/lib/java/jre1.7.0_21/lib/i386/libnpjp2.so

server linux server online tech support computer help computer technician computer problems computer ubuntu support java ubuntu java java.lang.SecurityException: Changing the SecurityManager is not allowed unix server unix sql database programming database oracle retirement planning retirement sql database oracle database sql

After having plugin libnpjp2.so in the Firefox directory and pointing to the new jre version it should make the Advfn Java applet to work.

If the last point didn’t make the Java to work then take a look at the the Java plugin under Tools -> Add-ons -> Plugins and make sure it is Enabled. If the Java Plugin 1.7.x_xx didn’t appear there then add a link into Mozilla (/usr/lib/mozilla/plugins) folder too as the following command.

# cd /usr/lib/mozilla/plugins

Create a new link as the following example.

# sudo ln -s /usr/lib/java/jre1.7.0_21/lib/i386/libnpjp2.so

server linux server online tech support computer help computer technician computer problems computer ubuntu support java ubuntu java java.lang.SecurityException: Changing the SecurityManager is not allowed unix server unix sql database programming database oracle retirement planning retirement sql database oracle database sql

Some Firefox versions are set to look for plugins from mozilla folder. Make sure you have only one Java plugin active or the Java wouldn’t work correctly.

server linux server online tech support computer help computer technician computer problems computer ubuntu support java ubuntu java java.lang.SecurityException: Changing the SecurityManager is not allowed unix server unix sql database programming database oracle retirement planning retirement sql database oracle database sql

Now has left only to try it out!

server linux server online tech support computer help computer technician computer problems computer ubuntu support java ubuntu java java.lang.SecurityException: Changing the SecurityManager is not allowed unix server unix sql database programming database oracle retirement planning retirement sql database oracle database sql

Sometimes the web browser will raise error: java.lang.SecurityException: Changing the SecurityManager is not allowed. The reason is that in your computer has more than one Java tool installed and the another tool is set as the default one. Firefox is opening the Java applet with link you just defined and the second default Java tool will take the Java session over because it is the main or the default one.

Online tech support has seen this type of computer problems mainly with the IceTea Web Plugin. To resolve the java.lang.SecurityException: Changing the SecurityManager is not allowed go to Firefox menu -> Tools -> Add-Ons and press “Disable” button after the “IceTea Web Plugin“. After you have disabled the IceTea Web Plugin close your all FireFox browsers and open them again and try the Advfn.com web page one more time.



See Also:
Home

 

This Online Tech Support page is based on examples to be easier to follow. The Oracle Delete statement removes a line or lines from Oracle database table or from an Oracle object. The delete operator syntax is:

DELETE FROM <table> WHERE <your condition>;

For example we are going to use table old_users in the Oracle database and to remove all lines from that table we will use the following command:

DELETE FROM old_users;

online tech support online computer help computer technician computer problems oracle delete oracle drop table oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

On the picture above you can see that we deleted 4221 lines from table old_users and since we didn’t apply any restrictions the statement took all possible lines – 4221.

To add some restrictions and avoiding deleting all lines from the Oracle table you should use keyword WHERE and set some conditions. This condition below will remove only users with name John.

DELETE FROM old_users 
 WHERE name = 'John';

online tech support online computer help computer technician computer problems oracle delete oracle drop table oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

As you see the Oracle delete statement returned “0 rows deleted” and that means no lines were removed from the table. The reason is there wasn’t any user named John.

Sometime the tables can have more than million lines and the Oracle delete statement takes very long time to finish. In that case if you need for example to remove all lines in a table we suggest using another command similar to Oracle Delete but named Oracle Truncate Table. Please keep in mind that truncate table in Oracle database not only removes all lines but as well does commit and all your transactions will be saved. The syntax of Oracle truncate table is:

TRUNCATE TABLE <table_name>;

In the next example we are going to remove all lines in table old_users using TRUNCATE TABLE operator.

TRUNCATE TABLE old_users;

online tech support online computer help computer technician computer problems oracle delete oracle drop table oracle truncate table oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The Oracle truncate table does not return a number of delete lines, but it should be quick enough. As always enough can be sometimes not enough and it can stay slow with even huge amount of data. The solution for slow Truncate table in Oracle would be to use Oracle drop table statement. The drop table Oracle syntax is:

DROP TABLE <table_name>;

The following method described by Online Tech Support is using the Oracle table old_users and goes through step by step of the fast removing lines process using Oracle drop table operator.

  1. Rename table “old_users” to “old_users_drop” – to keep the timeout small just rename it
  2. Create new table “old_users” with the same syntax as it was originally
  3. Drop all indexes, constraints, triggers etc for “old_users_drop”
  4. Create all indexes, constraints etc to fresh table “old_users”
  5. Recompile all objects to make sure all code and objects are valid again
  6. Drop table “old_users_drop”

If you need to delete only part of Oracle table’s data for a huge table you would need to re-design your Oracle table structure and start using the Oracle table partition method. Read more about oracle partition and table partitioning from here.

To describe the Oracle table partition concept we will use again table “old_users” and for example it has column “ACTIVE_YEAR” as “Number(4) Not NULL”. All our users have the column filled with a year number i.e. “2012” and “2011” and the table is partitioned by the “ACTIVE_YEAR” column. The Oracle partition names are “YEAR_2012” and “YEAR_2011”. To remove all users from year 2011 there are the following ways to do it:

Using oracle delete command and applying it directly to partition:

DELETE FROM old_users PARTITION (YEAR_2011);

Also you can truncate table partition using Oracle Truncate Partition command and additional keyword “DROP STORAGE” will free the allocated space:

ALTER TABLE old_users
   TRUNCATE PARTITION YEAR_2011 DROP STORAGE;

While you are dropping Oracle partitions you are amending the table and some global objects over all partitions can become invalid, so keep in mind that global indexes need to rebuild after doing it.

ALTER TABLE old_users DROP PARTITION YEAR_2011;



See Also:
Oracle Select Home

 

This Online Tech Support page is based on examples. There is some confusion using function Oracle ROWNUM in Oracle SQL queries and especially how to use it in the WHERE clause. This post is trying to bring some clear ideas about it and to explanation what are the up or down-sides using this functions in queries.

To explain the cases better we need some sample data. The following select statement will generate 20 lines with repeating values using Oracle MOD and ROWNUM functions.

 SELECT mod (rownum,3) AS numbers
   FROM dual
CONNECT BY rownum < 21;

The query output looks follow:

online tech support computer help computer technician computer problems oracle rownum oracle rownum in oracle database software database oracle sql database sql oracle retirement planning retirement

The next SQL will use the query above and it has additional condition that would limit the output only to values that has number “2” and the row limit is set to up to 3 rows. Take a look at the query below:

SELECT *
  FROM (SELECT mod (rownum,3) AS numbers
          FROM dual
       CONNECT BY rownum < 21)
 WHERE numbers = 2
   AND rownum <= 3;

online tech support computer help computer technician computer problems oracle rownum oracle rownum in oracle database software database oracle sql database sql oracle retirement planning retirement

The third example is done using ORDER BY condition and in this example we would like to have in the output up to 4 rows with the greatest numbers and the SQL query is following:

SELECT *
  FROM (SELECT mod (rownum,3) AS numbers
          FROM dual
       CONNECT BY rownum <= 21)
 WHERE rownum <= 4
 ORDER BY numbers DESC;

online tech support computer help computer technician computer problems oracle rownum oracle rownum in oracle database software database oracle sql database sql oracle retirement planning retirement

If you take a look at the output it isn’t what we did expect. Instead of returning four rows with value “2” the query returned all types of values. The reason is in Oracle execution mode that applies first the WHERE conditions and later comes turn to ORDER BY. This is common mistake done by developers and to get the result we are looking for we just need to bring the WHERE condition outside from the main query. See the query below there is two pair of SELECT FROM keywords and the output fills our expectations:

SELECT *
  FROM (SELECT mod (rownum,3) AS numbers
          FROM dual
       CONNECT BY rownum <= 21
         ORDER BY 1 DESC)
 WHERE rownum <= 4;

online tech support computer help computer technician computer problems oracle rownum oracle rownum in oracle database software database oracle sql database sql oracle retirement planning retirement

To avoid the mistakes described above keep in mind the Oracle SQL execution statement and how it does work.



See Also:
Oracle Select Home

 

This Online Tech Support computer technician guide will help you to go through step by step the unlocking process for Samsung Galaxy.

WARNING: this is only a demonstration guide and YOU would NOT be doing it with your mobile phone. Installing any custom image on the Samsung Galaxy will void the warranty.

Please keep in mind the unlocking process can damage your phone and to follow this guide do it totally on your OWN risk only.

On this example we are using phone Samsung Galaxy mobile connected via USB cable a PC computer.

  1. Download from web file universal_gb_root_v19.zip to your computer.
  2. Connect your phone with computer using your phone USB cable (you may use KIES program from Samsung web page) or via Bluetooth connection.
  3. Upload the universal_gb_root_v19.zip file to the top directory (/) on your SD card so it would be easier to find.
  4. Turn your mobile phone off and disconnect it from computer.
  5. Turn the phone on in recovery mode:
    • Samsung 551, Pro – hold down buttons “T” + “Power”
    • Samsung Ace, Gio, Fit, Mini, Pop – hold down buttons “Home” + “Power”
    • Samsung Pro GT-B5510 ,Ace GT-S5830i, Xcover, Young, Y-Duos, Y-Pro, Y-Pro Duos, M-Pro – hold down buttons “Volume Up” + “Home” + “Power”
  6. In Android system recovery menu use volume buttons to move up and down. Chose menu “apply update from sdcard” press “Home” button and select file universal_gb_root_v19.zip and press the “Home” button again to apply it.
    online tech support computer help computer technician computer problems computer how to unlock samsung galaxy unlock galaxy toolbox sql database programming database oracle retirement planning retirement sql database oracle database sql
  7. The installation process will end with message “Install form sdcard complete”. Select “reboot system now” and reboot your mobile.
  8. Open your app downloading tool for example “Play Store” and look for free app “Galaxy Toolbox” and install it on your phone. Execute the app and follow the introductions. On the main menu after the Warning window you can find option “Network unlock“. Applying this option will remove the network lock on your mobile phone.
    online tech support computer help computer technician computer problems computer how to unlock samsung galaxy unlock galaxy toolbox sql database programming database oracle retirement planning retirement sql database oracle database sql

This is the end of online tech support computer technician example how to unlock Samsung Galaxy. There could be different versions and screens of Samsung Galaxy, but the main unlocking process will remain the same.



See Also:
Home

 

This page is based on examples to be easier to follow. The Oracle Update allowing to change or update existing values in Oracle table or materialized view without deleting or inserting new rows. The statement is regulated by ANSI standard and the basic Oracle Update syntax is:

UPDATE <table>
   SET <column> = <new_value>
[ WHERE <the_condition(s)> ];

The first example will use table CUSTOMERS that has two columns FORENAME and SURNAME. We are going to change all “John” names to “Sean” and the SQL statement is following:

UPDATE customers
   SET forename = 'Sean'
 WHERE forename = 'John';

online tech support computer help computer technician computer problems computer oracle update oracle update in oracle sql update oracle sql update in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

As you the SQL output shows we did amend one row’s data. To update more than one column’s data we will just add additional column to the Oracle Update statement as it is done on the next example. On the next example we will change all “John” forenames to “Sean” and their new surname will become “Longs“.

UPDATE customers
   SET forename = 'Sean',
       surname = 'Longs'
 WHERE forename = 'John';

online tech support computer help computer technician computer problems computer oracle update oracle update in oracle sql update oracle sql update in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

Once again a line has been updated. Similar to the last example you can add as many columns you need to the update statement and change their values depending on the WHERE condition.

The third example will show how to use Oracle Select query in the Oracle Update statement. You can use this statement to change the column values with taken from another existing table. The same condition applies here as on the last example and all John names will be changed to “Sean Longs

UPDATE customers
   SET (forename,surname) = (SELECT ('Sean','Longs') FROM dual)
 WHERE forename = 'John';

online tech support computer help computer technician computer problems computer oracle update oracle update in oracle sql update oracle sql update in oracle sql database programming database oracle retirement planning retirement sql database oracle database sql

The output shows the same result as on all last pictures – one row has been updated. Take a look at the update statement we are using the Oracle Dual table to return the values but this has been done here only for the example purpose. You can use in the same way any Oracle table or a view, but it is important to know that column types and amount has to match in both brackets or your update query will raise an error and fails to complete the change.

The second hint about using select queries in the Oracle Update is to use as much as possible ID (unique identification) values in joining. When you do so your Oracle Select wouldn’t slow down your update statement too much.

Once you’ll become a master in writing Oracle updates there are still a few more things to know especially about updating big tables. We are recommending not use Oracle Update on very big Oracle tables or using it with SQL queries that returning huge amount of rows. And that mainly because Oracle Update is one of the slowest process and using it with big data amounts will slow down your entire Oracle database. You can use instead:

  • An Oracle Insert with Oracle global temporary tables. Basically you should write an Oracle Select in the way you want to look the updated lines and insert them into Oracle temporary table.
  • For next delete the existing lines from the original table that needs to be updated.
  • And for last do another Insert from your temporary table to the final table that was supposed to be updated in the first place.

Following this style all the updating process will finish much faster and you will give your code much better performance.


See Also:
Oracle Select Oracle Insert Oracle Delete Home

 

This Online Tech Support page is based on examples. Operator Oracle Insert allowing to add more lines into Oracle database tables or into other Oracle objects. The most basic Oracle Insert statement syntax is:

INSERT INTO <table_name> (<columns>)
     VALUES (<values>);

For example we have table CUSTOMERS that has columns FORENAME and SURNAME. Our Online Tech Support computer technician will add into that table customer “John Lennon”. To do it the insert Oracle statement would be following:

INSERT INTO customers (forename, surname)
     VALUES ('John', 'Lennon');

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement oracle insert oracle insert in oracle database oracle select oracle select in oracle insert returning insert oracle returning insert in oracle

The example above inserted 1 line, but the line will be lost when you log out from the Oracle database. To save the permanent to the database you need to apply COMMIT after your statement as on the following example.

INSERT INTO customers (forename, surname)
     VALUES ('John', 'Lennon');

COMMIT;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement oracle insert oracle insert in oracle database oracle select oracle select in oracle insert returning insert oracle returning insert in oracle

Another way to insert your lines is to take them from another Oracle table using a Select statement. To write Oracle insert using the select statement use the syntax below:

INSERT INTO <table_name> (<columns>)
      (SELECT <columns> FROM <table names> WHERE <conditions>);

Now Online Tech Support computer technician has the second example with Insert Oracle that takes lines from another Oracle table named CUSTOMERS_BACKUP and inserting them directly into the CUSTOMERS table.

INSERT INTO customers (forename, surname)
      (SELECT ocu.forename, ocu.surname FROM customers_backup ocu);

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement oracle insert oracle insert in oracle database oracle select oracle select in oracle insert returning insert oracle returning insert in oracle

Once again to store it use the commit command.

You can get back inserted values from the target table by keyword RETURNING and this is quite handy for new ID values or other values generated during the inserting process. The next Online Tech Support example shows a forename value returned by the Oracle insert statement and we are using a bind value to output it.

VARIABLE v_forename CHAR(200);

BEGIN
  INSERT INTO users_backup (forename,surname)
       VALUES ('John','Lennon' ) 
    RETURNING forename INTO :v_forename ;
END;
/

print v_forename;

online tech support computer help computer technician computer problems oracle database oracle sql database programming database oracle retirement planning retirement oracle insert oracle insert in oracle database oracle select oracle select in oracle insert returning insert oracle returning insert in oracle

As you see in the statement above we did insert name “John Lennon” and since we asked to return only forename the :v_forename bind variable shows value John.


See Also:
Home Oracle Select

 

This page is based on examples to be easier to follow. The Oracle Partition method gives you an opportunity to organize your table’s data for big Oracle tables and split the data between different partitions.

Before you are going to start trying out the Oracle Partition By operators make sure your Oracle database version has all necessary installed. Most Oracle 10g and 11g versions have Oracle Partition enabled by default but there are versions where it isn’t. Log on as SYS user and run the following command:

 SELECT *
   FROM v$option
  WHERE parameter = 'Partitioning';

online tech support computer help computer technician computer problems computer oracle create table oracle partition oracle partition by oracle retirement planning retirement

As you see in this Oracle XE version the Partitioning option is disabled (“FALSE“) and you would need to install additional database scripts. Make sure you’ll make a backup before running this scripts. Also it is better to shutdown the Oracle listener and have users logged out. The Oracle partition scripts can be found under directory $ORACLE_HOME/rdbms/lib and you can install them as the next commands:

$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk part_on
$ make -f ins_rdbms.mk ioracle

If your installation was successful you should see the Partitioning parameter showing “TRUE“. Now you are good to continue with the following examples.

The first example we are going to use table “SALES“. It contains daily basis sales information and every day we are working with data depending on the sale dates. Usually Oracle DBA has created Oracle table as follow:

CREATE TABLE sales
       ( id         NUMBER(17) NOT NULL,
         sale_date  DATE NOT NULL,
         sale_month VARCHAR2(6) NOT NULL,
         product_id NUMBER(17) NOT NULL
        );

online tech support computer help computer technician computer problems computer oracle create table oracle partition oracle partition by oracle retirement planning retirement

P.S. We are trying to keep the example as simple as possible, so we are using only 4 columns. But in a normal situation the table has much more columns and it would be classified as a “big” table. There is a column named “SALE_MONTH” that is character type and has value as “SALE_DATE” only with using to_char mask “YYYYMM”.

Since the sale dates are the main segments in this example then creating partitioned table in Oracle would look like following:

CREATE TABLE sales
      ( id         NUMBER(17) NOT NULL,
        sale_date  DATE NOT NULL,
        sale_month VARCHAR2(6) NOT NULL,
        product_id NUMBER(17) NOT NULL
      )
PARTITION BY LIST (sale_month)
 (PARTITION sale_201201 VALUES ('201201'),
  PARTITION sale_201202 VALUES ('201202'),
  PARTITION sale_201203 VALUES ('201203'),
  PARTITION sale_201204 VALUES ('201204') );

online tech support computer help computer technician computer problems computer oracle create table oracle partition oracle partition by oracle retirement planning retirement

In this example above we are using column “SALE_MONTH” and notSALE_DATE“; the reason is in the partitions – they are NOT getting created or dropped automatically. You have to do it manually using a PL/SQL or SQL script. If the amount of lines are huge daily basis then it makes sense to use daily partitioned table. We do recommend to use instead of DATE type VARCHAR2 types and the value is converted from date.

Important to know:

While you executed the CREATE TABLE statement above and it returned error “ORA-00439: feature not enabled: Partitioning” as on the picture below then go back to the top of this page and try the Partitioning parameter. If the parameter is TRUE then something went wrong with the installation scripts other wise try to install the Partitioning scripts.

online tech support computer help computer technician computer problems computer oracle create table oracle partition oracle partition by oracle ORA-00439: feature not enabled: Partitioning retirement planning retirement

Another common mistake is to forgot add brackets to the PARTITION BY LIST (<table_column>) keywords. In that case you will see error “ORA-00906: missing left parenthesis”. As on the picture below.

online tech support computer help computer technician computer problems computer oracle create table oracle partition oracle partition by oracle ORA-00906: missing left parenthesis retirement planning retirement

The second common mistake would be to forgot to declare partition values or misspell the VALUES keywords. The error is “ORA-00926: missing VALUES keyword” as on the next picture.

online tech support computer help computer technician computer problems computer oracle create table oracle partition oracle partition by oracle ORA-00926: missing VALUES keyword retirement planning retirement

The next example is about showing that the table partitioning can use more than one value in one partition. In case, when data amount is not so big to have a separate partition. There is an opportunity to add more then one value into the table partition. Using the table above we realized that four month in a partition is good enough to performance.

CREATE TABLE sales
 ( id         NUMBER(17)  NOT NULL,
   sale_date  DATE        NOT NULL,
   sale_month VARCHAR2(6) NOT NULL,
   product_id NUMBER(17)  NOT NULL
 )
PARTITION BY LIST (sale_month)
 (PARTITION sale_month_2012_1 VALUES ('201201','201202','201203','201204'),
  PARTITION sale_month_2012_2 VALUES ('201205','201206','201207','201208'),
  PARTITION sale_month_2012_3 VALUES ('201209','201210','201211','201212'));

online tech support computer help computer technician computer problems computer oracle create table oracle partition oracle partition by oracle retirement planning retirement

Now the table has only three partitions and the values are declared for each partition.

When table data doesn’t let us to chose certain values then the following example shows us how to use partition ranges. For example we would like to create partitions by product types and every product type starts with a certain number. Food is 1000000001, Drinks 2000000001, Books 3000000001 etc.

CREATE TABLE sales
 ( id         NUMBER(17)  NOT NULL,
   sale_date  DATE        NOT NULL,
   sale_month VARCHAR2(6) NOT NULL,
   product_id NUMBER(17)  NOT NULL
 )
PARTITION BY RANGE (product_id)
 (PARTITION sale_1 VALUES LESS THAN (2000000000),
  PARTITION sale_2 VALUES LESS THAN (3000000000),
  PARTITION sale_3 VALUES LESS THAN (4000000000)
);

online tech support computer help computer technician computer problems computer oracle create table oracle partition oracle partition by oracle retirement planning retirement

Now column “PRODUCT_ID” will be added to different partition depending on their value. For example into partition “SALE_1” goes all PRODUCT_IDs who are smaller than 2000000000.

In short, as Online Tech Support described – Oracle table partitioning should be done depending on business needs and the column’s data for partitioning should be taken as more unchangeable. The column data shouldn’t be constantly updated or it would be better to chose another column that still covers your business needs.



See Also:
Home Oracle Select

 

This tutorial page is based on examples to be easier to follow. The Oracle TO_CHAR function allows converting others’ data types value to character type and function To_Char always returns VARCHAR2 type. You can convert to VARCHAR2 for example DATE, NUMBER, NCHAR, NVARCHAR2, CLOB, or NCLOB data types. The TO_CHAR syntax is following:

TO_CHAR(<datatype>[,<data_mask>])

Take a look at the examples below to see how works Oracle TO_CHAR. For example the first query is number “01234” without To_Char function and the nature of this type is getting trimmed by Oracle database so the SQL output returns “1234“.

SELECT 01234 
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

The reason why Oracle database removed the first 0 (zero) is to keep the database tidy and that way the number looks less confusing for users. Having a zero in front of 1234 doesn’t make much sense either so it gets cleaned away.

Now to keep the number as with the original look, you should use Oracle To_Char function and store the value into Oracle database table as VARCHAR2 type because numbers like phone numbers or credit card numbers starting with 0 (zero) wouldn’t be valid after removing the first zero. We can’t treat all numbers as “numbers” since there are numbers that should keep their look to remain correct and valid.

The second example demonstrates how will stay a number treated as text and to keep its look. This is a demonstrational query so we wrote the number as text and To_Char function is not needed in this case but you can use this example with other datatype variables in place of ‘01234‘. Take a look at the following query and note that Oracle database left the first zero as is and your phone numbers wouldn’t be ruined using this VARCHAR2 type.

SELECT TO_CHAR('01234') 
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Also Oracle To_Char can be used to give numbers a certain look so they look better and more clear on a report or a bill. To format a number you would need to use a data mask and for numbers there are limited amount of key-letters. To see number formatting element take a look at the table below or this link will send you directly to Oracle webpage for possible mask elements. The mask key-letters should be used only with Oracle To_Char as on the following example. On this example we are filling in missing zeros after the coma point to have always two places after the decimal point.

SELECT TO_CHAR(111.2,'990D00') AS formatted_number
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Changing value from number to character is not as popular as turning it from a date type to text and the following part of this tutorial will continue with date values. To change a date look you would need to use a mask again but this time with different mask elements. For example the following Oracle To_Char example has a mask as year(YYYY)-month(MM)-day(DD) hours in 24 digits(HH24):minutes(MI):seconds(SS).

SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') AS date_to_char
  FROM DUAL;

online tech support online computer help computer technician computer problems computer oracle to_char oracle to_char in oracle sql to_char oracle sql to_char in oracle sql database sql retirement planning retirement sql database programming database oracle retirement planning retirement sql database oracle database sql

Also here you can find a list of possible Oracle masks elements for a date type. Use those mask elements only with To_Char function.


See Also:
Oracle Select Oracle SUBSTR Oracle INSTR Oracle Replace Oracle Length Home

 

Number Format Elements Example Description
, (comma) 9,999 Returns a comma in the specified position. You can specify multiple commas in a number format model.Restrictions:

  • A comma element cannot begin a number format model.
  • A comma cannot appear to the right of a decimal character or period in a number format model.
. (period) 99.99 Returns a decimal point, which is a period (.) in the specified position.Restriction: You can specify only one period in a number format model.
$ $9999 Returns value with a leading dollar sign.
0 09999990 Returns leading zeros.Returns trailing zeros.
9 9999 Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number.
B B9999 Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model).
C C999 Returns in the specified position the ISO currency symbol (the current value of the NLS_ISO_CURRENCY parameter).
D 99D99 Returns in the specified position the decimal character, which is the current value of the NLS_NUMERIC_CHARACTER parameter. The default is a period (.).Restriction: You can specify only one decimal character in a number format model.
EEEE 9.9EEEE Returns a value using in scientific notation.
G 9G999 Returns in the specified position the group separator (the current value of the NLS_NUMERIC_CHARACTER parameter). You can specify multiple group separators in a number format model.Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model.
L L999 Returns in the specified position the local currency symbol (the current value of the NLS_CURRENCY parameter).
MI 9999MI Returns negative value with a trailing minus sign (-).Returns positive value with a trailing blank.Restriction: The MI format element can appear only in the last position of a number format model.
PR 9999PR Returns negative value in <angle brackets>.Returns positive value with a leading and trailing blank.Restriction: The PR format element can appear only in the last position of a number format model.
RNrn RNrn Returns a value as Roman numerals in uppercase.Returns a value as Roman numerals in lowercase.Value can be an integer between 1 and 3999.
S S99999999S Returns negative value with a leading minus sign (-).Returns positive value with a leading plus sign (+).Returns negative value with a trailing minus sign (-).Returns positive value with a trailing plus sign (+).Restriction: The S format element can appear only in the first or last position of a number format model.
TM TM The text minimum number format model returns (in decimal output) the smallest number of characters possible. This element is case insensitive.The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, then Oracle Database automatically returns the number in scientific notation.Restrictions:

  • You cannot precede this element with any other element.
  • You can follow this element only with one 9 or one E (or e), but not with any combination of these. The following statement returns an error:SELECT TO_CHAR(1234, 'TM9e') FROM DUAL;
U U9999 Returns in the specified position the Euro (or other) dual currency symbol, determined by the current value of the NLS_DUAL_CURRENCY parameter.
V 999V99 Returns a value multiplied by 10n (and if necessary, round it up), where n is the number of 9’s after the V.
X XXXXxxxx Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle Database rounds it to an integer.Restrictions:

  • This element accepts only positive values or 0. Negative values return an error.
  • You can precede this element only with 0 (which returns leading zeroes) or FM. Any other elements return an error. If you specify neither 0 nor FM with X, then the return always has one leading blank.
Datetime Format Element TO_* Datetime functions? Description
-
/
,
.
;
:
"text"
Yes Punctuation and quoted text is reproduced in the result.
AD
A.D.
Yes AD indicator with or without periods.
AM
A.M.
Yes Meridian indicator with or without periods.
BC
B.C.
Yes BC indicator with or without periods.
CC
SCC
Century.

  • If the last 2 digits of a 4-digit year are between 01 and 99 (inclusive), then the century is one greater than the first 2 digits of that year.
  • If the last 2 digits of a 4-digit year are 00, then the century is the same as the first 2 digits of that year.

For example, 2002 returns 21; 2000 returns 20.

D
Yes Day of week (1-7). This element depends on the NLS territory of the session.
DAY
Yes Name of day.
DD
Yes Day of month (1-31).
DDD
Yes Day of year (1-366).
DL
Yes Returns a value in the long date format, which is an extension of the Oracle Database DATE format, determined by the current value of the NLS_DATE_FORMAT parameter. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format 'fmDay, Month dd, yyyy'. In the GERMAN_GERMANY locale, it is equivalent to specifying the format ‘fmDay, dd. Month yyyy‘.Restriction: You can specify this format only with the TS element, separated by white space.
DS
Yes Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE parameters. For example, in the AMERICAN_AMERICA locale, this is equivalent to specifying the format ‘MM/DD/RRRR‘. In the ENGLISH_UNITED_KINGDOM locale, it is equivalent to specifying the format ‘DD/MM/RRRR‘.Restriction: You can specify this format only with the TS element, separated by white space.
DY
Yes Abbreviated name of day.
E
Yes Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
EE
Yes Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars).
FF [1..9]
Yes Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type’s default precision. Valid in timestamp and interval formats, but not in DATE formats.Examples: 'HH:MI:SS.FF'SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;
FM
Yes Returns a value with no leading or trailing blanks.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
FX
Yes Requires exact matching between the character data and the format model.See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference
HH
HH12
Yes Hour of day (1-12).
HH24
Yes Hour of day (0-23).
IW
Week of year (1-52 or 1-53) based on the ISO standard.
IYY
IY
I
Last 3, 2, or 1 digit(s) of ISO year.
IYYY
4-digit year based on the ISO standard.
J
Yes Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers.
MI
Yes Minute (0-59).
MM
Yes Month (01-12; January = 01).
MON
Yes Abbreviated name of month.
MONTH
Yes Name of month.
PM
P.M.
Yes Meridian indicator with or without periods.
Q
Quarter of year (1, 2, 3, 4; January – March = 1).
RM
Yes Roman numeral month (I-XII; January = I).
RR
Yes Lets you store 20th century dates in the 21st century using only two digits.See Also: “The RR Datetime Format Element”
RRRR
Yes Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year.
SS
Yes Second (0-59).
SSSSS
Yes Seconds past midnight (0-86399).
TS
Yes Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the NLS_TERRITORY and NLS_LANGUAGE initialization parameters.Restriction: You can specify this format only with the DL or DS element, separated by white space.
TZD 
Yes Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in DATE formats.Example: PST (for US/Pacific standard time); PDT (for US/Pacific daylight time).
TZH
Yes Time zone hour. (See TZM format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZM
Yes Time zone minute. (See TZH format element.) Valid in timestamp and interval formats, but not in DATE formats.Example: 'HH:MI:SS.FFTZH:TZM'.
TZR
Yes Time zone region information. The value must be one of the time zone region names supported in the database. Valid in timestamp and interval formats, but not in DATE formats.Example: US/Pacific
WW
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
X
Yes Local radix character.Example: 'HH:MI:SSXFF'.
Y,YYY
Yes Year with comma in this position.
YEAR
SYEAR
Year, spelled out; S prefixes BC dates with a minus sign (-).
YYYY
SYYYY
Yes 4-digit year; S prefixes BC dates with a minus sign.
YYY
YY
Y
Yes Last 3, 2, or 1 digit(s) of year.