dynamic sql errors in procedure

Question

Why doesn't my dynamic sql procedure work? All I'm trying to do is show a simple column from a table. I get the following erros:

ERROR at line 1: ORA-06550: line 1, column 7: PLS-00905: object SYSTEM.JOIN is invalid ORA-06550: line 1, column 7: PL/SQL: Statement ignored

 create or replace procedure join
(p_table1 in varchar2,
p_joincolumn1 in varchar2,
p_joincolumn2 in varchar2)
 AS
lv_query varchar2(500);
lv_cursor number;
lv_col1 VARCHAR2(30);
lv_col2 VARCHAR2(30);
loopcount number:=0;
cursor_return_value integer;
 begin
lv_cursor:=dbms_sql.open_cursor;
dbms_output.put_line('Value assigned to cursor= ' || lv_cursor );
lv_query:='select p_joincolumn1 from p_table1';
dbms_output.put_line(lv_query);
dbms_sql.parse(lv_cursor, lv_query, dbms_sql.native);
dbms_sql.define_column (lv_cursor, 1, lv_col1);
dbms_sql.define_column (lv_cursor, 2, lv_col2);
cursor_return_value := dbms_sql.execute (lv_cursor);
LOOP
    loopcount:=loopcount+1;
    dbms_output.put_line('loopcount= ' || loopcount );
    if dbms_sql.fetch_rows (lv_cursor)=0 then
        EXIT;
    end if;

    dbms_sql.column_value(lv_cursor, 1, lv_col1);
    dbms_sql.column_value (lv_cursor, 2, lv_col2);
    dbms_output.put_line(lv_col1 || '   ' || lv_col2 ); 
END LOOP;
 dbms_output.put_line('At end of loop');
 dbms_sql.close_cursor(lv_cursor);
 end;
 /

 execute join ('PROJECT', 'PROJECT.P_ID', 'PROJECT.SKILL_ID');

Errors:

 Procedure created.

 SQL> 
 SQL> execute testing_j ('PROJECT', 'PROJECT.P_ID', 'PROJECT.SKILL_ID');
 Value assigned to cursor= 20
 select p_joincolumn1 from p_table1
 BEGIN testing_j ('PROJECT', 'PROJECT.P_ID', 'PROJECT.SKILL_ID'); END;

 *
 ERROR at line 1:
 ORA-00942: table or view does not exist
 ORA-06512: at "SYS.DBMS_SYS_SQL", line 906
 ORA-06512: at "SYS.DBMS_SQL", line 39
 ORA-06512: at "SYSTEM.TESTING_J", line 17

Try to run it without the execute join ('PROJECT', 'PROJECT.P_ID'); and then run show errors. BTW, is this- lv_col2 VARCHAR2(30;) a typo? if not this might be your problem - should be lv_col2 VARCHAR2(30); — A.B.Cade
– A.B.Cade, Commented Nov 22, 2012 at 6:15

Community · Accepted Answer · 2017-05-23 10:24:43Z

1

The procedure was probably created with compilation errors.

Try not to run the two commands together-
1. create the procedure - if it was created with errors you can show them (in sqlplus) with the command show err (you can see how to view errors here)
2. execute the procedure

As to your code:
In the declaration part you have the line

lv_col2 VARCHAR2(30;)

which should be

lv_col2 VARCHAR2(30);

This may be the compilation error.

BTW, IMHO naming your procedure with a keyword (join) is bad practice

edited May 23, 2017 at 10:24

CommunityBot

11 silver badge

answered Nov 22, 2012 at 6:24

A.B.Cade

16.9k1 gold badge40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1084561 Over a year ago

I fixed the typo and the code compiled without errors. I edited my post to show the exact errors I received.

APC Over a year ago

@user1084561 - yes, but you didn't include the revised version of the code.

APC · Accepted Answer · 2012-11-23 05:49:43Z

Which bit of

ORA-00942: table or view does not exist

is confusing you? Either there is no table called P_TABLE1 in your database or there is no table called P_TABLE1 in the SYSTEM schema. Database object names are unique only within a schema: if we want to reference an object owned by a different user we need to prefix the object name with that schema:

select p_joincolumn1 from user23.p_table1

Also, that user needs to grant us the requisite privileges on their object...

... except for users such as SYS and SYSTEM, powerful accounts with the full panoply of ANY privileges. You should not be creating objects in the SYSTEM schema, as it is an integral part of the the Oracle system and monkeying with it is likely to corrupt your database.

user1084561 · Accepted Answer · 2012-11-23 17:12:09Z

Fixed my code, and it works now. Changed dbms_sql.native to dbms_sql.v7, and also changed some syntax.

create or replace procedure testing_j
(table_name in varchar2,
column1 in varchar2)
AS
lv_query varchar2(500);
lv_cursor number;
lv_col1 number(6);
loopcount number:=0;
cursor_return_value integer;
begin
lv_cursor:=dbms_sql.open_cursor;
dbms_output.put_line('Value assigned to cursor= ' || to_char(lv_cursor));
lv_query:='SELECT ' || column1 || ' FROM ' || table_name;
dbms_output.put_line(lv_query);
dbms_sql.parse(lv_cursor, lv_query, dbms_sql.v7);



dbms_sql.define_column (lv_cursor, 1, lv_col1);
cursor_return_value := dbms_sql.execute (lv_cursor);
LOOP
    loopcount:=loopcount+1;
    --dbms_output.put_line('loopcount= ' || loopcount );
    if dbms_sql.fetch_rows (lv_cursor)=0 then
        EXIT;
    end if;

    dbms_sql.column_value(lv_cursor, 1, lv_col1);
    dbms_output.put_line(lv_col1);  
END LOOP;
dbms_output.put_line('At end of loop');
dbms_sql.close_cursor(lv_cursor);
end;
/


PL/SQL procedure successfully completed.

SQL> execute testing_j ('PROJECT', 'PROJECT.P_ID');
Value assigned to cursor= 12
SELECT PROJECT.P_ID FROM PROJECT
1
1
2
3
3
4
4
5
5
6
7
7
At end of loop

PL/SQL procedure successfully completed.

Collectives™ on Stack Overflow

dynamic sql errors in procedure

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related