Issue with select statements, vertical bars?

S

snoopy_

Hello,
I am trying to use DBI to perform a select statement, but it's
choking with a format problem:

ORA-00923: FROM keyword not found where expected (DBD ERROR:
OCIStmtExecute/Describe)

Could this be an issue with using vertical bars?

Select statement is as follows:

my $sql = qq{

select 'user|' || up.user_name || '|' || up.base_db || '|' || up.dept
|| '|' || up.group_id
from pcms_workset_info wsin, pcms_users_profile up
where up.user_name = UPPER ('$user_name' )
and up.privilege_level = 0
minus select null from dual

};
 
P

paul

Hello,
I am trying to use DBI to perform a select statement, but it's
choking with a format problem:

ORA-00923: FROM keyword not found where expected (DBD ERROR:
OCIStmtExecute/Describe)

Could this be an issue with using vertical bars?

Select statement is as follows:

my $sql = qq{

select 'user|' || up.user_name || '|' || up.base_db || '|' || up.dept
|| '|' || up.group_id
from pcms_workset_info wsin, pcms_users_profile up
where up.user_name = UPPER ('$user_name' )
and up.privilege_level = 0
minus select null from dual

};

I not very clear to what you want, but why you use 'qq' and '||'
simple easy is work perfect:
my $somthing=do("SELECT * FROM tb_name");

or

my $pre_db1 = prepare("SELECT * FROM tbname WHERE colum=?");
my $pre_db2 = $pre_db1->execute(23);

I think it may help
 
B

Brian McCauley

Hello,
I am trying to use DBI to perform a select statement, but it's
choking with a format problem:

ORA-00923: FROM keyword not found where expected (DBD ERROR:
OCIStmtExecute/Describe)

Could this be an issue with using vertical bars?

Select statement is as follows:

my $sql = qq{

select 'user|' || up.user_name || '|' || up.base_db || '|' || up.dept
|| '|' || up.group_id
from pcms_workset_info wsin, pcms_users_profile up
where up.user_name = UPPER ('$user_name' )
and up.privilege_level = 0
minus select null from dual

};

Have you tried printing out the value of $sql and pasting it into your
database program's interactive shell or similar tool?

My personal preference is to keep the SQL in DBI queries as plain and
simple as possilble. (By "plain" I mean avoid dialect specific SQL like
'||' and 'minus' and omitting the 'as').

I would create an SQL view (or procedure) to do the concatenation or do
it in the Perl.

BTW why do you CROSS JOIN pcms_workset_info then not use it?

Also be aware that interpolating data strings into SQL is a very good
way of making sure you can break into your system should you ever need
to. (Unfortunately it also means so can anyone else).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,482
Members
44,901
Latest member
Noble71S45

Latest Threads

Top