Home » SQL & PL/SQL » SQL & PL/SQL » Is Primary Key Function
Is Primary Key Function [message #293342] Fri, 11 January 2008 13:38 Go to next message
jayz240z
Messages: 11
Registered: August 2007
Junior Member
I'm trying to create a function, that when passed in the Owner, Table Name and Column; the function will return the record count (0 or 1) indicating whether or not a column is the primary key for that table.

I can get the needed query to run fine outside of the function, with the variables hardcoded:
select count(column_name)
from sys.dba_constraints dc,
     sys.dba_cons_columns dcc   
where dc.Owner = upper('saddleback')
AND dc.table_name = upper('prop_types')    
AND  dc.CONSTRAINT_TYPE = 'P'
and  dc.Owner = dcc.owner
and  dc.CONSTRAINT_NAME = dcc.CONSTRAINT_NAME
and  dcc.column_name = upper('prop_types_id')


The function was created without returning any errors. I get an error(below with sample query) when I try to use it though. I think the problem might be with how I'm handling the single quote marks. I think somehow I need quote marks inside the upper('') functions, but when I tried to include them I got errors compiling the function.

CREATE OR REPLACE FUNCTION SYS.is_pk(tname VARCHAR2,towner varchar2, clmn varchar2) return number 
    is
    x number;
    stmt VARCHAR2(2000);
Begin   
    stmt := 'select count(column_name)
            from sys.dba_constraints dc,
                 sys.dba_cons_columns dcc   
            where dc.table_name = upper('||tname||')
            AND   dc.Owner = upper('||towner||') 
            AND  dc.CONSTRAINT_TYPE = P
            and  dc.Owner = dcc.owner
            and  dc.CONSTRAINT_NAME = dcc.CONSTRAINT_NAME
            and  dcc.column_name = upper('||clmn||')';
        execute immediate stmt into x;
        return x;
EXCEPTION 
    WHEN NO_DATA_FOUND THEN
            RETURN 0;
END;


Sample Query:
select is_pk('prop_types','saddleback','prop_types_id') from dual;


Error message (from inside toad):
ORA-00904: "PROP_TYPES_ID" invalid identifier
ORA-06512: "SYS.IS_PK", line 15

I've search on Google and here for a similar function, but with no useful results.

Thanks again,

Jason
Re: Is Primary Key Function [message #293348 is a reply to message #293342] Fri, 11 January 2008 13:56 Go to previous messageGo to next message
jayz240z
Messages: 11
Registered: August 2007
Junior Member
I got it my function to work. I'm really trying to learn this stuff. I'm not sure whey I struggle with creating functions in Oracle.

Here is my solution:
CREATE OR REPLACE FUNCTION SYS.is_pk(tname VARCHAR2,towner varchar2, clmn varchar2) return number 
    is
    x number;
    
Begin   
    select count(column_name)  into x
    from sys.dba_constraints dc,
         sys.dba_cons_columns dcc   
    where dc.table_name = upper(tname)
    AND   dc.Owner = upper(towner) 
    AND  dc.CONSTRAINT_TYPE = 'P'
    and  dc.Owner = dcc.owner
    and  dc.CONSTRAINT_NAME = dcc.CONSTRAINT_NAME
    and  dcc.column_name = upper(clmn);
       
        return x;
EXCEPTION 
    WHEN NO_DATA_FOUND THEN
            RETURN 0;
END;

commit;


Can someone please explain to me why my first one wouldn't work?

Thanks

Jason
Re: Is Primary Key Function [message #293351 is a reply to message #293342] Fri, 11 January 2008 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Concatenation in dynamic query means slow and security hole.
Don't you know SQL injection?
Use bind or PL/SQL variables.

Regards
Michel

Re: Is Primary Key Function [message #293353 is a reply to message #293348] Fri, 11 January 2008 14:05 Go to previous message
Michel Cadot
Messages: 64131
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh! I see you found the solution by yourself when I was writing.
Very good!

But never put your objects in SYS schema.
Actually:
- Never ever use sys but for maintenance purpose (startup, shutdown, backup, recover)
- sys is special
- sys is Oracle proprietary (try to open a SR/TAR starting with "i did that with sys" and you'll see the immediate answer)
- sys does not act like any other user
- When you use sys Oracle deactivates some code path and activates others
- Whatever you do with sys will neither validate nor invalidate the same thing with any other user.

NEVER EVER use sys for anything that can be done by another user.
Use sys ONLY for something that can't be done by someone else.


Your first solution didn't work because you should write something like "upper('''||tname||''')".
Of course you MUST not do this for the reasons I mentioned in my previous post.

Regards
Michel


[Updated on: Fri, 11 January 2008 14:06]

Report message to a moderator

Previous Topic: Count of table column
Next Topic: WITH Clause Help
Goto Forum:
  


Current Time: Wed Dec 07 11:03:29 CST 2016

Total time taken to generate the page: 0.07418 seconds