Examine the following code:
What is the outcome?
A.
The procedure is created successfully and displays the values 20 and 30 when it is called.
B.
The procedure gives errors because the parameters should be in out mode.
C.
The procedure gives errors because the host variables cannot be referenced anywhere in the definition of a PL/SQL stored procedure.
D.
The procedure is created successfully but does not display any values when it is called because the host variables cannot be displayed inside the procedure.
I tired to execute it in sqlplus and it showed the error “PLS-00049: bad bind variable ‘N1′”
Answer is C
SQL> set serveroutput on
SQL> variable n1 number
SQL> variable n2 number
SQL> create or replace procedure proc1
2 (:n1 IN OUT NUMBER, :n2 IN OUT NUMBER) is
3 begin
4 :n1 := 20;
5 dbms_output.put_line(:n1);
6 :n2 := 10;
7 dbms_output.put_line(:n2);
8 end;
9 /
Warning: Procedure created with compilation errors.
SQL> show errors;
Errors for PROCEDURE PROC1:
LINE/COL ERROR
——– —————————————————————–
2/2 PLS-00049: bad bind variable ‘N1’
2/2 PLS-00103: Encountered the symbol “” when expecting one of the
following:
current delete exists prior
The symbol ” was inserted before “” to continue.
2/21 PLS-00049: bad bind variable ‘N2’
2/21 PLS-00103: Encountered the symbol “” when expecting one of the
following:
LINE/COL ERROR
——– —————————————————————–
current delete exists prior
The symbol ” was inserted before “” to continue.
4/1 PLS-00049: bad bind variable ‘N1’
5/22 PLS-00049: bad bind variable ‘N1’
6/1 PLS-00049: bad bind variable ‘N2’
7/22 PLS-00049: bad bind variable ‘N2’
Ans : C
c is correct answer…AS the pgm while compiling shows an error.
yes, the correct answer is C!
Yes C is the correct ans
C
C
OK I have run the code and it looks like the answer is A!
************** SQL *******************
C:\>sqlplus
SQL*Plus: Release 10.2.0.3.0 – Production on Mon Nov 3 16:01:34 2014
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Enter user-name: [email protected]
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SET SERVEROUTPUT ON;
SQL> VARIABLE n1 number
SQL> create or replace procedure proc_pj(:n1 IN OUT NUMBER) IS
2 BEGIN
3 :n1 := 20;
4 DBMS_OUTPUT.PUT_LINE(:n1);
5 END;
6 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE PROC_PJ:
LINE/COL ERROR
——– —————————————————————–
1/19 PLS-00049: bad bind variable ‘N1’
1/19 PLS-00103: Encountered the symbol “” when expecting one of the
following:
current delete exists prior
The symbol ” was inserted before “” to continue.
3/3 PLS-00049: bad bind variable ‘N1’
4/24 PLS-00049: bad bind variable ‘N1’
SQL>
Sorry the above Post should read “OK I have run the code and it looks like the answer is C!”
I can also confirm that the answer is also NOT B
***************** SQL ****************
SQL>
SQL> create or replace procedure proc_pj(:n1 OUT NUMBER) IS
2 BEGIN
3 :n1 := 20;
4 DBMS_OUTPUT.PUT_LINE(:n1);
5 END;
6 /
Warning: Procedure created with compilation errors.
SQL> show errors
Errors for PROCEDURE PROC_PJ:
LINE/COL ERROR
——– —————————————————————–
1/19 PLS-00049: bad bind variable ‘N1’
1/19 PLS-00103: Encountered the symbol “” when expecting one of the
following:
current delete exists prior
The symbol ” was inserted before “” to continue.
3/3 PLS-00049: bad bind variable ‘N1’
4/24 PLS-00049: bad bind variable ‘N1’
SQL>
Respuesta correcta la C;
Esto por que las variables bind y de sustitución no pueden ser referenciadas en cualquier lugar dentro de la definición de un procedimiento PL/SQL.
y en ninguna parte dentro de la sección BEGIN podemos referenciarlas tampoco.
Podemos modificar el ejemplo como sigue:
SET SERVEROUTPUT ON
VARIABLE n1 NUMBER
VARIABLE n2 NUMBER
exec :n1:=20
exec :n2:=30
CREATE OR REPLACE PROCEDURE PROC1(n1 IN OUT NUMBER, n2 IN OUT NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(n1);
DBMS_OUTPUT.PUT_LINE(n2);
END;
/
carissimo Manuel, vorrei capire meglio il concetto che con il tuo esempio dimostri validissimo.
Puoi per cortesia spiegarmi meglio questo concetto o segnalarmi un tutorial d’aiuto ?
in pl/sql che io sappia le bind variabili si possono usare e vengono valorizzate, dopo il parsing,
nella fase di binding. Perchè invece , come dimostri, le variabili bind non possono essere referenziate
all’interno della definizione di una procedura PL/SQL ? non riesco proprio a capirlo. GRAZIE
————————————————————————
querido Manuel, me gustaría entender mejor el concepto de que con su ejemplo demuestra valioso.
¿Puede usted explicar mejor este concepto me señala un tutorial o ayuda?
en pl / sql, que yo sepa las variables se unen se pueden utilizar y se miden, después de analizar,
en la fase de unión. ¿Por qué, en cambio, como se ha demostrado, se unen las variables no pueden ser referenciados
dentro de la definición de un PL / SQL? No puedo entenderlo. gracias
———————————————————————-
dear Manuel, I would like to better understand the concept that with your example proves valuable.
Can you please explain this concept better signal me a tutorial or help?
in pl / sql that I know the bind variables may be used and are measured, after parsing,
in the phase of binding. Why, instead, as demonstrated, bind variables can not be referenced
within the definition of a PL / SQL? I just can not understand it. Thanks
(AND THANKS TO ANYONE CAN HELP ME TOO)
carissimo Manuel,
inoltre, se io dalla mia postazione client sqlplus, anziche creare la procedura sul database server, nello stesso rapporto client server, la eseguo su server, ecco che al contrario, le variabili bind vengono referenziate correttamente :
————————————————————————
querido Manuel,
Además, si yo de mi cliente sqlplus escritorio, en lugar de crear el procedimiento en el servidor de base de datos, la misma relación de cliente-servidor, el plazo en el servidor, aquí por el contrario, se unen las variables están referenciadas correctamente:
———————————————————————–
dear Manuel,
Also, if I from my desk sqlplus client, instead of creating the procedure on the database server, the same client server relationship, the run on the server, here on the contrary, bind variables are referenced properly:
SQL> exec dbms_output.enable();
PL/SQL procedure successfully completed.
SQL>
SQL> set serveroutput on
SQL> variable
variable n1
datatype NUMBER
variable n2
datatype NUMBER
SQL> exec proc1(:n1,:n2);
20
30
PL/SQL procedure successfully completed.
Direi quindi che dal client
Non posso usare variabili host in un comando SQL quale est la istruzione CREATE PROCEDURE
Ma le posso usare in un comando pl/sql ovvero in una exec o in un blocco begin..end;
potrebbe essere questa la regola ?
—————————-
Así que yo diría que desde el cliente
No puedo utilizar variables en una host este comando SQL como la sentencia CREATE PROCEDURE
Pero puedo utilizar en un comando PL / SQL o en un ejecutivo o un begin..end bloque;
¿podría ser la regla?
————————————
So I would say that from the client
I can not use host variables in an SQL command which east the CREATE PROCEDURE statement
But I can use in a command pl / sql or in an exec or a block BEGIN..END;
could this be the rule?
——————————–
someone can confirm tha this is motivation for anser C please ?
SQL> set serveroutput on
SQL> variable n1 number
SQL> variable n2 number
SQL> create or replace procedure proc1
2 (n1 in out number, n2 in out number) is
3 Begin
4 n1 :=20;
5 DBMS_OUTPUT.put_line(n1);
6 n2 :=30;
7 DBMS_OUTPUT.put_line(n2);
8 END;
9 /
Procedure created.
SQL>
host variable Or bind variable can not used to parameter declaration
so C is a correct answer
example:
========
create or replace procedure hi_proc
(:a in out)
is
begin
:a :=10;
dbms_output.put_line(:a);
end;
/
Warning: Procedure created with compilation errors.
SQL> show err;
Errors for PROCEDURE HI_PROC:
LINE/COL ERROR
——– —————————————————————–
2/2 PLS-00049: bad bind variable ‘A’
2/2 PLS-00103: Encountered the symbol “” when expecting one of the
following:
current
C
C
I AM AGREE THE c IS THE CORRECT ANSWER !
“You can’t create a procedure with a bind variable in it because stored procedures are server-side objects and bind variables only exist on the client side.
Suppose I’m using SQL*Plus, and that I’ve created some bind variables. Once I exit SQL*Plus, any bind variables I created don’t exist any more. However, stored procedures have to persist in the database, and hence they can’t have any reference to anything that was created and then destroyed on the client.” (http://stackoverflow.com/questions/5209981/use-of-bind-variable)
Thanks yacek, nice clarity
Thanks yacek. Great answer
C is correct
reference
PLS-00049: bad bind variable
You can’t create a procedure with a bind variable in it because stored procedures are server-side objects and bind variables only exist on the client side.
so answer is C
Although i agree with everyone recommending C, look at the question though. I think it is not a good practice but still if you run exact codes you will see A is correct. I have tried it.
c
Option C
ANSWER is C 100%
SQL> set serveroutput on
SQL> variable n1 number
SQL> variable n2 number
SQL> create or replace procedure pro1 (:n1 in out number, :n2 in out number) is
2 begin
3 :n1 := 20;
4 dbms_output.put_line(:n1);
5 :n2 := 30;
6 dbms_output.put_line(:n2);
7 end;
8 /
create or replace procedure pro1 (:n1 in out number, :n2 in out number) is
begin
:n1 := 20;
dbms_output.put_line(:n1);
:n2 := 30;
dbms_output.put_line(:n2);
end;
ORA-01036: illegal variable name/number
n1
———
n2
———