Re: A Dynamic SQL problem.....Help!!

From: David Pattinson <david_at_addease.com.au>
Date: Mon, 31 May 1999 14:59:59 +1000
Message-ID: <3752174F.F629702D@addease.com.au>

Violin,

You can code a loop to fetch the rows from your 'cursor_name' one by one and insert them into a table (temp_table), then open the emp_cur refcursor on a select from temp_table. You might be able to do the same thing by poking them into a PL/SQL table and opening your refcursor on the PL/SQL table, but I haven't tried that.

You can add a column to hold the process id to handle multiple users referencing the same temp_table simultaneously. (The temp_table is a normal oracle table, we use a standard surrogate key domain so we can usually get away with only the one temp_table).

Here is an example:

CREATE OR REPLACE
 PROCEDURE SP_DIARY_LIST

(  AWHERE  IN      VARCHAR2,
  OUTREC           IN OUT  AXIOM_DIARY_PKG.TRC_DIARY_LIST)  AS

 v_CursorID  INTEGER;
 v_SelectStmt VARCHAR2(4000);
 v_DiaryNoteID INTEGER;

 v_Dummy INTEGER;
 v_SessionID VARCHAR2(12) := dbms_session.unique_session_id();

begin

--
-- LIST DIARY NOTES FOR THE SPECIFIED USER. IF USERID IS NULL THEN CURRENT USER.
--

 v_CursorID := DBMS_SQL.OPEN_CURSOR;
 v_SelectStmt := 'select tdiaryNote.DIARYNOTEID FROM TDIARYNOTE ' || replace(aWhere,'''''','''');

 DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);  DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_DiaryNoteID); --*** You need to look at this!!!
 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_DiaryNoteID);

  INSERT INTO TEMP_TABLE (sessionID, num_val)   VALUES (v_SessionID, v_DiaryNoteID);

 END LOOP; DBMS_SQL.CLOSE_CURSOR(v_CursorID);

• Return the results OPEN OUTREC FOR SELECT TDIARYNOTE.DIARYNOTEID, ACCOUNTMANAGER, PERSONCODE, COMPANYCODE, ORDERNUMBER, PRIORITY, SUBJECT FROM TDIARYNOTE, TEMP_TABLE WHERE TEMP_TABLE.sessionID = v_SessionID AND tDiaryNote.diaryNoteID = TEMP_TABLE.num_val ;

 DELETE FROM TEMP_TABLE
 WHERE TEMP_TABLE.sessionID = v_SessionID; END SP_DIARY_LIST ; Hope this helps, and if anyone has a better way please let me know!

Regards, David.

Violin wrote:

> Hello,
> If I create a procedure for SELECT statement in static SQL.
> Like this:
> create or replace procedure emp_list(
> dept in number,
> emp_cur in out emp_pkg.empcurtyp)
> is
> begin
> open emp_cur for
> select empno,ename,job from emp where deptno = dept;
> end;
> /
> create or replace package emp_pkg as
> type emptyp is record
> (empno number,
> ename varchar2(10),
> job varchar2(9));
> type empcurtyp is ref cursor return emptyp;
> end;
> /

>

> When executing emp_list,it works fine.

>

> If the SELECT must be a dynamic SQL .
> Like this:
> cursor_name := dbms_sql.open_cursor;
> dbms_sql.parse(cursor_name,
> 'select empno,ename,job from emp where deptno = :x',dbms_sql.native);
> dbms_sql.bind_variable(cursor_name, ':x', dept);
> rows_processed := dbms_sql.execute(cursor_name);
> dbms_sql.close_cursor;

>

> How to recreate the procedure?
> I try this with compile errors:
> begin
> open emp_cur for
> cursor_name := dbms_sql.open_cursor;
> dbms_sql.parse(cursor_name,
> 'select empno,ename,job from emp where deptno = :x',dbms_sql.native);
> dbms_sql.bind_variable(cursor_name, ':x', dept);
> rows_processed := dbms_sql.execute(cursor_name);
> dbms_sql.close_cursor;
> exception
> when others then
> dbms_sql.close_cursor(cursor_name);
> end;
> /

>

> LINE/COL ERROR
> -------- ----------------------------------------------------------------
> 9/6 PLS-00103: Encountered the symbol "CURSOR_NAME" when expecting
> one of the following:
> select

>

> How to create the procedure with dynamic SELECT and return the result?
> Thank you for any tips.

>

> Violin.
> violin.hsiao_at_mail.pouchen.com.tw
Received on Sun May 30 1999 - 23:59:59 CDT