I am trying to solve a task with a dynamic SQL, but facing an issue ora-00900 invalid sql statement. However, it works in the anonymous block treating the statement to be executed as a string. So where is the issue in the first case? It looks like with escape quotes, but can’t catch this. Answer As the error …
Tag: oracle
identifier ‘NEW.CITY_POPULATION’ must be declared
I am trying to declare a oracle trigger that will update the city name when the city population reaches 0 but I am getting this error. Answer If you’re going to reference the :new pseudo-record in a trigger body, it needs to be prefixed with a colon. A trigger on cities generally cannot query the cities…
How to insert a inner join from 3 different tables
I am making a view pulling data from 3 different tables for the query. Here is my code so far, I’m just not sure how to integrate the inner joins since I already have a select statement, I can’t …
Oracle SQL Error : Unable to execute Subquery
I am trying to execute below Oracle SQL query select m.*, n.region_building_count from (SELECT a.BUILDING_ID as “Building Id”, d.Building_name as “Building Name”, d.ADDRESS …
Extract a desired character from an alphanumeric column using concise REGEX in Oracle SQL
I am going through a “clean-up” process of a character column that holds employee rankings that should have a single character: 0-5 or M or U but instead it has up to 3 characters that are either numeric, alpha or alphanumeric. I spent the better part of two days researching online (Regex, Stack o…
How to add escape $ in Zend framework query
I need to add escape ‘$’ in zend framework. expected query: SELECT * FROM V_HOME_SEARCH sv WHERE sv.NAME LIKE ‘%gar$_%’ ESCAPE ‘$’ I tried this: public function selectData($str){ $select = $this->…
SQL – Compare rows based on date and transpose difference
Scenario: Table ‘HIST’ RID VALUE HIST_DATE 1 V111 2019-01-01 1 V112 2020-02-11 1 V112 2020-03-08 1 V113 2020-04-11 1 V114 2021-03-15 2 V211 2020-04-11 2 V211 2021-03-16 3 V311 2019-05-01 3 …
Why do I get an Error when I try to rename a table after FROM?
When I simply try to retrieve all of the rows in my table EMPLOYEE like this: SELECT * FROM EMPLOYEE AS emp; I get the following error: ORA-00933: SQL command not properly ended 00933. 00000 – &…
ORA-04091 TABLE ODB.EMPLOYEE IS MUTATING, TRIGGER/FUNCTION MAY NOT SEE IT. IS THERE SOMETHING WRONG WITH MY TRIGGER?
Trying to create a trigger when there is an update of Status on Employee Table and capture some values for the record in Employee table and Employee_Header table for that record and send an email. The trigger throws an error. Answer You can’t select from a table which is just being changed; it is mutati…
How to convert decimal values to time values in oracle?
I have a table that is formatted like this: I want the output to be like this: Basically transform the values in the dec_time field into time values like the example above in new_dec_time. This is my SQL so far: This simply changes replaces the decimal point with the semicolon but doesnt add the 0s where need…