How to use macro in Sqlplus
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to use macro in Sqlplus

  1. #1
    Join Date
    Dec 2001
    Posts
    8

    Exclamation

    I want ask you a private qize,please see the folling program,

    set serveroutput on 1
    declare 2
    p_item_id1 number := 1111 ; 3
    p_item_id2 number := 2222 ; 4
    p_item_id3 number := 3333 ; 5
    cnt number; 6
    tmp varchar2(20); 7
    begin 8
    for cnt in 1..3 loop 9
    tmp := 'p_item_id'|| to_char(cnt) ; 10
    dbms_output.put_line( tmp ); 11
    -- dbms_output.put_line( &tmp ); 12
    end loop ; 13
    end ; 14


    result (Execute in sqlplus environment):
    p_item_id1
    p_item_id2
    p_item_id3

    I want the result as the following:
    1111
    2222
    3333

    I used line 12 replaced line 11,but failed,could you help me(can I use macro to resolve this problem).

    Best regards,

    Michael Ni


  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,968
    TMP is a PL/SQL Variable. If you want to make an assignment to it or use it in a PL/SQL command you do not need to use the &. & is only used as a variable in a SQL command. for example if I have a sql file that connects as SYSTEM and does some stuff I could do the following.

    UNDEFINE mypasswd
    UNDEFINE myalias

    CONNECT SYSTEM/&&mypasswd@&&myalias

    -- do some stuff

    EXIT;

    You would run this inside a .sql file and when the connect statement comes up it will prompt you for the password and alias.

Posting Permissions

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



Click Here to Expand Forum to Full Width