I have created a query – Now I want the output such that ,according to the value of legal_emp and SAL_hour I will have to calculate grade and job from another table in the same query. Something like – How can i achieve this in a single query ? Answer sounds like CASE WHEN should be able to fix this
Tag: plsql
Issue in a function that converts a string into a valid date
I have been using this function for last few years that converts an input string into a valid date in decided format. The above query will return, 28-02-2002 00:58:15 However starting 29/02/2020, if the input string to the function changes to ‘20200229005947354241’, it returns a null! I am not sure if this something related to leap year but not able
Split and compare two Strings in Oracle SQL
I have a table with three columns structured as followed: Now I would like to perform an Oracle-SQL-query which gives me the edit-pattern of the two strings left and right. The result should be as followed: Al my tries with REGEX_SPLIT and CONNECT BY were not successful. Do you have any ideas how to solve that problem? Answer You can
table in database rows
I have a table People, having people_id,people_name,people_lastname. I am facing difficulties creating a pl/sql function to display all the rows of that table. I have done this much so far Answer One option is to return refcursor:
Too many open connections on sending Email
I’m trying to send email to every users from DB. I use a loop to get the message text per user as I loop need to call the send_email. But I got an error saying: ORA-30678: too many open connections …
Return multiple values in varchar2
I want to return the values of several columns (With a function) in a varchar2 but I get an error when I choose several columns in select. FUNCTION FU_PRUEBAS (P_IDNUM MAC.IDNUM%TYPE) RETURN VARCHAR2 …
IS NULL in CASE in predicates
tell me pls, that’s real – use the CASE construct, where the expression with IS NULL/IS NOT NULL will be returned in TNEN? I have a procedure in a package that receives an input parameter and it is …
Select statement inside for loop
I’m trying to put my select statement inside a for loop and after looping I want to insert the result on a table but upon my execution I bump in to this error. ORA-06550: line 5, column 7: PLS-…
Select row of data to be the output header
I have a very large set of data that is formed similar to this: I want to produce report output that drops the table headers (field1, field2, field3) and instead use data row1 as headers in output. I am able to do this nicely in SQLPlus and SPOOL to CSV, but I want to do everything in PLSQL so that
how to create pl sql function to Checking if data exists in database or not
I am trying to create a function that tells whether data exist in database or not. Trying to solve this using cursor if data exist then data will be fetched from database and value of temp will be TRUE otherwise data would not be fetched from DB and it will set value of temp to false. But when I compile