Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003

    Unanswered: build a sql request

    Hello, (sorry for my english, I'm french)

    I have a table of records:
    FLDNAM(1)= 'NUMCLI' FLDVAL(1)= null FLDTYP(1)='01' FLDLG(1)=20
    FLDNAM(2)= 'NAMCLI' FLDVAL(2)= null FLDTYP(2)='01' FLDLGT(2)=30
    FLDNAM(3)= 'PRNCLI' FLDVAL(3)= null FLDTYP(3)='01' FLDLGT(3)=40

    the values to assign to FLDVAL are in a table (my_table) where the FLDNAM values, in my records table, represent the name of the cols of my_table
    I know the primary key of my table

    my question is how to obtain quickly the values for FLDVAL.

    select FLDNAM(1), FLDNAM(2), FLDNAM(3) from my_table where keynum = 'aaa' into FLDVAL(1), FLDVAL(2), FLDVAL(3)

    my first idea was:

    cls_sel := '';
    cls_int := '';

    FOR i IN 1..(idx-1)
    cls_sel := cls_sel || tblval(i).FLDNAM || ', ';
    cls_int := cls_int || 'tblval(' || i || ').FLDVAL, ';
    cls_sel := cls_sel || tblval(idx).FLDNAM;
    cls_int := cls_int || 'tblval(' || idx || ').FLDVAL ';

    request := 'SELECT ' || cls_sel || ' FROM ma_table where keynum = ''10''';
    execute immediate request into cls_int;

    But the into clause cls_int is considered unique and then that doesn' work

    Please help me !!!!

  2. #2
    Join Date
    Sep 2003
    Virginia, USA
    I think you are on the right path. The INTO clause needs one variable for each column being returned. You are selecting N columns, so you need N variables to put the values into. Example:
    execute immediate request into var1, var2, varN.
    Author, Oracle Database 10g: From Nuts to Soup

Posting Permissions

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