Ansible error: The offending line appears to be:

I am getting code error in Ansible as below. please advise on this.

ERROR! We were unable to read either as JSON nor YAML, 
these are the errors we got from each:
JSON: Expecting value: line 1 column 1 (char 0)

Syntax Error while loading YAML.
  did not find expected key

The error appears to be in '/root/ansible_code/roles/sample_exercise/tasks/main.yml': 
                           line 10, column 6,
but may be elsewhere in the file depending 
on the exact syntax problem.

The offending line appears to be:

    oracle_home: "{{ oracle_home.stdout }}"
     oracle_sid: "{{ dbname }}"
     ^ here
We could be wrong, but this one looks like it might be an issue with
missing quotes. Always quote template expression brackets when they
start a value. For instance:

    with_items:
      - {{ foo }}

Should be written as:

    with_items:
      - "{{ foo }}"

Code--

#- hosts: localhost
- hosts: 192.168.56.10

- debug:
    msg: "{{ oracle_home.stdout }}"

- set_fact:
    oracle_home: "{{ oracle_home.stdout }}"
     oracle_sid: "{{ dbname }}"

- name: List PDB of given CDB
shell:
 cmd: |
    $ORACLE_HOME/bin/sqlplus / as sysdba <<EOF
    select instance_name, status from v$instance
    EOF
 environment:
        ORACLE_HOME: "{{ oracle_home }}"
        ORACLE_SID: "{{ oracle_sid }}"
 register: pdb

- name: List PDB
 debug:
   msg: "{{ pdb.stdout_lines|replace('\t', '') }}"

I am getting code error in Ansible as below. Please advise on this.

It is just a syntax error caused by incorrect indention. It is reproducible with a minimal example like

- hosts: localhost
  become: false
  gather_facts: false

  tasks:

  - set_fact:
      oracle_home: 'get_assigned'
       oracle_sid: 'will_cause_syntax_error'

resulting into an output of

ERROR! We were unable to read either as JSON nor YAML, these are the errors we got from each:
JSON: No JSON object could be decoded

Syntax Error while loading YAML.
  did not find expected key

The error appears to be in '/home/user/test/main.yml': line 9, column 8, but may
be elsewhere in the file depending on the exact syntax problem.

The offending line appears to be:

      oracle_home: 'get_assigned'
       oracle_sid: 'will_cause_syntax_error'
       ^ here

The correct syntax would be as follow

  - set_fact:
      oracle_home: 'get_assigned'
      oracle_sid: 'also_gets_assigned'

As already answered: your error is most likely syntax error in your playbook. Meaningful whitespace and sloppy coding/formatting don't mix very well when learning and using Ansible.

Rather than relying on Ansible error messages during playbook execution to find (syntax) errors a better way is to use something like ansible-lint before running ansible-playbook to check your playbooks and yaml files for syntax errors and common pitfalls.

https://ansible.readthedocs.io/projects/lint/