Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Posts
    4

    Unanswered: DBMS_SQL package question

    hello Experts,
    I have a question on DBMS_SQL package. I am trying to create a INSERT statement where I dont have the table_name or the column_name until runtime. What I am doing is that for a given account_number/table_name, I am creating two strings - one for all the columns for this table that need to be inserted and the other for their respective values. Then , whenever a table_name or account_number changes, I create the full sring and execute it and start creating the next string for insertion. However, when I execute the DBMS_SQL package, it gives me an error saying that "column not allowed here". Could some one please tel me what I am doign wrong.
    thx in advance

    DECLARE
    --tdate date;
    tmpVar INTEGER;
    county INTEGER;
    tvar varchar(150):=NULL;
    tvar3 varchar(150):=NULL;
    tvar4 varchar(150):=NULL;
    tvar5 varchar(150):=NULL;
    tvar6 varchar(150):=NULL;
    tvar1 varchar(150):=NULL;
    tvar2 varchar(150):=NULL;
    tvar10 varchar(150):=NULL;
    tvar11 varchar(150):=NULL;
    --tnum number;
    CURSOR in_up_cur IS SELECT t.*
    FROM TEMP_VALUES t, TEMP_VALUE_VIEW tv
    WHERE t.ACCT_NUM=tv.ACCT_NUM and t.TABLE_NAME=tv.TABLE_NAME and
    t.COLUMN_NAME=tv.COLUMN_NAME and t.seq_num=tv.seq;
    BEGIN
    tmpVar := DBMS_SQL.OPEN_CURSOR;

    tvar:='INSERT INTO ';

    tvar1:=') VALUES (';

    county:=1;

    for I in in_up_cur
    loop
    IF tvar3 IS NULL AND tvar4 IS NULL
    THEN
    tvar3:=I.ACCT_NUM;
    tvar4:=I.TABLE_NAME;
    END IF;
    <<label1>>
    IF (tvar3=I.ACCT_NUM AND tvar4=I.TABLE_NAME AND county=1)
    THEN
    tvar2:=tvar2||'('||I.COLUMN_NAME;

    tvar10:=tvar10||I.VALUE;
    county:=county+1;

    ELSIF tvar3=I.ACCT_NUM AND tvar4=I.TABLE_NAME
    THEN
    tvar2:=tvar2||', '||I.COLUMN_NAME;

    tvar10:=tvar10||', '||I.VALUE;
    ELSE

    tvar11:=tvar||tvar4||tvar2||tvar1||tvar10||')';
    DBMS_SQL.PARSE(tmpVar, tvar11, dbms_sql.v7);
    DBMS_OUTPUT.PUT_LINE(' RECORD IS '||tvar11);
    tvar3:=I.ACCT_NUM;
    tvar4:=I.TABLE_NAME;
    tvar2:=NULL;
    tvar10:=NULL;
    county:=1;
    goto label1;
    END IF;

    end loop;
    tvar11:=tvar||tvar4||tvar2||tvar1||tvar10||')';
    DBMS_SQL.PARSE(tmpVar, tvar11, dbms_sql.v7);
    DBMS_OUTPUT.PUT_LINE('FINAL RECORD IS '||tvar11);
    DBMS_SQL.CLOSE_CURSOR(tmpVar);
    END;

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: DBMS_SQL package question

    Spare us all a headache and show us what the constructed SQL statement (tvar11???) looks like!

    One of the things you are doing wrong is giving all your variables meaningless names! I struggle to guess what this all adds up to:

    tvar11:=tvar||tvar4||tvar2||tvar1||tvar10||')';


  3. #3
    Join Date
    Feb 2003
    Posts
    4
    The output looks like these - and this part (concatenation of the strings) works fine - its the DBMS_SQL that gives me the error.

    Output - "" INSERT INTO TEMP_TABLE(CIS_NUM, PCOLL_IND) VALUES (HI, 14-JAN-03) ""

    -- so I am creating the table_name, column name list and the values list that go into the columns simultaneously by getting these values from the cursor and then adding to the string as I go along. The table and view definition is as follows. I am on Oracle 8i (8.1.7.0.2)


    CREATE TABLE TEMP_VALUES (
    ACCT_NUM VARCHAR2 (10) NOT NULL,
    TABLE_NAME VARCHAR2 (50) NOT NULL,
    COLUMN_NAME VARCHAR2 (50) NOT NULL,
    VALUE VARCHAR2 (250),
    SEQ_NUM NUMBER (3))



    CREATE OR REPLACE VIEW TEMP_VALUE_VIEW ( ACCT_NUM,
    TABLE_NAME, COLUMN_NAME, SEQ ) AS SELECT ACCT_NUM, TABLE_NAME, COLUMN_NAME, MAX(SEQ_NUM) SEQ FROM TEMP_VALUES
    GROUP BY ACCT_NUM, TABLE_NAME, COLUMN_NAME

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by lordofthering
    The output looks like these - and this part (concatenation of the strings) works fine - its the DBMS_SQL that gives me the error.

    Output - "" INSERT INTO TEMP_TABLE(CIS_NUM, PCOLL_IND) VALUES (HI, 14-JAN-03) ""

    There's your error - nothing to do with DBMS_SQL at all. Try running the insert in SQL Plus and you will see it fail:

    SQL> INSERT INTO TEMP_TABLE(CIS_NUM, PCOLL_IND) VALUES (HI, 14-JAN-03) ;
    INSERT INTO TEMP_TABLE(CIS_NUM, PCOLL_IND) VALUES (HI, 14-JAN-03)
    *
    ERROR at line 1:
    ORA-00984: column not allowed here

    1) You need to put quotes around text strings.

    2) If PCOLL_IND is a DATE column (strangely named!) then you should use TO_DATE with a format mask on the value - otherwise a default format mask may trip you up later.

    So your statement should be:

    INSERT INTO TEMP_TABLE(CIS_NUM, PCOLL_IND) VALUES ('HI', TO_DATE('14-JAN-2003','DD-MON-YYYY'))

    Some other comments:

    3) Using 2-digit years is bad practice - remember Y2K?

    4) You should be using BIND VARIABLES, i.e.
    INSERT INTO TEMP_TABLE(CIS_NUM, PCOLL_IND) VALUES (:cis_num,:pcoll_ind)

    then use DBMS_SQL.BIND_VALUE like this:

    DBMS_SQL.BIND_VALUE( cursor, 'CIS_NUM', 'HI' );
    DBMS_SQL.BIND_VALUE( cursor, 'PCOLL_IND', TO_DATE('14-JAN-2003','DD-MON-YYYY'));

    If you don't use bind variables, you will flood Oracle with 1000s of virtually identical statements, and performance will suffer (everyone's performance, not just this program's).

  5. #5
    Join Date
    Feb 2003
    Posts
    4

    dbms_sql package error cont.

    thanks so much for yuor reply Tom.
    However, when I try to use bind_variable (I am assuming thats what you meant and not bind_value), a window pops up and prompts me for a value for my variable - inspite of me using
    DBMS_SQL.BIND_VARIABLE( tmpVar, ':tvar3', 'HI' );
    this is how my query looks now

    DECLARE
    tvar10 varchar(150);

    BEGIN
    DBMS_SQL.BIND_VARIABLE( tmpVar, ':tvar10', 'HI' );

    DBMS_SQL.PARSE(tmpVar,
    'insert into '||tvar||'('||tvar4||','||tvar5||')'||
    ' values('||:tvar10||','||row||')', dbms_sql.v7);


    rows_processed := dbms_sql.execute(tmpVar);
    i have two columns in the table where I am inserting indicated by tvar4 and tvar5 - should I be binding them aswell
    any ideas

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: dbms_sql package error cont.

    Yes, I meant BIND_VARIABLE - sorry.

    Remove the colon in the BIND_VARIABLE call:

    DBMS_SQL.BIND_VARIABLE( tmpVar, 'tvar10', 'HI' );

    Then use the bind variable literally in the SQL:

    DBMS_SQL.PARSE(tmpVar,
    'insert into '||tvar||'('||tvar4||','||tvar5||') values(:tvar10,'||row||')',
    dbms_sql.v7
    );

    tvar4 and tvar5 are the NAMES of the columns, so no, you don't want to (and can't) use BIND_VARIABLE for those. But you should be using a bind variable for ROW.

    Why don't you give your variables meaningful names? Like:
    tmpVar => v_cursor
    tvar => v_tablename
    tvar4 => v_colname1
    tvar5 => v_colname2
    :tvar10 => :value1
    row => :value2

    Then your statement becomes:

    DBMS_SQL.PARSE(v_cursor,
    'insert into '||v_tablename||'('||v_colname1||','||v_colname2|| ') values(:value1,:value2)',
    dbms_sql.v7
    );

    Seems a lot clearer to me!

  7. #7
    Join Date
    Feb 2003
    Posts
    4
    you are right, amd next time I will be sure to use meaningful names. So just bear with me this time.
    The query is still not working - now the error is
    "The following error has occurred:

    ORA-01003: no statement parsed
    ORA-06512: at "SYS.DBMS_SYS_SQL", line 837
    ORA-06512: at "SYS.DBMS_SQL", line 44
    ORA-06512: at line 49
    "

    the code now looks like this
    DBMS_SQL.BIND_VARIABLE( tmpVar, 'tvar10', 'HI' );

    DBMS_SQL.PARSE(tmpVar, 'insert into '||tvar||'('||tvar4||','||tvar5||
    ') values(:tvar10, '||','||row||')', dbms_sql.v7);

  8. #8
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Move the BIND_VARIABLE calls to after the PARSE call.

Posting Permissions

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