Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2009
    Posts
    10

    Unanswered: create a procedure

    I WANT TO CREATE A PROCEDURE FOR DOING FOLLOWING 4 ACTIONS

    1)

    That will first delete all data from xa and then load data from xls file (TOWN.XLS on d drive)with columns USER_ID, CLUSTER_CODE,TOWN, TOWN_CATEGORY, TOWN_TYPE, TYPE, STATUS
    INto the table XA




    TABLE XA
    (
    USER_ID VARCHAR2(40) NOT NULL,
    CLUSTER_CODE VARCHAR2(15) NOT NULL,
    TOWN VARCHAR2(50),
    TOWN_CATEGORY VARCHAR2(50),
    TOWN_TYPE VARCHAR2(50),
    TYPE VARCHAR2(25),
    STATUS VARCHAR2(10)
    )



    2) perform the insert as
    insert into XC select a.user_id,a.user_id,xa.cluster_code,a.full_name,a. CIRCLE, xa.TOWN, xa.TOWN_CATEGORY, xa.TOWN_TYPE, xa.TYPE, xa.STATUS, default
    from A,xa where a.user_id=xa.user_id and user_id in (select user_id from xa)

    TABLE XC
    (
    CRTCODE VARCHAR2(25) NOT NULL,
    USER_ID VARCHAR2(40) NOT NULL,
    CLUSTER_CODE VARCHAR2(15) NOT NULL,
    OUTLET_NAME VARCHAR2(100) NOT NULL,
    CIRCLE VARCHAR2(50) NOT NULL,
    TOWN VARCHAR2(50),
    TOWN_CATEGORY VARCHAR2(50),
    TOWN_TYPE VARCHAR2(50),
    TYPE VARCHAR2(25),
    STATUS VARCHAR2(10),
    CREATED_DATE DATE DEFAULT SYSDATE
    )

    3) perform the insert as
    insert into xb select user_id,full_name,default,circle,otc_code,default, default from a

    TABLE XB
    (
    USERID VARCHAR2(40) NOT NULL,
    USERNAME VARCHAR2(40) NOT NULL,
    USERTYPE NUMBER DEFAULT 0 NOT NULL,
    CIRCLE VARCHAR2(25) NOT NULL,
    OTC_CODE VARCHAR2(10),
    PASSWORD VARCHAR2(20) DEFAULT NULL,
    PWDCHANGEFLAG NUMBER DEFAULT NULL
    )


    4) if role id 130 not mapped for userid (xls sheet )then
    insert user_id with role_id 130 in table xd

    TABLE XD
    (
    USER_ID VARCHAR2(40),
    ROLE_ID NUMBER(4)
    )

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Seems like a standard homework assignment.
    Please proceed as directed.
    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
    Apr 2009
    Posts
    10
    a standard officework assignment it is
    but i need a solution

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Your answer is just a few clicks beyond the URL below.
    Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation
    Let us know when you have found your answer.
    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.

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking Do it and learn

    Quote Originally Posted by veer b singh
    a standard officework assignment it is
    but i need a solution
    And you want us to do your work?

    It's good practice and learning experience to try and do the work yourself and if you do get stuck, we will be happy to point you in the right direction.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Apr 2009
    Posts
    10
    Thanks for encouragement.
    i found a way, i made a batch file that runs sqlloader and then runs an sql script which calls the procedure doing those things mentioned above

    I still have a question can we save a clipboard data to a file through command prompt

    would i need a software for that?

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Quote Originally Posted by veer b singh
    Thanks for encouragement.
    i found a way, i made a batch file that runs sqlloader and then runs an sql script which calls the procedure doing those things mentioned above

    I still have a question can we save a clipboard data to a file through command prompt

    would i need a software for that?
    Open editor of choice such as notepad, wordpad, etc.
    Paste clipboard into editor.
    Click Save As & enter filename.
    click Exit
    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.

  8. #8
    Join Date
    Apr 2009
    Posts
    10
    i need this to be donne through script

  9. #9
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Clipit?

    Quote Originally Posted by veer b singh
    i need this to be donne through script
    And from where in the script would the "clipboard data" come from?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    How does the script get data into/onto the clipboard?

    What does this question have to do with Oracle?
    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.

  11. #11
    Join Date
    Apr 2009
    Posts
    10
    clipboard data needs to be inserted in a csv file which finds its way to an oracle table.


    clipboard data is from my mail

  12. #12
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What does this question have to do with Oracle?

    Exactly how does the mail get onto the clipboard?
    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.

  13. #13
    Join Date
    Apr 2009
    Posts
    10
    //Exactly how does the mail get onto the clipboard?//


    Would the reply to above get me an oracle specific solution?
    if not in your own words What does this question have to do with Oracle?

    The clipboard data is through selecting and copying text from my mail.


    __________________
    Knowledge is a product of analytical thinking,
    not an unprocessed and raw fact

  14. #14
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >The clipboard data is through selecting and copying text from my mail.
    How is this accomplished within a script?
    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.

  15. #15
    Join Date
    Apr 2009
    Posts
    10
    //
    How is this accomplished within a script?
    //

    this is exactly what i want to know

    i wrote above

    //
    i need this to be donne through script
    //

    and if you couldnt figureout what i meant to say then forget it

    and dont ask what has this to do with oracle?

    couldnt you even understand

    //
    Would the reply to above get me an oracle specific solution?
    if not in your own words What does this question have to do with Oracle?
    //

    or
    Are there some points you get for replying to posts?
    b'coz you are replying to me even when i dont want to listen to you

    and one thing more
    postscript doesnt make the writer more appealing

Posting Permissions

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