powerbuilder classic and connecting to a oracle 11g db

[Former Member]

Hi

I have the Oracle_developer_Day.ova downloaded with all its demo dbs (like HR OE etc...)

what i want to do is write a simple powerbuilder program to read and display in a table some of the data in say the HR db

and then secondarily C(R)UD it  create new records, read records and display them,update data in the records, and delete records

where would i find some examples to get me started?

[Former Member · Accepted solution]

Hi Rob;

  If you are on a newer version of PB (like 12.x or 12.5.x) Classic then just use the ORA option in your DB Profile painter of the PB IDE to connect to O11G using its native driver. Do not use the O90 or O10 drivers as they are not O11G

  Once connected to the O11G instance, developing a simple PB application to show DBMS interaction is no different than using ASE, SS, Informix, etc.

Good luck!

Regards ... Chris

[Former Member · Accepted solution]

Cool thats gotten me along...I had to download the 32 oracle inst client but I still have a problem using the ORA driver

I am assuming I need to add something to my tnsnames.ora to define the listener is that correct

here is what I have as of now

ODD =

(DESCRIPTION =

(ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)))

  (CONNECT_DATA =

  (SERVICE_NAME = orcl)

)

)

any hint as to what i need to add in this

[Former Member · Accepted solution]

Most likely your service name should be orcl.world

If you can connect through sqlplus to orcl, then check the value:

select value from v$parameter where name = 'service_names'

It appears you have no experience in PB at all.  If so, go through the 'Getting Started' manual, which has a tutorial to build a basic app.

[Former Member · Accepted solution]

sqlplus works yes

C:\Users\rob.merritt>sqlplus system/oracle@odd

SQL*Plus: Release 12.1.0.1.0 Production on Wed Dec 4 07:45:00 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter local_listener

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

local_listener                       string      LISTENER_ORCL

SQL> exit

I have no PB experience but I need to get up to speed programming in and oracle env

SQL> select value from v$parameter where name = 'service_names'

  2

not sure what 2 means??? what i need to know is how to get by this llistener issue is it a simple addition of a line in tnsnames.ora so the PB ORA driver knows what the listener is called?

[Former Member · Accepted solution]

That means you are running Personal Oracle and you have no listener configured, with Oracle using the bequeath protocol.

I haven't connected to PO in a long time using this method, but I think if you enter "2:" or "2:orcl" in PB for the servername, that will work.  But personally, I prefer configuring the listener.  Using Oracle's configuration tools, run the Net Manager and use it to configure the listener.

[Former Member · Accepted solution]

Doing some more research, I think you can just leave the servername blank when connecting to Personal Oracle.

In code you would set:  SQLCA.servername = ""

[Former Member · Accepted solution]

hmmm I am not sure what that means ....how does that relate to chris's posting it looks like there all you need to do is select the ORA driver and type in the information. I cannot see any where to enter

the listener info

[Former Member · Accepted solution]

Hi Rob;

   Terry is referring to the DBMS side of life in respect to the "listener" that Oracle needs in order to have a conversation with a client application like PB. My example was getting the PB client to communicate to Oracle by filling in the SERVER, Login ID, Password, etc information in the DB Profile painter.

   However, the Server name that PB's Oracle driver is told to use is passed to the real Oracle Client which needs to resolve the actual Server's IP address via and entry in the TSNames control file.

Regards ... Chris

PS: Isn't Oracle over complicated ... and its the most expensive DBMS out there too!  LOL

[Former Member · Accepted solution]

ok well here is what I have

which as you say leverages the instantclient i have installed using tnsnames.ora

ODD =

(DESCRIPTION =

(ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.101)(PORT = 1521)))

  (CONNECT_DATA =

  (SERVICE_NAME = orcl)

)

)

so is the issue that i am entering the IP address in there instead of a hostname?

[Former Member · Accepted solution]

P.S.

here is the listener.ora from the server (the oracle_developer_day.ora a canned oracle VM so it should have all the config correct)

# listener.ora Network Configuration File: /home/oracle/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 80))

      (PROTOCOL_STACK =

         (PRESENTATION = HTTP)

         (SESSION = RAW)

      )

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 21))

      (PROTOCOL_STACK =

         (PRESENTATION = FTP)

         (SESSION = RAW)

      )

    )

  )

ADR_BASE_LISTENER = /home/oracle/app/oracle

[Former Member · Accepted solution]

Yes, entering the IP address is wrong.  It needs to be the service name.

The listener file looks very strange.  I don't see an entry for the ORCL database.  And a host of 0.0.0.0 doesn't make sense either.  I would expect on a local laptop something like 127.0.0.1, 10.x.x.x. or 192.168.x.x

And never ever connect as SYSDBA unless you have some really really really good reason to do so.  SYSDBA account is used to do some serious DBA work.

[Former Member · Accepted solution]

Hi Terry

its not on a local laptop its on virtualbox I am using the oracle_developer_day ova its a vm

my laptop is 192.168.56.1 the vm of the oracle linux appliance is 192.168.56.101...does that clear it up

[Former Member · Accepted solution]

i changed it to use an entry from the host file on my local PC

[Former Member · Accepted solution]

Start by changing all the 0.0.0.0 entries in the listener.ora file to 192.168.56.101  (ip addres of vm)

What do you have under the ADR_BASE_LISTENER section.  There should be something like:

(SID_LIST=

(SID_DESC=

(SID_NAME=ORCL)

(ORACLE_HOME=/home/oracle/app/oracle)

)

)

You will need to restart the listener.  Run the lsnrctl utility and simply run the command 'restart'

Do you have an Oracle DBA you can talk to?

The servername entry in your PB Oracle profile should be set to ORCL.  And I repeat, do not use SYSDBA in the 'Connect As' field.  Leave it as 'Default'.

[Former Member · Accepted solution]

nope no DBA s to talk to here is what I am seeing

the listener seems to be working with 0.0.0.0

LSNRCTL> status

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                04-DEC-2013 14:57:11

Uptime                    0 days 17 hr. 45 min. 46 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))

Services Summary...

Service "orcl" has 1 instance(s).

  Instance "orcl", status READY, has 2 handler(s) for this service...

Service "orclXDB" has 1 instance(s).

  Instance "orcl", status READY, has 1 handler(s) for this service...

The command completed successfully

LSNRCTL>

when i change it to 192.186.56.101 I get

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production

Start Date                05-DEC-2013 08:44:58

Uptime                    0 days 0 hr. 0 min. 20 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora

Listener Log File         /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.101)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))

The listener supports no services

The command completed successfully

the log file says

type='UNKNOWN' level='16' host_id='localhost.localdomain'

host_addr='127.0.0.1' version='1'>

<txt>05-DEC-2013 09:07:57 * (CONNECT_DATA=(SERVICE_NAME=)(CID=(PROGRAM=C:\Program?Files??x86?\Sybase\PowerBuilder?12.5\PB125.EXE)(HOST=SPC13525)(USER=rob.merritt))) * establish * 12504

</txt>

</msg>

<msg time='2013-12-05T09:07:57.839-08:00' org_id='oracle' comp_id='tnslsnr'

type='UNKNOWN' level='16' host_id='localhost.localdomain'

host_addr='127.0.0.1'>

<txt>TNS-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

TNS-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

</txt>

</msg>

[Former Member · Accepted solution]

also I cannot use ORCL in the servername in PB ...if I put the IP address of the server or a hostname

ORACLE32 which i map to the IP address in the c:\windows\system32\drivers\etc\hosts file.

If I use ODD the from tnsnames.ora

ODD =

(DESCRIPTION =

(ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = oracle32)(PORT = 1521)))

  (CONNECT_DATA =

  (SERVICE_NAME = orcl)

)

)

I get....

<msg time='2013-12-05T09:50:01.731-08:00' org_id='oracle' comp_id='tnslsnr'

type='UNKNOWN' level='16' host_id='localhost.localdomain'

host_addr='127.0.0.1' version='1'>

<txt>05-DEC-2013 09:50:01 * (CONNECT_DATA=(SERVICE_NAME=orcl)(CID=(PROGRAM=C:\Program?Files??x86?\Sybase\PowerBuilder?12.5\PB125.EXE)(HOST=SPC13525)(USER=rob.merritt))) * (ADDRESS=(PROTOCOL=tcp)(HOST=172.24.57.19)(PORT=56708)) * establish * orcl * 12514

</txt>

</msg>

<msg time='2013-12-05T09:50:01.731-08:00' org_id='oracle' comp_id='tnslsnr'

type='UNKNOWN' level='16' host_id='localhost.localdomain'

host_addr='127.0.0.1'>

<txt>TNS-12514: TNS:listener does not currently know of service requested in connect descriptor

</txt>

</msg>

Message was edited by: rob merritt

[Former Member · Accepted solution]

Change the tnsnames.ora (SERVICE_NAME = orcl) to (SERVICE_NAME = orcl.world)

I'm also confused by oracle32 in your tnsnames.ora.  Is that the name of your PC or of the VM?  It looks like your PC, given the IP address it resolves to: 172.24.57.19.  But you should be using the ip address of your VM.

And finally, you might be running into an Oracle bug where it has problems with applications running from a directory containing parentheses ().  I thought it was fixed in the 11 client, but maybe not.  The 10 XE version most definitely had this bug.

If you can't solve this, I could setup a GoToAssist session to take a look on your PC.

[Former Member · Accepted solution]

aaaggghhhh i just set it up at home and it works

using

ODD =

(DESCRIPTION =

(ADDRESS_LIST =

  (ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1521)))

  (CONNECT_DATA =

  (SERVICE_NAME = orcl)

)

)

oracleserver is a host file entry pointing at the VM just like my work PC

Verdamt!