Whenever I tried to start my local TNS Listener service, OracleOraDb10g_home1TNSListener, I would get an error like this.

The OracleOraDb10g_home1TNSListener service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.

This forum thread, Can’t start Oracle service, suggested I do the following to diagnose the problem.

1. cmd
2. lsnrctl
3. start

This worked and I saw the following error which showed my Oracle install had not been properly done, the listener was pointing to the wrong server.

LSNRCTL> start
System parameter file is
  C:\oracle\product\10.2.0\db_1\network\admin\listener.ora
Error listening on:
  (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bad.betweengo.com)(PORT=1521)))
TNS-12545: Connect failed because target host or object does not exist
 TNS-12560: TNS:protocol adapter error
  TNS-00515: Connect failed because target host or object does not exist
    32-bit Windows Error: 1004: Unknown error

In C:\oracle\product\10.2.0\db_1\network\admin\listener.ora the listener is improperly configured for bad.betweengo.com.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bad.betweengo.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

I ran Oracle’s Net Configuration Assistant and updated the Listener configuration which fixed the above listener to point to my server. Now C:\oracle\product\10.2.0\db_1\network\admin\listener.ora points correctly to good.betweengo.com.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = good.betweengo.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

Sometimes I am faced with a situation where I need to know the source for a view or a procedure or a function. Fortunately Oracle stores the source in the database.

First I check to see what kind of object whose source I need is.

SQL> select object_name,object_type,status from user_objects where object_name = 'FOO';

If the object is a table I can simply recreate the table by doing a desc on that object.

SQL> desc FOO;

If the object is a function or stored procedure you can find its source in the user_source table.

SQL> select text from user_source where name = 'FOO' order by line;

If the object is a view you can find its source in the user_views table.

SQL> select text from user_views where view_name = 'FOO';

If the output seems to be truncated it is because text is of type LONG which is a big binary field. To allow more output do this.

SQL> set long 4000

Thanks to this article and this forum for helping me figure this out.

SQL> select privilege from user_tab_privs where lower(table_name)=’t_foo’ and lower(grantee)=’frank’;

PRIVILEGE
—————————————-
UPDATE
SELECT
INSERT
DELETE

SQL> select * from user_role_privs;

USERNAME GRANTED_ROLE ADM DEF OS_
—————————— —————————— — — —
FRANK AQ_ADMINISTRATOR_ROLE NO YES NO
PUBLIC SELECT_CATALOG_ROLE NO YES NO

To insert a text value with an ampersand, e.g. AT&T, one can do something like this.

insert into foo values(‘AT’||Chr(38)||’T’)

To learn more see How does one disable interactive prompting in SQL*Plus?

To insert a text value with a single quote, e.g. Don’t do it, one can do something like this.

insert into foo values(‘Don”t do it’)

To learn more see How does one escape special characters when building SQL queries?

Calling an Oracle function is quite simple. For example

SELECT package.register(1, "Frank", "Kim") FROM dual;

However calling Oracle stored procedures is a bit more tricky, especially if the Oracle stored procedure has output parameters. Here is an example of how to call an Oracle stored procedure that has three input parameters and two output parameters.


DECLARE p_credit_status_out INTEGER;
p_school_status_out INTEGER;

BEGIN
package.get_status(1, "Frank", "Kim", p_credit_status_out, p_school_status_out);
END;
/


Creation

CREATE TRIGGER trig_frank
    BEFORE INSERT ON table_frank
    FOR EACH ROW
    BEGIN
        SELECT seq_frank.nextval
        INTO :new.id
        FROM dual;
    END;
.
RUN;

References

• Oracle/PLSQL Topics: Creating Triggers
• Constraints and Triggers in Oracle