[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle syntax

Posted on 2013-02-04
7
Medium Priority
?
338 Views
Last Modified: 2013-02-07
I cant figure out what is wrong with this procedure???

CREATE OR REPLACE PROCEDURE ANET.SP_TEST_WARNING

IS

BEGIN
    DECLARE
        nWarning Number;
        nViolation Number;

    BEGIN
        Select (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_BLANK_WARN_DAYS') INTO nWarning  'Warnings',
        (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_MUST_RECONCILE_DAYS_BLANK') INTO nViolation 'Violation',
        CREATION_DATE, SYSDATE        
        FROM TPSA_MAIN;
    END;  
   
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
       NULL;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END SP_TEST_WARNING;
/


I get this error message
[Warning] ORA-24344: success with compilation error
11/126  PL/SQL: ORA-00923: FROM keyword not found where expected
11/5    PL/SQL: SQL Statement ignored
 (1: 0): Warning: compiled but with compilation errors
0
Comment
Question by:jknj72
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 20

Expert Comment

by:flow01
ID: 38852020
DECLARE
        nWarning Number;
        nViolation Number;
       dCreation_date;
 BEGIN
        Select (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_BLANK_WARN_DAYS') Warnings,
        (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_MUST_RECONCILE_DAYS_BLANK')  Violation,
        CREATION_DATE --, SYSDATE        
         INTO nWarning , nViolation ,   dCreation_date
        FROM TPSA_MAIN;
    END;  


-- but this will only get you creation date from tpsa_main , the other values are fixed
0
 
LVL 32

Expert Comment

by:awking00
ID: 38852083
A number of issues -
declare is not used in a stored procedure and, even if it were, would come before the begin statement, so the beginning of your procedure should look something like

CREATE OR REPLACE PROCEDURE ANET.SP_TEST_WARNING
IS
       nWarning Number;
       nViolation Number;
BEGIN

In PL/SQL all selects must be selected into a variable and the syntax is
SELECT some column(s)
INTO some variable(s)
FROM some table(s)
WHERE some condition(s)

Additionally, the select must only return one value.
Your select statement is also selecting a creation_date and sysdate, which would also need to be inserted into some variable.

I don't know what it is that you want your procedure to accomplish, since there doesn't seem to appear there is any data manipulation. Perhaps you can provide a more detailed description of what you want from the procedure with some sample data and expected results.
0
 

Author Comment

by:jknj72
ID: 38852091
Heres what Im trying to do

I need to write a query that has to check to see if there should be a violation or warning being reutrned in a procedure

Here what I have. I have a program that creates permits and when a permit is created you have to reconcile that permit before you receive a violation email. Also, you will be sent a warning email before you violate.  There is a table that holds the numbers that are set for a violation and a number for a warning..

So if warnings are set to 3 and a violation is set to 30 the logic would be
IF SYSDATE > (CREATE_DATE + Violation(30)) send out a violation
IF SYSDATE BETWEEN (CREATE_DATE + (Violation(30) - Warning(3)) AND  (CREATE_DATE + Violation(30)) then send out warnings

Can you help me write this in a view. The view would return all the permits that have Warnings and or Violations
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 32

Expert Comment

by:awking00
ID: 38852255
Can you provide a description of the omni_properties and tpsa_main tables?
0
 

Author Comment

by:jknj72
ID: 38852510
I was able to do it with this

SELECT TRUNC(CREATION_DATE) as CREATION_DATE, EMPLOYEE_NUMBER
FROM TPSA_MAIN WHERE
TRUNC(SYSDATE) BETWEEN (TRUNC(CREATION_DATE) + (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_MUST_RECONCILE_DAYS_BLANK') - (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_BLANK_WARN_DAYS')) --typically 30-3
AND (TRUNC(CREATION_DATE) + (SELECT TO_NUMBER(PROPERTY_VALUE) FROM OMNI_PROPERTIES WHERE PROPERTY_NAME='TPSA_MUST_RECONCILE_DAYS_BLANK'))--typically 30
AND STATUS IN('INCOMPLETE', 'BLANK');


I was trying to put them in variables for readability and ease of use but I guess this will work fine unless you have any suggestions?

Thanks
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 38863662
Hard to comment, but there are 3 subqueries that you assume will return 1 record (and no more than 1 record) if you get null or more than 1 row in any of these you are in trouble.

SELECT TRUNC(CREATION_DATE) AS CREATION_DATE, EMPLOYEE_NUMBER
FROM   TPSA_MAIN
WHERE  TRUNC(SYSDATE)   BETWEEN (TRUNC(CREATION_DATE) +
                                                       (SELECT TO_NUMBER(PROPERTY_VALUE)
                                                       FROM    OMNI_PROPERTIES
                                                       WHERE   PROPERTY_NAME='TPSA_MUST_RECONCILE_DAYS_BLANK'
                                                       )
                                                       -
                                                       (SELECT TO_NUMBER(PROPERTY_VALUE)
                                                       FROM    OMNI_PROPERTIES
                                                       WHERE   PROPERTY_NAME='TPSA_BLANK_WARN_DAYS'
                                                       )
                                                       ) --typically 30-3
                        AND
                               (
                                  TRUNC(CREATION_DATE) +
                                  (SELECT TO_NUMBER(PROPERTY_VALUE)
                                  FROM    OMNI_PROPERTIES
                                  WHERE   PROPERTY_NAME='TPSA_MUST_RECONCILE_DAYS_BLANK'
                                  )
                               )--typically 30
AND    STATUS IN('INCOMPLETE',
                 'BLANK');

Open in new window

for me: It was interesting to see sysdate being the subject of a between;
keep in mind the "between ... and ..." is the equivalent of:

[info] >= [low boundary] and [info] <= [high boundary]

i.e. both boundary dates ate INCLUDED
0
 

Author Closing Comment

by:jknj72
ID: 38865260
that will do it
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question