Results 1 to 10 of 10

Thread: SQL Profile

  1. #1
    Join Date
    Oct 2010
    Posts
    19

    Cool Unanswered: SQL Profile

    Hi friends,

    I want to create a sql profile that is, after inserting or updating of record i want this is to be updated in text file .

    I have written the stored procedure also.

    Please help and give me the commands such that after execution of stored procedure it should get updated in text file that log file to check the updated values.Please help me friends.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I have written the stored procedure also.
    modify procedure to write update to text file directly
    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
    Oct 2010
    Posts
    19
    Can some one help me out.pls.

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    a Hint, check out utl_file package and triggers
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

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

    Cool

    Quote Originally Posted by caroll View Post
    I want to create a sql profile that is, after inserting or updating of record i want this is to be updated in text file.
    SQL profile?
    Perhaps you are referring to a trigger?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Oct 2010
    Posts
    19

    Red face

    CREATE OR REPLACE DIRECTORY DATALOAD AS ‘D:\oracle\product\10.2.0\admin\orcl\udump’;

    CREATE TABLE TEST_TABLE
    (
    TEST_TABLE_ID NUMBER(9) NOT NULL,
    CITY VARCHAR(50),
    STATE VARCHAR(2),
    ZIP VARCHAR(10),
    CNTRY VARCHAR(50),
    CONSTRAINT PK_TEST_TABLE PRIMARY KEY(TEST_TABLE_ID)
    )
    /

    INSERT INTO TEST_TABLE(TEST_TABLE_ID,CITY,STATE,ZIP,CNTRY)
    VALUES(1,’ATLANTA’,'GA’,’30318′,’USA’)
    /
    INSERT INTO TEST_TABLE(TEST_TABLE_ID,CITY,STATE,ZIP,CNTRY)
    VALUES(2,’CALGARY’,'AB’,'T6H-5K9′,’CANADA’)
    /
    INSERT INTO TEST_TABLE(TEST_TABLE_ID,CITY,STATE,ZIP,CNTRY)
    VALUES(3,’JOHNS CREEK’,'GA’,’30097′,’USA’)
    /
    INSERT INTO TEST_TABLE(TEST_TABLE_ID,CITY,STATE,ZIP,CNTRY)
    VALUES(4,’FOREST HILLS’,'NY’,’11798′,’USA’)
    /


    CREATE TABLE external_city
    (
    ID,CITY,STATE,ZIP,CNTRY
    )
    ORGANIZATION EXTERNAL
    (
    TYPE oracle_datapump
    DEFAULT DIRECTORY dataload
    LOCATION (‘external_city.txt’)
    )
    AS
    SELECT TEST_TABLE_ID,CITY,STATE,ZIP,CNTRY
    FROM TEST_TABLE

    I have tried in this way also but its not updating in the log file.please help me how to update in log fle.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    EXTERNAL TABLE are always & only READONLY
    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
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I noticed that you used the example from Writing data to a text file from Oracle « Systems Engineering and RDBMS. What version are you running? use of type oracle_datapump works only in 10g and above and the directory that you are pointing to in create directory MUST exist and be writable by the oracle user. dont forget you must also grant the correct priviledges

    GRANT READ, WRITE ON DIRECTORY DATALOAD TO my_schema;

    The grant must be done by a priviledged user.
    Last edited by beilstwh; 08-09-11 at 10:49.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Oct 2010
    Posts
    19

    Exclamation

    i m using oracle 9g version.

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >CREATE OR REPLACE DIRECTORY DATALOAD AS ‘D:\oracle\product\10.2.0\admin\orcl\udump’;
    >i m using oracle 9g version.
    10? 9? post results from following use COPY & PASTE

    SELECT * FROM V$VERSION;
    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.

Posting Permissions

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