dynamic sql script wont print to screen

dynamic sql script wont print to screen

Post by Michael J Belgar » Sat, 13 Sep 1997 04:00:00



When I hardcode the value: 'GRIFFIN'  in VERSION A the results are
printed
to the screen.  However, when I use a bind variable in VERSION B
for this value and call the procedure with GRIFFIN as a parameter,
I receive no output.  Why is this happeneing?  I would appreciate any
help.

Thank you----

**************  VERSION A  ******************

SQL> grant select on bs.name to al_test;

Grant succeeded.

SQL> CREATE or REPLACE PROCEDURE get_name /* (last IN
name.last_name%TYPE) */ IS
  2  v_Cursor   NUMBER;
  3  v_NumRows  integer;
  4  firstname  name.first_name%TYPE;
  5  lastname   name.last_name%TYPE;
  6  v_stmt     VARCHAR2(100);
  7  
  7  BEGIN
  8     v_Cursor := DBMS_SQL.OPEN_CURSOR;
  9     v_stmt := 'SELECT LAST_NAME, FIRST_NAME
 10                             FROM NAME WHERE LAST_NAME = ''GRIFFIN''
';
 11     DBMS_SQL.PARSE (v_Cursor, v_stmt,
 12                             dbms_sql.v7);
 13   /*  DBMS_SQL.BIND_VARIABLE(v_Cursor, 'x', last); */
 14    dbms_sql.define_column (v_Cursor, 1, firstname,15);
 15    dbms_sql.define_column (v_Cursor, 2, lastname,15);
 16    v_NumRows := DBMS_SQL.EXECUTE (v_Cursor);
 17    
 17    loop
 18      if dbms_sql.fetch_rows (v_Cursor) > 0 then
 19         dbms_sql.column_value (v_Cursor, 1, firstname);
 20         dbms_sql.column_value (v_Cursor, 2, lastname);
 21         dbms_output.put_line(firstname || ' '|| lastname);
 22      else
 23        exit;
 24      end if;
 25    end loop;
 26  
 26    dbms_sql.close_cursor (v_Cursor);
 27  EXCEPTION
 28     WHEN OTHERS THEN
 29         dbms_sql.close_cursor (v_Cursor);
 30         raise;
 31  END;
 32  /

Procedure created.

SQL>      
SQL>
SQL> EXECUTE GET_NAME;
GRIFFIN
GRIFFIN         BARBARA
GRIFFIN         DAVE
GRIFFIN         DEREK
GRIFFIN         JASON
GRIFFIN         JOHN
GRIFFIN         JON
GRIFFIN         JOSEPH
GRIFFIN         LENYARD
GRIFFIN         LUKE
GRIFFIN         PEG
GRIFFIN         ROBERT
GRIFFIN         VALERIA

PL/SQL procedure successfully completed.

**************  VERSION B  ******************

SQL> grant select on bs.name to al_test;

Grant succeeded.

SQL> CREATE or REPLACE PROCEDURE get_name ( last IN
name.last_name%TYPE)  AS
  2  v_Cursor   NUMBER;
  3  v_NumRows  integer;
  4  firstname  name.first_name%TYPE;
  5  lastname   name.last_name%TYPE;
  6  v_stmt     VARCHAR2(100);
  7  
  7  BEGIN
  8     v_Cursor := DBMS_SQL.OPEN_CURSOR;
  9     v_stmt := 'SELECT LAST_NAME, FIRST_NAME
 10                             FROM NAME WHERE LAST_NAME = :x ';
 11     DBMS_SQL.PARSE (v_Cursor, v_stmt,
 12                             dbms_sql.v7);
 13    DBMS_SQL.BIND_VARIABLE(v_Cursor, ':x', last);
 14    dbms_sql.define_column (v_Cursor, 1, firstname,15);
 15    dbms_sql.define_column (v_Cursor, 2, lastname,15);
 16    v_NumRows := DBMS_SQL.EXECUTE (v_Cursor);
 17    
 17    loop
 18      if dbms_sql.fetch_rows (v_Cursor) > 0 then
 19         dbms_sql.column_value (v_Cursor, 1, firstname);
 20         dbms_sql.column_value (v_Cursor, 2, lastname);
 21         dbms_output.put_line(firstname || ' '|| lastname);
 22      else
 23        exit;
 24      end if;
 25    end loop;
 26  
 26    dbms_sql.close_cursor (v_Cursor);
 27  EXCEPTION
 28     WHEN OTHERS THEN
 29         dbms_sql.close_cursor (v_Cursor);
 30         raise;
 31  END;
 32  /

Procedure created.

SQL>  EXECUTE GET_NAME('GRIFFIN');

PL/SQL procedure successfully completed.

 
 
 

1. Help: Email script brings up print screen

Hi, People -

I'm fairly new to Filemaker 5.3 for the MAC.

When I write the script to send email (SendMail specifying "email," no
dialog), it pulls up the Print Screen.

Does anybody have any idea why?

I searched Claris' database but had no luck, and I don't see an email
address for tech support, so I guess they don't give me any.

Thanks for your help.

Dodge

___

Dodge Johnson
College Planning

___

2. SQL Svr 2000 quoted_identifier problem

3. Using the Print Screen Key to print to printer or to file

4. Data Administration - Anyone Interested?

5. How do i print to the screen in SQL plus

6. Assigning Access Report Label Caption at runtime

7. Wont print to Printer!!

8. Help with SQL over a VPN

9. Shared Printers wont print until killed

10. FoxPro 2.6a exe wont print logo

11. Replication from Scripts wont connect

12. Print command prints scripts not labels

13. Print & Print Setup script steps