Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: PL/SQL output/wrappers ...

Re: PL/SQL output/wrappers ...

From: mark tomlinson <marktoml_at_gdi.net>
Date: 1998/01/14
Message-ID: <34bd209c.168354541@newshost.us.oracle.com> 







On Wed, 14 Jan 1998 10:39:31 +0800, Trevor Phillips
<phillips_at_central.murdoch.edu.au> wrote:



Snippet ffrom a project of our own which does exactly that - this is
not likely going to be directly portable into your own project
'as-is', but you can see how to do it.





///////////////////////////////////////////////////////////////////////////////

//

// Code To Execute Stored Procedures

//





BOOL OraPls::ExecSP( const char* v_ProcedureName, CStringArray&
v_PValues,  char *sTempName, BOOL bWebStyle )
{


        static Cda_Def v_Cda;


	char *v_Param[50];
	CString t_BegStmt;
	CString t_MidStmt;
	CString t_EndStmt;
	CString t_SqlStmt;
	CString t_BindStmt;
	otext msgbuf[512];
	BOOL	bReturnCode = TRUE;
	HGLOBAL hMyMemHandle = NULL;

	// char
szDBMSOutputText[NUM_DBMS_OUTPUT_LINES][DBMS_OUTPUT_LINE_LEN];
	char *szDBMSOutputText;
	int	 nDBMSOutputNumLines;

	nDBMSOutputNumLines = NUM_DBMS_OUTPUT_LINES;

	t_BegStmt.Empty();
	t_BegStmt = "BEGIN DBMS_OUTPUT.ENABLE(2000); ";
    if( bWebStyle ) {
		t_EndStmt = " OWA_UTIL.SHOWPAGE;
DBMS_OUTPUT.GET_LINES( :lines, :numlines ); END; ";
	} else {
		t_EndStmt = " DBMS_OUTPUT.GET_LINES( :lines, :numlines
); END; ";
	}

	int nNumParams = v_PValues.GetSize();
	for( int j = 0; j < nNumParams; j++ )
	{
		v_Param[j] = new char[MAX_BIND_VAR_SIZE];
		strcpy((char*) v_Param[j], v_PValues.GetAt( j ) );
	}


//	strcpy( (char *) v_Param[nNumParams], "[END]");

	
	int ret;
	//

	t_MidStmt.Empty();
	t_MidStmt = v_ProcedureName;
	if( nNumParams>0 )
	{
		t_MidStmt += " ( ";
		for( j = 0; j < nNumParams; j++ )
		{
			t_BindStmt.Empty();
			t_BindStmt.Format( " :%d", j );
			if( j < (nNumParams-1) )
				t_BindStmt += ", ";
			else
				t_BindStmt += " ";
			t_MidStmt += t_BindStmt;
		} // [for]
 t_MidStmt += " )";
	} // [if]
	t_MidStmt += "; ";
	t_SqlStmt = t_BegStmt + t_MidStmt + t_EndStmt;


	if( oopen(&v_Cda, &lda, (unsigned char *) 0, 
		OCI_PARAM_NOT_USED, OCI_PARAM_NOT_USED, 
		(unsigned char *) 0, OCI_PARAM_NOT_USED))
	{
#ifdef _DEBUG
		AfxMessageBox("ERROR: Cannot open cursor");
#endif
		TRACE0("ERROR: Cannot open cursor\n");
		strcpy((char *)msgbuf, "Cannot open cursor\r\n");
	}

	if( oparse(&v_Cda, (unsigned char *)(LPCTSTR) t_SqlStmt,
OCI_PARAM_NOT_USED, 0, 2))
	{
		sword nchars = oerhms(&lda, v_Cda.rc, msgbuf, (int)
sizeof(msgbuf) );
		CString t;
		t.Format("ERROR: Cannot Parse Statment\nERROR: oparse


returned code: %d\n%s", v_Cda.rc, msgbuf);
#ifdef _DEBUG


                AfxMessageBox( (LPCTSTR) t );


#endif

		ProcessOCIError( v_Cda );
		bReturnCode = FALSE;

		TRACE0( (LPCTSTR) t );
		strcpy((char *) msgbuf, (LPCTSTR) t);

	}


	////////////////////////////////////////////////////////////
	//
	// Bind the Lines returned by DBMS_Output.Get_Lines(x,x)
	//
	//
	int i;
	ub2 lines_indp[NUM_DBMS_OUTPUT_LINES];

	ub2 lines_alen[NUM_DBMS_OUTPUT_LINES];
	for(i=0;i<NUM_DBMS_OUTPUT_LINES;i++)




lines_alen[i]=DBMS_OUTPUT_LINE_LEN;
        

	ub2 lines_rc[NUM_DBMS_OUTPUT_LINES];
	for(i=0;i<NUM_DBMS_OUTPUT_LINES;i++) lines_rc[i]=0;
	
	ub4 lines_cs = (ub4) NUM_DBMS_OUTPUT_LINES;
     hMyMemHandle = GlobalAllocPtr( GPTR,
sizeof(char)*NUM_DBMS_OUTPUT_LINES*DBMS_OUTPUT_LINE_LEN);
	 szDBMSOutputText = (char *)GlobalLock (hMyMemHandle);

	//	new char[NUM_DBMS_OUTPUT_LINES][DBMS_OUTPUT_LINE_LEN];

	ret = obndra(&v_Cda,
				(unsigned char *) ":lines",


// name of place holder

				OCI_PARAM_NOT_USED,


// -1 (length of string before)

				(unsigned char *) szDBMSOutputText,


// name of c data type

				DBMS_OUTPUT_LINE_LEN,


// length of data type

				STRING_TYPE,


// ext data type (5=NTS), page 3-7

				OCI_PARAM_NOT_USED,


// scale (used for packed decimal)

				(sb2 *) lines_indp,	(ub2 *)
lines_alen, (ub2 *) lines_rc,
				(ub4) NUM_DBMS_OUTPUT_LINES, 
				(ub4 *) &lines_cs, (unsigned char *)
0, 
				OCI_PARAM_NOT_USED, OCI_PARAM_NOT_USED
);
	if( ret != 0 )
	{
		sword nchars = oerhms(&lda, v_Cda.rc, msgbuf, (int)


sizeof(msgbuf) );


#ifdef _DEBUG

		CString t;
		t.Format("ERROR: Cannot bind :lines!\nERROR: obndrv
returned code: %d\n%s", v_Cda.rc, msgbuf);
		AfxMessageBox( (LPCTSTR) t );
#endif
		ProcessOCIError( v_Cda );
		bReturnCode = FALSE;
	}


	////////////////////////////////////////////////////////////
	//
	// Bind the NumLines returned by DBMS_Output.Get_Lines(x,x)
	//
	//
	ret = obndrv(&v_Cda,
				(unsigned char *) ":numlines",


// name of place holder

				OCI_PARAM_NOT_USED,


// -1 (length of string before)

				(unsigned char *)
&nDBMSOutputNumLines,	// name of c data type
				sizeof(nDBMSOutputNumLines),


// length of data type

				INT_TYPE,


// ext data type 

				OCI_PARAM_NOT_USED,


// scale (packed decimal only)

				(short *) 0,
				(unsigned char *) 0,
				OCI_PARAM_NOT_USED, OCI_PARAM_NOT_USED
);
	if( ret != 0 )
	{
		sword nchars = oerhms(&lda, v_Cda.rc, msgbuf, (int)
sizeof(msgbuf) );
		CString t;
		t.Format("ERROR: Cannot bind :lines!\nERROR: obndrv


returned code: %d\n%s", v_Cda.rc, msgbuf);
#ifdef _DEBUG


                AfxMessageBox( (LPCTSTR) t );


#endif

		ProcessOCIError( v_Cda );
		bReturnCode = FALSE;
	}

	////////////////////////////////////////////////////////////
	//
	// Bind the Enumerated Bind Variables to the internal SP
	//
	//
	for (int nSpCount=0; nSpCount < nNumParams; nSpCount++ )
	{
		t_BindStmt.Empty();
		t_BindStmt.Format( ":%d", nSpCount );

		ret = obndrv(&v_Cda,
				(unsigned char *) (LPCTSTR)
t_BindStmt,	
				OCI_PARAM_NOT_USED,

				(unsigned char *) v_Param[nSpCount],
				MAX_BIND_VAR_SIZE,

				STRING_TYPE,

				OCI_PARAM_NOT_USED,

				(short *) 0,
				(unsigned char *) 0,
				OCI_PARAM_NOT_USED, OCI_PARAM_NOT_USED
);
		if( ret != 0 )
		{
			sword nchars = oerhms(&lda, v_Cda.rc, msgbuf,
(int) sizeof(msgbuf) );
			CString t;
			t.Format("ERROR: Cannot bind param %d\nERROR:
obndrv returned code: %d\n%s", nSpCount, v_Cda.rc, msgbuf);
			#ifdef _DEBUG
			AfxMessageBox( (LPCTSTR) t );
			#endif
			TRACE0( (LPCTSTR) t );
			ProcessOCIError( v_Cda );
			bReturnCode = FALSE;
		}
 
	} // [for]






//////////////////////////////////////////////////////////////////////////////

	//
	// Execute the Anonymous PL/SQL Block containing the Stored
Procedure
	//
	//

	if( oexec(&v_Cda) )
	{
		CString t = "";
		sword nchars = oerhms(&lda, v_Cda.rc, msgbuf, (int)
sizeof(msgbuf) );
		t.Format("ERROR: oexec returned code: %d\n%s",
v_Cda.rc, msgbuf);
		AddCr((char *)&msgbuf[0]);
		ProcessOCIError( v_Cda );
		bReturnCode = FALSE;
	}

	//////////////////////////////////////////////////////////////
	//
	// This is where we show the output on a dialog.
	//
	//
	
	CSPOutputDialog odlg;
	CString sResults = "";
	CString sResults2 = "";

	if( bReturnCode == TRUE )
	{
		if( nDBMSOutputNumLines > 0 ) 
		{
			for(j=0; j < nDBMSOutputNumLines; j++)
			{
				sResults +=
szDBMSOutputText+(j*DBMS_OUTPUT_LINE_LEN);
				sResults += "\r\n";
			}
 sResults += "\r\n";
		}
		if (!bWebStyle) 
		{
			sResults += "PL/SQL procedure successfully
completed.\r\n\r\n";
		}
		CString t;
		for(j=0; j<nNumParams ; j++)
		{
			if( mode[j] > 0 ) {
				t.Format("%s = '%s'\r\n", argnm[j],
(char *)v_Param[j] );
				sResults2 += t;
			} // [if mode[j]>0]
		} // [for]
	} 
	else 
	{
		sResults += msgbuf;
		sResults += "\r\n\r\nPL/SQL procedure completed with
errors.";
	}


	if( bWebStyle )
	{
		char szTempPath[MAX_PATH+1];
		char szTempFileName[MAX_PATH+5];
		DWORD dwNumWritten;
		CString csTmpFn;

		::GetTempPath (sizeof(szTempPath)-1, szTempPath);
		::GetTempFileName ((LPCTSTR) szTempPath, (LPCTSTR)


"PLS", (UINT) 0, (LPTSTR) &szTempFileName[0]);
                

		csTmpFn = (LPCTSTR)szTempFileName;
		csTmpFn.MakeUpper();
		csTmpFn = csTmpFn.Left (csTmpFn.Find(".TMP"));
		csTmpFn += (LPCTSTR) ".TXT";

		::MoveFile((LPCTSTR)szTempFileName, (LPCTSTR)csTmpFn);
		strcpy(sTempName, (LPCTSTR)csTmpFn);
		
		HANDLE hFile = CreateFile(
							(LPCTSTR)
csTmpFn, 
							GENERIC_WRITE,

FILE_SHARE_READ,
							NULL,
							CREATE_ALWAYS,

FILE_ATTRIBUTE_TEMPORARY,
							NULL
						);
		BOOL bResult = WriteFile( 
							hFile,
							(LPCTSTR)




sResults,



sResults.GetLength(),

							&dwNumWritten,
							NULL
						);
		bResult = CloseHandle (hFile);
		::ShellExecute(NULL, NULL, (LPCTSTR)csTmpFn, NULL,


NULL, SW_SHOWNORMAL);



	}
	else
	{
		odlg.m_Output = sResults;
		odlg.m_Output2 = sResults2;
		odlg.DoModal();
	} // [if !bWebStyle]

	
	for( j=0; j < nNumParams; j++ )
	{
		delete [] v_Param[j];
	}
	GlobalUnlock (hMyMemHandle);
	GlobalFree (hMyMemHandle);
	return bReturnCode;





}





--mark


>Hi!
>
>Does anyone know a way to capture output from a PL/SQL procedure?? (Pref. a
>simple way).
>
>I'm writing something which will interface to PL/SQL written for the Oracle
>Web Server, and can't seem to figure out how to actually get what the PL/SQL
>procedure returns. Is there any easy way to do this?? I can't modify the
>procedures themselves, so I need PL/SQL code to act as a wrapper or similar...
>
>Also, are there any good PL/SQL discussion groups out there, or is this as
>good as it gets? ^_^
>
>Thanks...


Received on Wed Jan 14 1998 - 00:00:00 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US