SQL in proc not working

Im trying to run a procedure but when I try to compile it gives me an error. Here is the SQL statement it seems to have a problem with. I am doing alot more than just this statement but it highlights this one on compile. It also creates the procedure? When I try to compile it has a red line through it and it says
" ORA-00900: invalid SQL statement
 (48: 0): INNER
Error at line 1
ORA-00900: invalid SQL statement


Here is the SQL (bold line is highlighted when I try to compile)
        Select TITLE, DEPARTMENT, HAYPOINTS,
        NUM_POSITIONS, SALARYMIN, SALARYMAX,
        SUPERVISOR, LOCATION,b.PayGroup_Desc,
        CLOSING_DATE, ANNOUNCE_NUM, c.PARA_TEXT JobDesc, d.PARA_TEXT JobSkill      
        INTO vTitle, vDept, nGrade,
        nNumPositions, iSalaryMin, iSalaryMax,
        vSupervisor, vLocation, vPayroll,
        dCloseDate, nAnnouncement, vJobDesc, vJobSkill        
        FROM TAL_MAIN A
        INNER JOIN TAL_PAYGROUPS b ON A.PAYGROUP_ID = B.PAYGROUP_ID
       LEFT JOIN OMNI_ENT_PARAGRAPH c ON A.JOBDESC_ID=C.PARA_ID
        LEFT JOIN OMNI_ENT_PARAGRAPH d ON A.JOBSKILL_ID=D.PARA_ID
        WHERE a.TALENT_ID = iTAL_ID;
jknj72Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
are you using an old version of Oracle?  like 8i or lower?  Older versions don't support ANSI join syntax.

Your syntax looks valid to me provided all of the variables are valid and columns and tables names are correct.  You might want to consider prefixing all of the column references with the table aliases to make sure your references are correct.
0
sdstuberCommented:
It's also odd that your error says "Line 1"

Are you sure the error isn't somewhere else in the code?
0
jknj72Author Commented:
Im using version 11.6.0.43

I think your right, I think the problem is somewhere else just not sure where. I can run it from TOAD though with

        exec SP_TAL_USERPAGE_CREATE(104, 'HUMAN RESOURCES', 'VCSIJMK')

I attached a pic of the error. It also says something about INNERError? I know how everyone loves me posting pics about my errors but it may help
ORA-00900.jpg
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mark GeerlingsDatabase AdministratorCommented:
This may not be the problem, but you have to be careful with "select ...into" syntax in PL\SQL.  Implicit cursors like this may be easy to write and understand, but your join criteria then need to be tight enough (and your data needs to be clean enough!) to insure that you get exactly one row returned.  Otherwise, this may compile fine, but give a run-time error of either: "no data found" or "too_many_rows".
0
sdstuberCommented:
11.6.0.43  is not an Oracle version.  

That looks like a Toad version from a year or so ago.

Try this...

select * from all_errors where name = 'SP_TAL_USERPAGE_CREATE'
0
jknj72Author Commented:
Like I was sayihg I can run it from TOAD and it works fine and when I run it from my application it doesnt give me an error it just doesnt insert the records?

sdstuber:
sorry your right and I ran that sql and nothing was returned?

Ill keep checking on this and if you need me to try anything let me know

Thanks all
0
sdstuberCommented:
Perhaps you're not running or compiling the procedure in Toad correctly.

If you have an empty SQL Editor window and then put this line only

exec SP_TAL_USERPAGE_CREATE(104, 'HUMAN RESOURCES', 'VCSIJMK')

then press F9.  What happens?


Similarly, if your SQL Editor window only has the procedure's code and you press F9, what happens?
0
jknj72Author Commented:
F9 seems to run just fine for both scenarios sd? I realize this is hard for you to helpo. Would you like the entire proc. Its very l;arge. It calls 40 procs and queries but if youd llike to see it let me know..

Thanks
JK
0
sdstuberCommented:
I don't think sending the code will be needed

to summarize results thus far...

there are no syntax errors in the code
the procedure compiles fine by itself in toad
it runs fine by itself in toad
it compiles fine in sql (I assume you mean sql*plus)
it runs fine in sql

Is that correct?

If so, then what are you doing differently in Toad when you get the error?
There are multiple ways to execute code or pieces of code within the Toad editor.

Are you using a different key or set of keys? Are you clicking buttons within the gui?
Do you have other text within the editor? If so, what is that text?
Are you highlighting anything (even just one character?)
Where do you have the cursor when you attempt to execute or compile?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jknj72Author Commented:
Thanks
0
sdstuberCommented:
Did you get an answer?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.