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

Home -> Community -> Usenet -> c.d.o.server -> Need help with dynamic SQL

Need help with dynamic SQL

From: Marty Sarkar <SarkarMK_at_bv.com>
Date: 1998/12/17
Message-ID: <36798B75.F6F7C4A8@bv.com>#1/1

Howdy,

can somebody explain to me how do
DBMS_SQL.COLUMN_VALUE() and
DBMS_SQL.DEFINE_COLUMN()
work?

In the following stored procedure I'm trying to retrieve ONE column called dbname
and print its contents on the screen. For some reason, the DBMS_SQL.COLUMN_VALUE() command inside the loop is always being ignored and an exception (OTHERS) is raised with the following message: OTHERS EXCEPTION
-1007 ORA-01007: variable not in select list
I was using DBMS_SQL.COLUMN_VALUE(projName_cursor, 1, dbname) under the assumption that it'll put the values of the name column in the variable
dbname.

Any suggestions/comments?

thanks for the help
Marty
-------------------code for stored

procedure--------------------------------
          BEGIN
            projName_cursor := DBMS_SQL.OPEN_CURSOR;
            DBMS_SQL.PARSE(projName_cursor,
                           'select dbname from mtttstdproj where
dbtypind = :projType',
                           DBMS_SQL.V7);
            DBMS_SQL.BIND_VARIABLE(projName_cursor, ':projType', 'D');
    --        DBMS_SQL.DEFINE_COLUMN(projName_cursor, 1, dbname);
            ignore := DBMS_SQL.EXECUTE(projName_cursor);
       --     dbName_cursor := DBMS_SQL.OPEN_CURSOR;

            LOOP
              IF DBMS_SQL.FETCH_ROWS(projName_cursor) > 0 THEN
                DBMS_OUTPUT.PUT_LINE('Number of Rows > 0');
                DBMS_SQL.COLUMN_VALUE(projName_cursor, 1, dbname);
                DBMS_OUTPUT.PUT_LINE('DATABASE NAME:' || dbname);
              else
                DBMS_OUTPUT.PUT_LINE('Number of Rows < 0');
                EXIT;
              END IF;
            END LOOP;

            DBMS_SQL.CLOSE_CURSOR(projName_cursor);

          EXCEPTION
             when TOO_MANY_ROWS then
               if DBMS_SQL.IS_OPEN(projName_cursor) then
                 DBMS_SQL.CLOSE_CURSOR(projName_cursor);
               end if;
             when OTHERS then
               DBMS_OUTPUT.PUT_LINE('OTHERS EXCEPTION');
               error_num := SQLCODE;
               error_msg := SUBSTR(SQLERRM, 1, 100);
               DBMS_OUTPUT.PUT_LINE(error_num || ' ' || error_msg);
               if DBMS_SQL.IS_OPEN(projName_cursor) then
                 DBMS_SQL.CLOSE_CURSOR(projName_cursor);
               end if;
             ROLLBACK;
           --  RAISE;
           END;
Received on Thu Dec 17 1998 - 00:00:00 CST

Original text of this message

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