Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2013
    Posts
    5

    Unanswered: PL/SQL Dynamic Data Loading

    Hi ,
    I am basically sap bo developer, my superior assigned me task based on pl/sql.
    past one week i am struggling to solve the issue.If my question is silly, sorry for that.
    My task is to load the data dynamically.
    1.Load the data from source table to target table.
    e.g,
    create table load_test(
    SOURCE_TABLE VARCHAR2(32),
    TARGET_TABLE VARCHAR2(32),
    SOURCE_COL VARCHAR2(32),
    TARGET_COL VARCHAR2(32),
    )
    1. START WITH COPY THE ABOVE FILE TO TARGET PROCEDURE FILE
    2. READ THE SOURCE TABLE NAME FROM CONFIG TABLE
    3. GENERATE A DYNAMIC CURSOR STATEMENT WITH THIS SOURCE TABLE NAME
    4. WRITE THIS DYNAMIC STATEMENT TO TARGET PROCEDURE FILE 5. GENERATE DYNAMIC FETCH STATEMENT AND WRITE TARGET PROCEDURE FILE 6. READ SOURCE COLUMNS AND CORRESPONDING TARGET COLUMNS
    7. GENERATE A DYNAMIC INSERT STATEMENT USING THIS SOURCE COLUMNS AND TARGET COLUMNS. EG. INSERT INTO "TARGET TABLE NAME (TARGET COL1,
    COL2...) VALUES (C. SOURCE COL1, COL10, COL11, ...)
    8. WRITE THIS STATEMENT TO TARGET PROCEDURE FILE 9. WRITE "END LOOP, END; /' TO TARGET PROCEDURE FILE 10. CLOSE THE PROGRAM.

    Kindly help me to fix this issue.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    INSERT INTO TARGET_TABLE (TARGET_COL1, TARGET_COL2) SELECT SOURCE_COL1, SOURCE_COL2 FROM SOURCE_TABLE;
    do as above for as many columns as appropriate
    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
    Jul 2013
    Posts
    5
    Thank you for reply..How can i add this query dynamically in procedure.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    write SQL that writes SQL

    Code:
    SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';' FROM USER_TABLES;
    code above will report rows counts for every table owned by USER.
    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
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    You'll need to compose INSERT statement and then run it using EXECUTE IMMEDIATE (it is used when doing something "dynamically" in PL/SQL).

    Table you posted (LOAD_TEST) looks awkward (or, at least, not normalized); what does it contain?

  6. #6
    Join Date
    Jul 2013
    Posts
    5
    TABLE LOAD_TEST CONTAIN SOURCE TABLE,COLUMN AND TARGET TABLE AND COLUMN
    1.I WANT TO CALL THE ABOVE TABLE TO PROCEDURE.
    2.READ SOURCE TABLE.. FROM TABLE
    3.DYNAMIC CURSOR
    4.INSERT STATEMENT
    5.WRITE THESE STATEMNTS TO PROCEDURE

    GOAL is to load data from source to target dynamically.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >3.DYNAMIC CURSOR

    post working example of dynamic cursor; as opposed to static cursor.
    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
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I understand what you want to do. I still don't know contents of the LOAD_TEST table. "Solution" might depend on that.

    By the way, why are column datatypes VARCHAR2(32)?

  9. #9
    Join Date
    Jul 2013
    Posts
    5
    LOAD_TEST is just table,i need to write this table to procedure
    or else another option
    load data dynamicallly without creating procedure(no need tables)

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I'm sorry, but explanation you provided doesn't help at all. I *know* that LOAD_TEST is just a table, that's more than obvious. What does "i need to write this table to procedure" mean? How do you "write a table to a procedure"?

    What problem are you solving, really? Is it just an exercise? Why "dynamically"?

    If source and target tables are EXACTLY the same, you could even
    Code:
    insert into target select * from source
    .
    Or, you could copy data from one table to another using (data pump) export/import.

    Or you could use CTAS
    Code:
    create table target as select * from source
    Plenty of options, only if we knew what are you doing and why you are doing that.

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

    Cool

    Quote Originally Posted by nehal View Post
    Hi ,
    I am basically sap bo developer, my superior assigned me task . . . E t c . . .
    You have been provided several answers to your question but have yourself not provided and evidence you have tried to code or do anything on your own.

    The purpose of this forum is to provide help or assistance but NOT to do the work for you.

    Show us what you have done.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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