To search a query from all_source data dictionaly table
A very common requirement of developers and DBAs alike is to find the source program in the database where a particular query is getting called from. The normal way to do this is to take part of a query and to do a like search. An exact search of the query is not possible as the query may be written in multiple lines and the source for each DB OBJECT is stored line by line in the db source table.
Example
SELECT SOURCE_NAME FROM ALL_SOURCE WHERE UPPER(TEXT) LIKE '%SELECT EMP_FIRSTNAME%';
You can do this with various strings in the query to find the resultset which returns the least number of rows.
Matching on multiple words may also not give a correct result as you can never be sure that any two words will be on the same line in the source code. The search will become even more difficult if the query is a dynamic query.
A good approach to this problem would be to identify a few words that would be unique to the query, search for these words individually as inline tables and then join on the SOURCE_NAME.
Below is an example
You would like to search source for the below query
SELECT EMP_FIRSTNAME, EMP_LASTNAME, EMP_DOB,
EMP_DOJ, EMP_SAL, EMP_DEPT
FROM EMPLOYEES e
WHERE EMP_ID = v_employee_id
AND EMP_DEPT = (SELECT EMP_DEPT
FROM DEPARTMENTS
WHERE DEPT_ID = v_dept_id);
Once you identify a few keywords from the query you can use them as below
SELECT t1.SOURCE_NAME FROM
(SELECT DISTINCT(NAME) source_name
FROM ALL_SOURCE
WHERE UPPER(TEXT) LIKE UPPER('%EMP_FIRSTNAME%')) t1,
(SELECT DISTINCT(NAME) source_name
FROM ALL_SOURCE
WHERE UPPER(TEXT) LIKE UPPER('%EMP_LASTNAME%')) t2,
(SELECT DISTINCT(NAME) source_name
FROM ALL_SOURCE
WHERE UPPER(TEXT) LIKE UPPER('%EMPLOYEES%')) t3,
(SELECT DISTINCT(NAME) source_name
FROM ALL_SOURCE
WHERE UPPER(TEXT) LIKE UPPER('%EMP_ID%')) t4,
(SELECT DISTINCT(NAME) source_name
FROM ALL_SOURCE
WHERE UPPER(TEXT) LIKE UPPER('%DEPARTMENTS%')) t5
WHERE t1.source_name = t2.source_name
AND t1.source_name = t3.source_name
AND t1.source_name = t4.source_name
AND t1.source_name = t5.source_name;
This will still not give a unique record, but it should filter the search to as few records as possible. You can increase the number of keywords to search on by increasing the number of inline queries to get a more refined search. But that will increase the query execution time. So it will be a tradeoff.
If anyone has a better approach to this problem, do post a reply .
Display date Columns in sqldeveloper with timestamp
In sqldeveloper if you want to show the timestamp in date columns you need to do the following
Go to Tools -> Preferences -> NLS -> Database -> Date Format
Here you can change it to the format you want example : DD-MON-YYYY HH24:MI:SS
Close all the tables and open again and you will see the date with the timestamp format
To copy data from one table with LONG datatype into another table with LONG datatype
CREATE TABLE TBL_LONG
(
ID NUMBER,
RMKS LONG
);
INSERT INTO TBL_LONG VALUES (1, 'test long1');
INSERT INTO TBL_LONG VALUES (2, 'test long2');
INSERT INTO TBL_LONG VALUES (3, 'test long3');
CREATE TABLE BKP_TBL_LONG
(
ID NUMBER,
RMKS LONG
);
INSERT INTO BKP_TBL_LONG SELECT * FROM TBL_LONG;
INSERT INTO BKP_TBL_LONG SELECT * FROM TBL_LONG
Error at Command Line : 19 Column : 33
Error report -
SQL Error: ORA-00997: illegal use of LONG datatype
00997. 00000 - "illegal use of LONG datatype"
*Cause:
*Action:
COPY FROM HR/HR@XEPDB1 TO HR/HR@XEPDB1 INSERT BKP_TBL_LONG USING SELECT * FROM TBL_LONG;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
3 rows selected from HR@XEPDB1.
3 rows inserted into BKP_TBL_LONG.
3 rows committed into BKP_TBL_LONG at HR@XEPDB1.
APPEND - This will append data to the table specified in the APPEND clause based on the SELECT query used in USING.
Use APPEND when you want to insert data in an existing table, or create a new table if the destination table does not exist. APPEND specifies the following actions:
REPLACE
The REPLACE clause names the table to be created in the destination database and specifies the following actions:
CREATE
You can use the CREATE clause to avoid accidentally writing over an existing table. CREATE specifies the following actions:
INSERT
Use INSERT to insert data into an existing table. INSERT specifies the following actions:
Set Properties for COPY command
ARRAYSIZE
The variable ARRAYSIZE limits the number of rows that SQLPLUS fetches from the database at one time. This number of rows makes up a batch.
COPYCOMMIT
The variable COPYCOMMIT sets the number of batches after which COPY commits changes to the database.
You can specify a WHERE clause to insert data based on a give criteria.
Examples
COPY FROM HR/HR@XEPDB1 TO HR/HR@XEPDB1 APPEND BKP_TBL_LONG USING SELECT * FROM TBL_LONG WHERE ID < 2;
COPY FROM HR/HR@XEPDB1 TO HR/HR@XEPDB1 REPLACE BKP_TBL_LONG USING SELECT * FROM TBL_LONG;
COPY FROM HR/HR@XEPDB1 TO HR/HR@XEPDB1 CREATE TBL_LONG_1 USING SELECT * FROM TBL_LONG;
Quote for the day -
“People Who Are Crazy Enough To Think They Can Change The World, Are The Ones Who Do.”- Rob Siltanen
“People Who Are Crazy Enough To Think They Can Change The World, Are The Ones Who Do.”- Rob Siltanen
No comments:
Post a Comment