Alter schema in a session & language in a session

Sometimes while running queries some of the views don't work due to the default language settings in SQL developer being different to one's the views refer. Use the below query to set the language.

alter session set nls_language = 'AMERICAN'

Sometimes the error ORA-00942 table or view not found comes up due to being in a different schema. It is pain to enter schema. before all the tables.

Ex- select * from ap_invoices_all
gives me ORA-00942 due to the restricted access we have in our site.

Change the schema in the session to avoid this error.

alter session set current_schema=APPS

Now the above select query works without any issues.

Converting CLOB to VARCHAR2 in SQL

I wanted to convert a CLOB type column in VARCHAR2 and use the same in a query. Below is a simple way using substr and to_char. Using this approach you can use the CLOB type like any VARCHAR2 column.

SELECT to_char(substar(CLOB_COLUMN,1,100)) error FROM table_name
WHERE to_char(substar(CLOB_COLUMN,1,100)) LIKE'%ORA%'