here’s my cursor statement and call like this The variable countryName is declared along with the cursors, and this query runs fine but no data is fetched. no idea what I’m missing here.. Answer With some sample data like below: A_TBL ID COL_A COL_B IE 01-NOV-22 1 UK 02-NOV-22 2 FR 03-NOV-22 3 IT …
Tag: plsql
Oracle chr() diferent returns from SQL engine versus PL/SQL engine, why?
Same function, same argument, different outputs: PL/SQL procedure successfully completed. Answer In your case, chr() appears to be performing an implicit conversion to binary_float (IEEE 754 floating-point representation) as the datatype, as opposed to number (ANSI SQL NUMERIC). This results in different roun…
Oracle SQL – Create Sequence and add sequence values on new column
I have the following table called table_1 I create a new column_3 then I create a sequence CREATE SEQUENCE “sequence_1” MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOPARTITION and I alter the table_1 so the default for new values on column_…
Oracle: How to get first returned record and previous record
I want to grab the first returned record and previous record based on a user input date. Example, I put in 7/5/22, and I want to grab that date’s food value and somehow include the record previous to that, so output would be: If I put in 3/16/22, then I want my output to be: My totally wrong code: Answe…
Year, Next Year, Quarter e Next Quarter based in a FISCAL YEAR
I need to bring the results of this SQL showing all the data of “MY FISCAL YEAR” that started in 1st june, and other SQL to showing all the data of the “NEXT FISCAL YEAR”, because I made it based on the database calendar. btw, the last fiscal year ended in 31 MAY The user will sent the…
How to access Package Public variable into SELECT Statement using EXECUTE IMMEDIATE?
/ / error ORA-06553: PLS-221: ‘G_VAR’ is not a procedure or is undefined Answer Obviously, you cannot call an internal variable of a package like that, presumably might convert to this one and call like this : or this : Edit : Indeed, not needed; you can alternatively call the statement by using E…
PL/SQL table mutation and trigger Format
I just started with triggers, I want the trigger to give me the msg error if the new tauxHoraire is 50% greater than the current tauxHoraire (so *1.5 >). The trigger compiles but I’m getting ORA-04091: table name is mutating, trigger/function may not see it. There might a problem in how I wrote it. O…
pl/SQL for loop
i having an issue on changing the month according to the loop in where the clause i have used between function the month of todate function have to be changed according to the loop if a becomes 1 then the month in where clause has to be changed according to 1 if a becomes 2 the month have be changed
Oracle SQL Trigger not getting created
I have a table passenger as shown below I am learning triggers and I want to create a trigger that updates charges as charges + 100 before a new insert is done. Here is the trigger I created. However, I am getting the error ORA-04079: invalid trigger specification. I have tried replacing new with :new, adding…
SQL query optimisation by replacing subquery
The following query works and gives me the expected result but i’d like to optimise it. Is there a way to avoid the subquery, using only the conditions in the main query to arrive at the same result: PS: In PLSQL, NVL function lets us substitute a value when a null value is encountered Thanks for any in…