Hi Guys, We come across few queries in oracle and pl/sql on daily development tasks which we forget/wont remember correctly sometime. This post keeps track on those items. Keep visiting this post for the regular updates.
This can be used to refresh for your oracle and pl/sql interviews as well. Feel free to add your thoughts in the comments section to keep the post updated.
1. How to view the procedure full source code?
Run the below query to get the procedure name, note without privileges this query will run but returns nothing even if the procedure exist.
select text from user_source where name = ‘YOUR_PROCEDURE_NAME’ order by line;
2. How to use reserved column name in select query:
Sometime without knowing we would have used the reserved keywords as column names like DATE, DESC ETC.
To select that from the table use that inside the double quotes with the same case as you created.
If you created as DESC then use like this,
SELECT “DESC” FROM SAMPLESCHEMA.SAMPLETABLE;
3. How to get the list of user created stored procedures ?
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE IN (‘PROCEDURE’);
To get function or package
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE IN (‘FUNCTION’,’PACKAGE’);
To get all the procedures (both system and user)
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN (‘PROCEDURE’,’FUNCTION’,’PACKAGE’);
4. How to view the user created sequence details like max number increment by etc.?
SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME=’MY_SEQUENCE’;
5. How to get all the table row count of a given schema?
SELECT table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml(‘select count(*) c from schema.’||table_name)),’/ROWSET/ROW/C’)) count FROM USER_TABLES;
If you want to get the count only for few tables then give in the where condition,
select table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml(‘select count(*) c from schema.’|| table_name)),’/ROWSET/ROW/C’)) count from user_tables where table_name in (‘mytesttable’,’mytesttable1′);
6. How to get all the tables which has given column names?
SELECT * FROM ALL_TAB_COLUMNS WHERE COLUMN_NAME LIKE ‘%USER%’;
For cluster, tablespace and other stuffs you can also use
SELECT * FROM USER_ALL_TABLES;
7. How to get only the year from the sysdate() ?
SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
FOR YOUR COLUMN:
SELECT EXTRACT (YEAR FROM VALIDITY_PERIOD) FROM TESTTABLE;
FOR THE GIVEN INPUT:
SELECT EXTRACT(YEAR FROM TO_DATE(’27/12/2016′,’DD/MM/YYYY’)) FROM DUAL;
Extract year with between in oracle sample
SELECT * FROM TESTTABLE WHERE EXTRACT(YEAR FROM VALIDITY_PERIOD) BETWEEN EXTRACT(YEAR FROM SYSDATE) AND EXTRACT(YEAR FROM TO_DATE(’20/12/2020′,’DD/MM/YYYY’);
8. How to get all the table names and it’s row counts for the given database schema ?
select table_name, table_rows from information_schema.tables where table_schema = ‘czc1’;
596 total views, 1 views today