HELP - PL/SQL using DBMS_SQL in Store Procedures doesn't work

From: Larry Jones <lljo_at_chevron.com>
Date: 1997/07/28
Message-ID: <33DD12D9.2B49_at_chevron.com>

HELP!! [Quoted] Several of you tried to help me with an earlier problem, where I had a huge SQL statement in
which I was trying to return rows, as columns.

After beating my head against that wall for a while, I took the suggestions of many of you,
and started using a temporary table. I wrote the SQL, and it worked great. But then when I
put this into a stored procedure, it bombs!!!! The exact same code!

Can anyone look at this, and tell me what in the world is going on?

I have included a simplified version of my procedure, which still produces the same error. Sorry
for all the code, but no one around here can help, and I want to make sure you have enough
information to work with.

Here is the code, in the format that works, if I store it in a file on my PC as TEMP.SQL, then
execute it from SQL*PLUS by typing _at_TEMP.SQL:
================================ Start of Code

declare

	csr			INTEGER;
	errmsg		VARCHAR2(100);
	errnum		NUMBER := 0;
	known_error	EXCEPTION;
	process_yr	INTEGER;
	rc			INTEGER;
	sqldrop		VARCHAR2(65);
	sqlstmt		VARCHAR2(4500);
	tbl_name	VARCHAR2(50);

BEGIN process_yr := 1996;

tbl_name := USER || '.' || USER || '_TEMP_WITS';

sqldrop := 'DROP TABLE ' || tbl_name;

	sqlstmt := 'CREATE TABLE ' || tbl_name || ' AS ' ||
			   '	SELECT w_wellwork_common.* ' ||
			   '	  FROM w_wellwork_common, ' ||
			   '		   w_selected_wellwork ' ||
			   '	 WHERE ( w_selected_wellwork.wellwork_number =
w_wellwork_common.wellwork_number ) ' ||
			   '	   AND ( w_selected_wellwork.unique_id =
w_wellwork_common.unique_id ) ' ||
			   '	   AND ( w_selected_wellwork.userid = ''' || USER || ''' )';

	csr := DBMS_SQL.OPEN_CURSOR;	-- get the cursor id for a new cursor

/* drop the "temporary" table, if it exists */
	BEGIN
		DBMS_SQL.PARSE(csr, sqldrop, DBMS_SQL.V7);	-- parse the SQL statement
based on V7 syntax
		rc := DBMS_SQL.EXECUTE(csr);							-- execute the SQL statement for

the cursor

	EXCEPTION
		WHEN OTHERS THEN
			IF SQLCODE = -942 THEN	-- "table or view does not exist", ignore it -
nothing to drop
				NULL;
			ELSE
				errnum := -20001;
				errmsg := 'Drop Error: ' || SQLERRM(SQLCODE);
			END IF;
	END;

/* if no errors, create the temporary table */
	IF errnum = 0 AND SQLCODE = 0 THEN
		BEGIN
			DBMS_SQL.PARSE(csr, sqlstmt, DBMS_SQL.V7);
			rc := DBMS_SQL.EXECUTE(csr);

		EXCEPTION
			WHEN OTHERS THEN
				errnum := -20002;
				errmsg := 'Create Error: ' || SQLERRM(SQLCODE);
		END;
	END IF;

	DBMS_SQL.CLOSE_CURSOR(csr);	-- close the cursor

	IF errnum <> 0 THEN
		RAISE known_error;
	END IF;

EXCEPTION
	WHEN known_error THEN
		RAISE_APPLICATION_ERROR(errnum,errmsg);
	WHEN OTHERS THEN
		RAISE_APPLICATION_ERROR(-20999,'Error: ' || SQLERRM(SQLCODE));

END;
================================ End of Code

Here is the code to create the stored procedure, which is exactly the same (except for one
assignment statement - since I pass the date to the procedure):
================================ Start of Code

CREATE OR REPLACE PROCEDURE Credibility_Temp2 (credibility_yr INTEGER) AS

	csr			INTEGER;
	errmsg		VARCHAR2(100);
	errnum		NUMBER := 0;
	known_error	EXCEPTION;
	process_yr	INTEGER;
	rc			INTEGER;
	sqldrop		VARCHAR2(65);
	sqlstmt		VARCHAR2(4500);
	tbl_name	VARCHAR2(50);

BEGIN process_yr := credibility_yr;

tbl_name := USER || '.' || USER || '_TEMP_WITS';

sqldrop := 'DROP TABLE ' || tbl_name;

	sqlstmt := 'CREATE TABLE ' || tbl_name || ' AS ' ||
			   '	SELECT w_wellwork_common.* ' ||
			   '	  FROM w_wellwork_common, ' ||
			   '		   w_selected_wellwork ' ||
			   '	 WHERE ( w_selected_wellwork.wellwork_number =
w_wellwork_common.wellwork_number ) ' ||
			   '	   AND ( w_selected_wellwork.unique_id =
w_wellwork_common.unique_id ) ' ||
			   '	   AND ( w_selected_wellwork.userid = ''' || USER || ''' )';

	csr := DBMS_SQL.OPEN_CURSOR;	-- get the cursor id for a new cursor

/* drop the "temporary" table, if it exists */
	BEGIN
		DBMS_SQL.PARSE(csr, sqldrop, DBMS_SQL.V7);	-- parse the SQL statement
based on V7 syntax
		rc := DBMS_SQL.EXECUTE(csr);							-- execute the SQL statement for

the cursor

	EXCEPTION
		WHEN OTHERS THEN
			IF SQLCODE = -942 THEN	-- "table or view does not exist", ignore it -
nothing to drop
				NULL;
			ELSE
				errnum := -20001;
				errmsg := 'Drop Error: ' || SQLERRM(SQLCODE);
			END IF;
	END;

/* if no errors, create the temporary table */
	IF errnum = 0 AND SQLCODE = 0 THEN
		BEGIN
			DBMS_SQL.PARSE(csr, sqlstmt, DBMS_SQL.V7);
			rc := DBMS_SQL.EXECUTE(csr);

		EXCEPTION
			WHEN OTHERS THEN
				errnum := -20002;
				errmsg := 'Create Error: ' || SQLERRM(SQLCODE);
		END;
	END IF;

	DBMS_SQL.CLOSE_CURSOR(csr);	-- close the cursor

	IF errnum <> 0 THEN
		RAISE known_error;
	END IF;

EXCEPTION
	WHEN known_error THEN
		RAISE_APPLICATION_ERROR(errnum,errmsg);
	WHEN OTHERS THEN
		RAISE_APPLICATION_ERROR(-20999,'Error: ' || SQLERRM(SQLCODE));

END;
/
Show Errors
================================ End of Code

Now here is the code I use to test the stored procedure, it is called TEST_PROC.SQL and I
execute it from SQL*PLUS by typing _at_TEST_PROC.SQL:
================================ Start of Code

declare

process_yr INTEGER := 1996;

begin

Credibility_Temp2(process_yr);

end;
/
================================ End of Code

When I run the first bit of code (_at_TEMP.SQL) it creates the table as I need. When I run the
TEST_PROC procedure, I get the following error:
================================ Start of Error

declare
*
ERROR at line 1:

ORA-20002: Create Error: ORA-00942: table or view does not exist
ORA-06512: at "LLJO.CREDIBILITY_TEMP2", line 53
ORA-06512: at line 4


================================ End of Error


================================

You can see that the inital error number is 20002, and it is a "Create Error" which is the
error message I create in the step following the execution of the create statement.

BTW... the drop statement/process works with no problem.

I know this is a ton of stuff, but this has been driving me crazy for weeks now, and I want to
give you as much information as possible. Here is some more, on our software version:

	Windows 95 4.00.950a
	SQL*Plus: Release 3.3.2.0.2 - Production
	Oracle7 Server Release 7.3.2.3.0 - Production Release
	With the distributed and replication options
	PL/SQL Release 2.3.2.3.0 - Production

I GREATLY appreciate you taking a look at this, and ANY help at all in resolving this situation.

Larry Jones
Chevron Information Technology
Houston, TX
lljo_at_chevron.com Received on Mon Jul 28 1997 - 00:00:00 CEST

This message: [ Message body ]
Next message: Arnold Angel: "UTL_FILE with FOPEN, GET_LINE, and Wrong Number of Arguments?"
Previous message: Bjorn Lindstrom: "Passport or SAS?"
Next in thread: Steve Johnson: "Re: HELP - PL/SQL using DBMS_SQL in Store Procedures doesn't work"
Reply: Pavel Luzanov: "Re: HELP - PL/SQL using DBMS_SQL in Store Procedures doesn't work"
Reply: John Caputo: "Re: HELP - PL/SQL using DBMS_SQL in Store Procedures doesn't work"
Reply: Steve Johnson: "Re: HELP - PL/SQL using DBMS_SQL in Store Procedures doesn't work"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message