I am getting this error "ORA-01008: not all variables bound ORA-06512: at "SYS.DBMS

Question. Write a PL/SQL program to check whether a date falls on weekend i.e. ‘SATURDAY’ or ‘SUNDAY’.

I tried running the code on livesql.oracle.com

My Code:

I am very new to sql and don't really know why i am getting this error. It is saying that all variables are not bound. Please help.

The output which I was expecting is:

I tried changing single quotes to double but that didn't work.

CodePudding user response：

I don't know why LiveSQL complains but - nonetheless - your code wouldn't work because of a wrong format model. DAY would return day name in uppercase, right-padded with spaces up to the longest day name (in your language). You're comparing such a name with initcap day name with no right-padded spaces so - all days wouldn't be a weekend, regardless of what you enter.

This is a SQL*Plus example so I'm using a substitution variable.

First, setting date format and language (because my database's default format is different than this, and my database speaks Croatian, not English):

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> alter session set nls_date_language = 'english';

Session altered.

Now, code:

SQL> set serveroutput on
SQL> declare
  2   d   date;
  3   day varchar2(20);
  4  begin
  5    -- d := :input_a_date;                --> your
  6    d := '&input_a_date';                 --> mine
  7
  8    day := to_char(d, 'fmDay');           --> pay attention to format model!
  9
 10    if day in ('Saturday', 'Sunday') then
 11       dbms_output.put_Line('Day is ' || day || ' and is a weekend');
 12    else
 13       dbms_output.put_line('Day is ' || day || ' and is not a weekend');
 14    end if;
 15  end;
 16  /
Enter value for input_a_date: 30.10.2022
Day is Sunday and is a weekend

PL/SQL procedure successfully completed.

SQL> /
Enter value for input_a_date: 02.11.2022
Day is Wednesday and is not a weekend

PL/SQL procedure successfully completed.

SQL>

CodePudding user response：

Live SQL does not have a mechanism to prompt for bind variables. It also has no way to assign values to bind variables from the SQL Worksheet. Bind variables may only be used in Live SQL within dynamic SQL. See below for examples using EXECUTE IMMEDIATE or DBMS_SQL.

Live SQL: Dynamic SQL: Don't concatenate, bind!

Live SQL: Method 4 Dynamic SQL Example: Display contents of table