Oracle What savepoints are active for a given session?

HI PLEASE SEND ME ANSWER
Question Title: Oracle What savepoints are active for a given session?

Detailed Question: Does anybody know IF and HOW can I get a list of active savepoints for the current session? I need a native way, not solutions based on additional application-level housekeeping. Some query on the V$ tables/views would be the kind of answer I'm looking for. Details: Within one stored proc I'd like to obtain a list (in any form) of the savepoints issued currently in the current transaction. Example: Proc A issues savepoint svA; then proc B issues savepoint svB; and then proc C builds and uses a cursor having 'svA' and 'svB' as rows, or something like that. Of course, there are no intervening commits or rollbacks.

• Original Message ----- To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Tuesday, May 01, 2001 1:10 PM

> Don't know about the Java code...
> ora- 1008 is normally encountered because of undefined global variable or
> missing grants on the concerned table..
> Please check from your end...
>
> Thanks,
> Amar Kumar Padhi.
>
> -----Original Message-----
> Sent: Monday, April 30, 2001 2:41 PM
> To: Multiple recipients of list ORACLE-L
>
>
> We are having a table TestSite with the following fields.
>
> Name Null? Type
> ----------------------------------------- -------- ------------
> PK_SITE_ID NOT NULL NUMBER
> FK_CATEGORY NOT NULL NUMBER
> TITLE CLOB
> URL NOT NULL
> VARCHAR2(4000)
> DESCRIPTION CLOB
> STATUS NOT NULL NUMBER
> PAGE_HITS NOT NULL
> NUMBER
> EDITOR_CHOICE VARCHAR2(10)
>
> One of our developers has written code for inserting the data into
> this
> table using
> PreparedStatement class in Java. But he is getting "ORA-01008 - Not All
> Variables
> Bound" when using the code.
> Earlier code was with normal Statement class in Java. It was
> working
> fine without any errors. Could any of you please let me know as to what
the
> problem
> could be?
>
> The java code is as follows:
>
> import java.io.*;
> import java.sql.*;
>
> import oracle.sql.CLOB;
> import oracle.jdbc.driver.OracleResultSet;
> import oracle.jdbc.driver.OracleCallableStatement;
>
> public class TestPreparedStatement
> {
>
> private static void setSiteData(int pk_site_id, int fk_category,
> String
> title, String url, String description, int siteStatus, int page_hits,
String
> editor_choice)
> {
> ResultSet resultSet = null;
> file://Statement stmt = null;
> PreparedStatement stmt = null;
> Connection con = null;
>
> oracle.sql.CLOB clobTitle = null;
> oracle.sql.CLOB clobDescription = null;
>
> String strSQL = "",strSelectSQL = "";
> int nTemp1=0;
> int nTemp2=0;
> int k1 = 0;
> int k2 = 0;
>
> try
> {
> con=getConnection();
> con.setAutoCommit(false);
>
> /*
> stmt = con.createStatement();
> strSQL = "INSERT INTO SITE VALUES (" +pk_site_id +",
> "+fk_category +",
> empty_clob(), '"+url+"', empty_clob(), "+siteStatus+", "+page_hits+",
> '"+editor_choice+"')";
> stmt.execute (strSQL);
> strSelectSQL = "SELECT * FROM TESTSITE where
> pk_site_id=" + pk_site_id;
> ResultSet rset = stmt.executeQuery(strSelectSQL);
> */
>
> strSQL = "INSERT INTO TESTSITE VALUES (?, ?,
> empty_clob(),
> ?,empty_clob(), ?, ?, ?)";
> stmt = con.prepareStatement(strSQL);
> stmt.setInt(1,pk_site_id);
> stmt.setInt(2,fk_category);
> stmt.setString(3,url);
> stmt.setInt(4,siteStatus);
> stmt.setInt(5,page_hits);
> stmt.setString(6,editor_choice);
>
> stmt.executeUpdate(strSQL);
>
> strSelectSQL = "SELECT * FROM TESTSITE where
> pk_site_id = ?";
> stmt = con.prepareStatement(strSQL);
> stmt.setInt(1,pk_site_id);
> ResultSet rset = stmt.executeQuery(strSelectSQL);
>
> while(rset.next())
> {
> clobTitle =
> ((OracleResultSet)rset).getCLOB(3);
> clobDescription =
> ((OracleResultSet)rset).getCLOB(5);
> break;
> }
>
> // if the size of the title and description is
> greater than 60,000
> characters,
> // then copy the contents into a file and update the
> database after
> reading the
> // the contents from the file. Else update the
> database directly from the
> string.
> int nTitle = title.length();
> int nDescription = description.length();
>
> java.io.Writer titleWriter, descriptionWriter;
>
> if(nTitle > 60000)
> {
> File titleFile = new
> File("Site"+pk_site_id+".txt");
> // copy the contents of the of the string
> into the file
> BufferedWriter brWriterTitle = new
> BufferedWriter(new
> FileWriter(titleFile));
> brWriterTitle.write(title,0,nTitle);
> brWriterTitle.flush();
> brWriterTitle.close();
> FileInputStream instreamTitle = new
> FileInputStream(titleFile);
> titleWriter =
> ((CLOB)clobTitle).getCharacterOutputStream();
> int size = clobTitle.getBufferSize();
>
> BufferedReader br = new BufferedReader(new
> InputStreamReader(instreamTitle));
>
> while(true)
> {
> nTemp1=0;
> k1=0;
> char cData[] = new char[size];
> for(;nTemp1<size; nTemp1+=k1)
> {
> k1 =
> br.read(cData,nTemp1,size-nTemp1);
> if(k1==-1) break;
> }
> titleWriter.write(cData,0,size);
> if(k1==-1) break;
> }
>
> instreamTitle.close();
> titleWriter.flush();
> titleWriter.close();
>
> // delete the created file.
> titleFile.delete();
> }
> else
> {
> StringBuffer strBuff = new
> StringBuffer(title);
> titleWriter =
> ((CLOB)clobTitle).getCharacterOutputStream();
> titleWriter.write(strBuff.toString());
> titleWriter.flush();
> titleWriter.close();
> }
>
>
> if(nDescription > 60000)
> {
> File descriptionFile = new
> File("Site"+pk_site_id+".txt");
> // copy the contents of the of the string
> into the file
> BufferedWriter brWriterDescription = new
> BufferedWriter(new
> FileWriter(descriptionFile));
>
> brWriterDescription.write(description,0,nDescription);
> brWriterDescription.flush();
> brWriterDescription.close();
> FileInputStream instreamDescription = new
> FileInputStream(descriptionFile);
> descriptionWriter =
> ((CLOB)clobDescription).getCharacterOutputStream();
> int size = clobDescription.getBufferSize();
>
> BufferedReader br = new BufferedReader(new
> InputStreamReader(instreamDescription));
>
>
> while(true)
> {
> nTemp2=0;
> k2=0;
> char cData[] = new char[size];
> for(;nTemp2<size; nTemp2+=k2)
> {
> k2 =
> br.read(cData,nTemp2,size-nTemp2);
> if(k2==-1) break;
> }
>
> descriptionWriter.write(cData,0,size);
> if(k2==-1) break;
> }
>
> instreamDescription.close();
> descriptionWriter.flush();
> descriptionWriter.close();
>
> // delete the created file.
> descriptionFile.delete();
> }
> else{
> StringBuffer data = new
> StringBuffer(description);
> descriptionWriter =
> ((CLOB)clobDescription).getCharacterOutputStream();
> descriptionWriter.write(data.toString());
> descriptionWriter.flush();
> descriptionWriter.close();
> }
>
> if(con != null)
> con.commit();
>
> }
> catch(SQLException se)
> {
> System.out.println("method=setSiteData() FOCUS: "
> + se.toString());
> }
> catch(Exception e)
> {
> System.out.println("method=setSiteData() FOCUS: "
> + e.toString());
> }
> finally
> {
> try
> {
> stmt.close();
> if(con != null)
> {
> con.setAutoCommit(true);
> con.close();
> con=null;
> }
> }
> catch(Exception e)
> {
> System.out.println("method=setSiteData()
> FOCUS: " + e.toString());
> }
> }
> }
>
> public static void main(String args[])
> {
> try
> {
> int pk_site_id = 0;
> int fk_category =1;
> String title = "Sample Title";
> String url = "Sample URL";
> String description = "Sample Description";
> int siteStatus = 0;
> int page_hits = 0;
> String editor_choice = "Y";
> setSiteData(pk_site_id, fk_category, title, url,
> description, siteStatus,
> page_hits, editor_choice);
> }
> catch(Exception e)
> {
> System.out.println("Exception in Main : " +
> e.toString());
> }
> }
>
> private static Connection getConnection() throws Exception
> {
> try
> {
> Class.forName("oracle.jdbc.driver.OracleDriver");
> String dburl =
> "jdbc:oracle:thin:@172.19.24.123:1521:DMOZ";
> Connection dbcon =
> DriverManager.getConnection(dburl,"system","manager");
> return dbcon;
> }
> catch(SQLException nExp)
> {
> throw new Exception(nExp.toString());
> }
> }
> }
>
> Any help in this regard will be greatly appreciated.
>
> TIA and Regards,
>
> Ranganath
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ranganath K
> INET: ranganathk_at_subexgroup.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Amar Kumar Padhi
> INET: TS2017_at_emirates.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Venkata Apparao N
  INET: nvapparao_at_sol.net.in

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).