Results 1 to 8 of 8

Thread: date in sql

  1. #1
    Join Date
    Feb 2008
    Posts
    38

    Unanswered: date in sql

    Hi

    Oracle 9i on hp unix.
    I will make a job take controlfile trace periodically.
    How can I change the date part dynamically?
    like below:


    alter database backup controlfile to trace as '/some/arbitrary/control230509';


    alter database backup controlfile to trace as '/some/arbitrary/control240509';


    alter database backup controlfile to trace as '/some/arbitrary/control250509';
    ..
    ..
    ..

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select to_char(sysdate,'DDMMYYYY' from dual;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2009
    Posts
    1

    Rana_DBA

    You can solve the problem by adding below coluse in you procedure.

    Declare

    v_date varchar2(20);
    begin
    select to_char(sysdate,'DDMMYY') into v_date from dual;

    ---now your control file name can be like below. Just concate the v_date value with control file location.

    alter database backup controlfile to trace as '/some/arbitrary/control'||v_date;

    end ;


    I think above solution will help for you.
    Last edited by rana_dba; 05-24-09 at 06:02.

  4. #4
    Join Date
    May 2009
    Location
    Azerbaijan, Baku
    Posts
    6
    Quote Originally Posted by tjay83
    Hi

    Oracle 9i on hp unix.
    I will make a job take controlfile trace periodically.
    How can I change the date part dynamically?
    like below:


    alter database backup controlfile to trace as '/some/arbitrary/control230509';


    alter database backup controlfile to trace as '/some/arbitrary/control240509';


    alter database backup controlfile to trace as '/some/arbitrary/control250509';
    ..
    ..
    ..

    I don't why you keep asking this question in many forums, while you got answer from each of them. Please read my answer from here:
    date in sql - Oracle DBA Forums
    Last edited by Kamran Agayev A.; 05-25-09 at 01:47.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by rana_dba
    You can solve the problem by adding below coluse in you procedure.

    <snap>
    alter database backup controlfile to trace as '/some/arbitrary/control'||v_date;
    </snap>

    I think above solution will help for you.
    Well, as it won't work, it won't help.

    ALTER command is a DDL (data definition language) and can't be used that way (here's an example - I'll try to rename a column, but that's still ALTER):
    Code:
    SQL> create table test (col number(4));
    
    Table created.
    
    SQL> declare
      2    v_date varchar2(10);
      3  begin
      4    select to_char(sysdate, 'ddmmyy') into v_date from dual;
      5    alter table test rename column col to col || v_date;
      6  end;
      7  /
      alter table test rename column col to col || v_date;
      *
    ERROR at line 5:
    ORA-06550: line 5, column 3:
    PLS-00103: Encountered the symbol "ALTER" when expecting one of the following:
    begin case declare end exception exit for goto if loop mod
    null pragma raise return select update while with
    <an identifier> <a double-quoted delimited-identifier>
    <a bind variable> << close current delete fetch lock insert
    open rollback savepoint set sql execute commit forall merge
    pipe
    
    
    SQL>
    It means that you'll have to use dynamic sql, and execute immediate helps doing that:
    Code:
    SQL> declare
      2    v_date varchar2(10);
      3    str    varchar2(200);
      4  begin
      5    select to_char(sysdate, 'ddmmyy') into v_date from dual;
      6    str := 'alter table test rename column col to col' || v_date;
      7    execute immediate(str);
      8  end;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SQL> desc test
     Name                                                  Null?    Type
     ----------------------------------------------------- -------- ----------------
     COL250509                                                      NUMBER(4)
    
    SQL>
    Therefore, perhaps you should pay more attention while suggesting solutions to someone's questions. Testing before posting also helps (if possible, of course).

  6. #6
    Join Date
    May 2009
    Location
    Azerbaijan, Baku
    Posts
    6
    Hi Littlefoot. I thought that the user will user "execute immediate" clause by himself. I showed him a base structure, the base SQL which he will use to get it. Of course he MUST use it inside PL/SQL block and use "execute immediate" to run it. I just showed him that he can CONCATENATE a date value to get SQL string which he wants

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Kamran,

    I have replied to @rana_dba's message, not yours.

  8. #8
    Join Date
    May 2009
    Location
    Azerbaijan, Baku
    Posts
    6
    Oh yeap. Sorry

Posting Permissions

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