Re: Reguarding DBMS_SQL and Dynamic SQL

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Wed, 24 Feb 1999 14:58:52 GMT
Message-ID: <36d41240.81462196@inet16.us.oracle.com>

On Tue, 23 Feb 1999 17:30:38 -0700, "Mark Berry" <berrym_at_roomsystems.com> wrote:

>Ok, I am trying to perform the following function... (not this is just a
>sample clip, but the practicality is there..)
>
>
>
>Begin
>declare
>temp Char(230) := '';
>vGate char(230);
>inv integer;
>room integer := 100;
>counter integer;
>cid integer;
>Begin
>cid := dbms_sql.OPEN_CURSOR;
>counter := 1;
>loop
> vGate := 'cil_g' || counter;
> dbms_sql.parse(cid, 'select :v into :t from room_tbl where :r =
>room_number ', dbms_sql.v7);
> dbms_sql.bind_variable(cid, ':v', vGate);
> dbms_sql.bind_variable(cid, ':r', room);
> dbms_sql.bind_variable(cid, ':t', temp);
> dbms_output.put_line('"' || rtrim(vgate) || '"');
> counter := counter+1;
> exit when counter > 48;
> end loop;
> End;
>end;
>
>
>
>I need to get the RESULT of V into T (vGate into the Temp variable...)
>this is the results that I am getting from this compile...
>
>ORA-00905: missing keyword
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
>ORA-06512: at "SYS.DBMS_SQL", line 32
>ORA-06512: at line 15
>
>Any help would be VERY appreciated..
>
>BTW, excuse my ignorance on this issue, I am a new MSSQL convert trying to
>get our system to Oracle.
>
>Thanks.
>

Here is an example of dbms_sql which uses both bind variables and fetching. I think it shows everything that you need to use.

SQL>
  1 declare

  2    l_result varchar2(2000);
  3    l_column varchar2(2000);
  4    l_empno number := 7839;
  5    l_cursor number;
  6    l_status number;

  7 begin
  8 l_cursor := dbms_sql.open_cursor;   9 for x in ( select column_name

 10                 from dba_tab_columns
 11                where table_name = 'EMP'
 12                  and owner = 'SCOTT' )
 13    loop
 14      dbms_sql.parse( l_cursor,
 15         'select ' || x.column_name ||
 16         ' from emp where empno = :empno', dbms_sql.v7);
 17      dbms_sql.bind_variable( l_cursor, 'empno', l_empno );
 18      dbms_sql.define_column( l_cursor, 1, l_result, 2000 );
 19      l_status := dbms_sql.execute( l_cursor );
 20      l_status := dbms_sql.fetch_rows( l_cursor );
 21      dbms_sql.column_value( l_cursor, 1, l_result);
 22      dbms_output.put_line( x.column_name || ' - "' || l_result || '"' );

 23 end loop;
 24 dbms_sql.close_cursor( l_cursor );  25* end;

SQL>
EMPNO - "7839"
ENAME - "KING"
JOB - "PRESIDENT"
MGR - ""
HIREDATE - "17-NOV-81"
SAL - "4000"
COMM - ""
DEPTNO - "10" PL/SQL procedure successfully completed.

hope this helps.

chris.

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Feb 24 1999 - 08:58:52 CST