if there is a TNS-12541 /12560 ERROR when you connect to a database.
it's maybe the listener is not started, please just start the listener using the following command:
lsnrctl start
Sometimes, there are several listeners in the same server, please be careful.
Someone asked me for help on a SQL. The requirements are simplified as below:
It has something to do with select distince multiple columns from a table．
it's very simple:
select distinct column1, column2 from table1
it's maybe the listener is not started, please just start the listener using the following command:
lsnrctl start
Sometimes, there are several listeners in the same server, please be careful.
Someone asked me for help on a SQL. The requirements are simplified as below:
It has something to do with select distince multiple columns from a table．
it's very simple:
select distinct column1, column2 from table1
Once I composed the SQL as below:
select cod_hold_no, dat_txn from (
select cod_hold_no,dat_txn, min(rownumber) from (
select rownum rownumber,cod_hold_no, dat_txn from ch_hold_funds
) group by cod_hold_no,dat_txn
)
it's really sad that I try to solve such an easy problem by such difficult instructions.
Left outer join, right outer join and full outer join. Purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values.
Select last_name, department_name
From employees e, departments d
Where e.department_id = d.department_id(+)
or:
Select last_name, department_name
From employees e
left outer join
departments d
on e.department_id = d.department_id
which means when there is no value matching in the departments table, the selected columns from the departments table (which is the department_name) is NULL. That's simple master-detail relationship between the two tables, for left outer join, the left table is the master and for right outer join, the right table is the master table. So if there is no detail rows matching the master table, the columns which are supposed to be selected from the detail table are NULL.
DECODE() function and NVL() function.
IF (pi_department_id IS NULL) THEN
SELECT * FROM EMPLOYEES
ELSE
SELECT * FROM EMPLOYEES WHERE DEP_ID = pi_department_id;
END IF;
The above logic is very common in our everyday SQL programming, Let's merge it into a single SQL:
SELECT * FROM EMPLOYEES WHERE DEP_ID = NVL(pi_department_id, DEP_ID).
The SQL above could be bugging. Consider the following scenario: when the input parameter of pi_department_id IS NULL, and also the DEP_ID column could be NULL, those rows with DEP_ID IS NULL will not be selected. The correct way would be :
SELECT * FROM employees WHERE NVL(DEP_ID,'#') = NVL(pi_department_id, NVL(DEP_ID,'#'))
When construct complex SQL, try to use the NVL and DECODE function to make your SQL ｎｅａｔ．
NULL value in DECODE
1. everyone knows that NULL = NULL is false, but in DECODE function, it's different.
DECODE (NULL,NULL,1,0)
will return 1.
try it!
In order to find the database's version, just issue the following SQL command:
SELECT * FROM v$version WHERE banner LIKE 'Oracle%';
A sql to find out the real source of the PL/SQL code.
In the schema, the user can find its own pl/sql package's source by issue the following sql command:
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PK_EXT_TXN_DETAIL_ADHOC' ) FROM dual;
In another read only user's session, only the package declare can be get by another sql:
SELECT * FROM all_source WHERE type = 'PACKAGE' AND OWNER='C43CNHITR1' AND UPPER(name)='PK_EXT_TXN_DETAIL_ADHOC';
The result of the above sql is all the lines of the package declare, not the source code, have to append all the lines into the source.
select cod_hold_no,dat_txn, min(rownumber) from (
select rownum rownumber,cod_hold_no, dat_txn from ch_hold_funds
) group by cod_hold_no,dat_txn
)
it's really sad that I try to solve such an easy problem by such difficult instructions.
Left outer join, right outer join and full outer join. Purpose of an outer join is to include non-matching rows, and the outer join returns these missing columns as NULL values.
Select last_name, department_name
From employees e, departments d
Where e.department_id = d.department_id(+)
or:
Select last_name, department_name
From employees e
left outer join
departments d
on e.department_id = d.department_id
which means when there is no value matching in the departments table, the selected columns from the departments table (which is the department_name) is NULL. That's simple master-detail relationship between the two tables, for left outer join, the left table is the master and for right outer join, the right table is the master table. So if there is no detail rows matching the master table, the columns which are supposed to be selected from the detail table are NULL.
DECODE() function and NVL() function.
IF (pi_department_id IS NULL) THEN
SELECT * FROM EMPLOYEES
ELSE
SELECT * FROM EMPLOYEES WHERE DEP_ID = pi_department_id;
END IF;
The above logic is very common in our everyday SQL programming, Let's merge it into a single SQL:
SELECT * FROM EMPLOYEES WHERE DEP_ID = NVL(pi_department_id, DEP_ID).
The SQL above could be bugging. Consider the following scenario: when the input parameter of pi_department_id IS NULL, and also the DEP_ID column could be NULL, those rows with DEP_ID IS NULL will not be selected. The correct way would be :
SELECT * FROM employees WHERE NVL(DEP_ID,'#') = NVL(pi_department_id, NVL(DEP_ID,'#'))
When construct complex SQL, try to use the NVL and DECODE function to make your SQL ｎｅａｔ．
NULL value in DECODE
1. everyone knows that NULL = NULL is false, but in DECODE function, it's different.
DECODE (NULL,NULL,1,0)
will return 1.
try it!
In order to find the database's version, just issue the following SQL command:
SELECT * FROM v$version WHERE banner LIKE 'Oracle%';
A sql to find out the real source of the PL/SQL code.
In the schema, the user can find its own pl/sql package's source by issue the following sql command:
SELECT DBMS_METADATA.GET_DDL('PACKAGE_BODY','PK_EXT_TXN_DETAIL_ADHOC' ) FROM dual;
In another read only user's session, only the package declare can be get by another sql:
SELECT * FROM all_source WHERE type = 'PACKAGE' AND OWNER='C43CNHITR1' AND UPPER(name)='PK_EXT_TXN_DETAIL_ADHOC';
The result of the above sql is all the lines of the package declare, not the source code, have to append all the lines into the source.
In order to view the columns of a table, just use a SQL is enough, there is no need to use the GUI of PL/SQL developer. Just issue the following SQL:
desc table_you_want_to_see.
