CLOB & javascript - OO4O and Wizards(Archived)

hi
I am having some difficulty reading a CLOB value.
here is the javascript code **********************************************
// sql statement calls function that returns clob
var strSql = "select somepck.funcreturningclob clobval from dual" ;
// get db conn from pool
var objOraDb = gobjOraSession.getDatabaseFromPool(1000);
// create dynaset
var objOraDynaset = objOraDb.creatDynaset(strSql, 0);
// create clob
var objClob = objOraDynaset.Fields("clobval").Value;
// declare buffer variable and read in data
var sbuffer;
var amountread = objClob.Read(sbuffer);
Response.Write(sbuffer);
**********************************************************
i checked the amountread and in says that data was read but
the response object never contains anything ?
any help appreciated
chow

Related

Retrieving CLOB data type

Hi,
I want to retrieve CLOB data type using oci functions in php. Iam posting the sample i tried.
<?php
$sql = "SELECT XDBUriType('/x/y/z/xyz.xml').getXML).getClobVal() from dual";
$objDBCommon = OCINLogon("username", "password", "domain");
$statement = OCIParse($objDBCommon, $sql);
$success = OCIExecute($statement);
$arrResultSet = array();
$values = array();
while($moredata = #OCIFetchInto($statement, $values, OCI_ASSOC+OCI_RETURN_NULLS)) {
$arrResultSet[] = $values;
}
OCIFreeStatement($statement);
OCILogoff($objDBCommon);
echo var_dump($arrResultSet);
?>
please help me out 
http://www.oracle.com/technology/tech/opensource/php/php_troubleshooting_faq.html#uplobs
is an example that uploads and then queries a CLOB.
-- CJ 
For the record, the URL is now http://www.oracle.com/technology/tech/php/htdocs/php_troubleshooting_faq.html#uplobs

Using ODBC from eScript to store a BLOB in the database

Hi All,
I have been trying for a while to use ODBC in escript (Siebel 7.8) to insert a blob (JPG-image) into the database.
When I try to write anything but a string into the BLOB field using AppendChunk I get the error Message:
"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."
Any Ideas?
Thanks
Con
This is my escript code:
function InsLobDirect(Inputs, Outputs)
{
var sMethod = "GetGlobalVars";
var sObjectId = "";
var iStep = 0;
var objRec;
var StrConnect;
var o
var fp;
var buff;
//---- CursorTypeEnum Values ----
var adOpenForwardOnly = 0;
var adOpenKeyset = 1;
var adOpenDynamic = 2;
var adOpenStatic = 3;
//---- LockTypeEnum Values ----
var adLockReadOnly = 1;
var adLockPessimistic = 2;
var adLockOptimistic = 3;
var adLockBatchOptimistic = 4;
//---- CursorLocationEnum Values ----
var adUseServer = 2;
var adUseClient = 3;
try
{
var sErrorId = "";
var outputs = TheApplication().NewPropertySet();
o = COMCreateObject("ADODB.connection");
o.Open("DB","test","test");
o.beginTrans();
o.execute("INSERT INTO demo (id, theBlob) " + " VALUES (1, empty_blob())");
objRec = COMCreateObject("ADODB.Recordset");
objRec.Open ("SELECT theblob FROM demo WHERE id = 1", o, adOpenStatic, adLockOptimistic);
var filename = "D:\\test.jpg";
buff = new Buffer(100);
var bytesRead = 0;
fp = Clib.fopen(filename, "r");
if ( fp == null ) {
TheApplication().RaiseErrorText("Error opening file for reading: " + filename)
}
else {
bytesRead = Clib.fread(buff, 20, fp);
}
if (fp != null && fp != "undefined") {
Clib.fclose(fp);
fp = null;
}
// this works fine
objRec.fields("theBlob").appendChunk("BLA BLA BLA");
// this causes an error "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."
// objRec.fields("theBlob").appendChunk(buff.data);
objRec.update();
objRec.close();
o.commitTrans();
} //try
catch(e) {
o.rollbackTrans();
logWarning (e, Inputs, Outputs, sObjectId, sMethod, iStep);
TheApplication().RaiseErrorText(e);
}
finally {
objRec = null;
o = null;
buff = null;
fp = null;
}
}
How about that, we're trying to do the exact same thing in SiebelVB in the exact same version of Siebel.
And we are erroring out at exactly the same place, getting pretty much the same error message like 'Unsupported data type.'
We took the exact same VB code, ran it as an Excel macro, and succeded in populating the BLOB.
Support will likely register a bug on this any minute now, meanwhile we are looking at external means to get our PDFs into the database.
Bill. 
Hi Bill,
thanks for the info.
If it's of any help to you I have a "workaround".
As I mentioned in my original post, loading text is not a problem so what I did is the following:
* Define an integration object for the attachment
* query for the attachment I need
* convert to XML using "EAI Integration Object to XML Hierarchy Converter"
* extract the attachment contents using "SM.ListOfAccountAttachment.AccountAttachment.AccntFileBuffer.<value>"
This is not the binary contents but a Base64 encoded string representation of the attachment contents!
* write the string to the database as described in the original post, here it's better to use a CLOB instead of a BLOB
* once the encoded string is in the database it can be converted back to the original representation using the Oracle package "utl_encode"
Below is the function I use to decode.
Hope it helps.
Bye
Con
CREATE OR REPLACE FUNCTION DECODE_CLOB (
pSrcClob IN CLOB)
RETURN BLOB IS
/**
********************************************************************************
** Decode a base64 encoded BLOB stored as a CLOB String
**
** #param pSrcClob Base64 encoded BLOB
**
** #return Decoded BLOB
**
********************************************************************************
*/
vcBuffer VARCHAR2 (1000);
vnChunkSize BINARY_INTEGER := 1000;
vnPosition PLS_INTEGER := 1;
vnClobLen PLS_INTEGER;
vlDecodedBlob BLOB := EMPTY_BLOB;
vlRawBuffer RAW(2000);
BEGIN
-- initalize the new blob
dbms_lob.createtemporary(vlDecodedBlob,TRUE);
-- get the length of the CLOB
vnClobLen := dbms_lob.getlength(pSrcClob);
-- decode the CLOB one chunk at a time and add it to the BLOB
WHILE vnPosition < vnClobLen
LOOP
-- get next chunk
dbms_lob.read(pSrcClob, vnChunkSize, vnPosition, vcBuffer);
IF vcBuffer IS NOT NULL THEN
-- decode and write it to the blob
vlRawBuffer := utl_encode.base64_decode(utl_raw.cast_to_raw(vcBuffer));
dbms_lob.writeappend(vlDecodedBlob, utl_raw.length(vlRawBuffer), vlRawBuffer);
END IF;
-- position on next chunk
vnPosition := vnPosition + vnChunkSize;
END LOOP;
-- return the BLOB
RETURN vlDecodedBlob;
EXCEPTION
-- pass any errors back to the calling process
WHEN OTHERS THEN
RAISE;
END;
Edited by: conruane on Feb 20, 2009 2:33 PM

ODP.Net 720 , OracleDatareader does not return Resultset

Hi All,
This is a very basic Scenario
I am trying to return a Result set with % in my Parameter
My Stored procedure
CREATE OR REPLACE PROCEDURE
TESTSEARCH
(
P_PatientForename IN VARCHAR2 ,
P_PatientSurname IN VARCHAR2 ,
p_cv OUT sys_refcursor
)
AS
v_PatientForename VARCHAR2(1000);
v_PatientSurname VARCHAR2(1000);
BEGIN
v_PatientForename := P_PatientForename || '%';
v_PatientSurname := P_PatientSurname || '%';
OPEN p_CV for
SELECT OID,FORENAME FROM PATIENT WHERE SURNAME LIKE v_PatientSurname AND FORENAME LIKE v_PatientForename ;
END;
My ODP.net Code
OracleConnection objConn = new OracleConnection();
objConn.ConnectionString = "connectionsrting";
//Setting the command type and Command Definition
OracleCommand objCommand = new OracleCommand();
objCommand.CommandType = CommandType.StoredProcedure;
objCommand.CommandText = "TESTSEARCH";
objCommand.Parameters.Add("p_cv", OracleDbType.RefCursor);
objCommand.Parameters["p_cv"].Direction = ParameterDirection.Output;
//Adding parameters to the command object
objCommand.Parameters.Add("P_PatientForename", OracleDbType.Varchar2);
objCommand.Parameters.Add("P_PatientSurname", OracleDbType.Varchar2,1000);
objCommand.Parameters["P_PatientForename"].Value = "ra%";
//objCommand.Parameters["P_PatientSurname"].Size = 100;
objCommand.Parameters["P_PatientSurname"].Value ="su%";
OracleDataReader objdr = null;
objCommand.Connection = objConn;
objCommand.BindByName = true;
objConn.Open();
long lnResult = 0;
try
{
//objCommand.AddToStatementCache = false;
objdr = objCommand.ExecuteReader(CommandBehavior.CloseConnection);
using (objdr)
{
while (objdr.Read())
{
//string s = "";
MessageBox.Show(objdr[1].ToString());
}
}
}
// The Sp Exceution return ResultSet but the Datareader does not.
Any Ideas, I am going crazy of this simple issue.
Thanks
HB. 
that should work the same (with or without % in param values)
I know that you have objCommand.BindByName = true;[pre]
but give it a try with putting the params in the order that are being expected in the proc.
I've  had issues with BindByName
//Adding parameters to the command object
objCommand.Parameters.Add("P_PatientForename", OracleDbType.Varchar2);
objCommand.Parameters.Add("P_PatientSurname", OracleDbType.Varchar2,1000);
objCommand.Parameters["P_PatientForename"].Value = "ra%";
//objCommand.Parameters["P_PatientSurname"].Size = 100;
objCommand.Parameters["P_PatientSurname"].Value ="su%";
objCommand.Parameters.Add("p_cv", OracleDbType.RefCursor);
objCommand.Parameters["p_cv"].Direction = ParameterDirection.Output;                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
Thanks Tanging
I had the Fix in this case for performing a wild char search
The NLS Setting for Login Trigger had to be set as Linguistic
SQL Plus had this setting and hence the Stored Procedure exec statement was returning the resultset.
I also had to change my stored Procedure Logic , I had posted a short logic only
The Original Stored Procedure had insertion into a GTT
The GTT was set to Preserve Rows as i referred the GTT in another child Stored Procedure called by Parent SP.
ODP.Net didnt recognise the Inserts into GTT.So i wrote all the Logic of Child in the Parent SP itself.
We are converting SQL oriented SP's to Oracle . and any advise on the GTT's in child Procedure would be greatly appreciated.

ExecuteScalar and Stored Function

Hi,
I am trying to execute a simple function through ExecuteScalar, but keep on getting an error that this is not a StoredProcedure.
What am I missing. I am not passing any parameters and also not setting any "RETURN_VALUE" param.
CREATE OR REPLACE FUNCTION GetEmpSal()
return number as
aSal number;
begin
select sal into aSal from devemp where empno=pEmpNo;
return aSal;
end;
Hi,
I am trying to execute a simple function through
ExecuteScalar, but keep on getting an error that this
is not a StoredProcedure.
What am I missing. I am not passing any parameters
and also not setting any "RETURN_VALUE" param.ExecuteScalar is for returning resultsets and throwing away everying but the first row, first column. It's just shorthand for.
OracleDataReader r = cmd.ExecuteScalar();
if (r.Read())
return r[0]
It's a throughly silly and lazy function.
You want to use ExecuteNonQuery and set a RETURN_VALUE paramter. If you have any problems just wrap the function call in a block like
begin :c := GetEmpSal(); end;
And bind an output parameter.
David 
Thanks David, I found another way to execute Stored Function
                    OracleCommand cmd = new OracleCommand("getsal",con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    OracleParameter retParam = new OracleParameter();
                    retParam.Direction = ParameterDirection.ReturnValue;
                    retParam.ParameterName = "getsal";
                    //retParam.OracleDbType = OracleDbType.Int64;
                    retParam.OracleDbType = OracleDbType.Decimal;
                    //retParam.Precision = 38;
                    //retParam.Size = 38;
                    retParam.Value = DBNull.Value;
                    cmd.Parameters.Add(retParam);
                    object aRetObj = cmd.ExecuteScalar();
                    string x = cmd.Parameters[0].Value.ToString();
The important part to be noted is "ParameterDirection.ReturnValue". Upon function return topmost parameter will have the return value.

clob value includes "\n" characters. How to avoid this?

Hi ,
I have one query. I am storing one xml file into the database column as CLOB. And trying to retrieve and saving on to the hard disk through the C# code as follows.
oraConn = new OracleConnection(strConnectionString);
// Create Anonymous PL/SQL block string
String strSQLBlock = " BEGIN " +
" SELECT xml_data INTO :1 FROM xyz WHERE id = " + Id.ToString() + ";" +
" END; ";
// Set command to create Anonymous PL/SQL Block
oraCmd = new OracleCommand();
oraCmd.CommandText = strSQLBlock;
oraCmd.Connection = oraConn;
// Since executing an anonymous PL/SQL block, setting the command type
// as Text instead of StoredProcedure
oraCmd.CommandType = CommandType.Text;
// Setting Oracle parameters
// Bind the parameter as OracleDbType.Clob to command for retrieving the image
OracleParameter oraParam = oraCmd.Parameters.Add("Clob_XML", OracleDbType.Clob);
oraParam.Direction = ParameterDirection.Output;
try
{
oraConn.Open();
oraCmd.ExecuteNonQuery();
// fetch the value of Oracle parameter into the byte array
OracleClob oraclbXMLFile = null;
oraclbXMLFile = (OracleClob) oraCmd.Parameters[0].Value;
// Save the retrieved image to the DestinationLoc in the file system
// Create a byte array
byte[] byteData = new byte[Convert.ToInt64(oraclbXMLFile.Length)];
oraclbXMLFile.Read(byteData,0,byteData.Length);
// Write the Blob data fetched from database to the filesystem at the
// destination location
FileStream fstrmXML = new FileStream(strXMLFilePath,FileMode.OpenOrCreate, FileAccess.Write);
fstrmXML.Write(byteData, 0,byteData.Length);
fstrmXML.Flush();
fstrmXML.Close();
When I open the xml file it is displaying “\n” character in between the xml elements. Please help me some way to avoid this.
Because of this I am not able to open the xml file. It is giving root element is missing exception.
Thanks in advance…Regards
Anjan

Categories

Resources