Solved

SQL in proc not working

Posted on 2013-10-22
11
330 Views
Last Modified: 2013-11-06
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;
0
Comment
Question by:jknj72
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
11 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 39591146
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39591150
It's also odd that your error says "Line 1"

Are you sure the error isn't somewhere else in the code?
0
 

Author Comment

by:jknj72
ID: 39591241
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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 39591253
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39591260
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
 

Author Comment

by:jknj72
ID: 39591300
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39591333
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
 

Author Comment

by:jknj72
ID: 39591416
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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39591493
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
 

Author Closing Comment

by:jknj72
ID: 39627525
Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39627554
Did you get an answer?
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question