There is no documented LEFT() function in Oracle. Find the full set here.
Probably what you have is a user-defined function. You can check that easily enough by querying the data dictionary:
select * from all_objects
where object_name = 'LEFT'
But there is the question of why the stored procedure works and the query doesn't. One possible solution is that the stored procedure is owned by another schema, which also owns the LEFT() function. They have granted rights on the procedure but not its dependencies. This works because stored procedures run with DEFINER privileges by default, so you run the stored procedure as if you were its owner.
If this is so then the data dictionary query I listed above won't help you: it will only return rows for objects you have rights on. In which case you will need to run the query as the stored procedure's owner or connect as a user with the rights to query DBA_OBJECTS instead.