Java Solutions: September 2017

Friday, September 15, 2017

SQL*Plus: Copy a table from one user to another user

1. Log in SQL*Plus as SYSDBA

SQL> connect <oracle user> as SYSDBA;
password:

2. Grant the user with privilege to the table space

SQL> GRANT UNLIMITED TABLESPACE TO <oracle user>;

Grant succeeded.

3. Copy the table from schema 2 to schema 1.

SQL> create table <schema1>.<tablename> as select * from <schema2>.<tablename>;

Table created.

---------------------------------------------------------------------------------------------------------------

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book free here.

Thursday, September 14, 2017

Huawei Nexus 6P: Block calls from a phone number

If you just received a phone call which you feel annoying and decide not to receive further calls from that number. You can block it.

1. Tap the phone icon on your screen
2. Select the call history tab (the clock icon on top)
3. Press and keep holding your finger on the phone number util a pop-up menu is shown.
4. Select "Block Number", and then "Block"

To unblock it:

Press and keep holding your finger on the phone number, select "Unblock Number", and then "Unblock".

---------------------------------------------------------------------------------------------------------------

Wednesday, September 13, 2017

Oracle: Copy schema from one user to another user

Here are the steps to copy schema from one user to another.

1. Unlock the source Oracle user account. Login Oracle as SYSDBA and execute the following command.

SQL> Alter user <fromUsername> identified by <fromPassword> account UNLOCK;

2. Create the directory where you would like to export the schema.

SQL> CREATE OR REPLACE DIRECTORY schema_dump AS '/usr/myDirectory/oracleSchema';

SQL> GRANT READ, WRITE ON DIRECTORY schema_dump TO <user need to access>;

The schema_dump is a name you can use in your command. The physical location of the directory is '/usr/myDirectory/oracleSchema'.

quit SQL*Plus.

3. Export the source schema to the dump directory.

$ expdp <fromUsername> / <fromPassword> schemas=<schema name> directory=schema_dump dumpfile=schema.dmp logfile=dumplog.txt

The <fromUsername> is the source user Oracle login username and the <fromPassword> is the source user Oracle login password.

4. Import the schema and map the schema to the destination user.

$ impdp <toUsername> / <toPassword> directory=schema_dump dumpfile=schema.dmp logfile=dumplog.txt remap_schema=<fromUserSchemaName>:<toUserSchemaName>

---------------------------------------------------------------------------------------------------------------

Friday, September 8, 2017

SQL*Plus: Format column width to display a column at a specified length

Lets say that you have a FRUIT table with the following schema.

Name Type
----------------- ---------------------------
NAME VARCHAR2(100)
PRICE NUMBER
DESC VARCHAR2(100)

Since most of the names of your fruits are far more less than 100 characters, there is a big empty space after each name in your query result, and you want to reduce that.

SQL> COLUMN Name FORMAT A50
SQL> select * from FRUITS;

This will reduce the Name column in your query result to 50 characters. Any name longer than 50 characters is truncated.

Reference:
1. Formatting SQL*Plus Reports

---------------------------------------------------------------------------------------------------------------

Thursday, September 7, 2017

Crystal reports: make a certain field value display as the first row - change the detail display order

For example, your report has a NAME field. You would like to display the row having the NAME value "headquarter" at the top, and display the rest rows in alphanumeric order of the NAME field.

You can achieve this by creating a group on the NAME field and setting in which order the rows to be displayed.

1. Click Insert in the top ribbon menu and select Group.
2. Click the top drop down list and select the NAME field on the popped up window.
3. Click the bottom drop down list and select "in specific order"
3. On the Specified Order tab, click the drop down list and select the value, in this case "headquarter" to be displayed as the first row.

---------------------------------------------------------------------------------------------------------------

SQL SELECT: make a certain column value as the first row returned

For example, you have a PURCHASE table.

product amount payType payment
---------------- ---------- ---------------- -------------
product1 20 Credit card 40
product2 15 Cash 60
product3 30 Account 90

You want to query the table and you want the Cash payType to be returned as the first row and the rest rows order in alphanumeric order.

You expect this

product amount payType payment
---------------- ---------- ---------------- -------------
product2 15 Cash 60
product3 30 Account 90
product1 20 Credit card 40

Select * from PURCHASE order by payType;
returns this

product amount payType payment
---------------- ---------- ---------------- -------------
product3 30 Account 90
product2 15 Cash 60
product1 20 Credit card 40

This query will get what you want.

Select * from PURCHASE order by CASE WHEN upper(payType) = 'CASH' THEN 1 ELSE 2 END, payType;

---------------------------------------------------------------------------------------------------------------

Wednesday, September 6, 2017

SQL: NOT LIKE excludes not only values having the matched string but also NULL values

For example, you have a FRUIT table.

Name Price Description
------------- ------------ --------------------------
Mango 2.25 Very good
Banana 1.29 Bad after frozen
Avacado 2.45

Now, you want to list all the fruits except those have the word "bad" in its description. You use the following sql.

select * from fruit where lower(Description) not like '%bad%';

You expect this:

Name Price Description
------------- ------------ --------------------------
Mango 2.25 Very good
Avacado 2.45

But you get this:

Name Price Description
------------- ------------ --------------------------
Mango 2.25 Very good

To get the result you want, modify the sql to:

select * from fruit where lower(coalesce(Description, ' ')) not like '%bad%';

If it is a number field, the sql should be:

select * from fruit where lower(coalesce(<field>, 0)) = 0;

---------------------------------------------------------------------------------------------------------------

Sunday, September 3, 2017

java.sql.SQLSyntaxErrorException: ORA-00919: invalid function

By definition, this error is caused by that an entry is formatted like a function call, but it is not recognized as an Oracle Function. Below are some of the situations that will cause the error.

1. Miss spelling of an existing function. For example, you want to count the occurrences of an column, instead of using count(col), you miss typed it as cunt(col).

2. The called function does not exist in your database. For example, you have an integer column cost in your product table, you want to add 10 to all the cost in your report, and here is the query.

Select addTen(cost) as cost from product;

You will get the java.sql.SQLSyntaxErrorException: ORA-00919: invalid function error if in your database you don't have the addTen(INT param) function.

3. A comma is replaced by a period. For example, you want to insert a row into your product table, and here is your SQL.

Insert into product (?, ?. ?, ?, ?);

Because you have an "." instead of a "," after the second question mark, you will get this error.

References:
1. Oracle Error Message
2. http://ora-00919.ora-code.com/

-----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live