4 Replies Latest reply on Aug 21, 2019 1:40 PM by Mike Kutz

    New to Oracle SQL Developer - From SAS

    4067301

      Hi everyone,

       

      I've recently changed job and now use Oracle SQL Developer (Version 4.2), I've got several years experience using SAS and writing Proc SQL, most of my code writing is proving useful but there are a few things where I'm unsure whether or how I can replicate tasks. Any help would be appreciated.

       

      Performing functions during variable production:

       

      For example, taking a day off today's date:

      In SAS

      %LET udate = today()-1;

       

      In Oracle?

      I understand that DEFINE is similar to %LET, but can I perform functions?

      DEFINE udate = ?

       

      Second one, I could write a code to then run when called:

       

      %MACRO simple(option);

      SELECT

      *

      FROM table

      WHERE id = &option

      RUN;

      %MEND;

       

      %simple(1);

      %simple(2);

       

      This meant that if amendments were required I only needed to do this once. Can I do this?

       

      As I said, any help / tips / resource are appreciated.

       

      Seb

        • 1. Re: New to Oracle SQL Developer - From SAS
          thatJeffSmith-Oracle

          RIGHT NOW in Oracle = SYSDATE

           

          select sysdate from dual;

           

          SYSDATE           

          -------------------

          19-AUG-19  11.40.20

           

          But notice, it's not just the day, it's also the moment in time, up to the second.

           

          Yesterday would be select sysdate - 1 from dual;

           

          SYSDATE-1         

          -------------------

          18-AUG-19  11.41.05

           

          • 2. Re: New to Oracle SQL Developer - From SAS
            4067301

            Thanks for this and I've managed to implement the date side.

             

            Any thoughts on the ability to loop through code using different variables?

             

            Thanks.

            • 3. Re: New to Oracle SQL Developer - From SAS
              EdStevens

              4067301 wrote:

               

              Thanks for this and I've managed to implement the date side.

               

              Any thoughts on the ability to loop through code using different variables?

               

              Thanks.

              Looping requires a procedural language such as PL/SQL. 

              SQL is a declarative language.

              A lot of times people think they need to procedural loop over data because they don't understand the power of SQL.

               

              And please understand the difference between a language (such as SQL or PL/SQL) and your front-end tool, such as SQL Dev.  So far, your questions have been about language, not the SQL Dev tool.  SQL Dev is a very nice, very powerful front-end client.  But at the end of the day, that's all it is ... a front-end client.  All of the SQL you write using SQL Dev is simply sent to the database for processing, just like any other client (such as sqlplus) would.

              • 4. Re: New to Oracle SQL Developer - From SAS
                Mike Kutz

                Hello and Welcome to the forum.

                 

                When you get a chance, please change your display name:

                Change Name

                 

                You will want to ask some question regarding Oracle.  You should try to read the FAQ to get an understanding of what this forum likes.

                Re: 2. How do I ask a question on the forums?

                 

                Since you are new to Oracle, as mentioned in the FAQ, please start your journey with Oracle by reading the Concepts Manual.

                 

                Also, since this question is about the Language (and not the tool), it belongs in the SQL & PL/SQL forum.  You can move the thread yourself.  Or, you can request a Forum Manager to move it for you ( "Report Abuse" -> Wrong Forum )

                 

                For pulling data out of the database, you use SQL.

                For manipulating data within the database, you attempt to use SQL first.

                 

                Original Post comments

                Your example does not convert to Oracle very well because you are not doing anything with the results of the SELECT statement.

                We really need to know what your Business Requirement is prior to suggesting any solution.

                 

                no: you can't create a Macro.

                yes. you can create functions and procedures

                You can also create a Package of functions and procedures and variables and constants and cursors and Exceptions.  (Think of this as a library or Java Class)

                 

                There are multiple ways to convert  your example to Oracle.  The "correct one" will be highly dependent on your Business Requirement.

                 

                Direct Conversion of your code could look like this but is most likely the wrong solution for your undefined Business Requirement.

                create or replace
                package my_pkg
                as
                    cursor simple_select( option_id in my_table.id%type ) is
                        select *
                        from my_table a
                        where a.id = option_id;
                end;
                /
                

                 

                If you know you will need to loop over multiple IDs than this implementation is "a bad idea".

                The "correct" solution should be able to process all IDs of interest at once.

                 

                My $0.02

                 

                MK