Dynamic SQL - using cursors

From: <pkuditip_at_itds.com>
Date: Thu, 03 Sep 1998 15:15:16 GMT
Message-ID: <6smbq4$jte$1@nnrp1.dejanews.com>

I am porting some code from Ingres to Oracle

---

In Oracle:
I need to be able to call prepare, open, declare, and fetch in different functions.

I also need to be able to use variable names for cursor name and statement name in the each of the PREPARE, OPEN, DECLARE, FETCH CLOSE statements.

The Ingres code right now takes cursor names and statement names as parameters to functions prepare(), declare(), open() and fetch() and close()

For example: This works in Ingres:
 prepare(char *stmt_name, char* cursor_name, IISQLDA **p_sqlda)
{

EXEC SQL BEGIN DECLARE SECTION
char s_name[20];
char c_name[20];
EXEC SQL END DECLARE SECTION

strcpy (s_name, stmt_name);
strcpy (c_name, cursor_name);
strcpy (sql ,"SELECT .....");

EXEC SQL PREPARE :s_name FROM :sql;
EXEC SQL DESCRIBE :s_name INTO :*p_sqlda

}

open(char * cursor_name)
{

EXEC SQL BEGIN DECLARE SECTION
char c_name[20];
EXEC SQL END DECLARE SECTION strcpy (c_name, cursor_name);

EXEC SQL OPEN :c_name FOR READONLY;

}

PROBLEM IS IN ORACLE I HAVE TO HARD CODE THE CURSOR NAME AND STATEMENT NAME. I have tried to use SQL_CURSOR strCursorName and do a

EXEC SQL EXECUTE
  BEGIN
    OPEN :strCursorName FOR :sqlstmt;
  END;
END-EXEC; But I can't use :sqlstmt (the sql statement should be harde coded. I can't use REF_CURSORS either because of the same reason - since I can't use dynamic SQL then .
I have followed some threads on comp.databases.oracle and saw that I can use DBMS_SQL but I was't clear about a couple of things

1. Can I open cursor in one procedure, leave it go back to Pro C program and then later come back and do a fetch in another procedure using the previously opened cursor in the first procedure.
2. Can I do EXEC SQL DESCRIBE SELECT LIST FOR sqlstmt INTO select_dp;

My code won't know until run-time what tables and what columns in those tables I will need to access and as to how many such table(s) I will be accessing - So I don't know how many cursors ahead of time either.

Please help.
Padma
e-mail: pkuditip_at_itds.com

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Thu Sep 03 1998 - 10:15:16 CDT