|
Re: DBMS_SQL.BIND_VARIABLE example [message #1688 is a reply to message #1686] |
Wed, 22 May 2002 15:05 |
|
Mahesh Rajendran
Messages: 10707 Registered: March 2002 Location: oracleDocoVille
|
Senior Member Account Moderator |
|
|
CREATE TABLE dept_new
(deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13));
CREATE OR REPLACE PROCEDURE dbms_example
(deptnum IN dept_new.deptno%TYPE,
deptname IN dept_new.dname%TYPE,
location IN dept_new.loc%TYPE) IS
stmt_str varchar2(100);
rows_processed NUMBER;
cur_hdl NUMBER;
BEGIN
stmt_str := 'INSERT INTO dept_new VALUES(:deptno, :dname, :loc)';
cur_hdl := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur_hdl,stmt_str,DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cur_hdl,':deptno',deptnum);
DBMS_SQL.BIND_VARIABLE(cur_hdl,':dname', deptname);
DBMS_SQL.BIND_VARIABLE(cur_hdl,':loc',location);
rows_processed := dbms_sql.execute(cur_hdl);
DBMS_SQL.CLOSE_CURSOR(cur_hdl);
END;
/
|
|
|
Re: DBMS_SQL.BIND_VARIABLE example [message #1695 is a reply to message #1686] |
Thu, 23 May 2002 02:03 |
SURESH VENKATARAMAN
Messages: 5 Registered: May 2002
|
Junior Member |
|
|
I am aware of this usage of the DBMS_SQL.BIND_VARIABLE( I am sorry to say). But, my query was: Could you illustrate possible scenarios in which the ':deptno', ':dname' in
DBMS_SQL.BIND_VARIABLE(cur_hdl,':deptno',deptnum);
DBMS_SQL.BIND_VARIABLE(cur_hdl,':dname', deptname);
are interactively entered. I want to verify whether the following are the main usages of them(?):
SQL> VARIABLE deptno NUMBER (usage scenario 1)
CREATE PACKAGE xxx AS
deptno number;
...
END;
PROCEDURE
...
DBMS_SQL.BIND_VARIABLE(cur_hdl,':xxx.deptno',deptnum);
...
END; (usage scenario 2)
Form Builder "items" (usage scenario 3)
I get this doubt because the concerned variables are declared nowhere, so I have no idea about their sources/potential uses, so that I can apply them when the need arises.
Will you please clarify?
Regards,
Suresh Venkataraman
|
|
|