Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Location
    Belgium
    Posts
    1

    Question Unanswered: DBMS_SQL package

    I've described:

    col_value varchar2(4000);

    now i'm trying in a procedure the following

    DBMS_SQL.COLUMN_VALUE(c3,col_num,col_value);

    normally this should return data buth it gives nothing...

    Somebody has an idea???


    Here under I'll give you the full code:

    CREATE OR REPLACE PROCEDURE xxresponsabilities_sp3 (PIN_PAGE IN NUMBER) IS
    /* Cursor declaration */
    CURSOR C1 is
    SELECT id
    ,table_name
    FROM xxtables
    WHERE page_ID = PIN_PAGE;

    CURSOR C2(cin_table_id in number) is
    SELECT column_name, alias_name, typevar, leng
    FROM xxcolumns
    WHERE table_id = cin_table_id
    order by id;

    /* Type declaration */
    TYPE resrec IS RECORD(V_COL VARCHAR2(60),
    V_DATA VARCHAR2(240));
    TYPE t_restab is table of resrec index by binary_integer;
    TYPE colrec IS RECORD(V_COL VARCHAR2(240));
    TYPE t_coltab is table of colrec index by binary_integer;

    /* Variables */
    v_restab t_restab;
    v_coltab t_coltab;
    i number;
    j number;
    z number;
    lengte number;
    tabstr varchar2(4000);
    str varchar2(4000);
    v_value varchar2(4000) := null;
    tablename varchar2(30);
    r1 c1%rowtype;
    c3 INTEGER;
    ignore INTEGER;
    v_exec INTEGER;
    col_cnt INTEGER;
    col_num number;
    colposition INTEGER;
    rec_tab dbms_sql.desc_tab;
    col_value Varchar2(4000);
    num_value number;
    the_id number;

    BEGIN
    OPEN c1;
    FETCH c1 into r1.id, r1.table_name;
    IF c1%found THEN
    tablename := r1.table_name;
    i := 0;
    tabstr := 'SELECT ';
    FOR r2 in c2(r1.id) loop
    v_coltab(i).v_col := r2.column_name;
    tabstr := concat(tabstr,r2.column_name);
    tabstr := concat(tabstr,',');
    i:=i+1;
    if r2.column_name != 'ID' then
    ****print
    end if;
    END LOOP;
    lengte := length(tabstr);
    tabstr := substr(tabstr,1,lengte-1);
    tabstr := concat(tabstr,' from ');
    tabstr := concat(tabstr,tablename);
    END IF;

    j := 0;
    i := i-1;
    z := 1;
    CLOSE C1;

    c3 := dbms_sql.open_cursor;
    DBMS_SQL.PARSE(c3,tabstr,DBMS_SQL.native);

    v_exec := DBMS_SQL.EXECUTE_AND_FETCH(c3);

    dbms_sql.describe_columns(c3,col_cnt,rec_tab);

    htp.tableRowOpen;
    LOOP
    col_num := rec_tab.first;
    IF (col_num is not null) then
    LOOP
    DBMS_SQL.define_column(c3,col_num,rec_tab(col_num) .col_name,rec_tab(col_num).col_max_len);
    if rec_tab(col_num).col_name = 'ID' then
    DBMS_SQL.column_value(c3,col_num,col_value);
    the_id := col_value;
    else
    str := 'SELECT '||rec_tab(col_num).col_name||' from '||tablename||' where id = '||the_id;
    dbms_output.put_line(str);
    EXECUTE IMMEDIATE str INTO v_value;
    v_restab(j).v_data := v_value;
    j := j+1;
    EXIT WHEN j > i;
    end if;
    col_num := rec_tab.next(col_num);
    EXIT WHEN (col_num is null);
    END LOOP;
    END IF;
    IF DBMS_SQL.FETCH_ROWS(c3)=0 THEN
    EXIT;
    END IF;
    END LOOP;
    htp.tableRowClose;
    DBMS_SQL.CLOSE_CURSOR(c3);

    FOR k IN v_restab.FIRST .. v_restab.LAST LOOP
    ****Print
    END LOOP;
    END;

  2. #2
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    native dynamic sql ...

    have a look at: http://download-uk.oracle.com/docs/c...9dyn.htm#26586

    Some two years ago I completely switch from using the dbms-sql package to "native dynamic sql", which seems to be easer for my approaches.

    coming back to your coding.
    (1) I have never used a dbms_sql.execute_and_fetch Procedure ... only dbms_sql.fetch_rows
    (2) code the dbms_sql.define_columns BEFORE the dbms_sql.fetch_rows ...

Posting Permissions

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