Solved

SQL in proc not working

Posted on 2013-10-22
11
321 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
  • 6
  • 4
11 Comments
 
LVL 73

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 73

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
 
LVL 34

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 73

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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 73

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 73

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 73

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now