Re: Dynamic SQL Query

Kevin,

a lot depends on how long your sql-string is. I ran into the limit once and had to use the array processing option. You can find limited descriptions the manual's, so I include a small example.

ie
create or replace bla-bla
MY_TABLE sys.dbms_sys_sql.varchar2s;

LFFLG       BOOLEAN := TRUE;
I1          BINARY_INTEGER := 0; -- until position in table
I2          BINARY_INTEGER := 1; -- start position in table
C_CUR       INTEGER;
E_EXE       INTEGER;

begin
-- fill my_table

       C_CUR := DBMS_SQL.OPEN_CURSOR;        DBMS_SQL.PARSE (C_CUR,MY_TABLE,I2,I1,LFFLG,DBMS_SQL.V7);        E_EXE := DBMS_SQL.EXECUTE(C_CUR);        DBMS_SQL.CLOSE_CURSOR(C_CUR); In article <35B49300.2BA5_at_hrb.com>,
  Kevin <kjk_at_hrb.com> wrote:
> I am trying to create a query on the fly and for some reason I can't get
> the thing to work. Basically what I have done is created a string that
> contains exactly what I want my SELECT statement to be. This was done
> by concatinating conditions into a WHERE clause. Is there some way that
> I can execute this string? I have tried to use DBMS_SQL to do this and
> all it does is crash. Such as:
>
> v_CursorID := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(v_CursorID, sql_string, DBMS_SQL.V7);
> DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m1', p_last_name);
> DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_emp_no,4);v_Dummy:=
> DBMS_SQL.EXECUTE(v_CursorID);
> loop
> if DBMS_SQL.FETCH_ROWS(v_CursorID)=0 then
> EXIT;
> end if;
> DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_emp_no);
>
> (print values)
>
> end loop;
> DBMS_SQL.CLOSE_CURSOR(v_CursorID);
>
> Not sure if there is another better way to do this. Any suggestions?
>
> Thanks in advance...
> Kevin
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Jul 22 1998 - 05:55:01 CDT