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

Hi Larry!
First of all:
If you're using DBMS_SQL to execute dynamic DDL commands you shouldn't use the dbms_sql.execute, because
whith the call of dbms_sql.parse the ddl-statements are parsed and immediately executed!
You may set an event in your session to find the statement (maybe an internal one), which causes the ORA-942: alter session set events '942 trace name errorstack forever level 10'; Next time, when the ora-942 will arrive, just check the tracefile, which is usually located in $ORACLE_HOME/rdbms/log

Tobias.

Larry Jones wrote:

> HELP!!
>
> 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 @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 @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 (@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 Fri Aug 01 1997 - 00:00:00 CDT