Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Unanswered: Error with Dynamic query

    Hi,

    I have written a procedure, which will accept values dynamically & executes. In this I can insert values manually, but through program data is not inserting.

    Dynamic Query:

    SET v_QUERY = 'INSERT INTO FR_1
    (DB_NAME,TABLE_NAME,COLUMN_NAME,S_COLUMN_TYPE,DATA )
    (SELECT '''||v_DATABASE||''','''||v_TABLE_NAME||''','''||v _COLUMN_NAME||''','''||v_COLUMN_TYPE||''','||v_COL UMN_NAME||' FROM '||v_DATABASE||'.'||v_TABLE_NAME||' FETCH FIRST '||SAMPLE||' ROWS ONLY)';

    manually insertion:

    INSERT INTO FR_1
    (DB_NAME,TABLE_NAME,COLUMN_NAME,S_COLUMN_TYPE,DATA )
    (SELECT 'SAMPLE','VE.PATTERN_TEST','LAST_NAME',
    'VARCHAR',LAST_NAME FROM SAMPLE.VE.PATTERN_TEST
    FETCH FIRST 8 ROWS ONLY);

    what I need to change when I am doing this dynamically.

    Kindly forgive me if it is a basic thing as I am learning now.

    Thanks in advance

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    have you looked at prepare statement? I am guessing that is what you have left out. I know a few folks in your last few queries to the forum here have pointed you to the documentation and samples database. Have you tried looking through them?
    Dave

  3. #3
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    try this:
    Code:
    set serveroutput on@
    
    begin
      DECLARE v_QUERY VARCHAR(200);
      DECLARE v_DATABASE VARCHAR(10) DEFAULT 'SAMPLE';
      DECLARE v_TABLE_NAME VARCHAR(20) DEFAULT 'VE.PATTERN_TEST';
      DECLARE v_COLUMN_NAME VARCHAR(10) DEFAULT 'LAST_NAME';
      DECLARE v_COLUMN_TYPE VARCHAR(10) DEFAULT 'VARCHAR';
      DECLARE SAMPLE VARCHAR(10) DEFAULT '10';
      
      SET v_QUERY = 
        'INSERT INTO FR_1 (DB_NAME,TABLE_NAME,COLUMN_NAME,S_COLUMN_TYPE,DATA)'
      ||' SELECT '''||v_DATABASE||''','''||v_TABLE_NAME||''','''||v_COLUMN_NAME||''','''||v_COLUMN_TYPE||''',LAST_NAME'
      ||' FROM '||v_DATABASE||'.'||v_TABLE_NAME
      ||' FETCH FIRST '||SAMPLE||' ROWS ONLY';
    
      CALL DBMS_OUTPUT.PUT_LINE(v_QUERY);
    end@
    Regards,
    Mark.

  4. #4
    Join Date
    Jul 2014
    Posts
    294
    Thanks Mark,

    Now it is working.

Tags for this Thread

Posting Permissions

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