Results 1 to 9 of 9

Thread: #define

  1. #1
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73

    Unanswered: #define

    Hello,

    Is there anything in pl/sql like #define macrodefinition in c?
    I'm suprised I haven't found anything about...I think it would be very useful in certain situations...
    Last edited by HrabiaGrzegorz; 12-14-04 at 06:17.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I learned C ... gosh! has it been so long? ... 15 years ago and didn't use it since. Don't remember much; what does #define do? You mentioned "macrodefinition". Could it be, perhaps, declaring procedures, functions, cursors, etc. in a package and then use them elsewhere?

  3. #3
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    If you have code
    Code:
    #include <stdio.h>
    
    #define MY_PRINTF(str) printf(stdout, str)
    
    int main(int, char**) {
      MY_PRINTF("anything");
    
      return 0;
    }
    parser would expand it to
    Code:
    #include <stdio.h>
    
    int main(int, char**) {
      printf(stdout, "anything");
    
      return 0;
    }
    Got it?

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    I don't think there are any #define macro equivalents, the closest I can think of for a #define value (similar to a pascal const) would be ...

    Code:
      cnDetailNone CONSTANT NUMBER(2) := 10;
    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There is no MACRO per se in PL/SQL. Instead you could do this:
    Code:
    DECLARE
      l_file UTL_FILE.FILE_TYPE;
      PROCEDURE pr( p_str in varchar2 ) IS BEGIN UTL_FILE.PUT_LINE(l_file,p_str); END;
    BEGIN
      ...
      pr('Hello');
    END;
    You can even imitate #INCLUDE in SQL Plus scripts like this:

    Code:
    DECLARE
    @@stdio.sql
    BEGIN
      ...
      pr('Hello');
    END;

  6. #6
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    Thanks for your replies, but...
    (*) billm - constants aren't what I need
    (*) Tony - I'm not sure if you got me right (or I didn't catch your answer).
    Let's say there is such a directive, exactly like in c. When I write
    Code:
    #define MY_WHERE_CLAUSE "a.id = b.id AND b.comment IS NOT NULL"
    I expect parser to change every occurance of MY_WHERE_CLAUSE in my code to "a.id = b.id AND b.comment IS NOT NULL". This is like constant. But:
    When I write
    Code:
    #define MY_WHERE_CLAUSE(pid) a.id = b.id AND b.pid = pid
    I want to be able to write
    Code:
    ...
    OPEN rc FOR
      SELECT ...
        FROM ...
        WHERE MY_WHERE_CLAUSE(17)

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    No, you can't do that, other than using dynamic SQL (which I would not recommend introducing just for this "convenience"):
    Code:
    DECLARE
      rc SYS_REFCURSOR
      my_where_clause VARCHAR2(2000) := 'a.id = b.id AND b.pid = :pid';
    BEGIN
      OPEN rc FOR
        'SELECT ...
          FROM ...
          WHERE ' || my_where_clause
         USING 17;
    But really I think you are looking for a solution to a problem that doesn't exist. The only point of a macro is if you are going to use it many times within the code. Why would you have a requirement to attach the same WHERE clause to many different queries in a program? Probably there is a better way...

  8. #8
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    I'm looking for solution to a problem that exists.
    I got a query and then I need to pivot it. There will be ~45 columns, so I have ~45 rows in select clause which all looks like
    Code:
    DECODE(t.rnum, 1, MAX(DECODE(r.r, 0, r.s)), MAX(r.GroupCount + 2), SUM(DECODE(r.r, 0, r.Scans)), MAX(r.GroupCount + 3), SUM(DECODE(r.r, 0, r.PERCENT)), MAX(r.GroupCount + 4), MAX(DECODE(r.r, 0, r.PercentCum)), MAX(DECODE(r.r, 0, DECODE(r.GroupIDRank, t.rnum - 1, r.Scans)))) AS Day0
    That's what I need this macro for.

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In that case you could try something like this:
    Code:
    DECLARE
      l_sql VARCHAR2(32767);
      FUNCTION pivot_col (p_col INTEGER) RETURN VARCHAR2 IS
      BEGIN
        RETURN
    ', DECODE(t.rnum, ' || p_col || ', MAX(DECODE(r.r, 0, r.s)), MAX(r.GroupCount + 2), SUM(DECODE(r.r, 0, r.Scans)), MAX(r.GroupCount + 3), SUM(DECODE(r.r, 0, r.PERCENT)), MAX(r.GroupCount + 4), MAX(DECODE(r.r, 0, r.PercentCum)), MAX(DECODE(r.r, 0, DECODE(r.GroupIDRank, t.rnum - 1, r.Scans)))) AS Day' || p_col-1;
      END;
    BEGIN
      ...
      FOR i IN 1..45 LOOP
        l_sql := l_sql || pivot_col(i);
      END LOOP;
      ...
    You may need to make more use of the p_col parameter in the return statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •