CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloper
tom
tom

Reputation: 215

Anonymous block not working on SQL Developer but running on Toad

I have a script that is running Toad but not SQL Developer. I tried to bind variables but not working.

DECLARE
  retval                  sys_refcursor;

BEGIN
  retval :=
  SCHEMA_NAME.student_pkg.get_info_fn
                                      (pi_no,                              
                                        );
  DBMS_OUTPUT.put_line ('PO_NO: ' || pi_no);
  :A := retval;
END;
/

I tried &:A and &&:A but it is not working.My error is :

 Bind Variable "A" is NOT DECLARED
 anonymous block completed

Do you have any ideas?

Upvotes: 1

Views: 250

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

I tried &:A and &&:A

You are mixing bind variables (:) and substitution variables (&).

If you want to run as a script (F5) then you need to declare the bind variable in the client:

variable a refcursor

DECLARE
retval                  sys_refcursor;

BEGIN
  retval :=
  SCHEMA_NAME.student_pkg.get_info_fn
                                      (pi_no,                              
                                        );
  DBMS_OUTPUT.put_line ('PO_NO: ' || pi_no);
  :A := retval;
END;
/

print a

(You don't really need retval; you can do :a := ...get_info_fn(...); directly.)

The print is a client command to display the result.

If you run your block as a statement (F5) then you will be shown a dialog box to assign bind values used in the statement; but that's more helpful for passing in values than getting them out.

Upvotes: 3

Related Questions